Having methods that can perform database operations in bulk is always a plus considering the amount of efficiency they add to such operations. Rails already had methods like update_all and delete_all to bulk update and bulk delete records respectively. But, Ruby on Rails developers always felt a need for similar methods to bulk insert records.

Before Rails 6, Rails community used ActiveRecord Import gem to insert or update records in bulk. If you are still on a Rails version less than Rails 6, please check out this gem.

Finally with Rails 6, this need is addressed with the addition of insert_all, insert_all!, upsert_all and some more similar methods.

Consider, we have created a table for model Commodity.

create_table commodities do |t|
  t.string :name, null: false
  t.float :price, null: false

  t.index :name, unique: true
end

1. insert_all(attributes, returning: nil, unique_by: nil)

This method can be used to insert multiple records with a single SQL INSERT statement. The method simply skips ActiveRecord callbacks or validations. It accepts array of hashes, where every hash determines the attributes for a single record or row.

Now, let’s say, we want to insert records for multiple commodities into the database. Here is how we can use insert_all method to do it with one query.

Commodity.insert_all([
  { id: 1, name: "Crude Oil", price: 58.14 },
  { id: 2, name: "Copper", price: 2.66 },
  { id: 3, name: "Natural Gas", price: 2.42 },
  { id: 4, name: "Natural Gas", price: 2.01 }
])

Here, the last record contains the attribute name which is duplicate of the record with id 3. insert_all will simply skip the insertion of the duplicate records and proceed further.

This call will generate following SQL query:

INSERT INTO "commodities"("id","name","price") VALUES (1, \'Crude Oil\', 58.14), (2, \'Copper\', 2.66), (3, \'Natural Gas\', 2.42), (4, \'Natural Gas\', 2.01) ON CONFLICT  DO NOTHING RETURNING "id"

Options

• :returning

This option is used tell what attributes the table, you wish to include in the array that is going to be returned for all the successfully inserted records. If not specified, it returns an array containing the primary key – ids of the records. This option is supported only by PostgreSQL adapter. Above example can be modified if we want it to return ids and names of the commodities:

Commodity.insert_all([
  { id: 1, name: "Crude Oil", price: 58.14 },
  { id: 2, name: "Copper", price: 2.66 },
  { id: 3, name: "Natural Gas", price: 2.42 },
  { id: 4, name: "Natural Gas", price: 2.01 }
  returning: %w[id name]
])

We can also pass returning: false to make it return nothing.

• :unique_by

This attribute allows us to select specific set of attributes by which records should be filtered for uniqueness. If we don\’t pass this option then the records will be filtered for uniqueness based on all unique indexes added to the table. All duplicate records are skipped from inserting into the table. This option is supported only for PostgreSQL and SQLite adapters. We can pass unique_by: %i[ id name ] to above call if we want to specify that unique records be found out based on uniqueness of the id and name attributes of commodities table.

2. insert_all!(attributes, returning: nil)

This method works same as that of insert_all but in addition to that it raises ActiveRecord::RecordNotUnique if any record violates a unique index on the table.

Let’s clean up the table and then make following call to see how insert_all! treats the same set of data.

Commodity.insert_all!([
  { id: 1, name: "Crude Oil", price: 58.14 },
  { id: 2, name: "Copper", price: 2.66 },
  { id: 3, name: "Natural Gas", price: 2.42 },
  { id: 4, name: "Natural Gas", price: 2.01 }
])

Notice that records with IDs 3 and 4 clearly violate uniqueness constraint for the attribute name. Hence this call throws ActiveRecord::RecordNotUnique error. This call ends up inserting no records.

This call generates following SQL query:

INSERT INTO "commodities"("id","name","price") VALUES (1, \'Crude Oil\', 58.14), (2, \'Copper\', 2.66), (3, \'Natural Gas\', 2.42), (4, \'Natural Gas\', 2.01) RETURNING "id"

3. upsert_all(attributes, returning: nil, unique_by: nil)

It updates the records if they exist or simply inserts them into database with a single SQL INSERT statement.

Let’s assume that commodities table contains following records before we make call to upsert_all:

+----+-------------+-------+
| id | name        | price |
+----+-------------+-------+
| 1  | Crude Oil   | 58.14 |
| 2  | Copper      | 2.66  |
| 3  | Natural Gas | 2.42  |
+----+-------------+-------+

Then lets make a call to upsert_all method:

Commodity.upsert_all([
  { id: 1, name: "Crude Oil", price: 51.27 },
  { id: 2, name: "Copper", price: 2.84 },
  { id: 4, name: "Gold", price: 1480.35 }
])

Notice that the records with IDs 1, 2 and 3 already exist in the commodities table. This call will generate following SQL query.

INSERT INTO "commodities"("id","name","price") VALUES (1, \'Crude Oil\', 51.27), (2, \'Copper\', 2.84), (4, \'Gold\', 1480.35) ON CONFLICT ("id") DO UPDATE SET "name"=excluded."name","price"=excluded."price" RETURNING "id"

The first three records will be updated and fourth one will be inserted. The commodities table will now contain:

+----+-------------+---------+
| id | name        | price   |
+----+-------------+---------+
| 1  | Crude Oil   | 51.27   |
| 2  | Copper      | 2.84    |
| 3  | Natural Gas | 2.42    |
| 4  | Gold        | 1480.35 |
+----+-------------+---------+