Data Manipulation Layer (DML)

The DML layer provides CRUD operations for your tables.

Basic CRUD Operations

sql"SELECT * FROM $tasks WHERE ${tasks.done} = ${false}".show

SELECT * FROM dml_tasks WHERE done = false

sql"SELECT * FROM $tasks WHERE ${tasks.title} LIKE ${"Learn%"} AND ${tasks.done} = ${false}".show

SELECT * FROM dml_tasks WHERE title LIKE 'Learn%' AND done = false

Running DML Operations

xa.run(for
  _    <- ddl.createTable[Task](ifNotExists = true)
  _    <- dml.insert(Task(-1, "Task 1", false))
  _    <- dml.insert(Task(-1, "Task 2", false))
  _    <- dml.insert(Task(-1, "Task 3", true))
  all  <- sql"SELECT * FROM $tasks".query[Task]
  done <- sql"SELECT * FROM $tasks WHERE ${tasks.done} = ${true}".query[Task]
yield (all, done))
  .either

Right((Chunk(Task(1,Task 1,false),Task(2,Task 2,false),Task(3,Task 3,true)),Chunk(Task(3,Task 3,true))))

Insert with RETURNING

For databases that support it (PostgreSQL, SQLite), get the inserted row back:

xa.run(for
  _      <- ddl.createTable[Task](ifNotExists = true)
  result <- dml.insertReturning(Task(-1, "New Task", false))
yield result)
  .either

Right(Task(4,New Task,false))

Custom Queries

Use the sql interpolator for any query:

xa.run(for
  _      <- ddl.createTable[Task](ifNotExists = true)
  _      <- dml.insert(Task(-1, "Alpha", false))
  _      <- dml.insert(Task(-1, "Beta", true))
  sorted <- sql"SELECT * FROM $tasks ORDER BY ${tasks.title}".query[Task]
yield sorted)
  .either

Right(Chunk(Task(5,Alpha,false),Task(6,Beta,true),Task(4,New Task,false),Task(1,Task 1,false),Task(2,Task 2,false),Task(3,Task 3,true)))

Update Operations

Update records by primary key or with custom conditions:

xa.run(
  for
    _        <- ddl.createTable[Item](ifNotExists = true)
    inserted <- dml.insertReturning(Item(-1, "Widget", 10))

    // Update by primary key
    _ <- dml.update(inserted.copy(quantity = 15))

    // Update with RETURNING (get the updated row back)
    updated <- dml.updateReturning(inserted.copy(name = "Super Widget", quantity = 20))

    // Verify the update
    result <- sql"SELECT * FROM $items WHERE ${items.id} = ${inserted.id}".queryOne[Item]
  yield (updated, result)
).either

Right((Item(1,Super Widget,20),Some(Item(1,Super Widget,20))))

Update multiple rows with a WHERE clause:

xa.run(for
  _ <- ddl.createTable[Item](ifNotExists = true)
  _ <- dml.insert(Item(-1, "Gadget A", 5))
  _ <- dml.insert(Item(-1, "Gadget B", 3))

  // Update all items with quantity < 10
  rowsUpdated <- dml.updateWhere(
    Item(-1, "Low Stock Item", 0), // Values to set (id ignored)
    sql"${items.quantity} < 10",
  )

  all <- sql"SELECT * FROM $items".query[Item]
yield (rowsUpdated, all))
  .either

Right((2,Chunk(Item(1,Super Widget,20),Item(2,Low Stock Item,0),Item(3,Low Stock Item,0))))

Delete Operations

Delete records by primary key or with custom conditions:

xa.run(
  for
    _      <- ddl.createTable[LogEntry](ifNotExists = true)
    entry1 <- dml.insertReturning(LogEntry(-1, "INFO", "Application started"))
    entry2 <- dml.insertReturning(LogEntry(-1, "DEBUG", "Processing request"))
    _      <- dml.insertReturning(LogEntry(-1, "ERROR", "Something failed"))

    // Delete by primary key
    _ <- dml.delete(entry2)

    // Delete with RETURNING (get the deleted row back)
    deleted <- dml.deleteReturning(entry1)

    remaining <- sql"SELECT * FROM $logs".query[LogEntry]
  yield (deleted, remaining)
).either

Right((LogEntry(1,INFO,Application started),Chunk(LogEntry(3,ERROR,Something failed))))

Delete multiple rows with a WHERE clause:

xa.run(
  for
    _ <- ddl.createTable[LogEntry](ifNotExists = true)
    _ <- dml.insert(LogEntry(-1, "DEBUG", "Debug 1"))
    _ <- dml.insert(LogEntry(-1, "DEBUG", "Debug 2"))
    _ <- dml.insert(LogEntry(-1, "INFO", "Important info"))

    // Delete all DEBUG entries
    rowsDeleted <- dml.deleteWhere[LogEntry](sql"${logs.level} = ${"DEBUG"}")

    // Delete with WHERE and RETURNING (get all deleted rows)
    deletedEntries <- dml.deleteWhereReturning[LogEntry](sql"${logs.level} = ${"ERROR"}")

    remaining <- sql"SELECT * FROM $logs".query[LogEntry]
  yield (rowsDeleted, deletedEntries, remaining)
).either

Right((2,Chunk(LogEntry(3,ERROR,Something failed)),Chunk(LogEntry(6,INFO,Important info))))

Type-Safe Mutation Builders

Saferis provides type-safe builders for INSERT, UPDATE, and DELETE operations. These builders use Scala 3 macros to extract column names at compile time.

Insert Builder

Build INSERT statements with type-safe column selectors:

Insert[BuilderUser]
  .value(_.name, "Alice")
  .value(_.email, "alice@example.com")
  .value(_.age, 30)
  .build
  .sql

insert into dml_builder_users (name, email, age) values (?, ?, ?)

Insert[BuilderUser]
  .value(_.name, "Bob")
  .value(_.email, "bob@example.com")
  .value(_.age, 25)
  .returning
  .sql

insert into dml_builder_users (name, email, age) values (?, ?, ?) returning *

Update Builder (Builder/Ready Pattern)

The Update builder uses a Builder/Ready pattern to prevent accidental updates of all rows. You must either:

Call .where(...) to specify which rows to update
Call .all to explicitly update all rows

xa.run(for
  _     <- ddl.createTable[BuilderUser](ifNotExists = true)
  _     <- dml.insert(BuilderUser(-1, "Alice", "alice@example.com", 30))
  _     <- dml.insert(BuilderUser(-1, "Bob", "bob@example.com", 25))
  users <- sql"SELECT * FROM ${Table[BuilderUser]}".query[BuilderUser]
yield users)
  .either

Right(Chunk(BuilderUser(1,Alice,alice@example.com,30),BuilderUser(2,Bob,bob@example.com,25)))

Update[BuilderUser]
  .set(_.name, "Alice Updated")
  .set(_.age, 31)
  .where(_.id)
  .eq(1)
  .build
  .sql

update dml_builder_users set name = ?, age = ? where dml_builder_users.id = ?

Update[BuilderUser]
  .set(_.email, "new@example.com")
  .where(_.name)
  .eq("Bob")
  .where(_.age)
  .gt(20)
  .build
  .sql

update dml_builder_users set email = ? where dml_builder_users.name = ? and dml_builder_users.age > ?

Update[BuilderUser]
  .set(_.age, 35)
  .where(_.id)
  .eq(1)
  .returning
  .sql

update dml_builder_users set age = ? where dml_builder_users.id = ? returning *

Update[BuilderUser]
  .set(_.age, 0)
  .all // Required - prevents accidental "UPDATE ... SET" without WHERE
  .build
  .sql

update dml_builder_users set age = ?

Delete Builder (Builder/Ready Pattern)

Like Update, the Delete builder requires either .where(...) or .all:

Delete[BuilderUser]
  .where(_.id)
  .eq(1)
  .build
  .sql

delete from dml_builder_users where dml_builder_users.id = ?

Delete[BuilderUser]
  .where(_.age)
  .lt(18)
  .where(_.name)
  .neq("Admin")
  .build
  .sql

delete from dml_builder_users where dml_builder_users.age < ? and dml_builder_users.name <> ?

Delete[BuilderUser]
  .where(_.email)
  .eq("old@example.com")
  .returning
  .sql

delete from dml_builder_users where dml_builder_users.email = ? returning *

Delete[BuilderUser].all // Required - prevents accidental "DELETE FROM ..."
  .build.sql

delete from dml_builder_users

Available WHERE Operators

All mutation builders support these operators in WHERE clauses:

Method	SQL	Description
`.eq(value)`	`= ?`	Equality
`.neq(value)`	`<> ?`	Not equal
`.lt(value)`	`< ?`	Less than
`.lte(value)`	`<= ?`	Less than or equal
`.gt(value)`	`> ?`	Greater than
`.gte(value)`	`>= ?`	Greater than or equal
`.isNull()`	`is null`	Null check
`.isNotNull()`	`is not null`	Non-null check

You can also use raw SqlFragment for complex conditions:

{
  // Using SqlFragment for complex WHERE
  val users = Table[BuilderUser]
  Update[BuilderUser]
    .set(_.age, 25)
    .where(sql"${users.name} LIKE ${"A%"}")
    .build
    .sql
}

update dml_builder_users set age = ? where name LIKE ?

Complex WHERE with OR and Grouping

Use andWhere with a lambda for complex conditions with OR logic:

{
  // Query for unclaimed or expired claims
  val now = java.time.Instant.now()
  Update[ClaimTask]
    .set(_.claimedBy, Some("worker-1"))
    .where(_.deadline)
    .lte(now)
    .andWhere(w => w(_.claimedBy).isNull.or(_.claimedUntil).lt(Some(now)))
    .build
    .sql
}

update dml_claim_tasks set claimedBy = ? where dml_claim_tasks.deadline <= ? and (dml_claim_tasks.claimedBy is null or dml_claim_tasks.claimedUntil < ?)

This generates: update ... where deadline <= ? and (claimed_by is null or claimed_until < ?)

The andWhere lambda provides a builder that supports:

w(_.column) - Start a condition on a column
.isNull / .isNotNull - Null checks
.eq(value) / .lt(value) / etc. - Comparisons
.or(_.column) - Chain with OR
.and(_.column) - Chain with AND

Delete also supports andWhere:

{
  val now2 = java.time.Instant.now()
  Delete[ClaimTask]
    .where(_.deadline)
    .lt(now2)
    .andWhere(w => w(_.claimedBy).isNotNull.or(_.claimedUntil).lt(Some(now2)))
    .build
    .sql
}

delete from dml_claim_tasks where dml_claim_tasks.deadline < ? and (dml_claim_tasks.claimedBy is not null or dml_claim_tasks.claimedUntil < ?)

Type-Safe UPDATE with RETURNING

Return updated rows atomically using returningAs:

{
  // returningAs provides type-safe query execution
  val newExpiry = java.time.Instant.now().plusSeconds(60)
  Update[LockRow]
    .set(_.expiresAt, newExpiry)
    .where(_.instanceId)
    .eq("instance-1")
    .where(_.nodeId)
    .eq("node-1")
    .returningAs
    .build
    .sql
}

update dml_lock_rows set expiresAt = ? where dml_lock_rows.instanceId = ? and dml_lock_rows.nodeId = ? returning *

The returningAs method:

Returns ReturningQuery[A] with type-safe query and queryOne methods
Only compiles when the dialect supports RETURNING (PostgreSQL, SQLite)
Uses capability constraint: requires Dialect & ReturningSupport

{
  // Execute and get the updated row
  val result: ScopedQuery[Option[LockRow]] = Update[LockRow]
    .set(_.expiresAt, java.time.Instant.now())
    .where(_.instanceId)
    .eq("id")
    .returningAs
    .queryOne // Returns ScopedQuery[Option[LockRow]]
  result.getClass.getSimpleName
}

FoldZIO

Delete also supports returningAs:

Delete[LockRow]
  .where(_.instanceId)
  .eq("instance-1")
  .returningAs
  .build
  .sql

delete from dml_lock_rows where dml_lock_rows.instanceId = ? returning *

Data Manipulation Layer (DML)

#Basic CRUD Operations

#Running DML Operations

#Insert with RETURNING

#Custom Queries

#Update Operations

#Delete Operations

#Type-Safe Mutation Builders

#Insert Builder

#Update Builder (Builder/Ready Pattern)

#Delete Builder (Builder/Ready Pattern)

#Available WHERE Operators

#Complex WHERE with OR and Grouping

#Type-Safe UPDATE with RETURNING