ActiveRecord upsert example

active_record_upsert_example.rb

# configure activerecord (in-memory sqlite db to keep it simple)
require 'active_record'
ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'

# helper method for asserting expectations
def check!(expected, operator = :==, actual)
  equal = expected.public_send(operator, actual) and return
  raise RuntimeError, <<~MESSAGE, caller
    test:     #{expected.inspect} #{operator} #{actual.inspect}
    expected: true
    actual:   #{equal.inspect}
  MESSAGE
end

# schema
ActiveRecord::Schema.define do
  self.verbose = false
  create_table :whatevers do |t|
    t.string :a
    t.string :b
    t.string :c
    t.string :d
    t.timestamps
    t.index [:a, :b], unique: true # in practice, these are probably foreign keys
  end
end

# models
Whatever = Class.new ActiveRecord::Base

# turn logging on
require 'logger'
ActiveRecord::Base.logger = Logger.new $stdout
ActiveSupport::LogSubscriber.colorize_logging = false

# ----- Tests -----

# helper method to make it clear what differs between invocations + hide implementation distractions
# `unique_by` tells it which columns we'll use to decide whether or not a record already exists
# `record_timestamps` tells it to set `created_at`/`updated_at` when inserting and `updated_at` when updating.
def self.upsert(**attrs)
  Whatever.upsert attrs, unique_by: %i[a b], record_timestamps: true
  Whatever.last # have to query it b/c the sqlite adapter doesn't support the returning clause
end

# `upsert` inserts a record
check! 0, Whatever.count
wtvr1 = upsert a: 'omg', b: 'wtf', c: 'bbq', d: 'lol'
check! 1, Whatever.count

# the values are what you would expect
check! 'omg',            wtvr1.a
check! 'wtf',            wtvr1.b
check! 'bbq',            wtvr1.c
check! 'lol',            wtvr1.d
check! wtvr1.created_at, wtvr1.updated_at

# `upsert` updates that record
check! 1, Whatever.count
wtvr2 = upsert a: 'omg', b: 'wtf', c: 'NEW BBQ', d: 'NEW LOL'
check! 1, Whatever.count

# some values should not have changed
check! wtvr1.id,         wtvr2.id
check! wtvr1.a,          wtvr2.a
check! wtvr1.b,          wtvr2.b
check! wtvr1.created_at, wtvr2.created_at

# others should have changed
check! 'NEW BBQ',            wtvr2.c
check! 'NEW LOL',            wtvr2.d
check! wtvr1.updated_at, :<, wtvr2.updated_at

# >> D, [2022-05-26T17:27:45.098947 #45661] DEBUG -- :   Whatever Count (0.0ms)  SELECT COUNT(*) FROM "whatevers"
# >> D, [2022-05-26T17:27:45.099084 #45661] DEBUG -- :    (0.0ms)  SELECT sqlite_version(*)
# >> D, [2022-05-26T17:27:45.099683 #45661] DEBUG -- :   Whatever Upsert (0.1ms)  INSERT INTO "whatevers" ("a","b","c","d","created_at","updated_at") VALUES ('omg', 'wtf', 'bbq', 'lol', STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'), STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')) ON CONFLICT ("a","b") DO UPDATE SET updated_at=(CASE WHEN ("c" IS excluded."c" AND "d" IS excluded."d") THEN "whatevers".updated_at ELSE STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') END),"c"=excluded."c","d"=excluded."d"
# >> D, [2022-05-26T17:27:45.100021 #45661] DEBUG -- :   Whatever Load (0.0ms)  SELECT "whatevers".* FROM "whatevers" ORDER BY "whatevers"."id" DESC LIMIT ?  [["LIMIT", 1]]
# >> D, [2022-05-26T17:27:45.101880 #45661] DEBUG -- :   Whatever Count (0.0ms)  SELECT COUNT(*) FROM "whatevers"
# >> D, [2022-05-26T17:27:45.102056 #45661] DEBUG -- :   Whatever Count (0.0ms)  SELECT COUNT(*) FROM "whatevers"
# >> D, [2022-05-26T17:27:45.102286 #45661] DEBUG -- :   Whatever Upsert (0.1ms)  INSERT INTO "whatevers" ("a","b","c","d","created_at","updated_at") VALUES ('omg', 'wtf', 'NEW BBQ', 'NEW LOL', STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'), STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')) ON CONFLICT ("a","b") DO UPDATE SET updated_at=(CASE WHEN ("c" IS excluded."c" AND "d" IS excluded."d") THEN "whatevers".updated_at ELSE STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') END),"c"=excluded."c","d"=excluded."d"
# >> D, [2022-05-26T17:27:45.102458 #45661] DEBUG -- :   Whatever Load (0.0ms)  SELECT "whatevers".* FROM "whatevers" ORDER BY "whatevers"."id" DESC LIMIT ?  [["LIMIT", 1]]
# >> D, [2022-05-26T17:27:45.102620 #45661] DEBUG -- :   Whatever Count (0.0ms)  SELECT COUNT(*) FROM "whatevers"