Bulk Upsert in Ruby/Rails

The upsert gem is great for individual upserts, but for performant bulk upserts, use the activerecord-import gem.

Add a unique index on the columns to upsert on (if it’s not your primary key)

class AddUpsertIndexOnForecasts < ActiveRecord::Migration[5.2]
  def change
    add_index :forecasts, [:date], unique: true
  end
end

Prep your records

records = [
  {date: "2018-01-01", value: 10},
  {date: "2018-02-01", value: 15},
  {date: "2018-03-01", value: 23}
]

For PostgreSQL 9.5+ and SQLite 3.24+, do:

Forecast.import(records,
  validate: false,
  on_duplicate_key_update: {
    conflict_target: [:date],
    columns: [:value]
  }
)

For MySQL, do:

Forecast.import(records,
  validate: false,
  on_duplicate_key_update: [:value]
)

Official docs

Published June 16, 2018


You might also enjoy

Bootstrapping Postgres Users

Scaling Reads

The Safely Pattern


All code examples are public domain.
Use them however you’d like (licensed under CC0).