skip to content

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%

answer

  1. null is a value
  2. undefined means do nothing
  3. an empty filter matches all
  4. a preview flag and a symbol

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.

solid answer

~50 s

Prisma Client distinguishes `null`, a value meaning SQL `NULL`, from `undefined`, which means "do nothing": the serializer drops any argument key whose value is `undefined`. With `categoryId` undefined the filter becomes `{}`, and `deleteMany` sends `DELETE FROM "public"."Product" WHERE 1=1`, resolving to a `{ count }` of every row, with no error. `updateMany` and `findMany` fail the same way: a listing built as `where: { categoryId: req.query.cat }` silently returns everything. The guard is the `strictUndefinedChecks` preview feature in the generator block: an explicit `undefined` then throws a `PrismaClientValidationError` before any SQL runs, and `Prisma.skip`, exported only with that flag, marks a deliberate omission. Add TypeScript's `exactOptionalPropertyTypes`, validate input at the boundary, and cap destructive bulk calls with the `limit` option or a non-empty-filter check. Single-record `update` and `delete` are safe here: their `where` must keep a unique field.

code

typescript · 16 lines
typescript
// Bug: categoryId is optional, and undefined drops the only filter
async function purgeCategory(categoryId?: number) {
  const { count } = await prisma.product.deleteMany({ where: { categoryId } });
  // categoryId === undefined  ->  DELETE FROM "public"."Product" WHERE 1=1
  return count;
}

// Hardened
async function purgeCategorySafely(categoryId: number) {
  if (!Number.isInteger(categoryId)) throw new Error("categoryId is required");
  const { count } = await prisma.product.deleteMany({
    where: { categoryId, inStock: false },
    limit: 500,
  });
  return count;
}

go deeper

for a junior

Recall that null is a value and undefined means the field is ignored, and that deleteMany and updateMany return a count, not rows.

for a middle

Explain how an undefined key disappears from the filter, why an empty where matches every row, and how Prisma.skip replaces undefined once strictUndefinedChecks is on.

for a senior

Diagnose the wipe from the statement and the returned count, then harden it: the preview flag, exactOptionalPropertyTypes, boundary validation, limit, and alerts on unusual counts.

for a principal

Decide whether to adopt a preview flag across services, and which guard rails, such as reviewing bulk writes and alerting on counts, a team requires for destructive jobs.

## `null` and `undefined` mean different things | Value in a query argument | Prisma Client reads it as | In a `where` | In a `data` | |---|---|---|---| | `null` | a value, SQL `NULL` | matches rows where the column is `NULL` | sets the column to `NULL` | | `undefined` | "do nothing" | the condition is dropped | the field is left unchanged | | `Prisma.skip`, with the preview flag | an explicit "do nothing" | the condition is dropped | the field is left unchanged | Dropping `undefined` is deliberate. It lets one object literal carry optional input, as in `data: { name: input.name, priceCents: input.priceCents }` for a partial update, where an absent field is simply not written. The same convenience is dangerous inside a filter. ## What happened to the cleanup job 1. The job called `prisma.product.deleteMany({ where: { categoryId } })` with `categoryId` undefined, perhaps from a missing argument or a renamed property. 2. While serialising the call, Prisma Client skips every key whose value is `undefined`, so the filter arrives as `where: {}`. 3. An empty filter matches every row. The statement sent is `DELETE FROM "public"."Product" WHERE 1=1`. 4. `deleteMany` resolves to a `BatchPayload` such as `{ count: 18432 }`: no error and no warning. `updateMany` behaves the same and rewrites every row, and so does `findMany`. A listing endpoint written as `where: { categoryId: Number(req.query.category) || undefined }` returns the whole catalogue when the parameter is absent. That may be the intent for a public catalogue; it is a data leak when the dropped condition was an owner or tenant id. ## Turning `undefined` into an error By default nothing guards against this, but Prisma Client ships an opt-in guard, the **`strictUndefinedChecks`** preview feature: ```prisma generator client { provider = "prisma-client" output = "../generated/prisma" previewFeatures = ["strictUndefinedChecks"] } ``` After `prisma generate`: - an explicit `undefined` in the arguments throws a `PrismaClientValidationError`, "explicitly `undefined` values are not allowed", before any SQL is sent; - the generated `Prisma` namespace exports **`Prisma.skip`**, written where omission is intended: `categoryId: input.categoryId ?? Prisma.skip`; - the generated input types accept `Prisma.skip` in those positions. Even without the flag, `undefined` as an array element, such as `id: { in: [1, undefined] }`, is rejected with "Can not use `undefined` value within array". ## Guards beyond the flag - Turn on TypeScript's `exactOptionalPropertyTypes`, which catches many places where a possibly-undefined value flows into an optional property at build time. - Validate job and request input at the boundary, so a required id is a number before it reaches a query. - For destructive bulk calls, assert that the filter object has at least one condition, and use the `limit` option of `updateMany` and `deleteMany` as a ceiling on rows touched per call. - Log the `count` returned by bulk writes and alert when it is far outside the usual range. ## Reading the evidence afterwards When a bulk write has already gone wrong, the trail is usually short: - the job's own log of the returned `count`, if it kept one, shows the size of the damage; - a client created with `log: ["query"]` prints each statement, and a `WHERE 1=1` on a `DELETE` or `UPDATE` is the signature of a dropped filter; - the calling code shows which variable could be `undefined`, often an optional parameter, a renamed request field or a failed parse that fell back to `undefined`. Recovery is a database matter, a restore or a replay, so the guards above are cheaper than the cleanup. ## What the write methods return | Method | Statement sent | Resolves to | |---|---|---| | `createMany` | one multi-row `INSERT`; `ON CONFLICT DO NOTHING` with `skipDuplicates` | `{ count }` | | `updateMany` / `deleteMany` | one `UPDATE` / `DELETE` over the filter | `{ count }` | | `createManyAndReturn` / `updateManyAndReturn` | the same write with `RETURNING` | the rows, on PostgreSQL, CockroachDB and SQLite | | `update` / `delete` | one statement on a unique `where`, with `RETURNING` | the row, or `P2025` when none matched | The single-record methods are the safe shape for one row: their `where` is a `WhereUniqueInput`, so `update({ where: { id: undefined } })` fails validation with "needs at least one of `id` or `sku` arguments" instead of matching every row. `skipDuplicates` is not available on SQLite or SQL Server, and `createMany` cannot carry nested relation writes.

  • Does the limit option make deleteMany safe on its own?
    No. `limit` caps the rows one call touches, so a dropped filter deletes up to that many arbitrary rows instead of all of them: less damage, but still damage. A job that loops until `count` is 0 deletes everything in batches. Use `limit` as a ceiling beside the real guards, `strictUndefinedChecks` or a non-empty-filter check, never instead of them.
  • Does strictUndefinedChecks change what null does in a filter?
    No. `null` stays a value: `where: { discountCode: null }` still matches rows whose column is `NULL`, and `data: { discountCode: null }` still clears it. The flag only rejects explicit `undefined` and adds `Prisma.skip`, so code that used `undefined` for "ignore this field" must switch to `Prisma.skip`.

A warehouse order form says 'remove stock from aisle ___'. A clerk who reads a blank aisle as 'no restriction' clears every aisle. strictUndefinedChecks is the clerk who sends a blank form back, and Prisma.skip is writing 'any aisle' on purpose.

  • Prisma throws by default when a where field is undefined.
  • where: { categoryId: undefined } matches rows whose categoryId is NULL.
  • deleteMany refuses to run with an empty filter unless you pass a flag.
  • Passing null is the way to leave an optional filter out.
  • update({ where: { id: undefined } }) updates every row, like updateMany.