Rails support bulk insert/upsert on relation


Rails 6.1 has added support for bulk insertion for associations. Previously, bulk insertion for associations did not work as expected.

Before Rails 6.1

Rails 6.0 has added support in ActiveRecord for bulk record insertion which works similar to update_all. It provides methods like insert_all, insert_all!, upsert_all etc for the bulk creation. We can do bulk insert in a table as:

Address.insert_all(
  [
    {
      address1: 'address1',
      city: 'city1',
      state: 'state1'
    },
    {
      address1: 'address2',
      city: 'city2',
      state: 'state2'
    }
  ]
)

# Address Bulk Insert (198.5ms)  INSERT INTO
# "addresses"("address1","city","state") VALUES 
# ('address1', 'city1', 'state1'), ('address2', 'city2', 'state2')

puts Address.count
=> 2

It will create 2 records for Address.

Now, if we try to create multiple records using an association as below:

user.addresses.insert_all(
  [
    {
      address1: 'address1',
      city: 'city1',
      state: 'state1'
    },
    {
      address1: 'address2',
      city: 'city2',
      state: 'state2'
    }
  ]
)

# Address Bulk Insert (198.5ms)  
# INSERT INTO "addresses"("address1","city","state") 
# VALUES ('address1', 'city1', 'state1'), ('address2', 'city2', 'state2')

puts Address.count
=> 2
puts user.addresses.count
=> 0

It creates 2 address records but not for this user. We can see that the same query is fired in both cases.

In both cases, multiple records are getting created but the association is ignored in the second example.

Rails 6.1

Rails 6.1 has provided support for bulk insert with relation. We can now use:

  user.addresses.insert_all

If we use the above example and try to create multiple addresses for the user, it will create multiple addresses for that user like this:

user.addresses.insert_all(
  [
    {
      address1: 'address1',
      city: 'city1',
      state: 'state1'
    },
    {
      address1: 'address2',
      city: 'city2',
      state: 'state2'
    }
  ]
)

# Address Bulk Insert (306.5ms)  
# INSERT INTO "addresses" ("address1","city","state","user_id") 
# VALUES ('address1', 'city1', 'state1', 1), ('address2', 'city2', 'state2', 1)

puts Address.count
=> 2
puts user.addresses.count
=> 2

It creates 2 address records for the user.

Even if we provide different users as value, it still creates records for the relation user as expected.

user.addresses.insert_all(
  [
    {
      address1: 'address1',
      city: 'city1',
      state: 'state1',
      user_id: user1.id
    },
    {
      address1: 'address2',
      city: 'city2',
      state: 'state2',
      user_id: user1.id
    }
  ]
)

# Address Bulk Insert (206.8ms)
# INSERT INTO "addresses" ("address1","city","state","user_id")
# VALUES ('address1', 'city1', 'state1', 1), ('address2', 'city2', 'state2', 1)

puts user.addresses.count
=> 2

Limitations

Bulk insertion is not supported for has_many: through association yet.