The Prisma migration that passes CI and fails in production

Denis Borovikov — Fri, 09 Oct 2026 10:59:06 +0000

Making a Prisma field required passes review and CI, then fails on deploy if production has nulls. How to check first, and how to make the change safely.

The change

Every invoice now needs a due date, so the field becomes required:

 // schema.prisma
 model Invoice {
   id      Int      @id @default(autoincrement())
-  dueDate DateTime?
+  dueDate DateTime
 }

prisma migrate dev generates the migration:

-- prisma/migrations/20261008120000_require_invoice_due_date/migration.sql
ALTER TABLE "Invoice" ALTER COLUMN "dueDate" SET NOT NULL;

Review approves it. CI applies it to an empty database, so it passes. Prisma warns only if your local database has nulls there, and it rarely does.

In production

Production has 12.4 million invoices, and an old import left some without a due date. prisma migrate deploy fails:

ERROR: column "dueDate" of relation "Invoice" contains null values

Prisma marks the migration failed, and every later deploy stops with P3009 until someone runs prisma migrate resolve. A linter can flag SET NOT NULL as risky, but it can't see whether the column has nulls.

Check before you merge

Postgres keeps planner statistics for every column, including its share of nulls. Estimate them without reading a row:

SELECT s.null_frac,
       c.reltuples::bigint                     AS estimated_rows,
       (s.null_frac * c.reltuples)::bigint     AS estimated_null_rows
FROM pg_stats s
JOIN pg_class c ON c.relname = s.tablename
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = s.schemaname
WHERE s.schemaname = 'public' AND s.tablename = 'Invoice' AND s.attname = 'dueDate';

Here: 0.31%, about 38,400 rows. Anything above zero means the migration fails. A column with only a few nulls can still show 0, so count when it matters.

The safe version

Backfill, add a CHECK without validating it, validate it, then set NOT NULL. Since Postgres 12, SET NOT NULL skips its table scan when a validated CHECK already proves it. Use two migrations, so the first statement's lock isn't held during the scan.

-- 20261008120000_backfill_invoice_due_date/migration.sql
UPDATE "Invoice" SET "dueDate" = "createdAt" + interval '30 days' WHERE "dueDate" IS NULL;
ALTER TABLE "Invoice" ADD CONSTRAINT "Invoice_dueDate_not_null" CHECK ("dueDate" IS NOT NULL) NOT VALID;

-- 20261008120100_require_invoice_due_date/migration.sql
ALTER TABLE "Invoice" VALIDATE CONSTRAINT "Invoice_dueDate_not_null";
ALTER TABLE "Invoice" ALTER COLUMN "dueDate" SET NOT NULL;
ALTER TABLE "Invoice" DROP CONSTRAINT "Invoice_dueDate_not_null";

The backfill value is a product decision. On a very large table, backfill in batches outside the migration.

On every pull request

DbProof runs this check for you. It captures production's schema and planner statistics, never rows, and runs each pull request's prisma migrate deploy against a copy in your GitHub Actions. A live example of the failing check: dbproof/prisma-example#2.