--- title: "Bulk insert support in Rails 6" description: "Rails 6 introduced methods such as insert_all, insert_all! and update_all to support bulk insert" canonical_url: "https://www.bigbinary.com/blog/bulk-insert-support-in-rails-6" markdown_url: "https://www.bigbinary.com/blog/bulk-insert-support-in-rails-6.md" --- # Bulk insert support in Rails 6 Rails 6 introduced methods such as insert_all, insert_all! and update_all to support bulk insert - Author: Vishal Telangre - Published: April 15, 2019 - Categories: Rails 6, Rails Rails 6 has added support for bulk inserts similar to how bulk update is supported using `update_all` and bulk delete is supported using `delete_all`. Bulk inserts can be performed using newly added methods: `insert_all`, `insert_all!` and `upsert_all`. All of these new methods allow the insertion of multiple records of the same model into the database. A single `INSERT` SQL query is prepared by these methods and 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. Let's discuss `insert_all`, `insert_all!` and `upsert_all` methods in detail, which are all used to perform bulk insert. We will create an `articles` table with two unique indexes. ```ruby create_table :articles do |t| t.string :title, null: false t.string :slug, null: false t.string :author, null: false t.text :description t.index :slug, unique: true t.index [:title, :author], unique: true end ``` Note that we do not allow duplicate `slug` columns. We also prevent records from having duplicate `title` and `author` columns together. To try out the examples provided in this blog post, please ensure to always clean up the `articles` table 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 `ActiveRecord::Persistence#insert_all` method. ##### 1.1 Behavior with PostgreSQL Let's run the following example on a PostgreSQL database. ```ruby result = Article.insert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, { id: 1, # duplicate 'id' here title: 'Type Safety in Elm', author: 'George', slug: 'elm-type-safety' }, { id: 2, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-1' }, { id: 3, title: 'Authentication with Devise - Part 1', author: 'Laura', # duplicate 'title' & 'author' here slug: 'devise-auth-2' }, { id: 4, title: 'Dockerizing and Deploying Rails App to Kubernetes', author: 'Paul', slug: 'rails-on-k8s' }, { id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }, # duplicate 'slug' here { id: 6, title: 'Working Remotely', author: 'Greg', slug: 'working-remotely' } ] ) # Bulk Insert (2.3ms) INSERT INTO "articles"("id","title","author","slug") VALUES (1, 'Handling 1M Requests [...snip...] 'working-remotely') ON CONFLICT DO NOTHING RETURNING "id" puts result.inspect ##}> puts Article.count # 4 ``` The `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. Notice the `ON CONFLICT DO NOTHING` clause in the `INSERT` query. 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 `articles` table. One of the records being inserted has a duplicate `id: 1` attribute, which violates unique primary key constraint. Another record that has duplicate `title: 'Authentication with Devise - Part 1', author: 'Laura'` attributes violates the multi-column unique index defined on `title` and `author` columns. Another record has duplicate `slug: '1m-req-per-second'` attributes violates the unique index defined on the `slug` column. All of these records that violate any unique constraint or unique index are skipped and are not inserted into the database. If successful, `ActiveRecord::Persistence#insert_all` returns an instance of `ActiveRecord::Result`. 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. For PostgreSQL, by default, `insert_all` method appends `RETURNING "id"` clause to the SQL query where `id` is the primary key(s). This clause instructs the database to return the `id` of every successfully inserted record. By inspecting the result, especially the `@columns=["id"], @rows=[[1], [2], [4], [6]]` attributes of the result instance, we can see that the records having `id` attribute with values `1, 2, 4 and 6` were successfully inserted. What if we want to see more attributes and not just the `id` attribute of the successfully inserted records in the result? We should use the optional `returning` option, which accepts an array of attribute names, which should be returned for all successfully inserted records! ```ruby result = Article.insert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, #...snip... ], returning: %w[ id title ] ) # Bulk Insert (2.3ms) INSERT INTO "articles"("id","title","author","slug") VALUES (1, 'Handling 1M Requests [...snip...] 'working-remotely') ON CONFLICT DO NOTHING RETURNING "id","title" puts result.inspect ##, "title"=>#}> puts result.pluck("id", "title").inspect #[[1, "Handling 1M Requests Per Second"], [2, "Authentication with Devise - Part 1"], [4, "Dockerizing and Deploying Rails App to Kubernetes"], [6, "Working Remotely"]] ``` Notice how the `INSERT` query appends `RETURNING "id","title"` clause and the result now holds the `id` and `title` attributes 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 `insert_all` when we run our example on a SQLite database. ```ruby result = Article.insert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, { id: 1, title: 'Type Safety in Elm', author: 'George', slug: 'elm-type-safety' }, # duplicate 'id' here #...snip... ] ) # Bulk Insert (1.6ms) INSERT INTO "articles"("id","title","author","slug") VALUES (1, 'Handling 1M Requests [...snip...] 'working-remotely') ON CONFLICT DO NOTHING puts result.inspect # puts Article.pluck(:id, :title) #[[1, "Handling 1M Requests Per Second"], [2, "Authentication with Devise - Part 1"], [4, "Dockerizing and Deploying Rails App to Kubernetes"], [6, "Working Remotely"]] puts Article.count # 4 ``` Note that since SQLite does not support `RETURING` clause, it is not being added to the SQL query. Therefore, the returned `ActiveRecord::Result` instance does not contain any useful information. If we try to explicitly use the `returning` option when the database being used is SQLite, then the `insert_all` method throws an error. ```ruby Article.insert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, #...snip... ], returning: %w[ id title ] ) # ActiveRecord::ConnectionAdapters::SQLite3Adapter does not support :returning (ArgumentError) ``` ##### 1.3 Behavior with MySQL The records that violate primary key, unique key constraints, or unique indexes are skipped during bulk insert operation performed using `insert_all` on a MySQL database. ```ruby result = Article.insert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, { id: 1, title: 'Type Safety in Elm', author: 'George', slug: 'elm-type-safety' }, # duplicate 'id' here #...snip... ] ) # Bulk Insert (20.3ms) INSERT INTO `articles`(`id`,`title`,`author`,`slug`) VALUES (1, 'Handling 1M Requests [...snip...] 'working-remotely') ON DUPLICATE KEY UPDATE `id`=`id` puts result.inspect # puts Article.pluck(:id, :title) #[[1, "Handling 1M Requests Per Second"], [2, "Authentication with Devise - Part 1"], [4, "Dockerizing and Deploying Rails App to Kubernetes"], [6, "Working Remotely"]] puts Article.count # 4 ``` Here, the `ON DUPLICATE KEY UPDATE 'id'='id'` clause in the `INSERT` query is essentially doing the same thing as the `ON CONFLICT DO NOTHING` clause supported by PostgreSQL and SQLite. Since MySQL does not support `RETURING` clause, it is not being included in the SQL query and therefore, the result doesn't contain any useful information. Explicitly trying to use `returning` option with `insert_all` method on a MySQL database throws `ActiveRecord::ConnectionAdapters::Mysql2Adapter does not support :returning` error. ## 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. The optional `unique_by` option of the `insert_all` method 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 unique index `:index_articles_on_title_and_author` using `unique_by` option. The duplicate records that do not violate `index_articles_on_title_and_author` index are not skipped, and therefore throw an error. ```ruby result = Article.insert_all( [ { .... }, { .... }, # duplicate 'id' here { .... }, { .... }, # duplicate 'title' and 'author' here { .... }, { .... }, # duplicate 'slug' here { .... } ], unique_by: :index_articles_on_title_and_author ) # PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "articles_pkey" (ActiveRecord::RecordNotUnique) # DETAIL: Key (id)=(1) already exists. ``` In case of SQLite, the error appears as shown below. ```text # SQLite3::ConstraintException: UNIQUE constraint failed: articles.id (ActiveRecord::RecordNotUnique) ``` In this case we get `ActiveRecord::RecordNotUnique` error which is caused by the violation of primary key constraint on the `id` column. It didn't skip the second record in the example above which violated the unique index on primary key `id` since the `unique_by` option was specified with a different unique index. When an exception occurs, no record persists to the database since `insert_all` executes just a single SQL query. The `unique_by` option can be identified by columns or a unique index name. `~~~ruby` unique_by: :index_articles_on_title_and_author ## is same as unique_by: %i[ title author ] ## Also, unique_by: :slug ## is same as unique_by: %i[ :slug ] ## and also same as unique_by: :index_articles_on_slug ```ruby Let's remove (or fix) the record that has duplicate primary key and re-run the above example. ~~~ruby result = Article.insert_all( [ { .... }, { .... }, { .... }, { .... }, # duplicate 'title' and 'author' here { .... }, { .... }, # duplicate 'slug' here { .... } ], unique_by: :index_articles_on_title_and_author ) # PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_articles_on_slug" (ActiveRecord::RecordNotUnique) # DETAIL: Key (slug)=(1m-req-per-second) already exists. ``` In case of SQLite, the error looks appears as shown below. ```ruby # SQLite3::ConstraintException: UNIQUE constraint failed: articles.slug (ActiveRecord::RecordNotUnique) ``` The `ActiveRecord::RecordNotUnique` error in the example above now says the `index_articles_on_slug` unique constraint is violated. Note how it intentionally didn't raise an error for the unique constraint violated on the `title` and `author` columns by the fourth record in the examples above. Now we will remove (or fix) the record that has same slug. ```ruby result = Article.insert_all( [ { .... }, { .... }, { .... }, { .... }, # duplicate 'title' and 'author' here { .... }, { .... }, { .... } ], unique_by: :index_articles_on_title_and_author ) # Bulk Insert (2.5ms) INSERT INTO "articles"("id","title","author","slug") VALUES (1, 'Handling 1M Requests Per Second', [...snip...] 'working-remotely') ON CONFLICT ("title","author") DO NOTHING RETURNING "id" puts result.inspect ##}> ``` Here, the fourth record was skipped since that record violates the unique index `index_articles_on_title_and_author` specified by the `unique_by` option. Similarly, we can specify a different unique index using the `unique_by` option. For example, if we specify `unique_by: :slug` option then the records containing duplicate `slug` columns will be skipped, but would raise `ActiveRecord::RecordNotUnique` exception if any record violates other unique constraints. ##### 2.2 Behavior with MySQL The `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 The `insert_all!` method (with bang version) never skips a duplicate record. If a record violates any unique constraints, then `insert_all!` method would simply throw an `ActiveRecord::RecordNotUnique` error. When database is PostgreSQL, `insert_all!` method can accept optional `returning` option, which we discussed in depth in 1.1 section above. The `unique_by` option is not supported by the `insert_all!` method. ## 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. The `upsert_all` method in Rails 6 allows performing bulk upserts. Let's see it's usage and behavior with different database systems. ###### 4.1 `upsert_all` in MySQL Let's try to bulk upsert multiple articles containing some duplicates. ```ruby result = Article.upsert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, { id: 1, .... }, # duplicate 'id' here { id: 2, .... }, { id: 3, .... }, # duplicate 'title' and 'author' here { id: 4, .... }, { id: 5, .... }, # duplicate 'slug' here { id: 6, .... } ] ) # Bulk Insert (26.3ms) INSERT INTO `articles`(`id`,`title`,`author`,`slug`) VALUES (1, 'Handling 1M Requests Per Second', 'John', [...snip...] 'working-remotely') ON DUPLICATE KEY UPDATE `title`=VALUES(`title`),`author`=VALUES(`author`),`slug`=VALUES(`slug`) puts result.inspect # puts Article.count # 5 puts Article.all #, #
, #
, #
, #
]> ``` 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 `id: 3` replaced the attributes of the third row since both had duplicate "title" and "author" attributes. The rest of the rows were not duplicates or no longer became duplicates, and therefore were inserted without any issues. Note that the `returning` and `unique_by` options are not supported in the `upsert_all` method when the database is MySQL. ###### 4.2 `upsert_all` in SQLite Let's try to execute the same example from the above section 4.1 when database is SQLite. ```ruby result = Article.upsert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, { id: 1, title: 'Type Safety in Elm', author: 'George', slug: 'elm-type-safety' }, # duplicate 'id' here { id: 2, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-1' }, { id: 3, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-2' }, # duplicate 'title' and 'author' here { id: 4, title: 'Dockerizing and Deploying Rails App to Kubernetes', author: 'Paul', slug: 'rails-on-k8s' }, { id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }, # duplicate 'slug' here { id: 6, title: 'Working Remotely', author: 'Greg', slug: 'working-remotely' } ] ) # Bulk Insert (4.0ms) INSERT INTO "articles"("id","title","author","slug") VALUES (1, 'Handling 1M Requests Per Second', [...snip...] 'working-remotely') ON CONFLICT ("id") DO UPDATE SET "title"=excluded."title","author"=excluded."author","slug"=excluded."slug" # SQLite3::ConstraintException: UNIQUE constraint failed: articles.title, articles.author (ActiveRecord::RecordNotUnique) ``` The bulk upsert operation failed in the above example due to `ActiveRecord::RecordNotUnique` exception. 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, by default, 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 `ActiveRecord::RecordNotUnique` exception. The `ON CONFLICT ("id") DO UPDATE` clause in the SQL query above conveys the same intent. Therefore, `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 `upsert_all` calls with the usage of `unique_by` option. If a duplicate record is encountered during the upsert operation, which violates the unique index specified using `unique_by` option then it will replace the attributes of the existing matching record. Let's try to understand this workaround with another example. ```ruby Article.upsert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, { id: 1, title: 'Type Safety in Elm', author: 'George', slug: 'elm-type-safety' }, # duplicate 'id' here ], unique_by: :id ) Article.upsert_all( [ { id: 2, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-1' }, { id: 3, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-2' }, # duplicate 'title' and 'author' here { id: 4, title: 'Dockerizing and Deploying Rails App to Kubernetes', author: 'Paul', slug: 'rails-on-k8s' }, { id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }, # duplicate 'slug' here { id: 6, title: 'Working Remotely', author: 'Greg', slug: 'working-remotely' } ], unique_by: %i[ title author ] ) puts Article.count # 5 puts Article.all #, #
, #
, #
, #
]> ``` Here, we first tried to upsert all the records which violated the unique primary key index on `id` column. Later, we upsert successfully all the remaining records, which violated the unique index on the `title` and `author` columns. Note that since the first record's `slug` attribute was already replaced with the second record's `slug` attribute; the last second record having `id: 5` didn't raise an exception because of duplicate `slug` column. ###### 4.3 `upsert_all` in PostgreSQL We will run the same example in the 4.1 section above with PostgreSQL database. ```ruby result = Article.upsert_all( [ { id: 1, title: 'Handling 1M Requests Per Second', author: 'John', slug: '1m-req-per-second' }, { id: 1, title: 'Type Safety in Elm', author: 'George', slug: 'elm-type-safety' }, # duplicate 'id' here { id: 2, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-1' }, { id: 3, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-2' }, # duplicate 'title' and 'author' here { id: 4, title: 'Dockerizing and Deploying Rails App to Kubernetes', author: 'Paul', slug: 'rails-on-k8s' }, { id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }, # duplicate 'slug' here { id: 6, title: 'Working Remotely', author: 'Greg', slug: 'working-remotely' } ] ) # PG::CardinalityViolation: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time (ActiveRecord::StatementInvalid) # HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. ``` The bulk upsert operation failed in the above example due to `ActiveRecord::StatementInvalid` exception which was caused by another `PG::CardinalityViolation` exception. The `PG::CardinalityViolation` exception originates from [here](https://github.com/postgres/postgres/blob/beeb8e2e0717065296dc7b32daba2d66f0f931dd/src/backend/executor/nodeModifyTable.c#L1335-L1355). The `PG::CardinalityViolation` exception occurs when a row cannot be updated a second time in the same `ON CONFLICT DO UPDATE` SQL query. PostgreSQL assumes 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](https://github.com/rails/rails/issues/35519). Therefore, the `upsert_all` method doesn't work as intended due to the above limitation in PostgreSQL. As a workaround, we can divide the single `upsert_all` query into multiple `upsert_all` queries with the use of `unique_by` option similar to how we did in case of SQLite in the 4.2 section above. ```ruby Article.insert_all( [ { id: 1, title: 'Handling 1M requests per second', author: 'John', slug: '1m-req-per-second' }, { id: 2, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-1' }, { id: 4, title: 'Dockerizing and deploy Rails app to Kubernetes', author: 'Paul', slug: 'rails-on-k8s' }, { id: 6, title: 'Working Remotely', author: 'Greg', slug: 'working-remotely' } ] ) Article.upsert_all( [ { id: 1, title: 'Type Safety in Elm', author: 'George', slug: 'elm-type-safety' }, # duplicate 'id' here ] ) Article.upsert_all( [ { id: 3, title: 'Authentication with Devise - Part 1', author: 'Laura', slug: 'devise-auth-2' }, # duplicate 'title' and 'author' here ], unique_by: :index_articles_on_title_and_author ) Article.upsert_all( [ { id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }, # duplicate 'slug' here ] ) puts Article.count # 5 puts Article.all #, #
, #
, #
, #
]> ``` For reference, note that the `upsert_all` method also accepts `returning` option for PostgreSQL which we have already discussed in the 1.1 section above. ## 5. `insert`, `insert!` and `upsert` Rails 6 has also introduced three more additional methods namely `insert`, `insert!` and `upsert` for convenience. The `insert` method inserts a single record into the database. If that record violates a uniqueness constrain, then the `insert` method will skip inserting record into the database without raising an exception. Similarly, the `insert!` method also inserts a single record into the database, but will raise `ActiveRecord::RecordNotUnique` exception if that record violates a uniqueness constraint. The `upsert` method inserts or updates a single record into the database similar to how `upsert_all` does. The methods `insert`, `insert!` and `upsert` are wrappers around `insert_all`, `insert_all!` and `upsert_all` respectively. Let's see some examples to understand the usage of these methods. ```ruby Article.insert({ id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }, unique_by: :slug) # is same as Article.insert_all([{ id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }], unique_by: :slug) ``` ```ruby Article.insert!({ id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }, returning: %w[ id title ]) # is same as Article.insert_all!([{ id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }], returning: %w[ id title ]) ``` ```ruby Article.upsert({ id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }, unique_by: :slug, returning: %w[ id title ]) # is same as Article.upsert_all([{ id: 5, title: 'Elm on Rails', author: 'Amanda', slug: '1m-req-per-second' }], unique_by: :slug, returning: %w[ id title ]) ``` --- To learn more about the bulk insert feature and its implementation, please check [rails/rails#35077](https://github.com/rails/rails/pull/35077), [rails/rails#35546](https://github.com/rails/rails/pull/35546) and [rails/rails#35854](https://github.com/rails/rails/pull/35854). ## Links - [Human page](https://www.bigbinary.com/blog/bulk-insert-support-in-rails-6)