Skip to content

SQL Upsert Destination

Thibaut Barrère edited this page Feb 10, 2020 · 7 revisions

Kiba Pro SQLUpsert destination provides a way to easily "insert or update" rows, one by one (no batching).

Currently tested against: PostgreSQL 9.5+ (requires ON CONFLICT UPDATE), Ruby 2.4-2.7.

Requirements: make sure to add those to your Gemfile:

  • sequel gem
  • pg gem

Typical use

Here we will insert products if their SKU isn't present yet and will update them otherwise:

require 'kiba-pro/destinations/sql_upsert'

Sequel.connect(connection_config) do |db| do
    # SNIP
    destination Kiba::Pro::Destination::SQLUpsert,
      database: db,
      table: :products,
      unique_key: :sku

Update data vs Insert data

To provide different data in the case the record is updated (vs inserted), use the update_row_pre_processor parameter:

require 'active_support/core_ext/hash/except'

destination Kiba::Pro::Destination::SQLUpsert,
  update_row_pre_processor: -> (row) { row.except(:created_at) }