postgres.md

Prisma Next — Queries (Postgres)

Load this guide when db.ts imports from @prisma-next/postgres/runtime.

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

Key Concepts

Postgres (postgres<Contract>(...) from @prisma-next/postgres/runtime):

• db.orm.<ns>.<Model> — ORM, namespace-qualified 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, namespace-qualified lowercase storage name (db.sql.public.user). Produces a plan executed via db.runtime().execute(plan). Use when the ORM is too high-level — explicit JOIN, computed projections, set operations, window functions.

The namespace coordinate comes from your PSL: models declared at the top level of the schema live in the default public namespace, and models declared inside a namespace <name> { ... } block are addressed by that namespace (db.orm.<ns>.<Model>, db.sql.<ns>.<table>). The examples below use public; substitute your own namespace where a model is declared in one.

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:

Need	Choose	Why
Standard CRUD with relations	ORM (db.orm.<ns>.<Model>)	Highest ergonomics; fully typed; model-shaped.
Eager-load related records	ORM .include(...)	Composes with .where / .select / .orderBy / .take per branch.
Aggregate (count, sum, avg)	ORM .aggregate(...)	Typed result; works with grouping (.groupBy(...).aggregate(...)).
INSERT ... RETURNING / UPDATE ... RETURNING typed result	ORM mutations (returns updated rows) or db.sql.<t>.insert(...).returning(...)	ORM returns inserted/updated rows; SQL builder exposes .returning(...) explicitly.
Computed projection (e.g. ST_DistanceSphere(location, point) AS meters) alongside model fields	SQL builder (db.sql.<t>)	The ORM projects model fields; arbitrary expression projection is the SQL builder's seam.
Complex JOIN, set operation, window function	SQL builder	The ORM doesn't express arbitrary joins.
Postgres-specific feature (LATERAL, FILTER, custom aggregates)	SQL builder, falling back to extension operators when the extension provides them	DSL first; extensions can contribute operators (postgis, pgvector, cipherstash).

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. 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())
  .take(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(...), cipherstash's .cipherstashEq(...) / .cipherstashGt(...) / …).

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-next/sql-orm-client'; // façade re-export pending — see *What PN doesn't do yet* in SKILL.md
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. These currently come from the internal @prisma-next/sql-orm-client package — see What Prisma Next doesn't do yet in SKILL.md:

import { and, or, not } from '@prisma-next/sql-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). .take(n) limits; .skip(n) offsets.

await db.orm.public.Post
  .where((p) => p.authorId.eq(userId))
  .orderBy([(p) => p.createdAt.desc(), (p) => p.id.desc()])
  .take(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())
  .take(20)
  .all();

const last = page1[page1.length - 1];
const page2 = last
  ? await db.orm.public.Post
      .orderBy((p) => p.createdAt.desc())
      .cursor({ createdAt: last.createdAt })
      .take(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 / .take 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())
      .take(5),
  )
  .take(10)
  .all();
// → Array<{ id, email, posts: Array<{ id, title, createdAt }> }>

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 prisma-next-contract to add the required capability declarations and use prisma-next-queries 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). Project the aggregates into named result keys; the result type narrows accordingly.

Aggregate nullability matches SQL semantics:

Aggregate	Type	Empty result
count()	number	0
sum(field)	number | null	null (SQL SUM over zero rows is NULL)
avg(field)	number | null	null
min(field)	number | null	null
max(field)	number | null	null

This 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 execute it through the runtime with db.runtime().execute(plan). 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().execute(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. Pass rows as an array — the
// single-object form currently fails at result decoding (verified on 0.14.0).
const plan = db.sql.public.user
  .insert([{ email }])
  .returning('id', 'email')
  .build();
const [row] = await db.runtime().execute(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().execute(updatePlan);

// Delete with predicate.
const deletePlan = db.sql.public.user
  .delete()
  .where((f, fns) => fns.eq(f.id, userId))
  .build();
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().execute(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.execute(plan) executes a SQL-builder plan within it. 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.User.create({ id, email });
  await tx.orm.Post.create({ userId: user.id, title: 'hello' });

  // SQL-builder plan inside the transaction.
  const plan = tx.sql.post.update({ status: 'archived' })
    .where((f, fns) => fns.lt(f.createdAt, cutoff))
    .build();
  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

When the contract declares multiple namespaces, both db.sql and db.orm expose a namespace coordinate alongside the flat bare-name surface:

// db.sql.<namespace>.<table>
const plan = db.sql.public.users.select('id', 'email').build();
const authPlan = db.sql.auth.users.select('id', 'token').build();
await db.runtime().execute(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.users / db.orm.User form: the namespace coordinate is always required on Postgres. A bare model access returns undefined, so the first chained call throws.

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 — the runtime already substitutes 0 for the empty case. The ?? 0 belongs on sum / avg / min / max.
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 a Postgres façade subpath. The combinators currently live in @prisma-next/sql-orm-client — an internal package. See What Prisma Next doesn't do yet in SKILL.md.
6. Trying to db.sql.from(tables.user). That surface does not exist. The builder is table-shaped: db.sql.<tableName>.select(...). There is no db.schema.tables either.
7. Trying to db.execute(plan) directly. Plans execute through the runtime: db.runtime().execute(plan). Inside a transaction, use tx.execute(plan).
8. Setting capabilities: { lateral: true } in prisma-next.config.ts. defineConfig 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 prisma-next-contract).
9. Confabulating a db.sql.raw(...), TypedSQL, or .stream() surface. None of those exist today. See What Prisma Next doesn't do yet in SKILL.md.
10. Mixing the ORM mutation return with runtime.execute(plan). ORM terminals issue the query themselves and return rows. runtime.execute is for SQL-builder plans.
11. Top-N grouped queries written as groupBy(...).aggregate(...).sort().slice() in JS. That's a fallback because the grouped collection doesn't expose .orderBy(...) / .take(...). Fine at small cardinalities; for large grouped result sets, drop to db.sql.<ns>.<table>.

Reference Files

• Example queries under examples/prisma-next-demo/src/orm-client/ and examples/prisma-next-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 with ?? 0 at the consumption site when zero-fill is desired — did NOT coalesce count(), which is number.
• 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 }).take(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 the (currently internal) @prisma-next/sql-orm-client and noted the façade gap to the user.
• Executed SQL-builder plans via db.runtime().execute(plan) (or tx.execute(plan) inside a transaction).
• 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 db.sql.raw, TypedSQL, .stream(), db.batch, .between(...), a capabilities field on defineConfig, or a db.sql.from(tables.user) API — routed to What Prisma Next doesn't do yet / prisma-next-feedback instead.