Insert_all and Upsert_all in Rails 6

Tram Ho

There are many cases where we need to add a series of records for example when we have a list of users using CSV and need to import into our application.

Rails has methods like delete_all or update_all to delete or update the corresponding records. But the methods for adding multiple records at once are not available for Rails 6.

Rails 6 added 3 methods insert_all , insert_all! and upsert_all to ActiveRecord::Persistence , to solve the above problem.

Before Rails 6

Before Rails 6, to insert multiple records you could use one of the following ways

  • Use the gem activerecord-import

  • Create each record one by one

In Rails 6

insert_all and insert_all!

Using insert_all can add a series of records:

In the query we can see ON DUPLICATE KEY UPDATE 'name'='name' clause ON DUPLICATE KEY UPDATE 'name'='name' , this clause appears if using MySQL database, if using SQLite or PostgreSQL database we will see a clause. OTHER ON CONFLICT DO NOTHING . These two clauses are essentially the same thing, will do when a collision or unique key constraint violation will ignore the collision record and perform the next insert record.

For example:

If we want to make sure the records are insert_all! then we can use insert_all! , when a conflict occurs, the exception will be raised

For example:

upsert_all

If a record already exists but want to update the record or if we want to create a new one, we can use upsert_all

In the above example, there are 2 records with a duplicate id of id='1' and only 1 record is added, the new record with id='2' refund should be added without issue What, the record with id='3' is the record with an existing id, the new name will be updated.

Efficiency

Inserting or updating multiple records continuously has poor performance

For example, try inserting 1000 consecutive records:

1000 User created 6.04 seconds with 1000 transactions performed 1000 query insert

If we use the insert_all statement we can import 1000 records with only one insert query

We can see a dramatic improvement in performance, down from 6.04 seconds to 0.03 seconds

Refer

https://blog.saeloun.com/2019/11/26/rails-6-insert-all.html

Share the news now

Source : Viblo