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 ($1, $2, $3, $4) on conflict ("instanceId") do update set "nodeId" = $5, "acquiredAt" = $6, "expiresAt" = $7

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 ($1, $2, $3, $4) on conflict ("instanceId") do update set "nodeId" = $5, "acquiredAt" = $6, "expiresAt" = $7 where "upsert_locks"."expiresAt" < $8

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 ($1, $2, $3, $4) on conflict ("instanceId") do update set "nodeId" = $5, "acquiredAt" = $6, "expiresAt" = $7 where "upsert_locks"."expiresAt" < $8 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 ($1, $2, $3, $4) 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 ($1, $2, $3, $4) on conflict ("instanceId") do update set "nodeId" = $5, "acquiredAt" = $6, "expiresAt" = $7 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 ($1, $2, $3, $4) on conflict ("instanceId") do update set "nodeId" = $5, "acquiredAt" = $6, "expiresAt" = $7 where "upsert_locks"."expiresAt" < $8 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 ($1, $2, $3, $4) on conflict ("tenantId", "sku") do update set "name" = $5, "quantity" = $6

Full Atomic Lock Acquisition Example

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

(
  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
  .provideLayer(DocsTransactor.layer)

Right((Some(AtomicLock(lock-1,node-A,2026-10-14T21:42:15.291434Z,2026-10-14T21:43:15.291434Z)),Some(AtomicLock(lock-1,node-A,2026-10-14T21:42:15.291434Z,2026-10-14T21:44:15.291434Z))))

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