Selecting Fields
Control which fields are returned from queries using select and include
select
Return only specified fields:
const users = await client.user.findMany({
select: {
id: true,
email: true,
name: true,
},
});
// Type: { id: string; email: string; name: string }[]
include
Return all scalars plus specified relations:
const users = await client.user.findMany({
include: {
posts: true,
profile: true,
},
});
// Type: { id: string; email: string; ...; posts: Post[]; profile: Profile | null }[]
omit
omit is the inverse of select — every readable scalar except the ones you
name. It composes with include, and it can subtract scalar fields from an
explicit select:
const users = await client.user.findMany({
select: { id: true, email: true, passwordHash: true },
omit: { passwordHash: true },
});
// { id: string; email: string }[]
See Omitting fields for the full surface, including the
defaultOmit() client default and how the three layers of omission rank.
select vs include
| Aspect | select |
include |
|---|---|---|
| Base scalars | Only selected | All scalars |
| Relations | Must select | Add to base |
| Default | Nothing selected | All scalars |
Nested Selection
Select fields within relations:
const users = await client.user.findMany({
select: {
id: true,
email: true,
posts: {
select: {
id: true,
title: true,
},
},
},
});
// Type: { id: string; email: string; posts: { id: string; title: string }[] }[]
Nested Include
Include relations within relations:
const users = await client.user.findMany({
include: {
posts: {
include: {
comments: true,
},
},
},
});
Mixing select and include
Use select within include:
const users = await client.user.findMany({
include: {
posts: {
select: {
id: true,
title: true,
},
},
},
});
Use include within select:
const users = await client.user.findMany({
select: {
id: true,
email: true,
posts: {
include: {
tags: true,
},
},
},
});
Recursive relations
A self-relation — a model that relates to itself — can be projected
recursively with recurse on its relation node. The node’s projection is
repeated at every level, so a tree, a chain or a graph comes back as one
nested value from one provider statement, whatever its depth.
const tree = await client.category.findUnique({
where: { id: "root" },
select: {
name: true,
children: {
recurse: { depth: 3 },
where: { published: true },
orderBy: { position: "asc" },
select: { name: true },
},
},
});
// Type: { name: string; children: { name: string; children?: … }[] } | null
recurse is admitted on an ordinary self-relation whose target has a complete
primary key: a foreign-key owning side (parent), its proven one-to-one
inverse, a child-held collection (children), or a paired junction graph
(friends through a join table). Bulk createMany/updateMany/deleteMany
results, count, exist, aggregate and groupBy do not take it.
| Form | Meaning |
|---|---|
recurse: true or recurse: {} |
follow the relation to depth 100 |
recurse: { depth: n } |
follow it n levels, 1 ≤ n ≤ 1000 |
recurse: { depth: false } |
exhaustive: follow it until the data ends, within the output budget |
recurse: { preventCycles: false } |
graph relations only, with a bounded depth: unfold a cycle instead of pruning it |
recurse: { maxOccurrences: n } |
decode at most n occurrences under each outer row instead of 10,000; combines with the forms above |
recurse: { fewRows: true } |
you state that each outer row’s walk reaches few rows of its table; on PostgreSQL its rows are then read by key (see Few rows per walk below); combines with the forms above |
At a numeric cutoff the repeated key is absent from the deepest occurrence
(that is why it is optional in the type); where the data ends before the
cutoff the key is present and holds [] for a collection or null for a
nullable singular. With depth: false the key is always present. A foreign-key
cycle inside the traversed window is refused with an
UnsupportedOperationError (V8003) naming the relation
(Recursive relation 'parent' contains a cycle.); this is a property of the
data, not an engine defect; a graph cycle is pruned path-locally by default.
Filters prune every hop, each level keeps its own orderBy, and the node’s
select/include/omit — including other relations and another recursive
slot — repeat at every level. Results compose with the cache, omit,
extensions, arrays and callback transactions like any other relation
projection. A provider may cap an exhaustive traversal with its own
recursion limit. MySQL’s cte_max_recursion_depth defaults to 1,000. That
failure surfaces as the driver’s error, never as a truncated result. On
MySQL the error has meta.providerErrno 3636 (ER_CTE_MAX_RECURSION_DEPTH),
and the provider’s message is redacted.
Few rows per walk
Performance opt-ins shows the SQL and the plans with and without it.
recurse: { fewRows: true } states a fact about your data that the schema
cannot: each row’s walk reaches few rows of its table, such as a document’s
folder chain, a comment’s thread above it, or two levels of a large tree.
// Each category with its ancestors: a chain of a few rows per category.
const categories = await client.category.findMany({
take: 1000,
select: {
name: true,
parent: { recurse: { depth: false, fewRows: true }, select: { name: true } },
},
});
On PostgreSQL (pg, postgres, PGlite, Neon and every other PostgreSQL
driver) each row the walk reaches is then read by its primary key, instead
of joining the table to the walk. PostgreSQL guesses that a recursive walk
reaches ten rows a level, so for a long list that join may scan the whole
table for every listed row; read by key, each row costs one index probe.
PostgreSQL 18, a 5,000-row tree:
| List | without | fewRows: true |
|---|---|---|
| 1,040 rows with their ancestors | 129 ms | 13 ms |
| all 5,000 rows with their ancestors | 558 ms | 68 ms |
| 1,040 rows with two levels of descendants | 164 ms | 89 ms |
| 5,000 rows with their ancestors’ 4 KiB documents | 696 ms | 51 ms |
| a governed 100-row page of a 10,000-row tree, with ancestors | 6.3 ms | 1.7 ms |
On PGlite such lists are 4–20× faster.
State it only when it is true. On a walk that reaches most of its table,
one probe per reached row is slower than the join. The 9,999 descendants
of the root of a 10,000-row tree took 22 → 27 ms (1.21×) on PostgreSQL 18,
and 1.18–1.22× on PGlite; the same walk ordered by a relation, 1.14×; wide
8 KiB rows on PGlite, 1.10–1.12×. Where PostgreSQL already probes the
index (pages of 10 to 1,000 rows of a 100,000-row tree), it typically
changes little (1–2 % either way): benchmark your projection. The read by
key also raises the planner’s cost estimate a little; on a server with JIT
enabled, a statement near jit_above_cost may cross it and pay JIT
compilation, which these measurements (a server without JIT) do not show.
SQLite (and D1) and MySQL accept fewRows and ignore it: they already join
by index, and the read by key measured 3–17 % slower there. The values,
their order and the statement count are the same with or without it, on
every database. Without fewRows, nothing is guessed.
The same recurse options, except maxOccurrences and fewRows, filter in where:
{ parent: { recurse: true, some: { … } } } matches a row when an ancestor
matches. A filter reads the closure as a set, so it never refuses a
foreign-key cycle. See
Recursive filters.
Output grows with paths, not rows: a graph can return exponentially more
occurrences than it stores, even with cycle prevention, so depth 100 is not a
size bound. The size bound is the occurrence budget: one recursive
relation decodes at most 10,000 occurrences under each outer row, or
maxOccurrences when the node sets it. It counts output rows, one per path,
not distinct nodes, so a node reached by three paths counts three times, and
depth: false is exhaustive only within it. The rows still travel in one
statement; past the budget the call is refused with an
UnsupportedOperationError (V8003) whose message names the operation that
ran and whose meta.feature is recursive output budget, never a truncated
result. Raise maxOccurrences, lower depth or filter the relation to stay
inside it. take, skip, cursor and distinct cannot be combined with
recurse, and a singular recursive node accepts only select, include and
omit. recurse: false is refused; leave recurse out for an ordinary node.
The recursive key cannot be spelled again inside its own node’s select or
include, because recurse already produces it. A recursive node may
select a point’s distance; it is returned as the node’s _distance, a name no
relation can take (reserved member names).
A node filter must be deterministic across the
traversal: a volatile raw expression (such as random()) can make the
provider’s answer disagree with itself, which is refused as an invalid
provider result rather than decoded. The operation then rejects with the
QueryEngineError (V9001) of a malformed driver answer, such as
Driver "…" returned a malformed recursive depth scalar for operation "…": ….,
and its meta.scalarType names the kind of check that refused the answer;
inside a $transaction([...]) array on a batch-only driver (D1, neon-http)
the failure surfaces instead as QueryError (V2001), as it does for an
ordinary malformed member.
Filtering Relations
Apply filters to included relations:
const users = await client.user.findMany({
include: {
posts: {
where: { published: true },
orderBy: { createdAt: "desc" },
take: 5,
},
},
});
Count Relations
Get relation counts:
const users = await client.user.findMany({
include: {
_count: {
select: {
posts: true,
followers: true,
},
},
},
});
// user._count.posts: number
// user._count.followers: number
_count means relation counts and nothing else: a model member cannot be
named _count (see Reserved member names).
Examples
Public Profile
async function getPublicProfile(userId: string) {
return client.user.findUnique({
where: { id: userId },
select: {
id: true,
name: true,
bio: true,
avatarUrl: true,
createdAt: true,
posts: {
where: { published: true },
select: {
id: true,
title: true,
createdAt: true,
},
orderBy: { createdAt: "desc" },
take: 10,
},
_count: {
select: { posts: true, followers: true },
},
},
});
}
API Response
async function getPostForAPI(postId: string) {
const post = await client.post.findUnique({
where: { id: postId },
select: {
id: true,
title: true,
content: true,
createdAt: true,
author: {
select: {
id: true,
name: true,
avatarUrl: true,
},
},
tags: {
select: { name: true },
},
_count: {
select: { comments: true, likes: true },
},
},
});
return post;
}
Type Safety
Return types are automatically inferred:
// Full record
const user = await client.user.findUnique({ where: { id } });
// Type: User | null
// Selected fields
const user = await client.user.findUnique({
where: { id },
select: { id: true, email: true },
});
// Type: { id: string; email: string } | null
// With relations
const user = await client.user.findUnique({
where: { id },
include: { posts: true },
});
// Type: (User & { posts: Post[] }) | null