We often come across use cases in a Rails application where we need to create, update, or delete records in bulk. Most ORMs naively update or insert each record individually by executing N+1 database queries. To address this problem, Rails 6 added the upsert_all method that provides a faster way to update and insert a large number of records.

upsert_all uses a single SQL INSERT statement for updating/inserting the records. And it does so without instantiating any models. Nor does it trigger any Active Record callbacks or validations !

How upsert_all works?

If we check the source code of upsert_all, we understand that it calls execute method of InsertAll under the hood which later updates or inserts (upserts) multiple records into the database using a single SQL INSERT statement.

For Postgres and SQlite3 upsert_all utilizes ON CONFLICT clause, whereas for MySQL it uses ON DUPLICATE KEY clause.

Check implementation of build_insert_sql method under postgresql_adapter.rb, sqlite3_adapter.rb and abstract_mysql_adapter.rb to get further details.

In this article, we will first see the available options with upsert_all in Rails 6, and then the newly introduced ones in Rails 7.

We often come across use cases in a Rails application where we need to create, update, or delete records in bulk. Most ORMs naively update or insert each record individually by executing N+1 database queries. To address this problem, Rails 6 added the upsert_all method that provides a faster way to update and insert a large number of records.

upsert_all uses a single SQL INSERT statement for updating/inserting the records. And it does so without instantiating any models. Nor does it trigger any Active Record callbacks or validations !

How upsert_all works?

If we check the source code of upsert_all, we understand that it calls execute method of InsertAll under the hood which later updates or inserts (upserts) multiple records into the database using a single SQL INSERT statement.

For Postgres and SQlite3 upsert_all utilizes ON CONFLICT clause, whereas for MySQL it uses ON DUPLICATE KEY clause.

Check implementation of build_insert_sql method under postgresql_adapter.rb, sqlite3_adapter.rb and abstract_mysql_adapter.rb to get further details.

In this article, we will first see the available options with upsert_all in Rails 6, and then the newly introduced ones in Rails 7.

We often come across use cases in a Rails application where we need to create, update, or delete records in bulk. Most ORMs naively update or insert each record individually by executing N+1 database queries. To address this problem, Rails 6 added the upsert_all method that provides a faster way to update and insert a large number of records.

upsert_all uses a single SQL INSERT statement for updating/inserting the records. And it does so without instantiating any models. Nor does it trigger any Active Record callbacks or validations !

How upsert_all works?

If we check the source code of upsert_all, we understand that it calls execute method of InsertAll under the hood which later updates or inserts (upserts) multiple records into the database using a single SQL INSERT statement.

For Postgres and SQlite3 upsert_all utilizes ON CONFLICT clause, whereas for MySQL it uses ON DUPLICATE KEY clause.

Check implementation of build_insert_sql method under postgresql_adapter.rb, sqlite3_adapter.rb and abstract_mysql_adapter.rb to get further details.

In this article, we will first see the available options with upsert_all in Rails 6, and then the newly introduced ones in Rails 7.

We often come across use cases in a Rails application where we need to create, update, or delete records in bulk. Most ORMs naively update or insert each record individually by executing N+1 database queries. To address this problem, Rails 6 added the upsert_all method that provides a faster way to update and insert a large number of records.

upsert_all uses a single SQL INSERT statement for updating/inserting the records. And it does so without instantiating any models. Nor does it trigger any Active Record callbacks or validations !

How upsert_all works?

If we check the source code of upsert_all, we understand that it calls execute method of InsertAll under the hood which later updates or inserts (upserts) multiple records into the database using a single SQL INSERT statement.

For Postgres and SQlite3 upsert_all utilizes ON CONFLICT clause, whereas for MySQL it uses ON DUPLICATE KEY clause.

Check implementation of build_insert_sql method under postgresql_adapter.rb, sqlite3_adapter.rb and abstract_mysql_adapter.rb to get further details.

In this article, we will first see the available options with upsert_all in Rails 6, and then the newly introduced ones in Rails 7.

We often come across use cases in a Rails application where we need to create, update, or delete records in bulk. Most ORMs naively update or insert each record individually by executing N+1 database queries. To address this problem, Rails 6 added the upsert_all method that provides a faster way to update and insert a large number of records.

upsert_all uses a single SQL INSERT statement for updating/inserting the records. And it does so without instantiating any models. Nor does it trigger any Active Record callbacks or validations !

How upsert_all works?

If we check the source code of upsert_all, we understand that it calls execute method of InsertAll under the hood which later updates or inserts (upserts) multiple records into the database using a single SQL INSERT statement.

For Postgres and SQlite3 upsert_all utilizes ON CONFLICT clause, whereas for MySQL it uses ON DUPLICATE KEY clause.

Check implementation of build_insert_sql method under postgresql_adapter.rb, sqlite3_adapter.rb and abstract_mysql_adapter.rb to get further details.

In this article, we will first see the available options with upsert_all in Rails 6, and then the newly introduced ones in Rails 7.

We often come across use cases in a Rails application where we need to create, update, or delete records in bulk. Most ORMs naively update or insert each record individually by executing N+1 database queries. To address this problem, Rails 6 added the upsert_all method that provides a faster way to update and insert a large number of records.

upsert_all uses a single SQL INSERT statement for updating/inserting the records. And it does so without instantiating any models. Nor does it trigger any Active Record callbacks or validations !

How upsert_all works?

If we check the source code of upsert_all, we understand that it calls execute method of InsertAll under the hood which later updates or inserts (upserts) multiple records into the database using a single SQL INSERT statement.

For Postgres and SQlite3 upsert_all utilizes ON CONFLICT clause, whereas for MySQL it uses ON DUPLICATE KEY clause.

Check implementation of build_insert_sql method under postgresql_adapter.rb, sqlite3_adapter.rb and abstract_mysql_adapter.rb to get further details.

In this article, we will first see the available options with upsert_all in Rails 6, and then the newly introduced ones in Rails 7.