# 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`](./queries.md). ## Key Concepts **Postgres** (`postgres({ contract, ... })` from `@internal/postgres/runtime`): - **`db.orm..`** — 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..`** — 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.query(plan)`; a write with no `RETURNING` runs through `db.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:** | Need | Choose | Why | | --- | --- | --- | | Standard CRUD with relations | **ORM (`db.orm..`)** | Highest ergonomics; fully typed; model-shaped. | | Eager-load related records | **ORM `.include(...)`** | Composes with `.where` / `.select` / `.orderBy` / `.limit` 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...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..`)** | 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`). | ## Workflow — ORM reads The concept: `db.orm..` 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.(value)`. ```typescript // 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: ```typescript // 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(...)`). **Full-text search** is built into the Postgres target, on any text column: `.fullTextMatches(q)` is the predicate, `.fullTextRank(q)` scores a row so you can order by relevance, and `.fullTextHeadline(q)` returns the text with `` around the matches. The argument `q` is a `tsquery`, built with a helper from `@prisma/orm-postgres/target/full-text`. A bare string is a type error, because Postgres would read it as `tsquery` syntax without lowercasing or stemming it. Pick the helper by where the text comes from: - A search box: `websearchToTsquery(input)`. `"an exact phrase"`, `-excluded` and `or` work, and it never errors. `plaintoTsquery` requires every word; `phrasetoTsquery` requires the words in order. - User input inside operator syntax, such as typeahead: the `tsquery` tag, `` tsquery`${term}:*` ``. The literal parts are trusted `tsquery` syntax you write. Each interpolated value becomes exactly one quoted term, so user input cannot add operators or break the syntax; an empty value adds no words, like a stop word. A value with several words becomes a phrase: `` tsquery`${'new y'}:*` `` gives `'new':* <-> 'y':*`, so the words must be adjacent and in order, and `:*` applies to each word. Do not put quotes around the interpolation yourself: `` tsquery`'${term}':*` `` is a syntax error for every input. Postgres `to_tsquery` then lowercases and stems every word. `` tsquery({ language: 'german' })`...` `` picks the configuration. - Operator syntax you write in full: `toTsquery("'zebra' & !'graze'")`. Malformed text fails at execution, so never pass user input to it; use the `tsquery` tag for that. The four parsers are also `fns` members in the SQL builder. Each takes the text (a string, or a text column of any kind, `varchar` included) and `{ language? }`, and binds the text as a parameter. The `tsquery` tag is an import in both the ORM and the SQL builder. A `tsquery` value read back from a query can be passed straight back as the query. Each operation takes an options object as its second argument. `language` (default `'english'`) is the configuration for the column's `to_tsvector`, the expression the index covers; the parser's or tag's own `language` governs the query side, and the two normally match. It only accepts the configurations a stock PostgreSQL server ships with (`'simple'`, `'german'`, `'french'`, …); `fullTextRank` also takes `normalization` (the `ts_rank` bitmask, 0 to 63) and `coverDensity` (for `ts_rank_cd`); `fullTextHeadline` also takes `startSel`, `stopSel`, `maxWords`, `minWords` and `highlightAll`. Every one of them is written into the SQL as a literal, so anything invalid throws `RUNTIME.ARGUMENT_INVALID` when the query is built. ```typescript import { tsquery, websearchToTsquery } from '@prisma/orm-postgres/target/full-text'; // ORM: filter by the search-box query, order by relevance. Build the query once and reuse it. const q = websearchToTsquery(query); const hits = await db.orm.public.Message .select('id', 'text') .where((m) => m.text.fullTextMatches(q)) .orderBy((m) => m.text.fullTextRank(q).desc()) .limit(20) .all(); // ORM: typeahead, the typed text as a prefix term. const suggestions = await db.orm.public.Message .select('id', 'text') .where((m) => m.text.fullTextMatches(tsquery`${term}:*`)) .all(); // SQL builder: the same query filters and highlights, so the snippet marks what selected the row. const snippets = db.sql.public.message .select('id') .select('snippet', (f, fns) => fns.fullTextHeadline(f.text, q, { startSel: '', stopSel: '', maxWords: 20 }), ) .where((f, fns) => fns.fullTextMatches(f.text, q)) .build(); ``` Without an index Postgres recomputes `to_tsvector` for every row, and it only uses one whose expression is the same `to_tsvector` over the same configuration literal and the same column. `@@fullTextIndex` declares that index for you — pass it the field and, if you use one, the same language: ```prisma @@fullTextIndex([text], name: "message_text_search") @@fullTextIndex([text], where: sql`archived_at IS NULL`, name: "message_text_search_live") ``` Give the index and the operation the same `language`: a mismatch raises no error, the query silently falls back to a sequential scan. In a TypeScript contract, the same helper from `@prisma/orm-postgres/contract-builder`: ```typescript model('Message', { fields: { id, text } }).sql(({ cols }) => ({ indexes: [fullTextIndex(cols.text, { name: 'message_text_search' })], })); ``` **Searching several columns with weights.** One index can cover several columns. Each top-level item of the list is a weight group, strongest first; fields in a nested list share a weight: ```prisma @@fullTextIndex([[title, subtitle], body], name: "post_search") ``` **Searching an index in the ORM: `using`.** Every collection has a method under `using` for each full-text index of its model's table, named after the index. It filters to the matching rows and orders them by relevance, best first, and returns the same collection, so it chains like `where`: ```typescript const q = websearchToTsquery(query); const posts = await db.orm.public.Post.select('id', 'title').using.post_search(q).limit(20).all(); const mine = await db.orm.public.Post.where({ userId }).using.post_search(q).all(); const users = await db.orm.public.User .include('posts', (posts) => posts.using.post_search(q).limit(3)) .all(); ``` The relevance order is added where the method is called: an `orderBy` before it sorts first, one after it breaks ties. A custom collection class stays the class, so `this.using.post_search(q)` works in a class method. A model with no full-text index has no full-text methods, and a misspelled name is a type error. With the pgvector extension, `using` also has `_nearest(vector, maxDistance?)` for each vector field, such as `db.orm.public.Post.using.embedding_nearest(v, 0.5)`: nearest by cosine distance first. `using` is reserved: a custom class cannot define its own `using`. **Searching an index by hand: name it.** For anything `using` does not do, such as another order or `fullTextHeadline`, name the full-text index and pass it to `fns.fullTextMatches` and `fns.fullTextRank` in place of a column. The operation searches the document the index was built over, with the index's weight groups and language, so the query always matches the index, Postgres uses it, and a title match ranks above a body match. The index states its language, so passing `language` with one is a type error. An index of another type, or a name the model's table does not have, is a type error too. In the ORM, every `where` and `orderBy` callback receives a second argument `{ fns, indexes }`: `fns` is the SQL builder's functions, and `indexes` holds the indexes of the model's table under the name the contract gave each, `name:` or `map:`. A value from `fns`, such as a rank, has `.asc()` and `.desc()`: ```typescript const q = websearchToTsquery(query); const posts = await db.orm.public.Post .select('id', 'title') .where((_p, { fns, indexes }) => fns.fullTextMatches(indexes.post_search, q)) .orderBy((_p, { fns, indexes }) => fns.fullTextRank(indexes.post_search, q).desc()) .limit(20) .all(); ``` The same argument reaches the callbacks inside an include refinement, a fragment and a relation filter (`some`, `every`, `none`), where the index's columns follow the alias the ORM gives the related table. A condition from `fns` combines with the ORM's `and`, `or` and `not`. The body of a fragment for any model (`db.orm.fragment(fields, body)`) gets `fns` but no indexes. In the SQL builder, a table's `indexes` holds the same references: ```typescript const post = db.sql.public.post; const posts = post .select('id', 'title') .where((_f, fns) => fns.fullTextMatches(post.indexes.post_search, q)) .orderBy((_f, fns) => fns.fullTextRank(post.indexes.post_search, q), { direction: 'desc' }) .build(); ``` An index read from an aliased table searches that alias's columns, so `post.as('p').indexes.post_search` works in a self-join. To search several columns that no index covers, build the document with `fullTextDocument` from `@prisma/orm-postgres/target/full-text`, which takes the weight groups of columns, and pass the language in the options: `fns.fullTextMatches(fullTextDocument([[f.title, f.subtitle], [f.body]]), q, { language: 'english' })`. Postgres uses an index only for a query over the same document, so a document with another grouping, order or language than an index runs without it, and raises no error. `fullTextHeadline` stays per column. One column, `fns.fullTextMatches(f.title, q)` or `row.title.fullTextMatches(q)`, searches that column and uses a single-field index with the same language. **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: ```typescript // 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: ```typescript 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 calls `.asc()` / `.desc()` on a field, an extension-operation result, a to-one relation's field, or a to-many relation's `count(...)`. Every `.asc()` / `.desc()` takes `{ nulls: 'first' | 'last' }`. `.limit(n)` limits; `.offset(n)` offsets. ```typescript 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. `.cursor(...)` and `.distinctOn(...)` require a prior `orderBy` — the type system enforces this on the collection they are called on, so a cast on the argument does not get past it. Direction (forward or backward) follows the sort: ascending order means "greater than the cursor value", descending means "less than". ```typescript 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(); ``` **Ordering by a relation.** A to-one relation (`1:1`, `N:1`) exposes the related model's orderable fields; a to-many relation (`1:N`, `N:M`) exposes `count(predicate?)`, through the junction for `N:M`. One hop only. Each lowers to a correlated scalar subquery, so the main query gains no join. ```typescript await db.orm.public.Post .orderBy([(p) => p.author.name.asc(), (p) => p.id.asc()]) .all(); await db.orm.public.User .orderBy((u) => u.posts.count((p) => p.views.gt(10)).desc()) .all(); await db.orm.public.User .orderBy((u) => u.tags.count().desc()) .all(); await db.orm.public.User .orderBy((u) => u.invitedBy.name.desc({ nulls: 'last' })) .all(); ``` A missing related row (null foreign key) orders as `NULL`. To-one relations have no `count`; to-many relations expose no fields. 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. `cursor()` keys on plain columns only: it throws `ORM.ARGUMENT_INVALID` when an active order is a relation field, a relation `count(...)`, an extension-operation result (`fullTextRank`, vector distance) or sets `nulls`. `distinctOn()` throws the same only when one of its leading orders, as many as there are `distinctOn` columns, is not a plain column; relation, count and operation orders after them are fine. Paginate those orders with `.limit(n).offset(n)`. **`.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('', (branch) => branch.)` 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. ```typescript 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. ```typescript 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 ```typescript // 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. **A write needs a filter on every code path.** `update`, `updateAll`, `updateAndCount`, `delete`, `deleteAll` and `deleteAndCount` compile only on a collection that is known to be filtered. A collection that is filtered on one path and not on another is not: ```typescript const posts = search ? db.orm.public.Post.where({ title: search }) : db.orm.public.Post; await posts.deleteAll(); // error: The 'this' context of type '...' is not assignable to method's 'this' of type 'HasWhere' ``` Make the write only where the filter was applied, or filter on every path: ```typescript if (search) { await db.orm.public.Post.where({ title: search }).deleteAll(); } ``` The same holds for an `if` with an early return, a `switch`, a loop and a reassigned `let`. **A write refuses what it would ignore.** `updateAll`, `updateAndCount`, `deleteAll` and `deleteAndCount` change every row that matches the filter, and throw `ORM.ARGUMENT_INVALID` on a collection with a `limit`, an `offset`, a `cursor`, `distinct` or `distinctOn`; an `orderBy` is accepted. To change a page of rows, read their ids first and filter on them. `update` and `delete` change the one row `first()` returns, so the order, offset, cursor, `distinct` and `distinctOn` choose it; after `limit(0)` they change nothing and return `null`. `update` with a relation callback finds its row by the filter alone and throws on any of these and on an order. ## Workflow — Custom collection classes A custom collection class gives a model its own named queries. Extend `Collection` and pass the class to the client with the `collections` option. `db.orm` then returns the class for that model, and so does `tx.orm` inside `db.transaction(...)`: ```typescript import { Collection, type Filtered, type Ordered, type QueryFragment } from '@prisma/orm-postgres/orm-client'; import postgres from '@prisma/orm-postgres/runtime'; import { contractFromJson } from '@prisma/orm-postgres/static'; import type { Contract } from './prisma/contract.d'; import contractJson from './prisma/contract.json' with { type: 'json' }; class PostCollection extends Collection { byAuthor(userId: string) { return this.where({ userId }); } newestFirst() { return this.orderBy((p) => p.createdAt.desc()); } } export const contract = contractFromJson(contractJson); export const db = postgres({ contract, url: process.env['DATABASE_URL']!, collections: { Post: PostCollection } }); const { Post } = db.orm.public; ``` `postgresServerless({ contract, collections })` takes the same option, and every connection it opens uses the classes. The client's type carries the classes as its second type parameter: `PostgresClient`. A parameter typed `PostgresClient` still accepts a client with classes. Class methods chain with each other and with the built-in methods, in any order, and after `.include(...)`: ```typescript await Post.byAuthor(userId).newestFirst().limit(20).all(); await Post.where({ title }).byAuthor(userId).all(); await Post.include('user').newestFirst().all(); ``` After `.select(...)` or `.variant(...)` the class methods are gone: those return the base `Collection` type. Call class methods before them. Inside an include refinement, the related collection is the base `Collection` type, not its registered class. Inside a class body, a class method called on the result of another call loses what that call established. So a class method whose body chains two class methods loses the first call's facts for every caller: with `latest() { return this.byAuthor(id).newestFirst(); }`, `Post.latest()` is known to be ordered but not filtered. The same holds for `.prepared` after `.include(...)` inside the class: it describes the class's row without the included relation. Inside the class, follow a class method with built-in methods (`this.byAuthor(id).orderBy(...)`), or chain the class methods from outside the class, where they keep every fact. `with(fn)` calls a function with the collection and returns its result. The function is a query fragment, described in Workflow — Query fragments below, of type `QueryFragment`, so a query can be written once and applied to any collection of that class: ```typescript const newest: QueryFragment> = (posts) => posts.newestFirst(); await Post.with(newest).limit(20).all(); ``` What a chain has established is part of its type. Write a filtered collection as `Filtered` and an ordered one as `Ordered`; `Filtered` is `C & HasWhere`, the name error messages print. A function that takes `Filtered` accepts only a collection that is filtered: ```typescript function deleteMatching(posts: Filtered) { return posts.deleteAll(); } await deleteMatching(Post.byAuthor(userId)).toArray(); ``` ## Workflow — Query fragments A piece of a query used in several places is a function. Do not build a filter object and spread it into each query; write a function and pass it to `.where(...)`, `.orderBy(...)` or `.with(...)`. A **row fragment** is a function of the model accessor, and `.where(...)` and `.orderBy(...)` take it. A **query fragment** (a **fragment** for short) is a function from a collection to a collection, and `.with(...)` runs it. A **scope** is a fragment that only imposes conditions on the query, such as `createdSince` below, and is also run with `.with(...)`. When the condition is a function of one row and needs no declared fields, pass a row fragment to `.where(...)`. When the same condition should apply to every model that has some fields, write a scope: it declares the fields, checks them at run time, and returns the collection with the `Filtered` fact. `.with(...)` also runs what `.where(...)` cannot express, such as a shared `select` and `include`, an order, a limit or offset, or a variant. **The same filter on several models.** Define a scope with `db.orm.fragment(fields, body)`; the method is named `fragment` because it cannot know whether the body only filters. Declare each field the fragment needs with the same builder the schema uses, from the `field` and `type` that `@prisma/orm-postgres/contract-builder` exports, adding `.optional()` for a field that may be null. The body sees only those fields: ```typescript import { field, type } from '@prisma/orm-postgres/contract-builder'; const createdSince = (since: Temporal.Instant) => db.orm.fragment({ createdAt: field.column(type.TimestamptzTemporal()) }, (rows) => rows.where((r) => r.createdAt.gte(since)), ); await db.orm.public.User.with(createdSince(since)).all(); await db.orm.public.Post.with(createdSince(since)).deleteAll(); ``` Pick the builder whose codec matches the field's codec in `contract.d.ts`: a PSL `DateTime` is `field.column(type.DateTime())` (`pg/timestamptz-temporal@1`), a `String` is `field.column(type.String())` (`pg/text@1`), a `Uuid` is `field.column(type.Uuid())` (`pg/uuid@1`). `field.column(columnType)` is the explicit form. A package that offers a fragment and does not import the facade writes `{ codecId: 'pg/text@1', nullable: false }`, with a codec of the contract. For a list field such as `String[]`, add `.many()` to the builder or `many: { elementNullable: false }` to the object; for a `String?[]`, whose elements may be null, add `.many({ elementsNullable: true })` or `many: { elementNullable: true }`. Without it the declaration does not match a list field, and with it the element nullability must match too. A list of value objects such as `Address[]` is stored as one `jsonb` value and is still a list: `field.column(jsonbColumn).many()`. The body may call `where`, `orderBy`, `limit` and `offset`. The result keeps the collection's class and records the filter, so `update` and `delete` are allowed after a fragment that filters. `updateAll`, `deleteAll` and their `AndCount` forms change every matching row and throw `ORM.ARGUMENT_INVALID` when the chain has a `limit`, `offset`, `cursor`, `distinct` or `distinctOn`, so do not put a limit in a fragment that will be followed by one of them. `update` and `delete` change the one row `first()` returns, so the order, offset, cursor, `distinct` and `distinctOn` choose it, and after `limit(0)` they change nothing and return `null`; `update` with a relation callback throws on an order, a limit, an offset, a cursor, `distinct` or `distinctOn`, because it finds its row by the filter alone. A model without the field, or with the field under another codec or nullability, is a compile error, and a run-time `ORM.FIELD_UNKNOWN`. A custom collection class carries no namespace in its type, so at compile time it matches only a field that every model of that name has, with the same codec and nullability, and its real namespace is checked at run time. The codec, the nullability, whether the field is a list and the nullability of its elements are compared; the column type's parameters are not, so `field.column(type.Char(36))` (`char(36)`) also matches a `char(10)` field. For a filter used inside a larger `where`, a plain function of the row works too. Type the field with `CodecField`, the type of any field with that codec: ```typescript import { and, type CodecField } from '@prisma/orm-postgres/orm-client'; type CreatedAt = CodecField; const created = (since: Temporal.Instant) => (row: { createdAt: CreatedAt }) => row.createdAt.gte(since); await db.orm.public.Post.where((p) => and(created(since)(p), p.title.ilike('%orm%'))).all(); ``` Both forms check values against the codec's type, not the field's. For a field that narrows its codec's values, such as an enum stored as `pg/text@1`, they accept values the field would refuse: `row.kind.eq('superuser')` compiles even when `kind` has no such member. Write a filter on such a field inline, on the model, where the field's own type checks the value. **The same `select` and `include` in several queries.** Define it once with `.fragment(...)` on the model's root collection, such as `db.orm.public.Post`, run it with `.with(...)`, and name its row with `CollectionRowOf`: ```typescript import type { CollectionRowOf } from '@prisma/orm-postgres/orm-client'; const postSummary = db.orm.public.Post.fragment((posts) => posts.select('id', 'title', 'createdAt').include('tags'), ); type PostSummary = CollectionRowOf>; await db.orm.public.Post.where({ userId }).with(postSummary).limit(20).all(); await db.orm.public.User.include('posts', (posts) => posts.with(postSummary)).all(); ``` The body receives the plain collection of the model, without a custom class's methods. Apply the fragment before `.select(...)` or `.variant(...)`: it is refused on a collection they narrowed. Its result is always typed as the plain collection, even when the body keeps the row, so a custom class's methods are gone after it, and a filter or order applied before it is not recorded, although an earlier `.where(...)` still runs. `update` and `delete` are refused after it unless the body itself calls `where`, and `cursor` unless the body calls `orderBy`. For a filter on one model, use a class method or `db.orm.fragment`. A fragment for one model refuses a collection of another model or namespace at run time with `ORM.ARGUMENT_INVALID`. Calling `.fragment(...)` on a collection with chained calls throws `ORM.ARGUMENT_INVALID`, because the fragment is built from the model alone; a filter, an order or an include before it, or a receiver typed by a type parameter such as `this` in a class method, is a compile error. Inside a class method, chain on `this` directly instead. **A field to order by, from a request.** Pass the request's string to `orderByField` with the fields the endpoint allows. Do not index the field proxy with the raw string: ```typescript import { orderByField } from '@prisma/orm-postgres/orm-client'; await db.orm.public.Post .orderBy(orderByField(db.orm.public.Post, input.orderBy, input.direction, ['title', 'createdAt'])) .limit(20) .all(); ``` Pass the request's direction string as it is; `undefined` means `'asc'`. The allowed list is required: without it a request could order by a secret field and learn its values from the order of the results. A name outside the list, a relation, a field that cannot be ordered, or a direction other than `'asc'` or `'desc'` throws `ORM.ARGUMENT_INVALID` before the query runs; answer it as a bad request. Only the model's own fields can be named, not the fields of one variant. ## Workflow — Aggregates ```typescript 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: | Aggregate | Type | Empty result | | --- | --- | --- | | `count()` | `number` | `0` | | `countBigInt()` | `bigint` | `0n` | | `sum(field)` over `int2` / `int4` / `int8` / `BigIntNumber` | `number \| null` | `null` (SQL `SUM` over zero rows is `NULL`) | | `sumBigInt(field)` over any integer | `bigint \| null` | `null` | | `sum(field)` over `float4` / `float8` | `number \| null` | `null` | | `sum(field)` over `numeric` | decimal `string \| null` | `null` | | `sum(field)` over `UnboundedInt` | `bigint \| null` — that column's own family | `null` | | `avg(field)` over any integer | `number \| null` | `null` | | `avgDecimal(field)` over any integer or `numeric` | decimal `string \| null` | `null` | | `avg(field)` over a float | `number \| null` | `null` | | `min(field)` / `max(field)` | the column's own type `\| null` — `text` where the column is `varchar` | `null` | **`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: ```typescript 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..

`) The concept: `db.sql..

` 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 client. Pick the method by the result the plan declares: `db.query(plan)` for rows (it returns the same `AsyncIterableResult` as `.all()`, so `await` it for an array), `db.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. ```typescript // 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.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` ```typescript // Insert and return selected columns. `insert()` takes an array of rows. const plan = db.sql.public.user .insert([{ email, displayName, kind }]) .returning('id', 'email') .build(); const [row] = await db.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.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.execute(deletePlan); ``` Each row must include every column that is not nullable and has no default; leaving one out is a type error that names the column. A value typed `T | undefined` for such a column is also an error, so narrow it first. `.returning(...)` requires the target adapter to advertise the `returning` capability. The Postgres adapter advertises it by default. ### Computed projections and joins ```typescript // 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.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(); ``` The SQL builder's `.orderBy(column, { direction, nulls })` takes the same null placement: `.orderBy('invited_by_id', { direction: 'asc', nulls: 'first' })` renders `ORDER BY "invited_by_id" ASC NULLS FIRST`. ## Workflow — Transactions The concept: `db.transaction(fn)` opens a transaction and passes a `tx` context to the callback. `tx` offers the client's query members with the same types, and each runs inside the transaction: `sql`, `orm` (with the client's custom collection classes), `raw`, `prepare`, `query(plan)` (rows) and `execute(plan)` (affected count), plus `toSql`, `enums` and `nativeEnums`. A prepared statement runs on what it is given, so `ps.query(tx, params)` runs inside the transaction. See *Workflow — Transactions* in `references/runtime.md` for raw SQL and `tx.prepare`. The transaction commits on the callback's successful return and rolls back on any thrown error. ```typescript 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. To read a row and then write it without another transaction changing it in between, lock it when you read it. The lock lasts until the transaction ends; outside a transaction it is released when the statement ends. ```typescript await db.transaction(async (tx) => { // ORM: locks only the model's own rows (`FOR UPDATE OF "product"`). const product = await tx.orm.public.Product.where({ id }).forUpdate().first(); // SQL builder: the same lock on a plan. const [row] = await tx.query( tx.sql.public.product.select('id', 'stock').where((f, fns) => fns.eq(f.id, id)).forUpdate().build(), ); // Work queue: claim the next unclaimed job, skipping jobs other workers hold. const job = await tx.orm.public.Job.where({ state: 'queued' }) .orderBy((j) => j.createdAt.asc()) .forUpdate({ skipLocked: true }) .first(); }); ``` The four methods are `forUpdate()`, `forNoKeyUpdate()`, `forShare()` and `forKeyShare()`, each with `{ nowait: true }` or `{ skipLocked: true }`. On the ORM a lock cannot be combined with `include`, `groupBy`, `aggregate`, `distinct`, `distinctOn` or a mutation terminal (`ORM.LOCK_INCOMPATIBLE`). ## 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`: ```typescript // db.sql..

`. 13. **Locking a parent row with `forUpdate()` while other transactions write rows that reference it.** Every insert or update of a row with a foreign key to the locked row takes `FOR KEY SHARE` on it to check the key, and `FOR KEY SHARE` conflicts with `FOR UPDATE`, so those writes wait and can deadlock. Use `forNoKeyUpdate()`: it still blocks writes to the row, but not the foreign-key check. ## Reference Files - Example queries under [`examples/prisma-8-demo/src/orm-client/`](examples/prisma-8-demo/src/orm-client/) and [`examples/prisma-8-demo/src/queries/`](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-family/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`. - [ ] Wrote a query piece shared between places as a function: a query fragment from `db.orm.fragment(fields, body)` or `collection.fragment(body)` run with `.with(...)`, a `where` callback typed with `CodecField`, or `orderByField` for an order parameter from a request. - [ ] Ran SQL-builder plans via `db.query(plan)` when they return rows and `db.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..

` 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`.