Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

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

Was this page helpful?