Bulk Insert Records With Insert_all And Skip Duplicates In Rails
Importing thousands of rows one create! at a time issues one INSERT per record plus validations and callbacks. insert_all sends a single multi-row INSERT, which is orders of magnitude faster for imports and syncs.
Description
insert_all takes an array of attribute hashes and writes them in one statement. It bypasses validations, callbacks, and updated_at/created_at defaults — so you set the timestamps yourself and validate before calling it.
unique_by: names a unique index and turns the statement into ON CONFLICT DO NOTHING, so existing rows are skipped rather than raising RecordNotUnique.
Every hash must have the same keys in the same order, or you get a SQL error. Batch large arrays with each_slice to keep the statement and its bind parameters at a sane size.
Sample input:
rows = [
{ sku: "ABC-1", name: "Widget", price_cents: 1200 },
{ sku: "ABC-1", name: "Widget duplicate", price_cents: 1300 },
{ sku: "XYZ-9", name: "Gadget", price_cents: 4500 }
]
Sample Output:
# => 2 rows inserted, the duplicate SKU skipped
Answer
# Requires a unique index:
# add_index :products, :sku, unique: true
now = Time.current
rows = raw_rows.map do |r|
{
sku: r[:sku],
name: r[:name],
price_cents: r[:price_cents],
created_at: now,
updated_at: now
}
end
# Skip rows that collide on the unique index
result = Product.insert_all(rows, unique_by: :sku)
result.rows.size # => number actually inserted
# Update on conflict instead of skipping
Product.upsert_all(
rows,
unique_by: :sku,
update_only: [:name, :price_cents]
)
# Batch large imports
rows.each_slice(1_000) do |batch|
Product.insert_all(batch, unique_by: :sku)
end
# Get the inserted ids back (PostgreSQL)
Product.insert_all(rows, unique_by: :sku, returning: %w[id sku])
Check viewARU - Brand Newsletter!
Newsletter to DEVs by DEVs - boost your Personal Brand & career! 🚀
