Batch insertion in rails 3

Question

I want to do a batch insert of few thousand records into the database (POSTGRES in my case) from within my Rails App.

What would be the "Rails way" of doing it? Something which is fast and also correct way of doing it.

I know I can create the SQL query by string concatenation of the attributes but I want a better approach.

Solution 1

ActiveRecord .create method supports bulk creation. The method emulates the feature if the DB doesn't support it and uses the underlying DB engine if the feature is supported.

Just pass an array of options.

# Create an Array of new objects
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }])

Block is supported and it's the common way for shared attributes.

# Creating an Array of new objects using a block, where the block is executed for each object:
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }]) do |u|
  u.is_admin = false
end

Solution 2

I finally reached a solution after the two answers of @Simone Carletti and @Sumit Munot.

Until the postgres driver supports the ActiveRecord .create method's bulk insertion, I would like to go with activerecord-import gem. It does bulk insert and that too in a single insert statement.

books = []
10.times do |i| 
    books << Book.new(:name => "book #{i}")
end
Book.import books

In POSTGRES it lead to a single insert statemnt.

Once the postgres driver supports the ActiveRecord .create method's bulk insertion in a single insert statement, then @Simone Carletti 's solution makes more sense :)

Solution 3

You can create a script in your rails model, write your queries to insert in that script In rails you can run the script using

rails runner MyModelName.my_method_name

Is the best way that i used in my project.

Update:

I use following in my project but it is not proper for sql injection. if you are not using user input in this query it may work for you

user_string = " ('[email protected]','a'), ('[email protected]','b')"
User.connection.insert("INSERT INTO users (email, name) VALUES"+user_string)

For Multiple records:

new_records = [
  {:column => 'value', :column2 => 'value'}, 
  {:column => 'value', :column2 => 'value'}
]

MyModel.create(new_records)

Comments

[Admin]

See also: How to implement bulk insert in Rails 3 and Bulk Insert records into Active Record table.

[phoenixwizard]

I am looking for something like inserting 1000 objects into the database in a single query. USe case : I am logging in the user from facebook and saving all his facebook friends with a single DB call.

[phoenixwizard]

I think my question is not clear. I am thinking of the use case that a user logins with his facebook account I am saving all his friends. I am expecting around 1000 to 4000 records at a time. I want to do it from within my Rails App

[phoenixwizard]

So do you think for the case of postgres it will create a single insert statement?

[Simone Carletti]

It may depends on the driver version and PG version. You can try it in your console and see the SQL statement that is executed.

[phoenixwizard]

It seems it is creating separate queries. Though surrounding it by transaction seems to be making it faster. Any way I can ensure single query insert ?

[Simone Carletti]

Not sure the PG driver supports it at this time. bitbucket.org/ged/ruby-pg/pull-request/13

[phoenixwizard]

This is exactly what I wanted to avoid :) It seems it's either this way or a slower way ...

[Sumit Munot]

You can run the multiple queries in thread using g-server. It may help you

[Sumit Munot]

Interesting article it may help you coffeepowered.net/2009/01/23/…

[phoenixwizard]

That was the first article I read when I started. Just writing the sql didn't feel right :)

[phoenixwizard]

I think I will go with your method wrapped in a transaction. Lets hope the driver is updated soon :)

[Sumit Munot]

Use ar-extensions gem refer to this link stackoverflow.com/questions/4256898/…

[phoenixwizard]

It seems it is no more supported for rails 3. They have suggested activerecord-import in the github page. Will look at it.

[Sumit Munot]

Yes activerecord-import seems interesting

[phoenixwizard]

I ended up using activerecord-import gem. It seems to do the trick :)

[Admin]

Sadly this is still the best solution for such a simple problem. The 'write raw sql' solutions work and may be easier for one offs but if you do this more than once then this gem is the best solution to not sprinkle raw sql (or other languages) everywhere.

[lobati]

This method doesn't do any magic. It just loops over the array and calls create on each individual hash. github.com/rails/rails/blob/…

[Anwar]

It says method missing is postgres-9.4, Ubuntu 14.04, rails 4.2

[Pete]

@Anwar: Do you have gem 'activerecord-import' in your Gemfile? This is required to use Model#import.

[abhishek77in]

Use github.com/zdennis/activerecord-import gem if you have any significant importing to do. This way import is slowwww..