DEV Community

Atlas Whoff
Atlas Whoff

Posted on

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

#prisma #postgres #typescript #webdev

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

Running prisma migrate deploy in production without a plan is how you cause outages.
Here's how to do database migrations safely.

The Problem

Most migration guides show you the happy path. Production is messier:

  • Live traffic during the migration window
  • Old app version still running until deployment completes
  • Failed migrations that need rollback
  • Long-running migrations that lock tables

Non-Destructive Migration Pattern

The safest approach: expand, then contract.

Step 1 — Expand (backward-compatible change):

-- Add new column as nullable (safe while old code runs)
ALTER TABLE users ADD COLUMN display_name TEXT;
Enter fullscreen mode Exit fullscreen mode

Step 2 — Backfill (background job):

// scripts/backfill-display-name.ts
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 sleep(100)  // don't hammer the DB
}
Enter fullscreen mode Exit fullscreen mode

Step 3 — Contract (once new code is fully deployed):

-- Now safe to make it required
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;
Enter fullscreen mode Exit fullscreen mode

Prisma Migration Workflow 

# Development: create migration from schema changes
npx prisma migrate dev --name add_display_name

# Preview what will run in production
npx prisma migrate status

# Production: apply pending migrations
npx prisma migrate deploy
Enter fullscreen mode Exit fullscreen mode

Custom SQL in Migrations 

# Generate migration file without running it
npx prisma migrate dev --name backfill_slugs --create-only
Enter fullscreen mode Exit fullscreen mode

Then edit the generated SQL:

-- 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);
Enter fullscreen mode Exit fullscreen mode

Adding Indexes Without Locking

Regular CREATE INDEX locks the table. Use CONCURRENTLY:

-- 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);
Enter fullscreen mode Exit fullscreen mode

In Prisma, you can add this to your migration SQL directly.

Pre-Deploy Checklist 

#!/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
Enter fullscreen mode Exit fullscreen mode

Handling Failed Migrations 

# Check which migration failed
npx prisma migrate status

# Mark a failed migration as resolved (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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Seed Data for Staging 

// prisma/seed.ts
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())
Enter fullscreen mode Exit fullscreen mode 
// package.json
{
  "prisma": {
    "seed": "npx ts-node prisma/seed.ts"
  }
}
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)