In Prisma Client, how do select, include and omit shape a result, and why can select and include not share a level?
answer
- the default: scalars, no relations
- whitelist, add relations, blacklist
- contradictory shapes at one level
- nest one inside the other
- one statement per relation
basics
~20 sBy 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 sA 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 linesconst 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
Recall the default shape, all scalars and no relations, and which option adds relations, which whitelists and which removes fields.
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.
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.
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.