MIGRATIONS-production

Overview

Production database migrations require careful orchestration to prevent data loss and downtime. This covers safe migration deployment using

prisma migrate deploy

Production Migration Commands

Safe Command

prisma migrate deploy: Applies pending migrations only; records history in

_prisma_migrations

npx prisma migrate deploy

Prohibited Commands

prisma migrate dev: Creates migrations, can reset database (development-only)
prisma migrate reset: Drops/recreates database, deletes all data
prisma db push: Bypasses migration history, no rollback capability, risks data loss

CI/CD Integration

# GitHub Actions
name: Deploy to Production
on:
  push:
    branches: [main]
jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: '20'
      - run: npm ci
      - run: npx prisma generate
      - run: npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
      - run: npm run deploy

# GitLab CI
deploy-production:
  stage: deploy
  image: node:20
  only: [main]
  environment:
    name: production
  before_script:
    - npm ci && npx prisma generate
  script:
    - npx prisma migrate deploy
    - npm run deploy
  variables:
    DATABASE_URL: $DATABASE_URL_PRODUCTION

# Docker
FROM node:20-alpine
WORKDIR /app
COPY package*.json ./
RUN npm ci
COPY prisma ./prisma
RUN npx prisma generate
COPY . .
CMD ["sh", "-c", "npx prisma migrate deploy && npm start"]

Handling Failed Migrations

**Check status

**:

npx prisma migrate status

Resolution options:

• Temporary failure:

  npx prisma migrate resolve --applied <name> && npx prisma migrate deploy

• Partially reverted:

  npx prisma migrate resolve --rolled-back <name>

• Buggy migration: Create new migration to fix:

  npx prisma migrate dev --name fix_previous_migration

  , then deploy

Manual rollback: Create down migration (SQL to revert changes), apply via

npx prisma migrate dev --name rollback_* --create-only

Production Deployment Checklist

Pre-Deployment: All migrations tested in staging; backup created; rollback plan documented; downtime window scheduled; team notified

Deployment: Maintenance mode enabled (if needed); run

npx prisma migrate deploy

Post-Deployment: Verify all migrations applied; check functionality; monitor database performance; disable maintenance mode; document issues

Database Connection Best Practices

Connection pooling:

DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=20"

Security: Never commit DATABASE_URL; use environment variables, CI/CD secrets, or secret management tools (Vault, AWS Secrets Manager)

Read replicas: Separate migration connection from app connections; migrations always target primary database:

DATABASE_URL="postgresql://primary:5432/db"
DATABASE_URL_REPLICA="postgresql://replica:5432/db"

Zero-Downtime Migrations

Expand-Contract Pattern:

1. Expand (add new column, keep old):

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

   Deploy app writing to both.
2. Migrate data: `UPDATE "User" SET "email_new" = "email"

WHERE "email_new" IS NULL;

3. **Contract** (remove old):

Backwards-compatible: Make columns optional first, enforce constraints in later migration.

Monitoring and Alerts

Duration tracking:

time npx prisma migrate deploy

Failure alerts:

- run: npx prisma migrate deploy
- if: failure()
  run: curl -X POST $SLACK_WEBHOOK -d '{"text":"Production migration failed!"}'

Schema drift detection:

npx prisma migrate status

Common Production Issues

SET statement_timeout = '30s';

Configuration

Shadow Database (Prisma 6): Not needed for

migrate deploy

migrate dev

DATABASE_URL="postgresql://..."
SHADOW_DATABASE_URL="postgresql://...shadow"

Multi-Environment Strategy:

• Development:

  npx prisma migrate dev

• Staging:

  npx prisma migrate deploy

  (test production process)
• Production:

  npx prisma migrate deploy

  (apply only, never create)

References

Prisma Migrate Deploy Documentation | Production Best Practices | Troubleshooting Migrations