Query Builder

Saferis provides a unified, type-safe Query builder for constructing SQL queries. It supports single-table queries, multi-table joins (up to 5 tables), WHERE clauses, pagination, and subqueries - all with compile-time type safety.

Query Safety (Builder/Ready Pattern)

To prevent accidental unbounded queries that could fetch millions of rows, Saferis uses a Builder/Ready pattern. A query must have at least one safety constraint before it can be executed:

Safety Constraint	Description
`.where(...)`	Filter results with a WHERE clause
`.limit(n)`	Limit the number of rows returned
`.seekAfter(...)` / `.seekBefore(...)`	Cursor-based pagination
`.all`	Explicit opt-in to fetch all rows

{
  // These compile - they have safety constraints:
  val withWhere = Query[SafetyUser].where(_.name).eq("Alice").build.sql // Has WHERE
  val withLimit = Query[SafetyUser].limit(100).build.sql                // Has LIMIT
  val withAll   = Query[SafetyUser].all.build.sql                       // Explicit opt-in
  (withWhere, withLimit, withAll)
}

(select * from qb_safety_users as qb_safety_users_ref_1 where qb_safety_users_ref_1.name = ?,select * from qb_safety_users as qb_safety_users_ref_1 limit 100,select * from qb_safety_users as qb_safety_users_ref_1)

A query with no safety constraint cannot be built — .build simply doesn't exist on a bare Builder, so the snippet below does not compile (the compiler error is shown beneath it):

import saferis.*
import saferis.postgres.given

@tableName("qb_safety_users_fail")
case class SafetyUser(@generated @key id: Int, name: String) derives Table

// No WHERE / LIMIT / .all — .build is not available on a Builder.
Query[SafetyUser].build

value build is not a member of saferis.Query1Builder[SafetyUser]
  Query[SafetyUser].build

The pattern ensures you consciously choose to query all rows with .all rather than accidentally doing so.

Basic Queries

Start with Query[A] for single-table queries:

Query[QueryUser]
  .where(_.name)
  .eq("Alice")
  .build
  .sql

select * from qb_query_users as qb_query_users_ref_1 where qb_query_users_ref_1.name = ?

Query[QueryUser]
  .where(_.age)
  .gt(18)
  .orderBy(users.name.asc)
  .limit(10)
  .offset(20)
  .build
  .sql

select * from qb_query_users as qb_query_users_ref_1 where qb_query_users_ref_1.age > ? order by name asc limit 10 offset 20

Type-Safe WHERE Clauses

Use selector syntax for type-safe column references:

Query[QueryUser].where(_.name).eq("Alice").build.sql

select * from qb_query_users as qb_query_users_ref_1 where qb_query_users_ref_1.name = ?

Query[QueryUser].where(_.age).gt(21).build.sql

select * from qb_query_users as qb_query_users_ref_1 where qb_query_users_ref_1.age > ?

Query[QueryUser].where(_.email).isNotNull().build.sql

select * from qb_query_users as qb_query_users_ref_1 where qb_query_users_ref_1.email is not null

You can also use raw SqlFragment for complex conditions:

Query[QueryUser]
  .where(sql"${users.age} BETWEEN 18 AND 65")
  .build
  .sql

select * from qb_query_users as qb_query_users_ref_1 where age BETWEEN 18 AND 65

Joins

Chain joins with the fluent API. The on() method uses type-safe selectors:

Query[JoinUser]
  .innerJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .all
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 inner join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId

Query[JoinUser]
  .leftJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .all
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 left join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId

Query[JoinUser]
  .rightJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .all
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 right join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId

Query[JoinUser]
  .fullJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .all
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 full join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId

Finalizing Joins with `.endJoin`

After specifying the ON clause, you can either:

Use convenience methods like .where(), .limit(), .all directly on the join chain
Call .endJoin explicitly to finalize the join and return to the query builder

Query[JoinUser]
  .innerJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .where(_.name)
  .eq("Alice") // Convenience method
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 inner join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId where qb_join_users_ref_1.name = ?

Query[JoinUser]
  .innerJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .endJoin // Explicitly finalize join
  .orderBy(Table[JoinUser].name.asc)
  .all
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 inner join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId order by name asc

The .endJoin method is useful when you want to add operations like .orderBy() that aren't available as convenience methods on the join chain.

Multi-Table Joins

Chain up to 5 tables. Use onPrev() to reference the previously joined table:

Query[JoinUser]
  .innerJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .innerJoin[JoinItem]
  .onPrev(_.id)
  .eq(_.orderId)
  .all
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 inner join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId inner join qb_join_items as qb_join_items_ref_1 on qb_join_orders_ref_1.id = qb_join_items_ref_1.orderId

WHERE on Joined Queries

After joining, use where() for the first table or whereFrom() for joined tables:

Query[JoinUser]
  .innerJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .where(_.name)
  .eq("Alice")
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 inner join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId where qb_join_users_ref_1.name = ?

Query[JoinUser]
  .innerJoin[JoinOrder]
  .on(_.id)
  .eq(_.userId)
  .whereFrom(_.amount)
  .gt(BigDecimal(100))
  .build
  .sql

select * from qb_join_users as qb_join_users_ref_1 inner join qb_join_orders as qb_join_orders_ref_1 on qb_join_users_ref_1.id = qb_join_orders_ref_1.userId where qb_join_orders_ref_1.amount > ?

ON Clause Operators

All comparison operators are available in the ON clause:

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

Pagination

Offset-Based Pagination

Traditional LIMIT/OFFSET pagination:

Query[Article]
  .where(_.published)
  .eq(true)
  .orderBy(articles.views.desc)
  .limit(10)
  .offset(20)
  .build
  .sql

select * from qb_page_articles as qb_page_articles_ref_1 where qb_page_articles_ref_1.published = ? order by views desc limit 10 offset 20

Cursor/Seek Pagination

More efficient for large datasets - uses indexed lookups:

Query[Article]
  .seekAfter(articles.id, 100L)
  .limit(10)
  .build
  .sql

select * from qb_page_articles as qb_page_articles_ref_1 where id > ? order by id asc limit 10

Query[Article]
  .seekBefore(articles.id, 50L)
  .limit(10)
  .build
  .sql

select * from qb_page_articles as qb_page_articles_ref_1 where id < ? order by id desc limit 10

Sorting

Use column extensions for concise sorting:

Query[Article]
  .orderBy(articles.views.desc)
  .orderBy(articles.title.asc)
  .all
  .build
  .sql

select * from qb_page_articles as qb_page_articles_ref_1 order by views desc, title asc

Control NULL ordering:

Query[Article]
  .orderBy(articles.views.descNullsLast)
  .all
  .build
  .sql

select * from qb_page_articles as qb_page_articles_ref_1 order by views desc nulls last

Available sorting extensions:

.asc / .desc - basic ordering
.ascNullsFirst / .ascNullsLast
.descNullsFirst / .descNullsLast

Executing Queries

Use .query[R] to execute and decode results:

xa.run(
  for
    _      <- ddl.createTable[ExecUser](ifNotExists = true)
    _      <- ddl.createTable[ExecOrder](ifNotExists = true)
    _      <- dml.insert(ExecUser(-1, "Alice"))
    _      <- dml.insert(ExecUser(-1, "Bob"))
    _      <- dml.insert(ExecOrder(-1, 1, BigDecimal(100)))
    _      <- dml.insert(ExecOrder(-1, 1, BigDecimal(200)))
    result <- Query[ExecUser]
      .innerJoin[ExecOrder]
      .on(_.id)
      .eq(_.userId)
      .where(_.name)
      .eq("Alice")
      .limit(10)
      .query[ExecUser]
  yield result
).either

Right(Chunk(ExecUser(1,Alice),ExecUser(1,Alice)))

Query Builder

#Query Safety (Builder/Ready Pattern)

#Basic Queries

#Type-Safe WHERE Clauses

#Joins

#Finalizing Joins with `.endJoin`

#Multi-Table Joins

#WHERE on Joined Queries

#ON Clause Operators

#Pagination

#Offset-Based Pagination

#Cursor/Seek Pagination

#Sorting

#Executing Queries