Upsert A Record In Rails Using Upsert_all With Conflict Resolution
Syncing from an external system means “create it if it is new, update it if it exists” for thousands of rows. Doing that with find_or_initialize_by in a loop is two queries per record; upsert_all is one statement for the whole batch.
Description
upsert_all issues an INSERT ... ON CONFLICT ... DO UPDATE. unique_by: names the unique index that defines a conflict, and update_only: limits which columns get overwritten so you do not clobber locally-managed fields.
It bypasses validations and callbacks, and it does not set timestamps — supply created_at and updated_at yourself.
record_timestamps: true (Rails 7+) makes Rails manage them for you, which is usually what you want.
Use on_duplicate: for a custom SQL update clause when you need conditional logic, such as only overwriting when the incoming row is newer.
Sample input:
Product.upsert_all(rows, unique_by: :sku, update_only: [:name, :price_cents])
Sample Output:
INSERT INTO products (...) VALUES (...)
ON CONFLICT (sku) DO UPDATE SET name = excluded.name, price_cents = excluded.price_cents
Answer
# Requires: add_index :products, :sku, unique: true
rows = feed.map do |item|
{ sku: item["sku"], name: item["name"], price_cents: item["price_cents"] }
end
# Insert new, update existing — timestamps handled by Rails
Product.upsert_all(rows, unique_by: :sku, record_timestamps: true)
# Only overwrite specific columns; leave locally-managed ones alone
Product.upsert_all(
rows,
unique_by: :sku,
update_only: [:name, :price_cents],
record_timestamps: true
)
# Conditional update — only if the incoming row is newer
Product.upsert_all(
rows,
unique_by: :sku,
on_duplicate: Arel.sql(
"name = excluded.name, price_cents = excluded.price_cents, " \
"updated_at = excluded.updated_at " \
"WHERE products.updated_at < excluded.updated_at"
)
)
# Get the affected ids back (PostgreSQL)
result = Product.upsert_all(rows, unique_by: :sku, returning: %w[id sku])
result.rows
# Batch large feeds
rows.each_slice(1_000) { |batch| Product.upsert_all(batch, unique_by: :sku) }
Check viewARU - Brand Newsletter!
Newsletter to DEVs by DEVs - boost your Personal Brand & career! 🚀
