Prisma Next Queries

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 is querying through a Supabase role-bound db (asUser / asAnon / asServiceRole, RLS, auth.* admin reads) → prisma-next-supabase for the role-binding surface; everything in this skill then applies to the returned RoleBoundDb.
• 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:

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:

// `.toArray()` returns a genuine `Promise<Row[]>`. Reach for it only when
// something needs a real `Promise` and not merely a thenable: a slot typed
// `Promise<Row[]>` (an `AsyncIterableResult` has only `then`, not `catch` /
// `finally`, so it does not satisfy that annotation), or a runtime
// `instanceof Promise` check. Note that `await` and the `Promise.all` /
// `Promise.race` combinators all accept the thenable directly — those are
// NOT reasons to call `.toArray()`. Whenever you are just going to await it
// here, use `await ...all()` and skip `.toArray()`.
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)

1. Using Postgres examples on a Mongo project (or vice versa). Check db.ts and load the correct target guide (`postgres.md` or `mongo.md`).
2. 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.