skip to content

Generated Client & Queries

The generated query API: findUnique, findMany, create, update, upsert and delete, plus filtering, ordering, offset and cursor pagination, and shaping results with select and include. Interviewers ask because these calls decide how many SQL statements actually run and how much data crosses the wire.

on this pageshow

explore

questions

6

In Prisma Client, how do findUnique, findFirst and findMany differ, and what do their OrThrow variants change?

level: juniorimportance: must knowfreq 75%

answer

  1. unique key versus any filter
  2. object, object, or array
  3. what an empty result looks like
  4. a known error code instead of null

basics

~20 s

findUnique reads one row by id or unique key, returning it or null; findFirst returns the first row matching any filter and sort, or null; findMany returns an array, empty if nothing matches. The OrThrow forms throw P2025 instead of null.

solid answer

~40 s

`findUnique` takes a `where` typed as `WhereUniqueInput`: it must name the `@id`, a `@unique` field or a compound `@@unique`/`@@id` key, may add non-unique conditions beside it, and resolves to the record or `null`. `findFirst` accepts any `where` plus `orderBy`, `skip` and `take`, runs like `findMany` limited to one row, and also resolves to the record or `null`; without an `orderBy`, "first" is whatever order the database returns. `findMany` resolves to an array and gives `[]` when nothing matches, never `null`. `findUniqueOrThrow` and `findFirstOrThrow` throw a `PrismaClientKnownRequestError` with code `P2025` when no row matches, and their return type drops `null`. One cost difference matters in resolvers: `findUnique` calls issued in the same tick with the same shape are batched into one `IN (...)` statement, while `findFirst` calls are not.

code

typescript · 30 lines
typescript
import { PrismaClient, Prisma } from "./generated/prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";

const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
});

// By a @unique field: Product | null
const bySku = await prisma.product.findUnique({ where: { sku: "LAMP-001" } });

// Any filter plus a sort: Product | null
const cheapestLamp = await prisma.product.findFirst({
  where: { name: { contains: "lamp", mode: "insensitive" } },
  orderBy: { priceCents: "asc" },
});

// Always an array: [] when nothing matches
const inStock = await prisma.product.findMany({ where: { inStock: true }, take: 50 });

// Throws instead of returning null
async function getProduct(id: number) {
  try {
    return await prisma.product.findUniqueOrThrow({ where: { id } });
  } catch (e) {
    if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === "P2025") {
      return null; // map to a 404 in the HTTP layer
    }
    throw e;
  }
}

go deeper

for a junior

Recall the return shapes: record or null, record or null, array. Know that findUnique needs an id or unique key and that the OrThrow forms throw P2025.

for a middle

Explain WhereUniqueInput, including extra non-unique conditions beside the key, why findFirst needs an orderBy to be deterministic, and how a negative take reverses it.

for a senior

Talk about cost and errors: same-tick findUnique batching, the lazy PrismaPromise, and one handler mapping P2025 to a 404 instead of null checks spread through the code.

for a principal

Frame a team convention: OrThrow for lookups that must succeed, nullable reads where absence is normal, and a take on every list endpoint so error handling and result size stay predictable.

## Three read methods, three return shapes Prisma Client generates one **delegate** per model (`prisma.product`, `prisma.review`), and each delegate carries the same everyday read methods. They differ in what the `where` argument may contain and in what comes back when nothing matches. | Method | `where` type | Resolves to | When nothing matches | |---|---|---|---| | `findUnique` | `ProductWhereUniqueInput`, required | one record | `null` | | `findFirst` | `ProductWhereInput`, optional | one record | `null` | | `findMany` | `ProductWhereInput`, optional | an array | `[]` | | `findUniqueOrThrow` | as `findUnique` | one record | throws `P2025` | | `findFirstOrThrow` | as `findFirst` | one record | throws `P2025` | All of them accept `select`, `include` and `omit` to shape the result. `findFirst` takes the same query options as `findMany` (`orderBy`, `cursor`, `skip`, `take`, `distinct`), because it calls `findMany` behind the scenes. ## `findUnique`: a key, or nothing `findUnique` exists for lookups by identity. Its `where` is a **`WhereUniqueInput`**, which is only valid when it names one of the model's unique keys: - the `@id` field, for example `{ id: 42 }`; - any `@unique` field, for example `{ sku: "LAMP-001" }`; - a compound `@@unique` or `@@id`, addressed through its generated name, which by default joins the field names with an underscore (`{ categoryId_slug: { categoryId: 3, slug: "desk-lamp" } }`). The generated type exposes the other fields too, so non-unique conditions may sit beside the key: `{ id: 42, inStock: true }` returns the product only if it is also in stock. At least one unique field must appear outside `AND`, `OR` and `NOT`, so `{ OR: [{ sku: "A" }, { sku: "B" }] }` is rejected. A filter with no unique field at all, such as `{ name: { contains: "lamp" } }`, is a job for `findFirst` or `findMany`. Passing `{ id: undefined }` leaves no unique field, and Prisma Client throws a `PrismaClientValidationError` before any SQL is sent. ## `findFirst` and `findMany`: any filter `findFirst` accepts the full filter grammar: `equals`, `in`, `contains`, `gt`, relation filters such as `some`, and the combinators `AND`, `OR` and `NOT`. It behaves like `findMany` limited to one row, which has two consequences: - **"First" needs an `orderBy`.** Without one, the statement has no `ORDER BY`, so the row returned is whichever the database produces first, and that can change between runs. - **A negative `take` reverses the list.** `orderBy: { createdAt: "asc" }, take: -1` returns the newest matching row. `findMany` returns every matching row as an array. An empty result is `[]`, never `null`, so `if (!products)` is always false; check `products.length` instead. Pair it with `take` so that a missing filter cannot return a whole table. ## The `OrThrow` variants `findUniqueOrThrow` and `findFirstOrThrow` send the same query as their nullable twins. When no row matches they throw a **`PrismaClientKnownRequestError`** whose `code` is `P2025` and whose message begins "An operation failed because it depends on one or more records that were required but not found." Their TypeScript return type drops `| null`, which removes null checks from code where absence is an error. A common pattern maps `P2025` to an HTTP 404 in one error handler. `update` and `delete` throw the same code when their unique `where` matches nothing, so one handler covers reads and single-record writes. ## How many statements a read costs 1. `findUnique` and `findFirst` each compile to one `SELECT` with `LIMIT 1`, plus an `ORDER BY` when you gave one. 2. **Same-tick batching applies to `findUnique` only.** Prisma Client's dataloader collects `findUnique` calls made in the same tick with the same `select` and the same `where` shape, sends one `SELECT ... WHERE "id" IN ($1,$2,$3)`, and hands each caller its own row. Three `findFirst` calls in the same tick send three statements, and `findMany` calls are not batched either. 3. `findMany` sends one statement for the model; each relation loaded through `include` or a nested `select` adds statements of its own. 4. **Nothing runs until you await.** Every query method returns a `PrismaPromise`, a thenable that executes only when `await`, `.then()` or `.catch()` is called on it. A query assigned to a variable and never awaited never reaches the database. ## Picking the right one - Use `findUnique` for lookups by id, SKU, slug or email, and in per-parent resolvers where batching pays off. - Use `findFirst` with an explicit `orderBy` for "latest", "cheapest" or "any matching" questions about non-unique columns. - Use `findMany` for lists, always with a `take` on user-facing endpoints. - Use the `OrThrow` forms when a missing row is an error for the caller, and the nullable forms when absence is an ordinary outcome.

  • Can findUnique also check a non-unique column, for example return a product only if it is in stock?
    Yes. `WhereUniqueInput` exposes every field, so `where: { id: 42, inStock: true }` is valid as long as at least one unique field sits outside `AND`, `OR` and `NOT`. The row comes back only if both conditions hold; otherwise the result is `null`, or `P2025` with `findUniqueOrThrow`. Single-record ownership and soft-delete checks are written this way.
  • Why might a per-product resolver prefer findUnique over findFirst to load each product?
    Prisma Client's dataloader batches `findUnique` calls made in the same tick with the same `select` and `where` shape into one `WHERE id IN (...)` statement. Twenty resolvers each calling `findUnique` cost one round trip; twenty `findFirst` calls cost twenty. `findMany` calls are not batched.
  • What happens if code builds a findMany call but never awaits it?
    Nothing reaches the database. Prisma Client queries return a `PrismaPromise`, a thenable that executes only when `await`, `.then()` or `.catch()` is called, unlike a native promise that starts when created. A forgotten `await` on a write therefore skips the write instead of racing it.

  • findUnique accepts any filter, just like findFirst does.
  • findMany returns null when no rows match.
  • findFirst returns the oldest row even without an orderBy.
  • The OrThrow methods throw a dedicated NotFoundError class.
  • A Prisma query assigned to a variable runs even if nobody awaits it.
open as a page

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%

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.

open as a page

In Prisma Client, how does cursor paging with cursor, skip and take differ from skip/take offset paging, and why is skip: 1 needed?

level: middleimportance: should knowfreq 55%

basics

~20 s

skip and take compile to OFFSET and LIMIT. cursor names a row by unique key and starts the page at that row's sort position, including the row itself, so skip: 1 drops it; a negative take pages backwards.

open as a page

In Prisma Client, why is $queryRaw written as a tagged template, and when do $queryRawUnsafe or Prisma.raw reopen SQL injection?

level: middleimportance: should knowfreq 45%

basics

~20 s

$queryRaw is a tag function, so each ${value} becomes a bind parameter rather than SQL text. $queryRawUnsafe takes a plain string and Prisma.raw splices text verbatim; both are safe only with trusted text, so dynamic identifiers must come from an allowlist.

open as a page

After a Prisma 7 upgrade removed prisma.$use, how do you rebuild an audit middleware with $extends, and why might it miss some queries?

level: seniorimportance: should knowfreq 40%

basics

~20 s

Prisma 7 removed $use; a query extension created with $extends wraps operations instead. $extends returns a new client, so code that keeps the base instance bypasses it, and query hooks see top-level operations only, not relations loaded through include.

open as a page

In Prisma Client, why did a cleanup job's deleteMany({ where: { categoryId } }) delete every product when categoryId was undefined, and how do you prevent it?

level: seniorimportance: should knowfreq 35%

basics

~20 s

Prisma Client treats undefined as 'omit this', so where: { categoryId: undefined } loses its only condition and deleteMany deletes every row. The strictUndefinedChecks preview feature makes an explicit undefined throw, with Prisma.skip for deliberate omission.

open as a page