Rails 6 has added support for bulk inserts
similar to how bulk update is supported using
and bulk delete is supported using
Bulk inserts can be performed using newly added methods:
All of these new methods allow the insertion of multiple records
of the same model into the database.
INSERT SQL query
is prepared by these methods
a single sql statement is sent to the database,
without instantiating the model
or invoking Active Record callbacks or validations.
During bulk insertion, violation of primary key, violation of unique indexes, and violation of unique constraints is possible. Rails leverages database-specific features to either skip, or upsert the duplicates, depending on the case.
in detail, which are all used to perform bulk insert.
We will create an
with two unique indexes.
Note that we do not allow duplicate
We also prevent records from having
author columns together.
To try out the examples provided in this blog post,
please ensure to always clean up the
before running any example.
1. Performing bulk inserts by skipping duplicates
Let’s say we want to insert multiple articles at once into the database. It is possible that certain records may violate the unique constraint(s) of the table. Such records are considered duplicates.
In other words, rows or records are determined to be unique by every unique index on the table by default.
To skip the duplicate rows or records,
and insert the rest of the records at once,
we can use
1.1 Behavior with PostgreSQL
Let’s run the following example on a PostgreSQL database.
insert_all method accepts a mandatory argument
which should be an array of hashes
with the attributes of the same model.
The keys in all hashes should be same.
ON CONFLICT DO NOTHING clause in the
This clause is supported by PostgreSQL and SQLite databases.
This instructs the database that
when there is a conflict or a unique key constraint violation
during bulk insert operation,
to skip the conflicting record silently
and proceed with the insertion of the next record.
In the above example,
we have exactly 3 records
which violate various unique constraints
defined on the
One of the records being inserted has a duplicate
id: 1 attribute,
which violates unique primary key constraint.
Another record that has
title: 'Authentication with Devise - Part 1', author: 'Laura'
attributes violates the multi-column unique index
Another record has
slug: '1m-req-per-second' attributes
violates the unique index defined on the
All of these records that violate any unique constraint or unique index are skipped and are not inserted into the database.
returns an instance of
The contents of the result vary per database.
In case of PostgreSQL database,
this result instance holds information
about the successfully inserted records
such as the chosen column names,
values of the those columns in each successfully inserted row, etc.
insert_all method appends
RETURNING "id" clause
to the SQL query
id is the primary key(s).
This clause instructs the database
to return the
of every successfully inserted record.
By inspecting the result,
@columns=["id"], @rows=[, , , ]
attributes of the result instance,
we can see that the records having
1, 2, 4 and 6
were successfully inserted.
What if we want to see more attributes
and not just the
of the successfully inserted records in the result?
We should use the optional
which accepts an array of attribute names,
which should be returned for all successfully inserted records!
Notice how the
RETURNING "id","title" clause
and the result now holds the
of the successfully inserted records.
1.2 Behavior with SQLite
Similar to PostgreSQL,
the violating records are skipped
during the bulk insert operation performed using
when we run our example on a SQLite database.
Note that since SQLite does not support
it is not being added to the SQL query.
Therefore, the returned
does not contain any useful information.
If we try to explicitly use the
when the database being used is SQLite,
insert_all method throws an error.
1.3 Behavior with MySQL
The records that violate primary key,
unique key constraints, or unique indexes
are skipped during bulk insert operation
insert_all on a MySQL database.
ON DUPLICATE KEY UPDATE 'id'='id' clause
INSERT query is essentially doing the same thing
ON CONFLICT DO NOTHING clause
supported by PostgreSQL and SQLite.
Since MySQL does not support
it is not being included in the SQL query
and therefore, the result doesn’t contain any useful information.
Explicitly trying to use
insert_all method on a MySQL database throws
ActiveRecord::ConnectionAdapters::Mysql2Adapter does not support :returning
2. Performing bulk inserts by skipping duplicates on a specified unique constraint but raising exception if records violate other unique constraints
In the previous case, we were skipping the records that were violating any unique constraints. In some case, we may want to skip duplicates caused by only a specific unique index but abort transaction if the other records violate any other unique constraints.
unique_by option of the
allows to define such a unique constraint.
2.1 Behavior with PostgreSQL and SQLite
Let’s see an example
to skip duplicate records
that violate only the specified
The duplicate records
that do not violate
are not skipped, and therefore throw an error.
In case of SQLite, the error appears as shown below.
In this case we get
which is caused
by the violation of primary key constraint
It didn’t skip the second record in the example above
which violated the unique index on primary key
unique_by option was specified
with a different unique index.
When an exception occurs,
no record persists to the database
insert_all executes just a single SQL query.
unique_by option can be identified by columns or a unique index name.
Let’s remove (or fix) the record that has duplicate primary key and re-run the above example.
In case of SQLite, the error looks appears as shown below.
in the example above now says
index_articles_on_slug unique constraint is violated.
Note how it intentionally didn’t raise an error
for the unique constraint violated on the
by the the fourth record in the examplea above.
Now we will remove (or fix) the record that has same slug.
Here, the fourth record was skipped
since that record violates the unique index
specified by the
we can specify a different unique index
if we specify
unique_by: :slug option
then the records containing duplicate
slug columns will be skipped,
but would raise
if any record violates other unique constraints.
2.2 Behavior with MySQL
unique_by option is not supported when the database is MySQL.
3. Raising exception if any of the records being bulk inserted violate any unique constraints
insert_all! method (with bang version)
never skips a duplicate record.
If a record violates any unique constraints,
insert_all! method would simply
When database is PostgreSQL,
insert_all! method can accept optional
which we discussed in depth in 1.1 section above.
unique_by option is not supported by the
4. Performing bulk upserts (updates or inserts)
So far, in the sections 1, 2 and 3 above, we discussed either skipping the duplicates or raising an exception if a duplicate is encountered during bulk inserts. Sometimes, we want to update the existing record when a duplicate occurs otherwise insert a new record. This operation is called upsert because either it tries to update the record, or if there is no record to update, then it tries to insert.
upsert_all method in Rails 6 allows performing bulk upserts.
Let’s see it’s usage and behavior with different database systems.
upsert_all in MySQL
Let’s try to bulk upsert multiple articles containing some duplicates.
The persisted records in the database look exactly as intended. Let’s discuss it in detail.
The second row in the input array
that has the
id: 1 attribute
replaced the first row,
which also had the duplicate
id: 1 attribute.
The fourth row that has
replaced the attributes of the third row
since both had duplicate “title” and “author”
The rest of the rows were not duplicates or no longer became duplicates, and therefore were inserted without any issues.
Note that the
are not supported in the
when the database is MySQL.
upsert_all in SQLite
Let’s try to execute the same example from the above section 4.1 when database is SQLite.
The bulk upsert operation
failed in the above example
Why it didn’t work similar to MySQL?
As per the documentation of MySQL, an upsert operation takes place if a new record violates any unique constraint.
Whereas, in case of SQLite,
new record replaces existing record
when both the existing and new record have the same primary key.
If a record violates any other unique constraints
other than the primary key,
it then raises
ON CONFLICT ("id") DO UPDATE clause in the SQL query above
conveys the same intent.
upsert_all in SQLite doesn’t behave exactly same as in MySQL.
As a workaround,
we will need to upsert records
with the help of multiple
with the usage of
If a duplicate record is encountered during the upsert operation,
which violates the unique index
then it will replace the attributes of the existing matching record.
Let’s try to understand this workaround with another example.
we first tried to upsert all the records
which violated the unique primary key index on
Later, we upsert successfully all the remaining records,
which violated the unique index on the
since the first record’s
slug attribute was already replaced
with the second record’s
the last second record having
id: 5 didn’t raise an exception
because of duplicate
upsert_all in PostgreSQL
We will run the same example in the 4.1 section above with PostgreSQL database.
The bulk upsert operation failed in the above example
which was caused by another
PG::CardinalityViolation exception occurs
when a row cannot be updated a second time
in the same
ON CONFLICT DO UPDATE SQL query.
this behavior would lead
the same row to updated a second time
in the same SQL query,
in unspecified order, non-deterministically.
PostgreSQL recommends it is the developer’s responsibility to prevent this situation from occurring.
Here’s more discussion about this issue - rails/rails#35519.
upsert_all method doesn’t work
due to the above limitation in PostgreSQL.
As a workaround,
we can divide the single
with the use of
similar to how we did in case of SQLite
in the 4.2 section above.
note that the
returning option for PostgreSQL
which we have already discussed in the 1.1 section above.
Rails 6 has also introduced three more additional methods
upsert for convenience.
insert method inserts a single record into the database.
If that record violates a uniqueness constrain,
insert method will skip inserting record
into the database without raising an exception.
also inserts a single record into the database,
but will raise
if that record violates a uniqueness constraint.
upsert method inserts or updates
a single record into the database
similar to how
are wrappers around
Let’s see some examples to understand the usage of these methods.