skip to content

Prisma

Node's schema-driven ORM: you declare models in a .prisma file and get a fully typed client, migrations, and relation queries generated from it. Interviewers ask about Prisma because the generated client, the migration workflow, and its connection behaviour on serverless platforms are exactly where teams get burned.

on this pageshow

explore

questions

page 1 of 2

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 7, why should a Node.js application share one PrismaClient instance per process, and how do you stop dev hot reload from multiplying it?

level: juniorimportance: must knowfreq 50%

basics

~20 s

Each PrismaClient gets its own connection pool from its driver adapter, so every extra instance opens extra database connections. Create one in a module and reuse it; in development, keep it on globalThis so hot reload reuses it.

open as a page

In Prisma 7, what is the difference between `prisma db push` and `prisma migrate dev`, and when should each be used?

level: juniorimportance: must knowfreq 58%

basics

~20 s

Prisma's db push makes the database match the schema directly and leaves no migration files or migrations-table rows; migrate dev records each change as a SQL migration, applies it and tracks it. Push suits local prototyping; migrations suit anything shared.

open as a page

In a Prisma 7 schema, how does a relation field differ from the relation scalar field named in @relation(fields: [...], references: [...])?

level: juniorimportance: must knowfreq 70%

basics

~20 s

A relation field such as user User exists only in the Prisma schema and client; the relation scalar field such as userId Int is the real foreign-key column, and @relation(fields: [userId], references: [id]) links them on the side holding the key.

open as a page

In a Prisma 7 schema, how does a model block describe a table, and what do @id, @default, @unique and the ? modifier each do?

level: juniorimportance: must knowfreq 72%

basics

~20 s

A Prisma model block declares one table; each line is a field with a type, modifiers and attributes. @id marks the primary key, @default fills an omitted value, @unique forbids duplicates, and ? makes the field nullable.

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 7, how do `prisma migrate dev` and `prisma migrate deploy` differ, and why should only `migrate deploy` run against production?

level: middleimportance: must knowfreq 76%

basics

~20 s

Prisma's migrate dev authors migrations: it checks for drift in a shadow database, generates SQL from schema changes and may require a reset. Migrate deploy only applies committed, pending migration files, never generates or resets, so it is the production command.

open as a page

In Prisma 7, when can a many-to-many between Order and Product stay implicit, and when does it need an explicit join model?

level: middleimportance: must knowfreq 72%

basics

~20 s

Keep it implicit while the link carries no data: two list fields make Prisma manage a hidden _OrderToProduct table. Once the link needs a column such as quantity, its own referential actions or a composite-key model, write an explicit OrderItem model.

open as a page

In Prisma Client 7, how do nested create, connect, connectOrCreate, disconnect and set write across an order's relations in one call?

level: middleimportance: must knowfreq 68%

basics

~20 s

A nested write lets one create or update call write related records too, in one transaction that rolls back as a whole: create adds rows, connect links existing ones by a unique filter, connectOrCreate does either, disconnect unlinks and set replaces a list.

open as a page

In Prisma 7, how does the batch prisma.$transaction([...]) differ from the interactive prisma.$transaction(async (tx) => ...), and when do you need each?

level: middleimportance: must knowfreq 72%

basics

~20 s

The batch form runs an array of un-awaited queries in order inside one transaction, so no query can use another's result. The interactive form hands an async callback a transaction client, tx, for logic between queries; throwing rolls back.

open as a page

After a Prisma 6 project moves to Prisma 7, its schema's datasource url and output-less generator block fail; what must change in those two blocks?

level: seniorimportance: must knowfreq 48%

basics

~20 s

In Prisma 7 the datasource block keeps only provider and its options; url, directUrl and shadowDatabaseUrl move to prisma.config.ts. The generator becomes provider = "prisma-client" with a required output path, and imports switch from @prisma/client to that generated folder.

open as a page

In Prisma 7, which Prisma Client calls already run atomically on their own, so that wrapping them in prisma.$transaction adds nothing?

level: juniorimportance: should knowfreq 45%

basics

~20 s

Each single Prisma Client call is atomic: a nested write runs its parent and related writes in one transaction, the bulk *Many operations run as transactions, and a lone update is one statement. prisma.$transaction is for several separate calls.

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

In Prisma 7, what reads the datasource.url in prisma.config.ts, and where does Prisma Client get its connection string at runtime?

level: middleimportance: should knowfreq 30%

basics

~20 s

In Prisma 7, datasource.url in prisma.config.ts is read by the Prisma CLI for migrate, db and introspection commands. Prisma Client never reads it; at runtime it connects through the driver adapter you pass, such as PrismaPg with its connectionString.

open as a page

In Prisma 7 with PrismaPg, where do you set the connection pool size and timeouts, and which defaults changed from Prisma 6?

level: middleimportance: should knowfreq 25%

basics

~20 s

In Prisma 7 the pool belongs to node-postgres, so you set it in the PrismaPg constructor: max (default 10), connectionTimeoutMillis (default 0, no limit) and idleTimeoutMillis (default 10 seconds). Prisma 6's connection_limit and pool_timeout URL parameters no longer apply.

open as a page

In Prisma Migrate, how do you customise a generated migration's SQL, and what happens if you edit one that has already been applied?

level: middleimportance: should knowfreq 42%

basics

~20 s

Run prisma migrate dev --create-only to write the migration without applying it, edit migration.sql, then run migrate dev to apply it. Editing an applied migration breaks its stored checksum: migrate dev demands a reset, migrate deploy only warns.

open as a page

In Prisma 7, how do @@id, @@unique and @@index differ, and how does a composite key show up in a findUnique call?

level: middleimportance: should knowfreq 40%

basics

~20 s

@@id makes several fields the primary key, @@unique adds a multi-column unique index, and @@index adds a plain lookup index. The first two give Prisma Client a compound selector such as authorId_slug for findUnique; @@index gives none.

open as a page

In Prisma 7, how do @map and @@map let a schema keep PascalCase models and camelCase fields over existing snake_case tables and columns?

level: middleimportance: should knowfreq 42%

basics

~20 s

@map("created_at") binds a field or enum value to a differently named column or stored value; @@map("users") does the same for a model's table or an enum's type. Prisma Client keeps the schema names; only SQL uses the mapped ones.

open as a page

In Prisma 7 on PostgreSQL, what column type does a plain String, DateTime or Decimal field get, and when do you add a @db attribute?

level: middleimportance: should knowfreq 34%

basics

~10 s

Without a native type attribute, Prisma's PostgreSQL connector maps String to text, DateTime to timestamp(3) and Decimal to decimal(65,30). An attribute such as @db.VarChar(255), @db.Timestamptz(6) or @db.Decimal(10, 2) pins the exact column type instead.

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

With Prisma 7 behind PgBouncer in transaction mode, why can prisma migrate fail through the pooled URL, and how do you set up the two connection strings?

level: seniorimportance: should knowfreq 25%

basics

~10 s

Prisma Migrate's Schema Engine expects a single dedicated connection and can fail through PgBouncer with 'prepared statement "s0" already exists'. Point datasource.url in prisma.config.ts at a direct URL and give PrismaPg the pooled one.

open as a page

A Prisma 7 API on a serverless platform starts failing with too-many-connections errors during a traffic spike; why does it happen, and how do you fix it?

level: seniorimportance: should knowfreq 40%

basics

~20 s

Every function instance runs its own PrismaClient and PrismaPg pool, default max 10, so a spike multiplies connections by the instance count. Build the client outside the handler, shrink max, cap concurrency, and route runtime traffic through a connection pooler.

open as a page

A Prisma 7 app's Docker image throws PrismaClientValidationError 'Unknown field' for a column the schema already has; why is the client stale, and how should the build generate it?

level: seniorimportance: should knowfreq 30%

basics

~20 s

Prisma 7 never generates the client by itself: no install hook runs it, and migrate dev and db push stopped calling it. The image ships whatever the output folder held. Run prisma generate in the build, after copying the schema.

open as a page

With Prisma 7, after someone added a production column by hand, why does `prisma migrate deploy` fail with P3018 and then keep reporting P3009, and how do you recover?

level: seniorimportance: should knowfreq 40%

basics

~20 s

The hand-made column was drift that migrate deploy never checks for, so the migration adding it failed (P3018) and was recorded as failed, which blocks all later deploys (P3009). Mark it with prisma migrate resolve, applied or rolled back, then redeploy.

open as a page

After an upgrade from Prisma 6 to 7, why does `prisma migrate reset` leave tables empty and `migrate dev --skip-seed` fail, and how do you fix it?

level: seniorimportance: should knowfreq 32%

basics

~20 s

Prisma 7 stopped running the seed from migrate dev and migrate reset, stopped running generate from migrate dev and db push, and removed --skip-seed and --skip-generate. Run prisma generate and prisma db seed yourself, with the seed under migrations.seed.

open as a page

In Prisma 7, two concurrent guest checkouts use connectOrCreate on the same new email and one fails with P2002 - why, and what is the fix?

level: seniorimportance: should knowfreq 35%

basics

~20 s

connectOrCreate is a lookup then an insert, not one atomic statement. Two concurrent checkouts both miss the email and both insert; the unique index rejects one with P2002, rolling back its whole order. The documented fix is catching P2002 and retrying.

open as a page

In Prisma 7 on PostgreSQL, deleting a User fails with P2003 because of their orders - which onDelete default caused it, and how do you change it?

level: seniorimportance: should knowfreq 45%

basics

~20 s

With no onDelete, Prisma 7 gives a required relation Restrict and an optional one SetNull, so Order.user required means ON DELETE RESTRICT and the delete fails with P2003. Set onDelete on the Order side's @relation, then migrate.

open as a page

A Prisma 7 model uses @default(uuid()) and @updatedAt; why do rows written by a plain SQL script fail on the id or keep a stale updatedAt?

level: seniorimportance: should knowfreq 30%

basics

~20 s

uuid(), cuid() and @updatedAt are implemented by Prisma ORM, not the database: the migration creates the columns with no DEFAULT or trigger, and Prisma Client fills them in. SQL that bypasses the client must supply them itself.

open as a page

showing 1–30 of 35