We often come across cases in the Rails application where we need to bulk-insert records. Rails 6 introduced insert_all and upsert_all methods to solve bulk import issues.
upsert_all is nothing but a combination of update + insert.
In case a record exists, it is updated with the new attributes, or
a new record is inserted.
In Rails 6,
we can pass
unique_by options to the
returning option expects an array of model attributes that
should be returned for all successfully inserted/updated records.
Let’s say we have a Commodity model with three attributes
We want to bulk import three rows of the Commodity model and
returning option that returns
price of the updated attributes.
The code to do the above steps will be as shown below:
By default in Rails 6,
when we pass a duplicate record to
it will update the record with new attributes.
In few scenarios,
we might want to raise an error when duplicate records are passed,
or fire our custom SQL query.
If we try to pass
on_duplicate option to
upsert_all method it would raise the below error:
To resolve the above issue,
Rails 7 added on_duplicate option to upsert_all
The change also allows us to pass raw SQL queries to
Continuing with the Commodity example,
let’s say we want to set the higher price of the commodity, in case we try to
upsert duplicate records.
We can pass a custom SQL query to the
which sets the higher price of the commodity.
As seen above,
the Copper price is updated to
4.84 as it is higher when compared to
but the Gold price remains the same.
we can pass a custom query to the
xmax = '0' is
true when records are inserted and
false for existing records.
The above changes will not be applicable for all types of databases.
returning option is supported in PostgreSQL but not in SQLite3.