upsert_example.rb

#!/usr/bin/env ruby

require 'rom-sql'
require 'rom/sql/commands/postgres'

conn = Sequel.connect('postgres://localhost/upsert_example')

conn.drop_table?(:quotes)

conn.create_table :quotes do
  primary_key :id
  String :quote, unique: true, null: false
  Integer :likes, default: 1
end

conf = ROM::Configuration.new(:sql, conn)
conf.relation(:quotes)

class UpsertQuote < ROM::SQL::Commands::Postgres::Upsert
  relation :quotes
  register_as :create_or_update
  result :one

  conflict_target :quote
  update_statement likes: Sequel.+(:quotes__likes, 1)
end

conf.register_command(UpsertQuote)

rom = ROM.container(conf)

upsert_quote = rom.command(:quotes).create_or_update

dhh_said = %q[Programmers who haven’t learned to wield sharp knifes just aren’t going to make meringues yet.]

upsert_quote.call(quote: dhh_said) # {:id => 1, :quote => ..., :likes => 1}
upsert_quote.call(quote: dhh_said) # {:id => 1, :quote => ..., :likes => 2}