Zero-Downtime Database Migrations with Prisma

The scariest deploy on any backend team is a schema migration. Adding a feature is low risk. Changing the shape of a table that production traffic is hitting right now is a different game entirely, because for a window of time, old application code and new application code both have to work against the same database, and one wrong ALTER statement can take an entire service down.

This post walks through the expand-contract pattern for Prisma and Postgres: how to add columns without locking the table, how to backfill millions of rows without starving your connection pool, how to run a safe dual-write window, and how to drop the old column later without anyone noticing. Every command here is something you can run today with Prisma Migrate.

Why a Single ALTER Statement Can Take Down Production

A naive migration renames a column, changes its type, or adds a NOT NULL constraint in one step, then ships new application code that expects the change to already be live. The problem is deployment is never instant. There is always a window where old server instances are still running against the new schema, or new instances are running against the old one, especially in a rolling deploy behind a load balancer. If the migration and the code change are not decoupled, that window produces failed queries, 500s, or silent data corruption. Postgres compounds the risk: certain ALTER TABLE forms take an ACCESS EXCLUSIVE lock, and while a plain column addition is usually a fast metadata-only change, adding a column with a volatile default, a NOT NULL constraint that requires a full validation scan, or changing a column's type can force Postgres to rewrite the entire table and its indexes, blocking every other reader and writer until it finishes.

The Expand-Contract Pattern, In One Sentence

Never remove or repurpose something the running application still depends on in the same deploy that changes it: expand the schema by adding the new structure alongside the old one, migrate the data and the code over in small reversible steps, and only contract by removing the old structure once nothing reads it anymore. Below is the rollout timeline this post follows, using a fictional Invoice table that is migrating its amount field from integer cents to a proper Decimal column.

Step 1: Expand the Schema Without Locking the Table

The golden rule of the expand phase is: add the column, but do not attach a NOT NULL constraint or a volatile default in the same statement. Postgres can add a plain nullable column as a pure metadata change in milliseconds, because it does not need to touch a single existing row. The moment you add NOT NULL or a default value that is not a fixed constant, Postgres has to validate or rewrite every row, and that rewrite takes an exclusive lock for its entire duration. Prisma Migrate's default workflow runs migrations automatically, but for anything sensitive you should use the create-only flag so you can review and edit the generated SQL before it ever touches your database. That gives you the chance to strip out a NOT NULL constraint the CLI added, or split one migration into two separate, safer statements.

// schema.prisma — expand phase: add the new column alongside the old one
model Invoice {
  id         Int      @id @default(autoincrement())
  amountCents Int?    // old: integer cents, still read by the current release
  amount     Decimal? @db.Decimal(12, 2) // new: proper decimal, nullable for now
  status     String
}

# Generate a draft migration without applying it
npx prisma migrate dev --create-only --name add-invoice-amount-decimal

# Edit the generated migration.sql before it runs:
# prisma/migrations/2026XXXXXXXXXX_add_invoice_amount_decimal/migration.sql
ALTER TABLE "Invoice" ADD COLUMN "amount" DECIMAL(12,2);
-- No DEFAULT, no NOT NULL yet: this is a metadata-only change,
-- so Postgres does not rewrite the table and the ACCESS EXCLUSIVE
-- lock it takes is held for milliseconds, not minutes.

# Now apply it
npx prisma migrate dev

Step 2: Backfill in Small, Resumable Batches

Once the new column exists, you need to populate it for every existing row without competing with live traffic for connections or locks. A single UPDATE statement across a multi-million-row table holds locks on every row it touches until the transaction commits, which can stall other queries for the whole run. The safer approach is a script that walks the table in ordered batches, using the primary key as a cursor, committing each batch in its own short transaction. This keeps each lock window small, lets you log progress so you can resume after a crash, and lets you throttle the pace if you see replication lag or connection pool pressure building. Run it as a one-off job, not inside a request handler, and point it at a read replica for verification queries so it never competes with production reads.

// scripts/backfill-invoice-amount.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()
const BATCH_SIZE = 500

async function backfill() {
  let cursor: number | undefined = undefined
  let migrated = 0

  while (true) {
    const rows = await prisma.invoice.findMany({
      where: { amount: null },
      take: BATCH_SIZE,
      ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
      orderBy: { id: 'asc' },
    })

    if (rows.length === 0) break

    await prisma.$transaction(
      rows.map((row) =>
        prisma.invoice.update({
          where: { id: row.id },
          data: { amount: (row.amountCents ?? 0) / 100 },
        })
      )
    )

    cursor = rows[rows.length - 1].id
    migrated += rows.length
    console.log(`Backfilled ${migrated} rows...`)

    // Small pause keeps replication lag and connection pool pressure low
    await new Promise((r) => setTimeout(r, 50))
  }

  console.log(`Done. ${migrated} rows backfilled.`)
}

backfill().finally(() => prisma.$disconnect())

Step 3: The Dual-Write Window

Before you can trust the new column, deploy a release where application code writes to both the old and new columns on every insert and update, while reads still come from the old column. This is the safety net: if the backfill missed an edge case or the new column has a subtle bug, the old column is still the source of truth and nothing user-facing breaks. Keep this window as short as reasonably possible, but long enough to catch a full business cycle of writes, including any batch jobs or webhooks that write to the table outside the normal request path.

• Every code path that writes to the table, including background jobs, webhooks, and admin tools, must be updated to write both columns before you rely on the new one.
• Add a reconciliation query that periodically compares old and new column values for a sample of rows, so a silent bug in the dual-write logic surfaces before cutover, not after.
• Keep the dual-write release deployed for at least one full cycle of your slowest recurring job, such as a monthly billing run, before moving to cutover.

Step 4 and 5: Cutover, Then Contract

Once the reconciliation query has been clean for your chosen window, ship the cutover release: application code reads and writes only the new column, and the old column becomes dead weight rather than a dependency. This is still reversible, because the old column still exists with its last known values, so you can roll back the release without a data migration. Only after cutover has been stable in production do you contract: remove the old column in its own dedicated migration, deployed separately from any code change. Splitting it out matters because dropping a column is destructive and should never be bundled with unrelated schema or feature work.

1. Confirm in your metrics or logs that nothing has read from the old column in at least one full deploy cycle.
2. Generate a draft migration with the create-only flag so you can review the exact DROP COLUMN statement before it runs.
3. Take a backup or a point-in-time recovery snapshot immediately before applying the drop, since this step is not reversible without restoring data.
4. Apply the migration with the standard deploy command in your CI/CD pipeline, then remove the now-unused field from the Prisma schema in the same commit.

How This Maps to a Real Prisma Migrate Workflow

In practice this becomes five small, boring, individually reviewable pull requests rather than one large risky one: add the nullable column, run the backfill script as a deploy step or scheduled job, ship the dual-write code change, ship the cutover code change, and finally drop the old column. Each one deploys independently through the same migrate deploy command your CI/CD pipeline already runs, so there is nothing exotic about the release process itself, just discipline about not collapsing five safe steps into one risky one.