Conditional Upsert DSL

Saferis provides a type-safe UPSERT (INSERT ... ON CONFLICT) builder for PostgreSQL.

Basic Upsert

{
  // Basic upsert - update all non-key columns on conflict
  val now  = java.time.Instant.now()
  val lock = UpsertLock("instance-1", "node-1", now, now.plusSeconds(60))

  saferis
    .Upsert[UpsertLock]
    .values(lock)
    .onConflict(_.instanceId)
    .doUpdateAll
    .build
    .sql
}

insert into upsert_locks (instanceId, nodeId, acquiredAt, expiresAt) values (?, ?, ?, ?) on conflict (instanceId) do update set nodeId = ?, acquiredAt = ?, expiresAt = ?

Conditional Upsert with WHERE

Add conditions to control when the update happens:

{
  val now  = java.time.Instant.now()
  val lock = UpsertLock("instance-1", "node-1", now, now.plusSeconds(60))

  // Only update if the existing row has expired
  saferis
    .Upsert[UpsertLock]
    .values(lock)
    .onConflict(_.instanceId)
    .doUpdateAll
    .where(_.expiresAt)
    .lt(now)
    .build
    .sql
}

insert into upsert_locks (instanceId, nodeId, acquiredAt, expiresAt) values (?, ?, ?, ?) on conflict (instanceId) do update set nodeId = ?, acquiredAt = ?, expiresAt = ? where upsert_locks.expiresAt < ?

This generates: INSERT INTO ... ON CONFLICT (instance_id) DO UPDATE SET ... WHERE upsert_locks.expires_at < ?

Reference EXCLUDED Pseudo-Table

Use .eqExcluded to compare with the value being inserted:

{
  val now  = java.time.Instant.now()
  val lock = UpsertLock("instance-1", "node-1", now, now.plusSeconds(60))

  // Update only if we own the lock (same nodeId) OR it has expired
  saferis
    .Upsert[UpsertLock]
    .values(lock)
    .onConflict(_.instanceId)
    .doUpdateAll
    .where(_.expiresAt)
    .lt(now)
    .or(_.nodeId)
    .eqExcluded
    .build
    .sql
}

insert into upsert_locks (instanceId, nodeId, acquiredAt, expiresAt) values (?, ?, ?, ?) on conflict (instanceId) do update set nodeId = ?, acquiredAt = ?, expiresAt = ? where upsert_locks.expiresAt < ? or upsert_locks.nodeId = excluded.nodeId

The .eqExcluded generates table.column = excluded.column, referencing the value from the INSERT.

Upsert with DO NOTHING

Skip the update entirely on conflict:

{
  val now  = java.time.Instant.now()
  val lock = UpsertLock("instance-1", "node-1", now, now.plusSeconds(60))

  // Insert only if no conflict
  saferis
    .Upsert[UpsertLock]
    .values(lock)
    .onConflict(_.instanceId)
    .doNothing
    .build
    .sql
}

insert into upsert_locks (instanceId, nodeId, acquiredAt, expiresAt) values (?, ?, ?, ?) on conflict (instanceId) do nothing

Upsert with RETURNING

Get the resulting row back:

{
  val now  = java.time.Instant.now()
  val lock = UpsertLock("instance-1", "node-1", now, now.plusSeconds(60))

  // Upsert with RETURNING - returns ReturningQuery which wraps SqlFragment
  saferis
    .Upsert[UpsertLock]
    .values(lock)
    .onConflict(_.instanceId)
    .doUpdateAll
    .returning
    .build
    .sql
}

insert into upsert_locks (instanceId, nodeId, acquiredAt, expiresAt) values (?, ?, ?, ?) on conflict (instanceId) do update set nodeId = ?, acquiredAt = ?, expiresAt = ? returning *

{
  val now  = java.time.Instant.now()
  val lock = UpsertLock("instance-1", "node-1", now, now.plusSeconds(60))

  // Type-safe returning with WHERE clause
  saferis
    .Upsert[UpsertLock]
    .values(lock)
    .onConflict(_.instanceId)
    .doUpdateAll
    .where(_.expiresAt)
    .lt(now)
    .returning
    .build
    .sql
}

insert into upsert_locks (instanceId, nodeId, acquiredAt, expiresAt) values (?, ?, ?, ?) on conflict (instanceId) do update set nodeId = ?, acquiredAt = ?, expiresAt = ? where upsert_locks.expiresAt < ? returning *

Compound Conflict Columns

Specify multiple columns for the conflict target:

{
  // Conflict on compound key
  val item = UpsertItem("tenant-1", "SKU-001", "Widget", 10)

  saferis
    .Upsert[UpsertItem]
    .values(item)
    .onConflict(_.tenantId)
    .and(_.sku)
    .doUpdateAll
    .build
    .sql
}

insert into upsert_items (tenantId, sku, name, quantity) values (?, ?, ?, ?) on conflict (tenantId, sku) do update set name = ?, quantity = ?

Full Atomic Lock Acquisition Example

Here's a complete example of atomic lock acquisition, run against the database:

xa.run(
  for
    _ <- ddl.createTable[AtomicLock](ifNotExists = true)
    now  = java.time.Instant.now()
    lock = AtomicLock("lock-1", "node-A", now, now.plusSeconds(60))

    // First acquisition - should succeed
    result1 <- saferis
      .Upsert[AtomicLock]
      .values(lock)
      .onConflict(_.instanceId)
      .doUpdateAll
      .where(_.expiresAt)
      .lt(now) // Only if expired
      .or(_.nodeId)
      .eqExcluded // Or we own it
      .returning
      .queryOne

    // Second acquisition by same node - should succeed (we own it)
    result2 <- saferis
      .Upsert[AtomicLock]
      .values(lock.copy(expiresAt = now.plusSeconds(120)))
      .onConflict(_.instanceId)
      .doUpdateAll
      .where(_.expiresAt)
      .lt(now)
      .or(_.nodeId)
      .eqExcluded
      .returning
      .queryOne
  yield (result1, result2)
).either

Right((Some(AtomicLock(lock-1,node-A,2026-07-30T00:59:21.622328Z,2026-07-30T01:00:21.622328Z)),Some(AtomicLock(lock-1,node-A,2026-07-30T00:59:21.622328Z,2026-07-30T01:01:21.622328Z))))

Capability Requirements

The Upsert DSL requires UpsertSupport:

PostgreSQL: Full support
MySQL: Not supported (use ON DUPLICATE KEY UPDATE syntax via raw SQL)
SQLite: Not supported

For returningAs, also requires ReturningSupport:

PostgreSQL: Full support
SQLite: Supported
MySQL: Not supported

See Type-Safe Capabilities for how these constraints are enforced at compile time.

Conditional Upsert DSL

#Basic Upsert

#Conditional Upsert with WHERE

#Reference EXCLUDED Pseudo-Table

#Upsert with DO NOTHING

#Upsert with RETURNING

#Compound Conflict Columns

#Full Atomic Lock Acquisition Example

#Capability Requirements