Advanced queries

The SQL in the examples is what the Postgres adapter emits; values are always bound parameters ($1, $2, …).

Subqueries

A query can be used inside another: as a source, in a join, with in, with exists, or as a single value.

const postCounts = from(Post, "p")
  .groupBy(({ p }) => p.authorId)
  .select(({ p }) => ({ authorId: p.authorId, n: count() }));

// As a source: the binding exposes the subquery's select keys
from(subquery(postCounts), "c").where(({ c }) => c.n.gt(5));
// select "c"."authorId" as "c0", "c"."n" as "c1"
// from (select "p"."author_id" as "authorId", count(*) as "n" from "posts" as "p" group by "p"."author_id") as "c"
// where "c"."n" > $1

// In a join
from(User, "u").join(subquery(postCounts), "c", ({ u, c }) => c.authorId.eq(u.id));

// IN
from(User, "u").where(({ u }) => u.id.in(from(Post, "p").select(({ p }) => p.authorId)));
// … where "u"."id" in (select "p"."author_id" as "c0" from "posts" as "p")

// EXISTS, referring to the outer query
from(User, "u").where(({ u }) => exists(from(Post, "p").where(({ p }) => p.authorId.eq(u.id))));
// … where exists (select 1 from "posts" as "p" where "p"."author_id" = "u"."id")

// A single value in the select: null when the subquery returns no row
from(User, "u").select(({ u }) => ({
  user: u,
  posts: scalar(
    from(Post, "p")
      .where(({ p }) => p.authorId.eq(u.id))
      .select(() => count()),
  ), // number | null
}));

A subquery used as a source must select an object of expressions, or whole rows of a schema; anything else does not compile (and throws QueryError). Its binding exposes the select keys as columns, typed like the selected values; over whole rows it is the schema's binding. A query over it that selects nothing returns those objects or rows.
On the optional side of a leftJoin, every column of the subquery is nullable.
An inner callback can use the outer binding directly, as u is used above.
scalar(q) must select one expression, and is null when the subquery returns no row. A query of whole rows, an object or an array of values does not compile. Neither does one that selects a JSON column whose type is an object, since its type cannot be told from an object of keys: select it as .as<unknown>() and check its shape where you read it.
The inner query's values and named parameters become the outer query's: the outer query needs the inner one's named parameters when it runs.
A subquery in FROM or a join reads the queries around its query, not its query's own bindings: that would be a lateral join, which is not supported yet (QueryError).
When the inner query is built elsewhere and has no access to u, refer to it by alias with outerRef. A query around the one that uses it must bind that schema under that alias, and the query using it must not have a binding of that alias itself (it would be read instead); otherwise compiling the query throws QueryError:

const byThisUser = from(Post, "p").where(({ p }) => p.authorId.eq(outerRef("u", User).id));
from(User, "u").where(() => exists(byThisUser));

Common table expressions

const roots = from(Category, "c").where(({ c }) => c.parentId.isNull());
const children = from(Category, "c").join(cte("tree", Category), "t", ({ c, t }) => c.parentId.eq(t.id));

const tree = from(cte("tree", Category), "t").withCte("tree", roots.unionAll(children), {
  recursive: true,
});
// with recursive "tree" as (
//   select "c"."id" as "id", "c"."parent_id" as "parent_id", "c"."name" as "name" from "categories" as "c"
//   where "c"."parent_id" is null
//   union all (select … from "categories" as "c" inner join "tree" as "t" on "c"."parent_id" = "t"."id")
// ) select "t"."id" as "c0", "t"."parent_id" as "c1", "t"."name" as "c2" from "tree" as "t"

.withCte(name, query, { recursive?, materialized? }) attaches a CTE to a query. materialized: true computes it once, false lets the database inline it; left out, the database decides.
cte(name, shape) refers to it as a source. shape is a schema class, or the CTE's own query, and gives the binding its type. The query given to withCte must return the same: rows of that schema, or the same keys with the same types (QueryError otherwise). A recursive CTE's query refers to the CTE before it exists, so it names the schema of its rows.
A CTE's values come first in the SQL, so its parameters are $1, $2, ….

Set operations

const emails = from(User, "u").select(({ u }) => ({ email: u.email }));
const titles = from(Post, "p").select(({ p }) => ({ email: p.title }));

emails.union(titles); //        union         (removes duplicates)
emails.unionAll(titles); //     union all
emails.intersect(titles); //    intersect
emails.intersectAll(titles); // intersect all
emails.except(titles); //       except
emails.exceptAll(titles); //    except all

emails
  .union(titles)
  .orderBy(({ u }) => u.email)
  .limit(10);
// select "u"."email" as "c0" from "users" as "u"
// union (select "p"."title" as "c0" from "posts" as "p")
// order by "c0" asc limit $1

Both sides must have the same result type; a mismatch is a compile error. Columns are combined by position, so both sides must also select their keys in the same order, and neither may select a plain value (it is never sent to the database): both are checked when the query is built.
The query the method is called on is the combined query. orderBy, limit and offset added after the operation apply to the combined rows; an orderBy may only use selected expressions (or a selectedAs name).
Nothing else may be added after a set operation (where, join, select, …): it would apply to the left side only. Add it to a side before combining, or wrap the combined query in subquery(…). For the same reason the left side may not already have an orderBy, limit or offset.
An intersect after a union or except applies to everything before it: a.union(b).intersect(c) is (a union b) intersect c.

Window functions

from(Post, "p")
  .windows({ w: ({ p }) => ({ partitionBy: [p.authorId], orderBy: [p.createdAt.desc()] }) })
  .select(({ p }) => ({
    id: p.id,
    rank: rowNumber().over("w"),
    total: sum(p.views).over({ partitionBy: [p.authorId] }),
    previous: lag(p.views).over("w"),
    running: sum(p.views).over({
      orderBy: [p.createdAt],
      frame: fragment`rows between unbounded preceding and current row`,
    }),
  }));
// select "p"."id" as "c0", row_number() over "w" as "c1",
//   sum("p"."views") over (partition by "p"."author_id") as "c2", lag("p"."views") over "w" as "c3",
//   sum("p"."views") over (order by "p"."created_at" asc rows between unbounded preceding and current row) as "c4"
// from "posts" as "p" window "w" as (partition by "p"."author_id" order by "p"."created_at" desc)

Functions: rowNumber, rank, denseRank, percentRank, cumeDist, ntile, lag, lead, firstValue, lastValue, nthValue, and any aggregate with .over(). A window function without .over() does not compile, and takes no filter().
.over("w") uses a named window declared with .windows({ … }). The name is type-checked against the windows declared, so declare them before the select or orderBy that uses them.
.over({ partitionBy, orderBy, frame }) defines one inline. frame is a fragment.
.over() with no argument is the whole result.
Window functions are computed after the rows are filtered and grouped, so they belong in select and orderBy only: in where, having, groupBy, a join's on, set or returning they are a QueryError. To filter on one, select it in a subquery and filter the query over it (the top-N recipe below).

Conditions built at runtime

Clause callbacks already cover most dynamic queries. dynamic is for a condition that is built before the query it will be used in:

const filters = [
  dynamic<{ u: typeof User }>(({ u }) => u.age.gte(18)),
  dynamic<{ u: typeof User }>(({ u }) => u.role.eq("member")),
];

const all = filters.reduce((acc, f) => acc.and(f), dynamic.true());
from(User, "u").where(all);
// … where true and "u"."age" >= $1 and "u"."role" = $2

Using it on a query that has no binding u of User is a compile error. dynamic values are accepted by where, orWhere, having, orderBy, groupBy, select and join conditions, and combine with .and(…), .or(…) and .not().

The form above, with the bindings as a type, takes conditions without named parameters. To use named parameters, or to build an order item or a select shape, give the schemas as a value; the bindings are then also checked when the query runs the callback:

const minViews = dynamic({ p: Post }, ({ p }) => p.views.gte(param("minViews", t.integer())));
const newestFirst = dynamic({ p: Post }, ({ p }) => p.createdAt.desc());
from(Post, "p").where(minViews).orderBy(newestFirst); // needs { minViews: number }

field(binding, name) picks a column by a name known only at runtime. The name is checked against the binding's columns, and anything else throws:

from(User, "u").orderBy(({ u }) => field(u, sortColumn).asc());

Bulk updates and deletes

A query with .set(...) describes an UPDATE, run with repo.updateAll. A query without it can be run with repo.deleteAll.

// Plain values
from(Post, "p")
  .where(({ p }) => p.authorId.eq(1))
  .set({ published: true });
// update "posts" as "p" set "published" = $1 where "p"."author_id" = $2

// Expressions
const publish = from(Post, "p")
  .where(({ p }) => p.authorId.eq(1))
  .set(({ p }) => ({ views: p.views.add(1), tags: p.tags.append("featured") }))
  .returning(({ p }) => p.id);
// update "posts" as "p" set "views" = "p"."views" + $1, "tags" = array_append("p"."tags", $2)
// where "p"."author_id" = $3 returning "p"."id" as "c0"

const ids = await repo.updateAll(publish); // number[]

.set takes an object, or a callback returning one. Values are type-checked against the fields; plain values are bound with the field's type. The field also types a caseWhen or coalesce made only of values and NULLs: it is written cast(case … end as <the column's type>), so caseWhen(p.published, "live").else("draft") and caseWhen(c, 1).else(2) work on any column. .set may be called again: it adds fields, and a field set twice takes the later value.
.returning(...) takes the same shapes as select and makes the repo return rows. An update has no select: calling it is a QueryError that points at returning.
Joins become UPDATE ... FROM and DELETE ... USING on Postgres:

from(Post, "p")
  .join(User, "u", ({ p, u }) => p.authorId.eq(u.id))
  .where(({ u }) => u.role.eq("admin"))
  .set({ published: true });
// update "posts" as "p" set "published" = $1 from "users" as "u"
// where "p"."author_id" = "u"."id" and "u"."role" = $2

Prepared queries

prepare(q) marks a query that runs many times. Run it like any query, with its named parameters.

const byEmail = prepare(
  from(User, "u")
    .where(({ u }) => u.email.eq(param("email", t.string())))
    .limit(1),
);

await repo.one(byEmail, { email });

Today it compiles once per adapter and is then found in the adapter's plan cache by identity, as any query kept in a variable is; skipping that lookup as well is planned. What changes between runs are the named parameters; a value written into the query is fixed.

Other tools

Ordering by a select alias. selectedAs(expr, "total") names a selected expression, and selectedAs("total") refers to it in orderBy or groupBy; a name the select does not have is a QueryError:

from(Post, "p")
  .groupBy(({ p }) => p.authorId)
  .select(({ p }) => ({ authorId: p.authorId, total: selectedAs(sum(p.views), "total") }))
  .orderBy(() => selectedAs("total").desc());
// select "p"."author_id" as "c0", sum("p"."views") as "total" from "posts" as "p"
// group by "p"."author_id" order by "total" desc

Tuple comparison, for keyset pagination. A plain value takes the type of the column at the same position of the other row; where both rows hold a bound value, the typed one is cast. A row has no null item and is compared, not selected:

from(Post, "p")
  .where(({ p }) => tuple(p.createdAt, p.id).lt(tuple(cursor.createdAt, cursor.id)))
  .orderBy(({ p }) => [p.createdAt.desc(), p.id.desc()])
  .limit(20);
// … where ("p"."created_at", "p"."id") < ($1, $2) order by "p"."created_at" desc, "p"."id" desc limit $3

Recipes

Top N per group

const ranked = from(Post, "p").select(({ p }) => ({
  id: p.id,
  rn: rowNumber().over({ partitionBy: [p.authorId], orderBy: [p.createdAt.desc()] }),
}));

from(Post, "p").where(({ p }) =>
  p.id.in(
    from(subquery(ranked), "r")
      .where(({ r }) => r.rn.lte(3))
      .select(({ r }) => r.id),
  ),
);

When the groups are the parents of an association, a preload does this for you: preload("posts", (q) => q.orderBy(...).limit(3)). See Preloading.

Recursive tree

The CTE example above: the roots, then unionAll with the rows that join the CTE itself.

Keyset pagination

The tuple comparison above, with the last row of a page as the cursor of the next.

Rows with no match

from(User, "u").where(({ u }) => not(exists(from(Post, "p").where(({ p }) => p.authorId.eq(u.id)))));

Updating from a CTE

// Update the rows that exist, from a CTE that computes the new values once.
const totals = from(Post, "p")
  .groupBy(({ p }) => p.authorId)
  .select(({ p }) => ({ authorId: p.authorId, views: sum(p.views) }));
from(User, "u")
  .withCte("totals", totals)
  .join(cte("totals", totals), "t", ({ u, t }) => t.authorId.eq(u.id))
  .where(({ t }) => t.views.gt(1000))
  .set({ role: "admin" });

To insert a row or update the one that is there, use onConflict on repo.insert.

Conditional filters

The dynamic example above: build the conditions from the request, then fold them.

Not in the first release

Lateral joins, VALUES lists as a source, table-valued fragments, index hints, data-modifying CTEs and named server-side prepared statements are planned. See the Roadmap.

Good to know

Nothing after a set operation but orderBy, limit and offset: wrap the combined query in subquery(…) to filter it.
Window functions belong in select and orderBy only; filter on one through a subquery.
Bulk updates use .set(…) and are run with repo.updateAll; there is no guard against an update without where.

Advanced queries ​

Subqueries ​

Common table expressions ​

Set operations ​

Window functions ​

Conditions built at runtime ​

Bulk updates and deletes ​

Prepared queries ​

Other tools ​

Recipes ​

Top N per group ​

Recursive tree ​

Keyset pagination ​

Rows with no match ​

Updating from a CTE ​

Conditional filters ​

Not in the first release ​

See also ​