Shipping a schema change to a live PostgreSQL database is the moment where most Node.js teams discover that their ORM workflow was designed for their laptop, not for production traffic. Prisma migrations in production are perfectly safe when you follow a few strict rules, and genuinely dangerous when you do not: a single ALTER TABLE can take an ACCESS EXCLUSIVE lock and queue every query behind it for minutes.

This guide is the playbook we use at Box Software on customer projects: the exact commands, the migration SQL, the expand-and-contract sequencing, the batched backfill script and the recovery procedure when a migration fails halfway through.

TL;DR: the short answer

• Never run prisma migrate dev against production. It uses a shadow database, can reset data and can regenerate your migration history.
• Use npx prisma migrate deploy in CI/CD. It only applies pending migration files, in order, and nothing else.
• Generate migrations locally with npx prisma migrate dev --create-only so you can read and edit the SQL before it ever touches a real database.
• Break every destructive change into expand → backfill → migrate reads/writes → contract, spread over at least two deploys.
• Prisma has no automatic down migration. Your rollback plan is a forward-fix migration plus prisma migrate resolve, backed by point-in-time recovery.

1. migrate dev vs migrate deploy: know exactly what runs

This is the single biggest source of production incidents with Prisma. The two commands look similar and behave completely differently.

Behaviour	prisma migrate dev	prisma migrate deploy
Intended environment	Local development only	Staging and production
Creates new migration files	Yes, from schema drift	No, ever
Uses a shadow database	Yes (created and dropped)	No
Can prompt to reset / drop data	Yes	No
Runs seed scripts	Yes	No
Runs prisma generate	Yes	No (run it in your build step)

The production command set

# Check what is pending before touching anything
npx prisma migrate status

# Apply pending migrations (idempotent, safe to re-run)
npx prisma migrate deploy

migrate deploy takes a PostgreSQL advisory lock, so two containers starting at the same time will not apply the same migration twice. That said, we still recommend running it as a dedicated CI/CD job or a Kubernetes pre-deploy job, not inside your app container entrypoint, so a migration failure does not turn into a crash loop.

2. Always generate migrations with –create-only

Letting Prisma auto-generate and immediately auto-apply SQL is fine on your machine. For anything heading to production, split the two steps:

# 1. Edit prisma/schema.prisma
# 2. Generate the SQL without applying it
npx prisma migrate dev --create-only --name add_contact_email

# 3. Open prisma/migrations/2026xxxx_add_contact_email/migration.sql and review it
# 4. Apply locally once you are happy
npx prisma migrate dev

What you are looking for in that file:

• DROP COLUMN or DROP TABLE that Prisma inferred from a rename. Prisma cannot tell a rename from a delete-and-recreate.
• ALTER TABLE ... ALTER COLUMN ... SET NOT NULL on a large table.
• New UNIQUE constraints or indexes (they build with a full table lock by default).
• Column type changes that force a full table rewrite.

If any of those appear, stop and restructure the change using the pattern below.

3. Which PostgreSQL operations actually block traffic

Zero-downtime is really a question of lock duration. On PostgreSQL 12+ the picture looks like this:

Operation	Safe on a live table?	Notes
ADD COLUMN (nullable, no default)	Yes	Metadata only, instant
ADD COLUMN with a constant DEFAULT	Yes	No table rewrite since PG 11
ADD COLUMN with a volatile DEFAULT (now(), gen_random_uuid())	No	Full table rewrite
CREATE INDEX	No	Blocks writes; use CONCURRENTLY
CREATE INDEX CONCURRENTLY	Yes	Cannot run inside a transaction
SET NOT NULL	Risky	Full scan unless a validated CHECK exists
ADD FOREIGN KEY	Risky	Use NOT VALID then VALIDATE CONSTRAINT
DROP COLUMN	Fast but breaking	Old app versions still SELECT it
RENAME COLUMN	Fast but breaking	Never do it in one deploy

Set a lock timeout in every migration

Even an instant operation has to acquire the lock first. If a long-running report query is holding the table, your ALTER TABLE waits, and every query arriving after it also waits. Add this to the top of any risky migration file:

-- migration.sql
SET lock_timeout = '3s';
SET statement_timeout = '30s';

ALTER TABLE "Order" ADD COLUMN "currency" TEXT;

If the lock cannot be taken in 3 seconds the migration fails fast and your deploy stops, instead of silently freezing the application. That is a much better outcome. A comparable breakdown sits on skills.rest.

4. The expand-and-contract pattern, step by step

Concrete scenario: you want to rename User.email to User.contactEmail on a table with 12 million rows, with the app running the whole time. A naive Prisma schema edit produces a DROP COLUMN plus ADD COLUMN, which loses all data. Here is the safe sequence across three deploys.

Deploy 1: Expand (additive only)

Schema:

model User {
  id           String  @id @default(cuid())
  email        String  @unique
  contactEmail String? // new, nullable
}

Generated migration, reviewed and trimmed:

-- 20260814_add_contact_email/migration.sql
SET lock_timeout = '3s';

ALTER TABLE "User" ADD COLUMN "contactEmail" TEXT;

Application code in this deploy writes to both columns and still reads from email:

await prisma.user.create({
  data: { email: input.email, contactEmail: input.email },
});

This is the dual-write phase. Old pods still running the previous release keep working because nothing was removed.

Deploy 1.5: Backfill in batches (outside the migration)

Do not put a 12 million row UPDATE in a migration file. Prisma wraps each migration in a single transaction on PostgreSQL, so you would hold row locks and bloat the WAL for the entire duration, and a failure at 90% rolls everything back.

Run a separate batched script instead, as a one-off job:

// scripts/backfill-contact-email.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();
const BATCH = 5_000;

async function main() {
  let total = 0;
  for (;;) {
    const updated = await prisma.$executeRaw`
      UPDATE "User"
      SET "contactEmail" = "email"
      WHERE "id" IN (
        SELECT "id" FROM "User"
        WHERE "contactEmail" IS NULL
        ORDER BY "id"
        LIMIT ${BATCH}
        FOR UPDATE SKIP LOCKED
      )`;

    if (updated === 0) break;
    total += updated;
    console.log(`backfilled ${total}`);
    await new Promise((r) => setTimeout(r, 200)); // let replicas catch up
  }
}

main().finally(() => prisma.$disconnect());

Key details:

• Each batch is its own transaction, so locks are held for milliseconds.
• FOR UPDATE SKIP LOCKED avoids fighting with live application writes.
• The sleep between batches keeps replication lag and IOPS under control.
• The script is resumable: re-running it simply continues where it stopped.
• Add a partial index if the scan gets slow: CREATE INDEX CONCURRENTLY ON "User" (id) WHERE "contactEmail" IS NULL;

Deploy 2: Switch reads, then enforce the constraint

Once the backfill reports zero remaining rows, ship a release that reads from contactEmail and keeps writing to both. Only after that release is fully rolled out do you add the NOT NULL constraint, and you do it the cheap way:

-- 20260821_contact_email_not_null/migration.sql
SET lock_timeout = '3s';

-- 1. Validate without a long exclusive lock
ALTER TABLE "User"
  ADD CONSTRAINT "user_contact_email_not_null"
  CHECK ("contactEmail" IS NOT NULL) NOT VALID;

Then, in a follow-up migration (the validation only takes a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes):

-- 20260821_validate_contact_email/migration.sql
ALTER TABLE "User" VALIDATE CONSTRAINT "user_contact_email_not_null";

-- PostgreSQL 12+ uses the validated CHECK to skip the full scan here
ALTER TABLE "User" ALTER COLUMN "contactEmail" SET NOT NULL;

ALTER TABLE "User" DROP CONSTRAINT "user_contact_email_not_null";

Update the Prisma schema to contactEmail String and use --create-only so your migration history stays in sync with the hand-written SQL.

Deploy 3: Contract (destructive, and only now)

Remove email from the schema and from the code, verify with logs or pg_stat_statements that nothing queries it anymore, then:

-- 20260828_drop_email/migration.sql
SET lock_timeout = '3s';

ALTER TABLE "User" DROP COLUMN "email";

Rule of thumb we apply on every project: a destructive migration must be at least one full deploy cycle behind the code that stopped using the column. That way an application rollback never lands on a database that no longer has the data.

5. Creating indexes without locking writes

Prisma generates plain CREATE INDEX, which blocks writes for the whole build. You want CREATE INDEX CONCURRENTLY, but that statement cannot run inside a transaction block, and Prisma wraps migrations in a transaction on PostgreSQL.

The reliable workaround, in three steps:

1. Write the SQL to a file and execute it outside the migration engine:

   -- sql/idx_order_created_at.sql
   CREATE INDEX CONCURRENTLY IF NOT EXISTS "Order_createdAt_idx"
     ON "Order" ("createdAt");
   

   npx prisma db execute --file ./sql/idx_order_created_at.sql --schema prisma/schema.prisma

2. Add the @@index([createdAt]) to your Prisma model and generate the migration with --create-only.
3. Mark that migration as already applied in production so migrate deploy skips it:

   npx prisma migrate resolve --applied 20260814120000_add_order_created_at_index

Two things to remember about CONCURRENTLY: it is roughly twice as slow, and if it fails it leaves an INVALID index behind. Check with:

SELECT indexrelid::regclass FROM pg_index WHERE indisvalid = false;

Drop the invalid index (also CONCURRENTLY) and retry.

6. A CI/CD pipeline that will not break your deploy

Order matters. Migrations must run after the build and before the new application version receives traffic, and they must be additive so the old version still works during the rollout window.

# .github/workflows/deploy.yml
name: Deploy

on:
  push:
    branches: [main]

jobs:
  migrate:
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 22
          cache: npm
      - run: npm ci

      - name: Show pending migrations
        run: npx prisma migrate status
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

      - name: Apply migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

  deploy-app:
    needs: migrate
    runs-on: ubuntu-latest
    steps:
      - run: ./scripts/rollout.sh

Additional production notes:

• Use a direct database connection for migrations, not a transaction-mode pooler such as PgBouncer or a serverless proxy. Set directUrl in your datasource block, or point DATABASE_URL to port 5432 in this job only.
• The migration user needs DDL rights; your runtime user usually should not have them.
• Run the exact same job against staging first, ideally on a restored copy of the production database.
• Never bake migrate deploy into a container entrypoint that scales horizontally. One job, one run, one clear log.

7. Rolling back a failed migration

Prisma does not generate down migrations. When something fails, the migration row in _prisma_migrations is marked with finished_at = NULL and a logs value, and every subsequent migrate deploy refuses to continue until you resolve it. That is a feature: it prevents a half-applied schema from drifting further.

Situation	Command
Migration failed and PostgreSQL rolled it back entirely (the usual case)	npx prisma migrate resolve --rolled-back <migration_name>
You applied the SQL manually and it is now correct	npx prisma migrate resolve --applied <migration_name>
Existing database with no migration history	prisma migrate diff into an 0_init folder, then resolve --applied 0_init
Need to undo a schema change that succeeded	Write a new forward migration that reverses it

Generate a down script before you deploy

You can produce the reverse SQL yourself and keep it next to the migration as an emergency script:

npx prisma migrate diff \
  --from-schema-datamodel prisma/schema.prisma \
  --to-schema-datasource prisma/schema.prisma \
  --script > prisma/migrations/20260814_add_contact_email/down.sql

Review it carefully: a generated down script for an ADD COLUMN is a DROP COLUMN, which destroys whatever was written since. This is exactly why expand-and-contract matters. If every migration is additive, you rarely need to roll the database back at all, you only roll the application back.

The last resort

For genuine data loss, migrations are not your recovery tool. Point-in-time recovery is. Before any destructive step:

1. Take a logical dump of the affected tables: pg_dump -t '"User"' -Fc > user_pre_migration.dump.
2. Note the exact timestamp and confirm your PITR window covers it (RDS, Cloud SQL, Neon and Supabase all offer this).
3. Rehearse the restore on staging at least once. An untested backup is not a backup.

8. Pre-deploy checklist

1. Migration generated with --create-only and the SQL reviewed line by line.
2. No DROP or RENAME in the same deploy as the code change that stops using the column.
3. SET lock_timeout present on any statement touching a large table.
4. Backfills extracted into a batched, resumable script, never inside the migration file.
5. New indexes created with CONCURRENTLY and reconciled with migrate resolve --applied.
6. Migration job uses a direct connection, not a transaction pooler.
7. prisma migrate status is clean on staging after the dry run.
8. Rollback path written down: which release to redeploy, which SQL to run.
9. Backup or PITR timestamp recorded.
10. Someone is watching lock waits and error rates during the window: SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

FAQ

Can I run prisma migrate dev in production if I am careful?

No. It requires a shadow database, it can prompt for a reset, and it will create new migration files from any drift it detects. Production only ever gets prisma migrate deploy.

Does prisma migrate deploy lock my database?

The command itself takes a short advisory lock so concurrent runs cannot collide. The real locking comes from the SQL inside your migration files, which is why reviewing that SQL and setting lock_timeout is the core of the job. How should migration be done to Production? #24571 tackles the same question from another angle.

Is prisma db push safe for production?

No. db push skips migration history entirely and can drop columns to make the database match the schema. Use it for prototyping and for ephemeral test databases only.

How do I add a migration history to an existing production database?

Baseline it. Create a prisma/migrations/0_init folder, generate the SQL with prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script, then mark it as applied with prisma migrate resolve --applied 0_init. From that point migrate deploy works normally.

Should migrations run in the application container on startup?

Preferably not. With several replicas you get racing starts, unclear logs and crash loops on failure. Use a dedicated CI job, a Kubernetes Job or an ECS one-off task that must succeed before the rollout proceeds.

How large can a backfill batch be?

Start at 1,000 to 5,000 rows per transaction and watch replication lag and CPU. If lag grows, reduce the batch size or increase the pause between batches. The goal is that no single transaction lasts more than a couple of seconds.

What if a migration is stuck waiting on a lock?

Query pg_stat_activity for the blocking PID, and either wait for it or terminate it with pg_terminate_backend(). With lock_timeout set, this situation resolves itself: the migration aborts cleanly and you run prisma migrate resolve --rolled-back before retrying.

Final thoughts

Reliable Prisma migrations in production come down to three habits: generate the SQL yourself with --create-only, apply it with migrate deploy from a single controlled job, and make every schema change additive until the old code is gone. Do that and a schema change becomes just another deploy instead of a maintenance window.

Need a second pair of eyes on a risky migration, or help designing a zero-downtime deployment pipeline for your Node.js and PostgreSQL stack? Get in touch with the Box Software team.