Prisma Migrations in Production: Zero-Downtime Strategies and Rollback Patterns

Source: Dev.to

Introduction

Running prisma migrate deploy in production without a plan is a common cause of outages.
Most migration guides show the happy path, but production environments have additional constraints:

• Live traffic during the migration window
• Old app versions still running until the new deployment finishes
• Potentially failed migrations that need rollback
• Long‑running migrations that lock tables

The safest approach is expand, then contract.

Zero‑Downtime Migration Strategy

Step 1 – Expand (backward‑compatible change)

Add the new column as nullable so the existing code continues to work.

-- Add new column as nullable
ALTER TABLE users ADD COLUMN display_name TEXT;

Step 2 – Backfill (background job)

Populate the new column in batches to avoid long locks.

// scripts/backfill-display-name.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

const batchSize = 1000;
let cursor: string | undefined;

while (true) {
  const users = await prisma.user.findMany({
    take: batchSize,
    skip: cursor ? 1 : 0,
    cursor: cursor ? { id: cursor } : undefined,
    where: { displayName: null },
  });

  if (users.length === 0) break;

  await prisma.user.updateMany({
    where: { id: { in: users.map(u => u.id) } },
    data: { displayName: users.map(u => u.name) },
  });

  cursor = users[users.length - 1].id;
  await new Promise(r => setTimeout(r, 100)); // don't hammer the DB
}

Step 3 – Contract (once new code is fully deployed)

Make the column required and optionally drop the old one.

-- Make the new column NOT NULL
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

-- Old column can be dropped in a later deploy
ALTER TABLE users DROP COLUMN name;

Development Workflow

# Create a migration from schema changes
npx prisma migrate dev --name add_display_name

# Preview what will run in production
npx prisma migrate status

Production Workflow

# Apply pending migrations
npx prisma migrate deploy

Generate a migration file without executing it

npx prisma migrate dev --name backfill_slugs --create-only

Edit the generated migration.sql as needed (see example below).

Example: Backfilling Slugs

-- migrations/20240101_backfill_slugs/migration.sql
ALTER TABLE posts ADD COLUMN slug TEXT;

-- Backfill in batches to avoid lock contention
UPDATE posts
SET slug = lower(regexp_replace(title, '[^a-zA-Z0-9]+', '-', 'g'))
WHERE slug IS NULL;

ALTER TABLE posts ALTER COLUMN slug SET NOT NULL;
CREATE UNIQUE INDEX posts_slug_idx ON posts(slug);

Index creation without locking

-- This locks the table (bad in production)
CREATE INDEX posts_author_idx ON posts(author_id);

-- This doesn't lock (safe in production)
CREATE INDEX CONCURRENTLY posts_author_idx ON posts(author_id);

You can add the CONCURRENTLY clause directly in your migration SQL.

Deployment Script

#!/bin/bash
# deploy.sh

# 1. Check migration status
echo 'Checking migration status...'
npx prisma migrate status

# 2. Backup before destructive migrations
echo 'Creating backup...'
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d_%H%M%S).sql

# 3. Run migrations
echo 'Running migrations...'
npx prisma migrate deploy

# 4. Deploy app
echo 'Deploying app...'
# your deploy command here

# 5. Verify migration outcome
npx prisma migrate status

# 6. Resolve a failed migration after manual fix
# npx prisma migrate resolve --applied 20240101000000_migration_name
# Or mark as rolled back
# npx prisma migrate resolve --rolled-back 20240101000000_migration_name

CI/CD Integration

# .github/workflows/deploy.yml
- name: Run database migrations
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
  run: |
    npx prisma migrate deploy
    # Verify schema is in sync
    npx prisma db pull --print | diff - prisma/schema.prisma

Seed Data

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  await prisma.user.upsert({
    where: { email: 'test@example.com' },
    update: {},
    create: {
      email: 'test@example.com',
      name: 'Test User',
    },
  });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Package Configuration

{
  "prisma": {
    "seed": "npx ts-node prisma/seed.ts"
  }
}

The AI SaaS Starter Kit ships with Prisma configured, a migration workflow set up, and seed data ready for development. $99 one‑time.