Prisma Next — Queries
Edit your data contract. Prisma handles the rest.
Once the contract is emitted and the DB is up to date, this skill covers everything you do with the data: reading, writing, eager-loading relations, aggregating, and the choice between the ORM and the lower-level query lane.
When to Use
- User wants to read, write, update, or delete data.
- User wants to include / eager-load relations.
- User wants to paginate, sort, filter, project.
- User wants to wrap operations in a transaction (
db.transaction(...) — Postgres and SQLite).
- User wants to aggregate (
count, sum, avg, …).
- User asks about query lanes (ORM vs SQL builder / query builder).
- User mentions: query, select, where, orderBy, take, skip, include, eager load, first, all, count, aggregate, create, update, delete, upsert, returning, drizzle-style, kysely-style, prisma client.
When Not to Use
- User wants to add / change a model →
prisma-next-contract.
- User wants to wire
db.ts or add middleware → prisma-next-runtime.
- User wants to debug a query failure (structured error envelope) →
prisma-next-debug.
Pick your target
Prisma Next ships two query lanes per target on the same db value from src/prisma/db.ts. Before writing queries, read db.ts and load the matching target guide:
Runtime import in db.ts | Load |
|---|
@prisma-next/postgres/runtime | postgres.md — db.orm.<Model> + db.sql.<table> |
@prisma-next/mongo/runtime | mongo.md — db.orm.<root> + db.query.from(...) |
Both targets share the contract and connection on one db value. Reach for the ORM first; drop to the lower-level lane when the ORM can't express the shape. Lane choice is local — one query function picks one lane, not the whole app.
Do not mix target examples. Postgres uses PascalCase model roots (db.orm.User) and db.sql.user; Mongo uses lowercased plural roots (db.orm.users) and db.query.from('users'). There is no db.sql on Mongo and no db.query SQL-builder equivalent on Postgres.
Namespace-aware accessors
When a contract declares more than one namespace (e.g. public and auth), models and tables are addressed by namespace coordinate:
- ORM:
db.orm.<namespace>.<Model> — e.g. db.orm.public.User, db.orm.auth.User
- SQL builder:
db.sql.<namespace>.<table> — e.g. db.sql.public.users, db.sql.auth.users
The flat db.orm.User / db.sql.users form still works for single-namespace contracts (or when all table names are unique across namespaces). When the same bare name appears in more than one namespace, you must use the namespace coordinate.
See postgres.md § Namespace-aware accessors for a worked example.
Consuming the result: await, .toArray(), or for await
Critical to get right early — on both Postgres and Mongo, .all() returns an AsyncIterableResult<Row>, which is both a PromiseLike<Row[]> and an AsyncIterable<Row>. That means three consumption forms all work, and the canonical one is the shortest:
const users = await db.orm.User.select('id', 'email').all();
// ^? Row[] ← the Thenable resolves to a real array. This is the default idiom.
You do not need a collect() / toArray() helper — await is enough. Internally await invokes the result's then(...), which buffers the rows into an array. Two equivalent alternatives exist for the cases where they read better:
// Explicit buffering — same outcome as `await ... .all()`, useful when you
// want a named Promise<Row[]> to thread through downstream code.
const rows: Promise<User[]> = db.orm.User.select('id', 'email').all().toArray();
// Streaming — process rows one at a time without buffering the whole result.
// Use for genuinely large result sets (anything that wouldn't fit comfortably
// in memory) or pipelines where you can start work before all rows arrive.
for await (const user of db.orm.User.select('id', 'email').all()) {
process(user);
}
Two single-row shortcuts also exist on the result, in addition to the collection-level .first() (which issues LIMIT 1 on Postgres):
const user = await db.orm.User.where({ id }).all().first();
// ^? Row | null ← buffers, returns the first row or null. Issues no LIMIT.
const required = await db.orm.User.where({ id }).all().firstOrThrow();
// ^? Row ← buffers; throws `RUNTIME.NO_ROWS` if empty.
For genuine single-row reads, prefer the collection-level .first() (which adds LIMIT 1 to the SQL on Postgres) over .all().first() (which fetches all rows and discards the rest). The result-level helpers are for cases where you already need the full result and want the first row without an extra round-trip.
The result is single-consumption. Each AsyncIterableResult instance can be consumed once — by await, by .toArray(), or by for await. Trying to consume it a second time throws RUNTIME.ITERATOR_CONSUMED. The fix is almost always to store the array in a variable on first consumption and reuse the variable:
// Bad — second await throws RUNTIME.ITERATOR_CONSUMED.
const result = db.orm.User.select('id', 'email').all();
const a = await result;
const b = await result;
// Good — buffer once, reuse the array.
const users = await db.orm.User.select('id', 'email').all();
const a = users;
const b = users;
If you've seen collect(...) / toArray(...) helpers in a codebase wrapping .all(), they're vestigial — await does the same thing for free. Remove them when you touch the surrounding code.
Running queries from a short script
When the user is running a one-off tsx my-script.ts (not a long-lived server), call await db.close() at the end so the process exits cleanly — on Postgres the façade-owned pool keeps Node's event loop alive; on Mongo the façade-owned MongoClient does the same. See prisma-next-runtime § Running as a script (teardown) for the full pattern including await using.
// src/scripts/seed.ts
import { db } from '../prisma/db';
// Postgres — PascalCase model root from contract
for (const u of users) {
await db.orm.User.create(u);
}
// Mongo — lowercased plural root from contract (e.g. users, not User)
// for (const u of users) {
// await db.orm.users.create(u);
// }
console.log('Seeded.');
await db.close();
Common Pitfalls (cross-target)
- Using Postgres examples on a Mongo project (or vice versa). Check
db.ts and load the correct target guide (postgres.md or mongo.md).
- Writing a
collect() / toArray() helper to convert .all() to an array. .all() returns an AsyncIterableResult<Row> which is a PromiseLike<Row[]> — await collection.all() directly yields Row[]. See Consuming the result above.
- Consuming an
AsyncIterableResult twice. Each result is single-use. The second consumer throws RUNTIME.ITERATOR_CONSUMED. Buffer once into a variable and reuse the variable.
Target-specific pitfalls live in the per-target guides.
What Prisma Next doesn't do yet
- N:M
.include() across a junction table. The contract IR supports many-to-many relations with a through junction table, and N:M relations appear as valid relation names on the ORM collection. However, .include() on an N:M relation does not emit the two-step junction join — the query plan builder only handles the direct join columns (localColumn / targetColumn) and ignores the through metadata. Attempting it either produces wrong results or an error. Workaround: express the N:M traversal through db.sql.<table> with an explicit join on the junction table.
- N:M nested mutations.
mutation-executor.ts explicitly throws 'N:M nested mutations are not supported yet' for nested creates/links through an N:M relation.
and / or / not combinators in the postgres façade. The combinators currently import from @prisma-next/sql-orm-client (an internal package). Workaround today: import them from @prisma-next/sql-orm-client directly, the way the example apps do. If you want them on @prisma-next/postgres/runtime, file a feature request via prisma-next-feedback..orderBy(...) / .take(...) on grouped aggregates (Postgres). db.orm.<Model>.groupBy(...).aggregate(...) materializes a Promise<Array<Group & Aggregates>> and exposes neither ordering nor row limits at the DB layer. Result: a "top-N groups by SUM" query falls back to JS-side sort + slice over the full grouped result, which is fine at small cardinalities and bad at scale. Workarounds: (a) drop to db.sql.<table> and write the GROUP BY + ORDER BY + LIMIT against the aggregated table directly; (b) live with the JS-side sort/slice if the grouped cardinality is bounded. File a feature request via prisma-next-feedback if this is hitting you in production.- A raw-SQL lane. Prisma Next does not currently expose a user-facing raw-SQL surface (no
db.sql.raw(...)). Workaround: model the query through the SQL builder or — for shapes the builder can't yet express — file a feature request via prisma-next-feedback describing the shape so the team can decide whether to grow the builder or ship a raw lane.
- TypedSQL (
.sql files compiled into typed callables). Not implemented. Workaround: stick to the SQL builder; for repeated queries, extract a function that returns the built plan and call db.runtime().execute(plan) at the call site. If you want a .sql-file compile path, file a feature request via prisma-next-feedback.
EXPLAIN / query-plan inspection. Prisma Next does not expose an .explain() method. Workaround: connect a pg.Pool you control via the runtime's pg: binding (see prisma-next-runtime) and issue EXPLAIN ANALYZE through it. If you want a first-class plan-inspection surface, file a feature request via prisma-next-feedback.- Streaming large result sets. No
.stream() cursor today. Workaround: paginate via .skip(n).take(m) for moderate sizes; for very large sets, hold a pg.Client from the runtime's pg: binding and stream through it directly. If you want a built-in streaming surface, file a feature request via prisma-next-feedback.
- Multi-statement batching (Prisma-7-style
db.$transaction([call1, call2])). Prisma Next runs each call sequentially. Workaround: wrap atomically-related work in db.transaction(async (tx) => { ... }) on Postgres. If you want batch-as-array semantics, file a feature request via prisma-next-feedback.
- Mongo façade transactions.
@prisma-next/mongo/runtime does not expose db.transaction(...). Multi-document atomicity is not yet wrapped in the Prisma Next Mongo façade. Workaround: use the MongoDB driver's session API directly if you control the client binding (mongoClient: option). File a feature request via prisma-next-feedback if you need a first-class façade surface.
- Mongo ORM aggregates. No
.aggregate(...) / .groupBy(...) on db.orm.<root>. Workaround: express aggregations through db.query.from(...).group(...).build() and runtime.execute(plan).
- Mongo filter helpers on the façade. Rich filters (
.in, ranges, boolean composition) currently import from @prisma-next/mongo-query-ast/execution (MongoFieldFilter, etc.) — not yet re-exported on @prisma-next/mongo/runtime. Workaround: use object equality .where({ field: value }) where possible; import from the internal package only when necessary. Tracked alongside façade-completeness gaps in Linear TML-2526.
- Automatic N+1 detection. Prisma Next does not warn when an
.include(...) is missing. Workaround: be deliberate about .include(...) in code review; the lints middleware (see prisma-next-runtime) catches the more common authoring slips (missing WHERE on a DELETE / UPDATE, missing LIMIT on a SELECT).
Reference Files
This skill is split for selective loading. Target-specific reference paths live in the per-target guides:
Checklist