upsert_all

Updates or inserts (upserts) multiple records into the database in a single SQL INSERT statement. It does not instantiate any models nor does it trigger Active Record callbacks or validations. Though passed values go through Active Record’s type casting and serialization.

The attributes parameter is an Array of Hashes. Every Hash determines the attributes for a single row and must have the same keys.

Returns an ActiveRecord::Result with its contents based on :returning (see below).

By default, upsert_all will update all the columns that can be updated when there is a conflict. These are all the columns except primary keys, read-only columns, and columns covered by the optional unique_by.

Options

:returning

(PostgreSQL and SQLite3 only) An array of attributes to return for all successfully inserted records, which by default is the primary key. Pass returning: %w[ id name ] for both id and name or returning: false to omit the underlying RETURNING SQL clause entirely.

You can also pass an SQL string if you need more control on the return values (for example, returning: Arel.sql("id, name as new_name")).

:unique_by

(PostgreSQL and SQLite only) By default rows are considered to be unique by every unique index on the table. Any duplicate rows are skipped.

To skip rows according to just one unique index pass :unique_by.

Consider a Book model where no duplicate ISBNs make sense, but if any row has an existing id, or is not unique by another unique index, ActiveRecord::RecordNotUnique is raised.

Unique indexes can be identified by columns or name:

unique_by: :isbn
unique_by: %i[ author_id name ]
unique_by: :index_books_on_isbn

Because it relies on the index information from the database :unique_by is recommended to be paired with Active Record’s schema_cache.

:on_duplicate

Configure the SQL update sentence that will be used in case of conflict.

NOTE: If you use this option you must provide all the columns you want to update by yourself.

Example:

Commodity.upsert_all(
  [
    { id: 2, name: "Copper", price: 4.84 },
    { id: 4, name: "Gold", price: 1380.87 },
    { id: 6, name: "Aluminium", price: 0.35 }
  ],
  on_duplicate: Arel.sql("price = GREATEST(commodities.price, EXCLUDED.price)")
)

See the related :update_only option. Both options can’t be used at the same time.

:update_only

Provide a list of column names that will be updated in case of conflict. If not provided, upsert_all will update all the columns that can be updated. These are all the columns except primary keys, read-only columns, and columns covered by the optional unique_by

Example:

Commodity.upsert_all(
  [
    { id: 2, name: "Copper", price: 4.84 },
    { id: 4, name: "Gold", price: 1380.87 },
    { id: 6, name: "Aluminium", price: 0.35 }
  ],
  update_only: [:price] # Only prices will be updated
)

See the related :on_duplicate option. Both options can’t be used at the same time.

:record_timestamps

By default, automatic setting of timestamp columns is controlled by the model’s record_timestamps config, matching typical behavior.

To override this and force automatic setting of timestamp columns one way or the other, pass :record_timestamps:

record_timestamps: true  # Always set timestamps automatically
record_timestamps: false # Never set timestamps automatically

Examples

# Inserts multiple records, performing an upsert when records have duplicate ISBNs.
# Here "Eloquent Ruby" overwrites "Rework" because its ISBN is duplicate.

Book.upsert_all([
  { title: "Rework", author: "David", isbn: "1" },
  { title: "Eloquent Ruby", author: "Russ", isbn: "1" }
], unique_by: :isbn)

Book.find_by(isbn: "1").title # => "Eloquent Ruby"