Queries

A query is an immutable value that describes what to read. Building one runs nothing; a repo runs it.

const q = from(User, "u")
  .joinAssoc("u", "posts", "p")
  .where(({ u, p }) => u.age.gte(18).and(p.published))
  .groupBy(({ u }) => u.id)
  .having(({ p }) => count(p.id).gt(3))
  .orderBy(({ u }) => u.createdAt.desc())
  .limit(10)
  .select(({ u, p }) => ({ id: u.id, email: u.email, posts: count(p.id) }));

const rows = await repo.all(q); // { id: number; email: string; posts: number }[]

The result type comes from the query: the schema's row when there is no select, and the shape of the select when there is one. Nothing is generated; the types follow from the schema classes.

Bindings

Every table in a query has a name, its binding. from(User, "u") names the first one u, and each join adds another. Clause callbacks receive all of them as one object:

from(User, "u")
  .join(Post, "p", ({ u, p }) => p.authorId.eq(u.id))
  .where(({ u, p }) => u.age.gte(18).and(p.published));

A binding has one column per field of its schema (u.email, u.createdAt), each an expression with the field's type. Virtual fields have no column, and associations are not columns: join them (below) to use their columns.

The alias is required.
Using the same alias twice throws QueryError, and is a compile error.
Aliases that start with $ are reserved for tables the library joins itself (the join table of a many-to-many association).
The same schema can be joined twice under two aliases, a self-join:

// Pairs of users with the same name: two bindings of one schema.
from(User, "a").join(User, "b", ({ a, b }) => a.name.eq(b.name).and(a.id.neq(b.id)));

`from`

from(source, alias) starts a query. The source is a schema class, or one of the wrappers described in Advanced queries: subquery(q) or cte(...).

It does not accept a query. To continue a query, call more methods on it.

Clauses

Clause	Notes
`.where(callback)`	Several `where` calls are combined with `AND`.
`.where({ field: value })`	Object form on the first binding. See below.
`.orWhere(callback)`	Combined with `OR` against everything before it. Takes the object form too.
`.join(Schema, alias, on)`	Inner join.
`.leftJoin`, `.rightJoin`, `.fullJoin`	Outer joins.
`.crossJoin(Schema, alias)`	No condition.
`.joinAssoc(from, assoc, alias, on?)`	Inner join through an association.
`.leftJoinAssoc(from, assoc, alias, on?)`	Left join through an association.
`.select(callback)`	What to return. See Select. A query has one.
`.selectMerge(callback)`	Adds keys to an object select.
`.distinct()`	`SELECT DISTINCT`.
`.distinct(callback)`	`DISTINCT ON (...)`, Postgres only.
`.groupBy(callback)`	One expression or an array. Several calls add to the list.
`.having(callback)`	Needs a `groupBy`.
`.orderBy(callback)`	One expression or an array. A bare column sorts ascending. Several calls add to the list.
`.limit(n)`, `.offset(n)`	Bound parameters, so every page size shares one plan. A later call replaces an earlier one.
`.lock(mode, opts?)`	Row locks. See Locks.
`.schema(name)`	The database schema for this query.
`.exclude(clause)`	Removes a clause, such as `"orderBy"` or `"limit"`.
`.reverseOrder()`	Flips every `orderBy`; with none, orders by the primary key, descending.
`.preload(...)`	Loads associations. See Preloading.
`.pipe(fn)`	`q.pipe(f)` is `f(q)`.

`where` and `orWhere`

from(User, "u")
  .where(({ u }) => u.age.gte(18))
  .where(({ u }) => u.role.eq("member"))
  .orWhere(({ u }) => u.role.eq("admin"));
// WHERE (u.age >= $1 AND u.role = $2) OR u.role = $3

orWhere applies to everything before it. For an OR inside one condition, use the or function:

from(User, "u").where(({ u }) => or(u.role.eq("admin"), u.age.gte(65)));

A callback must return a condition (a boolean expression); anything else is a compile error and a QueryError.

Object form

from(User, "u").where({ role: "admin", name: null, id: [1, 2, 3] });
// WHERE u.role = $1 AND u.name IS NULL AND u.id = ANY($2)

A value means equals, null means IS NULL, an array means IN.
The keys are the fields of the first binding; the values are typed by those fields.
An array or JSON field holds arrays itself, so there an array is the value, compared whole, never a list. For anything else on such a column, use the callback form:

from(Post, "p").where({ tags: ["sql", "ts"] }); // WHERE p.tags = $1: the whole array
from(Post, "p").where(({ p }) => p.tags.contains(["sql"])); // the array holds "sql"

undefined throws. A filter that silently disappears, and so matches every row, is a common bug. Add the clause conditionally instead:

let q = from(User, "u");
if (filters.role) q = q.where({ role: filters.role });
if (filters.minAge !== undefined) {
  const minAge = filters.minAge;
  q = q.where(({ u }) => u.age.gte(minAge));
}

Joins and nullability

from(User, "u").join(Post, "p", ({ u, p }) => p.authorId.eq(u.id)); //      INNER JOIN
from(User, "u").leftJoin(Profile, "pr", ({ u, pr }) => pr.userId.eq(u.id)); // LEFT JOIN
from(Post, "p").rightJoin(User, "u", ({ u, p }) => p.authorId.eq(u.id)); //   RIGHT JOIN
from(User, "u").fullJoin(Profile, "pr", ({ u, pr }) => pr.userId.eq(u.id)); // FULL JOIN
from(User, "u").crossJoin(Post, "p"); //                                     CROSS JOIN
from(User, "u").joinAssoc("u", "posts", "p"); //                              through the association
from(User, "u").joinAssoc("u", "posts", "p", ({ p }) => p.published); //      with an extra ON condition

A left join makes the joined binding's columns nullable in the types; right and full joins also make the existing bindings nullable, and the rows of a query with no select (Post | null). Selecting a whole left-joined binding gives Row | null:

const q = from(User, "u")
  .leftJoin(Profile, "pr", ({ u, pr }) => pr.userId.eq(u.id))
  .select(({ u, pr }) => ({ user: u, bio: pr.bio, profile: pr }));
const rows = await repo.all(q); // { user: User; bio: string | null; profile: Profile | null }[]

A right or full join comes before the query's select and preload: both take the rows as they are, and a preload needs rows that exist.

joinAssoc writes the ON condition from the association: its keys, its where, the join table of a many-to-many, and every step of a through chain.

Select

select decides the result type.

q.select(({ u }) => u.email); //                                 string
q.select(({ u }) => u); //                                       User
q.select(({ u, p }) => ({ user: u, title: p.title })); //        { user: User; title: string }
q.select(({ u, p }) => [u.id, count(p.id)] as const); //         [number, number]
q.select(({ u }) => ({ id: u.id, kind: "user" })); //            plain values are returned as they are

An array is typed element by element, with or without as const: [u.id, u.email] gives [number, string]. That holds up to four levels deep (objects and arrays counted together); deeper, add as const.

With no select, a query returns rows of its first binding, with the class's getters and methods. A query has one select; calling it twice throws. selectMerge adds keys to an object select (a key it already has is replaced):

from(Post, "p")
  .select(({ p }) => ({ id: p.id, title: p.title }))
  .selectMerge(({ p }) => ({ views: p.views })); // { id: number; title: string; views: number }

Grouping, ordering and distinct

from(Post, "p")
  .groupBy(({ p }) => p.authorId)
  .having(({ p }) => count(p.id).gt(3))
  .select(({ p }) => ({ authorId: p.authorId, posts: count(p.id) }));

from(User, "u").orderBy(({ u }) => [u.createdAt.desc().nullsLast(), u.id]); // a bare column sorts ascending
from(User, "u")
  .orderBy(({ u }) => u.createdAt)
  .reverseOrder(); //              ORDER BY created_at DESC

from(User, "u").distinct();
from(User, "u")
  .distinct(({ u }) => [u.email])
  .orderBy(({ u }) => [u.email, u.createdAt.desc()]); // DISTINCT ON (email)

Limit and offset

from(User, "u")
  .orderBy(({ u }) => u.id)
  .limit(20)
  .offset(40); // LIMIT $1 OFFSET $2

Both take a whole number of zero or more, or a named parameter.

Locks

q.lock("update");
q.lock("update", { skipLocked: true });
q.lock("share", { noWait: true });
q.lock(fragment`FOR UPDATE OF ${fragment.ident("p")}`);

The modes are "update", "share", "noKeyUpdate" and "keyShare", with at most one of skipLocked and noWait. A lock is never a string of SQL: another lock clause is a fragment. SQLite has a single writer and ignores locks.

Other clauses

q.schema("tenant_1"); // the database schema for this query
q.exclude("orderBy"); // the same query without its orderBy
q.exclude("limit");

Queries are values

Every method returns a new query. A base query can be kept and extended without changing it:

const active = from(User, "u").where(({ u }) => u.deletedAt.isNull());

const admins = active.where({ role: "admin" });
const recent = active.orderBy(({ u }) => u.createdAt.desc()).limit(10);

Callbacks run once, when the clause is added. They must not have side effects.

Reusable pieces

A function from query to query is a reusable scope. QueryWith types it:

const published = <Q extends QueryWith<{ p: typeof Post }>>(q: Q) => q.where(({ p }) => p.published);

const paginate = <Q extends QueryWith>(q: Q, page: number, size = 20) =>
  q.limit(size).offset((page - 1) * size);

from(Post, "p")
  .pipe(published)
  .pipe((q) => paginate(q, 2));

The scope is a compile error on a query that has no binding p for Post. It returns the same query type it was given, so the result type and the named parameters are kept. QueryWith with no argument is any query.

Three limits inside a scope, since it does not know how the query it receives was built:

its bindings may come from a left join, so a select in the scope sees their columns as nullable (p.title is string | null);
its root is unknown, so the object form of where does not compile there; use the callback form;
for the same reason, preload does not compile there.

hasNamedBinding(q, "p", Post) is the runtime check, and narrows the query's type, for code that receives a query it did not build:

if (hasNamedBinding(someQuery, "p", Post)) {
  someQuery.where(({ p }) => p.published);
}

Named parameters

param(name, type) leaves a placeholder that is filled in when the query runs:

const adults = from(User, "u").where(({ u }) => u.age.gte(param("minAge", t.integer())));

await repo.all(adults, { minAge: 18 }); // the params object is required and typed

The query's type collects the named parameters of every clause, and of the callbacks of its preloads, which run with the same values. One name has one type, nullability included (t.integer() and t.integer().nullable() are two types). Using it with another type in a later clause or a preload callback is a compile error where the query runs (its parameter type becomes never, so no value can be given), and a QueryError when the clause is added. Within one expression it is a compile error at once.

Two field types that are the same TypeScript type are told apart only when the clause is added: t.integer() and t.bigint({ mode: "number" }) are both number, so using one name with both compiles, and throws QueryError.

Debugging

q.toString() prints a compact rendering without values:

from(User, "u")
  .where(({ u }) => u.age.gte(18))
  .orderBy(({ u }) => u.id)
  .limit(10)
  .toString();
// "from users as u where u.age >= ? order by u.id limit ?"

repo.toSql(q) returns the SQL and the parameters.
repo.explain(q) asks the database for its plan.
How a query becomes SQL explains the SQL you will see there, and how compiled plans are cached.

Errors

A mistake in a query throws QueryError when the clause is added, not when the query runs. The message names the clause and the binding or field involved.

Good to know

Values are always bound. Every value in a clause, limit and offset included, is a parameter, never SQL text.
One select per query. Use selectMerge to add keys, or exclude("select") first.
undefined in the object form throws instead of matching every row.
Removing a clause that holds a named parameter throws, because the query's type would still ask for the parameter.

Queries ​

Bindings ​

from ​

Clauses ​

where and orWhere ​

Object form ​

Joins and nullability ​

Select ​

Grouping, ordering and distinct ​

Limit and offset ​

Locks ​

Other clauses ​

Queries are values ​

Reusable pieces ​

Named parameters ​

Debugging ​

Errors ​

See also ​