In Prisma Client, how do findUnique, findFirst and findMany differ, and what do their OrThrow variants change?
answer
- unique key versus any filter
- object, object, or array
- what an empty result looks like
- a known error code instead of null
basics
~20 sfindUnique 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 linesimport { 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
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.
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.
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.
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.