skip to content

In Prisma Client, how do select, include and omit shape a result, and why can select and include not share a level?

level: middleimportance: must knowfreq 80%

answer

  1. the default: scalars, no relations
  2. whitelist, add relations, blacklist
  3. contradictory shapes at one level
  4. nest one inside the other
  5. one statement per relation

basics

~20 s

By default Prisma Client returns all scalar fields and no relations. include adds relations to that default, select returns only what it lists, and omit removes listed scalars. select cannot share a level with include or omit; nest them instead.

solid answer

~50 s

A query with no shaping option returns every scalar column and no relation fields. `include: { reviews: true }` keeps that default and adds the relation; `select` switches to a whitelist, so only the named scalars and relations come back, and a relation inside `select` can take its own nested `select`, `include`, `where`, `orderBy` and `take`. `omit` is the inverse of `select`: the default minus a few scalars, per query or globally on the `PrismaClient` constructor. Putting `select` beside `include`, or beside `omit`, at one level fails in the generated types and at runtime with a `PrismaClientValidationError`, because the two describe contradictory shapes; you say "these scalars plus that relation" by listing the relation inside `select`. With default relation loading, each relation costs one more statement, an `IN (...)` query over the parent keys, unless the `relationJoins` preview feature and `relationLoadStrategy: "join"` fold them into one.

code

typescript · 12 lines
typescript
const cards = await prisma.product.findMany({
  where: { inStock: true },
  take: 20,
  select: {
    id: true,
    name: true,
    priceCents: true,
    category: { select: { name: true } },
    reviews: { select: { rating: true }, orderBy: { id: "desc" }, take: 3 },
    _count: { select: { reviews: true } },
  },
});

go deeper

for a junior

Recall the default shape, all scalars and no relations, and which option adds relations, which whitelists and which removes fields.

for a middle

Explain why select and include conflict at one level, how nesting expresses scalars plus relations, and how a global omit works with a per-query override.

for a senior

Count the statements: one per relation with default loading, nested take trimmed in memory, _count folded into the parent, and when the join strategy is worth enabling.

for a principal

Decide a house rule for response shaping, such as select for every API response so columns added later never leak, and global omit for sensitive fields.

## The default result When a query passes none of `select`, `include` or `omit`, Prisma Client returns **every scalar field** of the model and **no relation fields**. `prisma.product.findMany()` gives `id`, `sku`, `name`, `priceCents`, `costCents`, `inStock`, `createdAt` and `categoryId`, but not `category` or `reviews`. The three options move away from that default in different directions. | Option | Starts from | Then | Typical use | |---|---|---|---| | `include` | all scalars | adds the named relations, each with all its scalars | a product page with its category | | `select` | nothing | returns only the named scalars, relations and `_count` | an API card with five fields | | `omit` | all scalars | removes the named scalars | everything except `costCents` | ## `include`: the default plus relations `include: { category: true, reviews: true }` keeps every product column and attaches `category` as an object and `reviews` as an array. A relation entry may be an object instead of `true`, carrying its own `where`, `orderBy`, `take`, `select`, `include` or `omit`: - `include: { reviews: { where: { rating: { gte: 4 } } } }` loads only good reviews; - `include: { _count: { select: { reviews: true } } }` adds a review count without loading the reviews. `include` never narrows anything: the parent still returns all its scalars. ## `select`: a whitelist `select` switches the query to an explicit list. Only what you name comes back, and a relation named inside `select` joins the result with its own nested shape. That is how you ask for "these scalars plus that relation": `select: { name: true, category: { select: { name: true } } }`. Prisma still reads the key columns it needs to attach relations, such as `id` and `categoryId`, but does not return the ones you did not select. The generated TypeScript type of the result follows the selection, so reading `product.costCents` is a compile error when it was not selected. ## `omit`: the default minus a few fields `omit: { costCents: true }` returns the default shape without that column, and the column is left out of the SQL. `omit` can also be set once on the client: - `new PrismaClient({ adapter, omit: { product: { costCents: true } } })` hides the column from every product query; - a query that needs it back passes `omit: { costCents: false }` or names it in `select`. `omit` applies to scalar fields; relations are already absent from the default. ## Why `select` cannot sit beside `include` or `omit` The options describe contradictory shapes at one level. `include` means "all scalars plus these relations" and `select` means "only these". Prisma Client refuses to guess, at two points: 1. **Types.** The generated argument type turns the object into an error message, ``'Please either choose `select` or `include`.'``, so the call does not compile. 2. **Runtime.** Untyped code throws a `PrismaClientValidationError` ending "Please either use `include` or `select`, but not both at the same time." The same rule, with its own message, covers `select` beside `omit`. The rule is per level. A top-level `select` may contain a relation with an `include` inside it, and a top-level `include` may give a relation a nested `select`. When you want chosen scalars and relations together, move the relations into `select`. ## What each shape costs in statements Without the `relationJoins` preview feature, Prisma Client loads relations with separate statements rather than joins: 1. One statement reads the parent rows and their columns. 2. Each relation in the shape adds one statement that reads related rows with `WHERE "productId" IN (...)` over the parent keys; Prisma stitches the results in memory. `include: { category: true, reviews: true }` sends three statements however many products come back. 3. A nested `take` or `skip` on a list relation is applied per parent in memory. The related-rows statement carries no `LIMIT`, so `reviews: { take: 3 }` reads every review of the fetched products before trimming to three each. 4. `_count` is folded into the parent statement as a grouped subquery. With `previewFeatures = ["relationJoins"]` in the generator block, `relationLoadStrategy: "join"` fetches parents and relations in one statement, through a lateral join on PostgreSQL. Independently of strategy, `select` narrows the columns in every statement, which is what reduces the bytes crossing the wire; `include` never narrows. Why loading relations row by row multiplies statements is covered in the ORM-concepts lesson on the N+1 trap.

  • How do you hide a column such as costCents from every product query but still read it in one admin report?
    Configure a global `omit` on the client, `new PrismaClient({ adapter, omit: { product: { costCents: true } } })`, so every product query leaves the column out of its SQL. The admin query re-enables it with `omit: { costCents: false }` or names it in a `select`. The generated result types follow both settings.
  • Why does reviews: { take: 3 } inside a select not make the reviews query cheap?
    With default relation loading, Prisma reads related rows in one `IN (...)` statement over all fetched parents, and a per-parent limit cannot be expressed there, so the statement carries no `LIMIT`. Every matching review is read and Prisma keeps three per product in memory. A `where` on the relation still filters in SQL.
  • When would you turn on relationLoadStrategy: "join"?
    When round trips dominate, for example a deep `include` over a remote database, since it fetches parents and relations in one statement. It needs the `relationJoins` preview feature in the generator block and a fresh `prisma generate`, and it is set once at the top level of a query, applying to all nested relations.

  • include returns only the relations you list, not the parent's own fields.
  • select and include can be combined at one level to mean scalars plus relations.
  • Prisma Client returns relations by default, so include only narrows them.
  • A nested take on a relation adds a LIMIT to the related-rows query.
  • include loads each relation with one query per parent row.