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?
answer
- null is a value
- undefined means do nothing
- an empty filter matches all
- a preview flag and a symbol
basics
~20 sPrisma 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 sPrisma 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// 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
Recall that null is a value and undefined means the field is ignored, and that deleteMany and updateMany return a count, not rows.
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.
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.
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.