Bulk Upsert with insert_all + Unique Index

9225 views

                  class AddUniqueIndexToExternalProfiles < ActiveRecord::Migration[6.1]
  def change
    add_index :external_profiles, %i[provider uid], unique: true
  end
end

                  class ExternalProfiles::Upsert
  def initialize(provider:, payloads:)
    @provider = provider
    @payloads = payloads
  end

  def call
    now = Time.current
    rows = @payloads.map do |p|
      {
        provider: @provider,
        uid: p.fetch(:uid),
        display_name: p[:display_name],
        raw: p,
        created_at: now,
        updated_at: now
      }
    end

    ExternalProfile.upsert_all(rows, unique_by: %i[provider uid])
  end
end

I stopped doing row-by-row imports once they started hammering the DB. In Migration (unique index), I added a real uniqueness guarantee on provider + uid, because bulk writes only stay correct if the database can enforce constraints. Then, in Bulk upsert, I build a rows array from the inbound payload, set created_at and updated_at explicitly using a single now, and hand everything to ExternalProfile.upsert_all with unique_by: %i[provider uid]. This gives me a single statement that inserts or updates deterministically, even when the same payload is replayed. The important detail is that the app code stays simple and the database owns truth, instead of sprinkling find_or_initialize_by all over the codebase.

Sarah Chen

More from Sarah Chen

Bulk Upsert with insert_all + Unique Index

0 Comments

More from Sarah Chen