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:

stateDiagram-v2
  [*] --> Builder
  Builder --> Ready: where
  Builder --> Ready: limit
  Builder --> Ready: seek
  Builder --> Ready: all
  Ready --> [*]: execute

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" = $1,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" = $1

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" > $1 order by "name" asc limit 10 offset 20

Columns the table names

An unannotated table renders select *. That is the right statement when the case class maps the table.

@projectColumns is for a case class that names a few columns of a wider table. The builder lists those columns, in field order, qualified by the table alias. A joined table without the annotation stays alias.*. An explicit .select still wins, and it stays a bare column list.

The annotation is only a select list. Insert, update, and schema verify do not look at it. A sql"select *" you wrote is left as you wrote it. The driver still reads only the columns the row type names, so an extra result column is never decoded.

Query[RunId].all.build.sql

select "qb_run_ids_ref_1"."id", "qb_run_ids_ref_1"."startedAt" from "qb_run_ids" as "qb_run_ids_ref_1"

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" = $1

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" > $1

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" = $1

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" = $1

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" > $1

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" = $1 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" > $1 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" < $1 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:

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

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

Query Builder

#Query Safety (Builder/Ready Pattern)

#Basic Queries

#Columns the table names

#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