Basics

Override: Helpers

Misc helpers cover clause composition, CTEs, and set operations.

Clause Helpers

Helpers like groupBy and having add explicit keywords and separators.

const query = q
  .select(
    q.c("orders.user_id"),
    q.count(q.c("orders.id")).as(q.c("order_count")),
  )
  .from(q.t("orders"))
  .groupBy(q.c("orders.user_id"))
  .having(q.gt(q.count(q.c("orders.id")), q.v(1)))

With / CTE

Use with to emit a CTE and continue the main query.

const query = q
  .with(
    q.i("recent_orders"),
    q.select(q.c("orders.id"))
      .from(q.t("orders"))
      .where(q.gt(q.c("orders.created_at"), q.raw`NOW() - INTERVAL '7 days'`)),
  )
  .select(q.c("recent_orders.id"))
  .from(q.t("recent_orders"))

Set Operations

Set ops like union, intersect, andexcept wrap subqueries in parentheses.

const query = q
  .select(q.c("users.id")).from(q.t("users"))
  .unionAll(
    q.select(q.c("admins.id")).from(q.t("admins")),
  )
  .orderBy(q.c("id"))

Pagination

Use limit and offset for offset-based paging.

const query = q
  .select(q.c("events.id"))
  .from(q.t("events"))
  .limit(q.v(25))
  .offset(q.v(50))

Identifiers

as, t, and c handle identifiers with Postgres rules.

const query = q
  .select(
    q.c("users.id").as(q.c("user_id")),
    q.c("users.email").as(q.c("email")),
  )
  .from(q.t("users"))

Windows

Use over and partitionBy for window clauses.

const query = q.select(
  q.rowNumber().over(q.partitionBy(q.column("department_id")))
  )