Prisma 8 — Queries (Postgres)

Load this guide when db.ts imports from @internal/postgres/runtime.

Shared concepts (result consumption, script teardown, cross-target pitfalls, capability gaps) live in queries.md.

Key Concepts

Postgres (postgres<Contract>(...) from @internal/postgres/runtime):

• db.orm.<ns>.<Model> — ORM, PascalCase model name (db.orm.public.User). Fluent .where(...).select(...).orderBy(...).all(), fully typed against Contract. Default lane for CRUD with relations.
• db.sql.<ns>.<table> — SQL builder, lowercase storage name (db.sql.public.user). Produces a plan. A plan that returns rows (select, or a write with .returning(...)) runs through db.runtime().query(plan); a write with no RETURNING runs through db.runtime().execute(plan), which resolves { affectedRows }. Use when the ORM is too high-level — explicit JOIN, computed projections, set operations, window functions.

Reach for the ORM first; drop to db.sql when the ORM can't express the shape. Lane choice is local — one query function picks one lane, not the whole app.

Lane decision table:

Workflow — ORM reads

The concept: db.orm.<ns>.<Model> returns a collection you compose method-by-method. Each call returns a new collection (immutable chaining); the terminal verb (.all() / .first() / .aggregate(...)) issues the query. There is no .count() terminal on the collection — count() is an include reducer and an aggregate(...) operation (both below). Predicates are lambdas over a field proxy: u.field.<op>(value).

// src/queries/users.ts — one directory deep under src/, so the import is '../prisma/db'
import { db } from '../prisma/db';

// Find one record by primary key shorthand.
const user = await db.orm.public.User.first({ id: userId });
// Returns the full row or `null`.

// Find one matching a predicate.
const alice = await db.orm.public.User
  .where((u) => u.email.eq('alice@example.com'))
  .first();

// Find many with projection, sort, and limit.
const recentUsers = await db.orm.public.User
  .select('id', 'email', 'createdAt')
  .orderBy((u) => u.createdAt.desc())
  .limit(10)
  .all();

Predicates (.where(...)) come in two forms:

// Lambda form — full expression power.
db.orm.public.User.where((u) => u.email.eq('alice@example.com'));

// Shorthand object form — equality on the named fields.
db.orm.public.User.where({ kind: 'admin' });

Operators on the field proxy include .eq, .neq, .lt, .lte, .gt, .gte, .like, .ilike, .in([...]), .isNull(), .isNotNull(). Extensions add target-specific operators on extension-typed columns (pgvector's .cosineDistance(...), postgis's .within(...) / .intersectsBbox(...) / .distanceSphere(...)).

There is no .between(a, b) operator. Express ranges either as two chained .where(...) clauses (the idiomatic form — clauses AND-compose) or with the and(...) combinator inside one clause:

// Chained .where() — each clause AND-composes with the previous one.
await db.orm.public.Sale
  .where((s) => s.day.gte(start))
  .where((s) => s.day.lte(end))
  .all();

// Equivalent with an explicit `and(...)` inside one clause.
import { and } from '@prisma/orm-postgres/orm-client';
await db.orm.public.Sale
  .where((s) => and(s.day.gte(start), s.day.lte(end)))
  .all();

The two forms emit the same SQL. Pick chained .where() when each clause adds a separate condition that reads as its own thought; pick and(...) when one logical predicate happens to have two parts and you want the visual grouping. Don't reach for a between helper — there isn't one.

Combinators (and, or, not) compose predicates, and relation predicates (.some(...), .none(...), .every(...)) recurse into a relation. .some() and .none() take an optional predicate — u.posts.none() means "has no posts", u.posts.some() means "has at least one" — while .every(...) requires one: u.posts.every((p) => p.published.eq(true)). The combinators are exported from the façade's orm-client subpath:

import { and, or, not } from '@prisma/orm-postgres/orm-client';

await db.orm.public.User
  .where((u) =>
    and(
      or(u.kind.eq('admin'), u.email.ilike('%@example.com')),
      not(u.posts.none((p) => p.title.ilike('%draft%'))),
    ),
  )
  .all();

Sorting and pagination. .orderBy(...) accepts a single lambda or an array of lambdas (each calling .asc() / .desc() on a field). .limit(n) limits; .offset(n) offsets.

await db.orm.public.Post
  .where((p) => p.authorId.eq(userId))
  .orderBy([(p) => p.createdAt.desc(), (p) => p.id.desc()])
  .limit(20)
  .all();

Cursor pagination. Call .cursor({ field: lastValue }) after .orderBy(...) to resume from a known position. The cursor requires a prior orderBy — the type system enforces this. Direction (forward or backward) follows the sort: ascending order means "greater than the cursor value", descending means "less than".

const page1 = await db.orm.public.Post
  .orderBy((p) => p.createdAt.desc())
  .limit(20)
  .all();

const last = page1[page1.length - 1]!;
const page2 = await db.orm.public.Post
  .orderBy((p) => p.createdAt.desc())
  .cursor({ createdAt: last.createdAt })
  .limit(20)
  .all();

Cursor keys must match fields in the active orderBy. For a composite orderBy, pass a value for each ordering column — a partial cursor seeks only on the columns you supply, which gives an incomplete keyset. An empty cursor object is a no-op: you get the unfiltered first page back.

.first() vs .first({ pk }) vs .all(). Use .first() for a single row (issues a LIMIT 1); use .first({ pk }) for primary-key lookups; reserve .all() for the genuine many case (no implicit LIMIT).

Workflow — Eager-loading relations (.include)

The concept: .include('<relation>', (branch) => branch.<chain>) adds a relation branch to the parent query. The branch is its own collection — compose .where / .select / .orderBy / .limit on it just like the parent.

await db.orm.public.User
  .select('id', 'email')
  .include('posts', (post) =>
    post
      .select('id', 'title', 'createdAt')
      .orderBy((p) => p.createdAt.desc())
      .limit(5),
  )
  .limit(10)
  .all();
// → Array<{ id, email, posts: Array<{ id, title, createdAt }> }>

Reduce a to-many relation to a scalar. A refinement callback may return a reducer — count(), sum(field), avg(field), min(field), max(field), plus the lossless countBigInt(), sumBigInt(field), and avgDecimal(field) — instead of a collection. The parent's relation field then carries that one value rather than an array. Reducers exist only inside an include(...) callback; calling one elsewhere throws.

await db.orm.public.User.include('posts', (posts) => posts.count()).all();
// → Array<{ ...user, posts: number }> — a parent with no posts reads 0

await db.orm.public.User.include('posts', (posts) => posts.sum('views')).all();
// → Array<{ ...user, posts: number | null }>

await db.orm.public.User.include('posts', (posts) => posts.avg('views')).all();
// → Array<{ ...user, posts: number | null }>

await db.orm.public.User.include('posts', (posts) => posts.min('views')).all();
await db.orm.public.User.include('posts', (posts) => posts.max('views')).all();
// → Array<{ ...user, posts: number | null }>

// The lossless form, for a total that may outgrow a JS number:
await db.orm.public.User.include('posts', (posts) => posts.sumBigInt('views')).all();
// → Array<{ ...user, posts: bigint | null }>

// Several sub-views of one relation at once:
await db.orm.public.User.include('posts', (posts) =>
  posts.combine({ recent: posts.limit(3), total: posts.count() }),
).all();
// → Array<{ ...user, posts: { recent: Post[]; total: number } }>

A reducer's result type is the one the target declares for that aggregate — the same types Workflow — Aggregates tabulates below, including the null a field-taking reducer answers for a parent with no related rows. The reducer set is read from the contract, so an operation a target or extension contributes shows up as a reducer under its own name, with no client change.

Nested 1:N → 1:N includes (e.g. User → posts → comments) require the contract to advertise the lateral + jsonAgg capabilities for the active target. The Postgres adapter advertises both by default, so most apps get this for free; if the type system rejects a nested include with a missing capability error, route to references/contract.md to add the required capability declarations and use references/queries.md for query-shape guidance.

Workflow — ORM writes

// Create — returns the inserted row.
const user = await db.orm.public.User.create({ id, email, displayName, kind, createdAt });

// Create with selected return — narrows the return shape.
const summary = await db.orm.public.User
  .select('id', 'email', 'kind')
  .create({ id, email, displayName, kind, createdAt });

// Update by predicate.
await db.orm.public.User.where({ id }).update({ email: newEmail });

// Update with selected return.
await db.orm.public.User
  .where({ id })
  .select('id', 'email', 'kind')
  .update({ email: newEmail });

// Delete by predicate.
await db.orm.public.User.where({ id }).delete();

// Upsert — typed by the create branch's shape.
await db.orm.public.User
  .select('id', 'email', 'kind', 'createdAt')
  .upsert({
    create: { id, email, displayName, kind, createdAt: new Date() },
    update: { email, displayName, kind },
  });

The ORM returns inserted / updated rows by default. The .returning(...) selector lives on the SQL builder (next section), where you build a plan and execute it explicitly.

Workflow — Aggregates

const totals = await db.orm.public.User.aggregate((aggregate) => ({
  totalUsers: aggregate.count(),
}));

const adminTotals = await db.orm.public.User
  .where({ kind: 'admin' })
  .aggregate((aggregate) => ({
    adminUsers: aggregate.count(),
  }));

// Group-by + aggregate.
const byKind = await db.orm.public.User
  .groupBy('kind')
  .having((having) => having.count().gte(minUsers))
  .aggregate((aggregate) => ({
    totalUsers: aggregate.count(),
  }));

aggregate exposes .count(), .sum(field), .avg(field), .min(field), .max(field), and the lossless .countBigInt(), .sumBigInt(field), .avgDecimal(field). Project the aggregates into named result keys; the result type narrows accordingly.

The bare operations answer as JS numbers. The suffixed ones answer losslessly. An aggregate's type is the one its target declares and its nullability matches SQL semantics, both read from the contract. On PostgreSQL:

count(), and sum over an integer column, throw outside ±(2^53 − 1) — they never round. Those two are the results a guarded integer codec produces. The rows above the guard does not reach: sum over numeric, UnboundedInt, or a float column, and every avg, which is a fraction already. The error is RUNTIME.DECODE_FAILED (… value must be an integer within the safe integer range, got …), and it fires on the include path too. That is the trade the defaults make: a number you can compare, serialise, and do arithmetic with, and a loud failure instead of a quietly wrong total. Switch that one call to sumBigInt / countBigInt where the magnitude is real.

Float and Decimal columns keep their own family. sum over numeric is still a decimal string, and sum over float8 still a number — the defaults policy is about integers, and a column whose author chose numeric already chose its representation.

SQLite states the same policy. count, sum over integers, and avg all read as number; countBigInt and sumBigInt are there too. There is no avgDecimal — SQLite has no exact decimal type, so the method does not exist on a SQLite contract.

The ORM's having(...) stays a number. Its comparand is typed number outright, whatever result type the aggregate carries, so having.count().gte(minUsers) takes a plain number — the value is inlined as a SQL literal and compared inside the database. Only the aggregate's result, the value that reaches your code, carries its target's type. The lossless variants are projection-only and have no HAVING method at all.

The SQL builder types its comparands differently. fns.gt(a, b) types both operands from one codec, so a literal compared against an aggregate follows that aggregate's result codec — fns.gt(fns.count(), 1), not 1n. That applies in having(...), in where(...), and inside any larger expression.

Nullability isn't a typing bug — it's faithful to what the database returns. Coalesce client-side when you want zero-fill:

const revenue = await db.orm.public.Sale
  .where((s) => s.day.gte(start))
  .aggregate((a) => ({ total: a.sum('amount') }));
// revenue.total: number | null

const safe = revenue.total ?? 0;   // ← apply at the consumption site, not in the aggregate spec.

If ?? 0 is showing up on every aggregate, that's a signal you're calling sum (or peers) over potentially-empty filters — which is exactly when SQL returns NULL. The pattern is correct; the typing is honest.

Workflow — SQL builder (db.sql.<ns>.<table>)

The concept: db.sql.<ns>.<table> is a table-shaped builder that produces a plan. The plan is a serialisable description of the query (AST + parameters); you run it through the runtime. Pick the runtime method by the result the plan declares: db.runtime().query(plan) for rows (it returns the same AsyncIterableResult as .all(), so await it for an array), db.runtime().execute(plan) for a write with no RETURNING (it resolves { affectedRows } and returns no rows). The builder gives you the lanes the ORM doesn't express — explicit JOIN, arbitrary expression projection, target-specific operations through extension helpers — without dropping to raw SQL.

// src/queries/posts.ts — adjust the relative import to match file depth.
import { db } from '../prisma/db';

// Select with predicate and limit.
const plan = db.sql.public.post
  .select('id', 'title', 'userId', 'createdAt')
  .where((f, fns) => fns.eq(f.userId, userId))
  .limit(limit)
  .build();

const rows = await db.runtime().query(plan);

The .where(...) callback receives (fields, fns) — fields is the field proxy (column references), fns is the operator namespace (fns.eq, fns.ne, fns.gt, …). Extensions inject extension-shaped helpers into the same fns namespace (fns.distanceSphere, fns.cosineDistance, etc.).

INSERT / UPDATE / DELETE with RETURNING

// Insert and return selected columns. `insert()` takes an array of rows.
const plan = db.sql.public.user
  .insert([{ email }])
  .returning('id', 'email')
  .build();
const [row] = await db.runtime().query(plan);

// Update with predicate and returning.
const updatePlan = db.sql.public.user
  .update({ email: newEmail })
  .where((f, fns) => fns.eq(f.id, userId))
  .returning('id', 'email')
  .build();
const rows = await db.runtime().query(updatePlan);

// Delete with predicate, no RETURNING — `execute` resolves the affected count.
const deletePlan = db.sql.public.user
  .delete()
  .where((f, fns) => fns.eq(f.id, userId))
  .build();
const { affectedRows } = await db.runtime().execute(deletePlan);

.returning(...) requires the target adapter to advertise the returning capability. The Postgres adapter advertises it by default.

Computed projections and joins

// Project a computed expression alongside model fields.
const plan = db.sql.public.cafe
  .select('id', 'name')
  .select('meters', (f, fns) => fns.distanceSphere(f.location, point))
  .orderBy((f, fns) => fns.distanceSphere(f.location, point), { direction: 'asc' })
  .orderBy((f) => f.id, { direction: 'asc' })
  .limit(limit)
  .build();
const rows = await db.runtime().query(plan);

// Self-join with an alias.
db.sql.public.post
  .innerJoin(db.sql.public.post.as('p2'), (f, fns) => fns.ne(f.p1.userId, f.p2.userId))
  // ...
  .build();

Workflow — Transactions

The concept: db.transaction(fn) opens a transaction and passes a tx context to the callback. tx.orm and tx.sql mirror db.orm / db.sql but ride the same transaction; tx.query(plan) / tx.execute(plan) run a SQL-builder plan within it (rows vs affected count, as on the runtime). The transaction commits on the callback's successful return and rolls back on any thrown error.

await db.transaction(async (tx) => {
  const user = await tx.orm.public.User.create({ id, email });
  await tx.orm.public.Post.create({ userId: user.id, title: 'hello' });

  // SQL-builder plan inside the transaction — no RETURNING, so `execute`.
  const plan = tx.sql.public.post.update({ status: 'archived' })
    .where((f, fns) => fns.lt(f.createdAt, cutoff))
    .build();
  const { affectedRows } = await tx.execute(plan);

  // If anything throws, all three operations roll back.
});

The callback's return value passes through db.transaction(...). Capture inserted ids out of the callback and use them downstream after commit.

Namespace-aware accessors

On Postgres both db.sql and db.orm are keyed by storage namespace (the Postgres schema) — always, not only when a contract declares more than one. A model outside any namespace { } block is in public:

// db.sql.<namespace>.<table>
const plan = db.sql.public.user.select('id', 'email').build();
const authPlan = db.sql.auth.users.select('id', 'token').build();
const rows = await db.runtime().query(plan);

// db.orm.<namespace>.<Model>
const user = await db.orm.public.User.create({ id: 1, email: 'a@x.io' });
const authUser = await db.orm.auth.User.create({ id: 2, token: 'tok' });

There is no flat db.sql.user / db.orm.User on the Postgres façade (Db is "one facet per storage namespace, and nothing else"); reaching for one is a type error. The flat spelling belongs to SQLite, whose façade exposes its single unbound namespace directly — see references/queries.md § Namespace-aware accessors.

Cross-namespace relations (e.g. public.Profile → auth.User) follow the same .include() syntax; the ORM resolves the correct schema-qualified join automatically.

Common Pitfalls (Postgres)

1. Reaching for the lower-level lane when the ORM would have done. The ORM covers most CRUD shapes; drop to db.sql only for shapes the ORM can't express. Default to the ORM.
2. Using .all() when you wanted one row. .all() issues no implicit limit. Use .first() or .first({ pk }).
3. Coalescing count() with ?? 0 "just in case". count() is number, not number | null — SQL answers an empty set with 0. The ?? 0 belongs on sum / avg / min / max, and its zero should match the aggregate's own type (0 for an integer sum, 0n for sumBigInt, '0' where the result is a decimal string).
4. Reaching for .between(a, b) on a field proxy. It doesn't exist. Either chain .where((m) => m.field.gte(a)).where((m) => m.field.lte(b)) or use and(m.field.gte(a), m.field.lte(b)) inside one .where() clause.
5. Importing and / or / not from an internal package. They are exported from @prisma/orm-postgres/orm-client; do not reach into @internal/sql-orm-client.
6. Trying to db.sql.from(tables.user). That surface does not exist. The builder is table-shaped: db.sql.<ns>.<tableName>.select(...). There is no db.schema.tables either.
7. Trying to db.execute(plan) directly, or reading rows with execute. Plans run through the runtime: db.runtime().query(plan) for rows, db.runtime().execute(plan) for a non-returning write ({ affectedRows }). Inside a transaction, tx.query(plan) / tx.execute(plan). execute never yields rows — a select or .returning(...) plan passed to it gives you statistics, not data.
8. Setting capabilities: { lateral: true } in prisma.config.ts. The ORM config (ormConfig({...})) does not take capabilities. Capabilities are declared by the active adapter and become part of the emitted contract; the Postgres adapter advertises lateral, jsonAgg, and returning out of the box. Enable extension capabilities through extensions: [...] in the config (see references/contract.md).
9. Confabulating a TypedSQL or .stream() surface. Neither exists. Raw SQL does: the client's raw lane, db.raw.sql`…` . Reusable statements do: db.prepare(...) (see Prepared statements in queries.md). Streaming: for await over a read terminal or runtime.query(plan) — with the caveats in Streaming in queries.md.
10. Mixing the ORM mutation return with runtime.query(plan) / runtime.execute(plan). ORM terminals issue the query themselves and return rows. The runtime methods are for SQL-builder plans.
11. Ordering grouped rows by an aggregate metric. The grouped collection supports .orderBy(...) on group keys plus .limit(...) / .offset(...), but it cannot order by an aggregate alias such as SUM(amount). Sorting the materialized aggregate result in JS is fine at small cardinalities; for large grouped result sets, drop to db.sql.<ns>.<table>.

Reference Files

• Example queries under examples/prisma-8-demo/src/orm-client/ and examples/prisma-8-demo/src/queries/ — canonical ORM and SQL-builder shapes.
• ORM client source under packages/3-extensions/sql-orm-client/src/.
• SQL builder source under packages/2-sql/4-lanes/sql-builder/src/.

Checklist

• Chose the right lane (ORM by default; db.sql for shapes the ORM doesn't express).
• Used .first() / .first({ pk }) for single-row reads — not .all().
• Coalesced sum / avg / min / max results at the consumption site when zero-fill is desired, with a zero of the aggregate's own type — did NOT coalesce count(), which is number and never null.
• Reached for countBigInt / sumBigInt / avgDecimal where the value can outgrow a JS number or the exact decimal matters — count() and sum over an integer column throw RUNTIME.DECODE_FAILED outside ±(2^53 − 1) rather than rounding, and avg rounds as any double does.
• Compared and serialised aggregate results as what they are — a bigint from a suffixed variant needs 0n literals and String(value) rather than bare JSON.stringify — leaving the ORM's having(...) operands as numbers, and matching each SQL-builder comparison literal to the aggregate's own result codec (fns.gt(fns.count(), 1)).
• Expressed ranges as chained .where(...) clauses or a single and(...) clause — did NOT reach for a non-existent .between(...) operator.
• For cursor pagination, used .orderBy(...).cursor({ field: lastValue }).limit(n).all() — did NOT hand-write a .where(p => p.field.lt(cursor)) workaround when the .cursor() API serves the same purpose.
• For ORM combinators, imported and / or / not from @prisma/orm-postgres/orm-client.
• Ran SQL-builder plans via db.runtime().query(plan) when they return rows and db.runtime().execute(plan) only for non-returning writes (tx.query / tx.execute inside a transaction). Passed insert() an array of rows.
• Wrapped multi-statement work in db.transaction(async (tx) => { ... }) where atomicity matters.
• For top-N grouped aggregates at meaningful scale, dropped to db.sql.<ns>.<table> rather than JS-side sort + slice over groupBy(...).aggregate(...).
• Did NOT confabulate TypedSQL, .stream(), db.batch, .between(...), a collection-level .count(), a capabilities field on the config, or a db.sql.from(tables.user) API — routed to What Prisma 8 doesn't do yet / references/feedback.md instead. Raw SQL is spelled db.raw.sql, not db.sql.raw.