Prisma migrations in CI/CD: patterns, pitfalls and fixes

Database migrations in production are where careful planning meets real consequences. A poorly timed schema change can lock tables for hours, corrupt data, or trigger a rollback that leaves your team scrambling at 2 AM. Prisma migrations in CI/CD: patterns, pitfalls and fixes gives us a framework to treat database changes with the same rigor we apply to code reviews, automated testing, and staged deployments.

Migrate dev locally, migrate deploy in pipelines

The foundation of safe database evolution is a clear separation of concerns: local development uses prisma migrate dev, while CI/CD pipelines use prisma migrate deploy. This distinction matters because they behave differently and serve different purposes.

prisma migrate dev is interactive. It detects schema drift (when your schema.prisma doesn't match the database), prompts you to reset if needed, generates new migrations, and applies them immediately. It's perfect for local iteration—you make schema changes, run the command, and get instant feedback. When you commit your work, the migration files go into version control as part of your pull request.

In contrast, prisma migrate deploy is deterministic and non-interactive. It reads the migrations directory, compares it against the database's migrations table, and runs only the pending ones. No prompts, no resets, no generation. This is what runs in your staging and production pipelines. The command fails if the database is in an unexpected state, which is intentional—it prevents silent data loss and forces you to investigate issues explicitly.

Here's a realistic workflow:

// Local: You're adding a new user field
// schema.prisma changes
model User {
  id Int @id @default(autoincrement())
  email String @unique
  name String
  tier String @default("free")  // NEW
}

// Run locally
$ prisma migrate dev --name add_user_tier

// Creates 20240215120000_add_user_tier.sql
// Applies it immediately to your local database
// You test, verify, commit

// In CI/CD pipeline (staging or production)
$ prisma migrate deploy

// Applies only pending migrations (the one you just created)
// Fails if database state is unexpected
// Succeeds silently if already applied (idempotent)

Never mix these. Don't run prisma migrate dev against a shared environment. Don't run prisma migrate deploy locally and expect interactivity. Keep your local workflow loose and your pipeline workflow strict.

Never run migrate dev against production

This deserves its own section because the consequences are severe enough to warrant paranoia.

prisma migrate dev can reset your database. If Prisma detects that your local schema doesn't match the migrations, it will ask if you want to reset. In an automated pipeline with the --skip-generate flag and no human at the keyboard, "asking" becomes "do it anyway" on some CI systems. More commonly, a developer with production database credentials might accidentally run prisma migrate dev locally against prod, intending to troubleshoot, and suddenly their schema is reset.

Prevent this with a multi-layered defense:

1. Use environment-specific database URLs. Your CI/CD pipeline should inject a production URL that is different from your local .env. Never store production credentials locally. If you absolutely need to inspect production data, use read-only replica credentials.
2. Add a pre-flight check. Some teams add a script that refuses to run Prisma commands if certain environment variables are set to production values. This is a safety rope.
3. Use database user roles. Your CI/CD pipeline runs migrations with a specific database user that has schema-modification permissions. Your application runs with a different user that has only read/write permissions on existing tables. A stray command can't reset the schema.
4. Require explicit approval for production migrations. Use your CI/CD platform's approval gates. A pipeline should never touch production without human sign-off.

Example of environment-based safety:

// .env.local (never committed, only on your machine)
DATABASE_URL="postgresql://localhost:5432/myapp_dev"

// .env.production (injected by CI/CD at deploy time)
DATABASE_URL="postgresql://prod-user:$SECRET@prod-db.internal:5432/myapp"

// CI script that refuses to proceed
if [[ "$DATABASE_URL" == *"prod-db"* ]]; then
  if [[ -z "$CI_APPROVAL_TOKEN" ]]; then
    echo "Production migrations require approval"
    exit 1
  fi
fi

prisma migrate deploy

Review SQL in pull requests

Migrations are code. The SQL they generate should be reviewed with the same care as business logic, because bad SQL can cause downtime, data corruption, or performance degradation.

When a developer runs prisma migrate dev --name add_user_tier, Prisma generates a SQL file in your prisma/migrations/ directory. This file gets committed and becomes part of the pull request. Your team should review it before merging.

What are you looking for?

• Does it match the schema intent? A new field should be added with correct type and constraints. A renamed field should use a two-step migration (add, copy, drop), not a single rename that Prisma might generate incorrectly for some databases.
• Will it lock the table? Some operations (adding a NOT NULL column without a default, or adding a UNIQUE constraint) lock the table during the migration. In PostgreSQL, these need special handling with CONCURRENTLY or staged approaches.
• Does it handle rollback? Each migration file should have a -- Down section if your team practices reversible migrations, though Prisma doesn't generate these by default.
• Is the data transition correct? If you're adding a NOT NULL field, is there a default? If you're renaming a field, are you copying data correctly?

Here's a problematic migration that code review should catch:

-- prisma/migrations/20240215_add_status.sql
-- PROBLEM: This locks the entire 'orders' table during migration
ALTER TABLE "orders" ADD COLUMN "status" VARCHAR(50) NOT NULL;

-- BETTER: Use a two-step approach for zero-downtime
-- Step 1: Add nullable, populate it, then make NOT NULL
ALTER TABLE "orders" ADD COLUMN "status" VARCHAR(50);
UPDATE "orders" SET "status" = 'pending' WHERE "status" IS NULL;
ALTER TABLE "orders" ALTER COLUMN "status" SET NOT NULL;

Make SQL review a checklist item in your PR template. Have one or two senior engineers on your team become migration specialists. Run experimental migrations against a production-like replica to measure lock times. Document any blocking migrations so the on-call team knows what to expect during deployment.

Backward-compatible migrations for zero downtime

The gold standard for production safety is a migration that your old code can run against while you're deploying new code. This requires thinking in stages: code that reads but doesn't write to new columns; code that writes but doesn't break on new columns; code that uses new columns as primary logic.

A classic pattern is the expand-contract migration:

1. Expand: Add the new column or table. Old code ignores it; new code starts writing to it.
2. Migrate: Run a backfill job to populate existing rows. This can happen async, in the background.
3. Contract: Remove the old column or logic. At this point, all data is on the new path.

Here's a real scenario: you're adding a user_tier column to replace inline tier logic.

// schema.prisma (Expand)
model User {
  id Int @id @default(autoincrement())
  email String @unique
  isPremium Boolean @default(false)  // OLD
  userTier String @default("free")   // NEW
}

// Migration 1: Add the column
ALTER TABLE "users" ADD COLUMN "user_tier" VARCHAR(50) DEFAULT 'free';

// Migration 2: Backfill data
UPDATE "users" SET "user_tier" = CASE 
  WHEN "is_premium" = true THEN 'premium'
  ELSE 'free'
END;

// Application code (Expand phase)
// Old code reads is_premium, new code writes user_tier
export async function getUserTier(userId: number) {
  const user = await prisma.user.findUnique({ where: { id: userId } });
  return user.userTier || (user.isPremium ? 'premium' : 'free');
}

// Later: Contract phase
// schema.prisma removes is_premium
model User {
  id Int @id @default(autoincrement())
  email String @unique
  userTier String @default("free")
}

// Migration 3: Drop the old column (only after you're confident new code is stable)
ALTER TABLE "users" DROP COLUMN "is_premium";

This approach means you can deploy new code that writes to userTier, then deploy the schema change, then run the backfill, then deploy again to remove the old logic—all without downtime. The database state is always compatible with at least one version of your code.

For large tables, backfill jobs should be chunked and run separately from the main migration:

// A separate job, not in the main prisma migrate
async function backfillUserTiers() {
  const BATCH_SIZE = 1000;
  let offset = 0;
  
  while (true) {
    const users = await prisma.user.findMany({
      where: { userTier: 'free', isPremium: true },
      take: BATCH_SIZE,
      skip: offset,
    });
    
    if (users.length === 0) break;
    
    await prisma.user.updateMany({
      where: { id: { in: users.map(u => u.id) } },
      data: { userTier: 'premium' },
    });
    
    offset += BATCH_SIZE;
    await new Promise(r => setTimeout(r, 100)); // Rate limit
  }
}

Common mistakes

• Committing .env.production or credentials: A migration might reference hardcoded environment-specific values. Use environment variables, not magic strings. Review every migration file for secrets before merge.
• Forgetting to commit migration files: A developer runs prisma migrate dev locally, the migration works, but they forget to commit the generated SQL file. The pipeline runs prisma migrate deploy and finds nothing to apply. The schema changes don't reach production. Always verify migration files are in git before pushing.
• Running migrations in parallel: Two deployments shouldn't run migrations simultaneously. Use database-level locking or a deployment queue to serialize migrations. Prisma uses the _prisma_migrations table to prevent this, but some CI systems can still trigger race conditions if not configured correctly.
• Ignoring lock times in tests: A migration that takes 2 seconds locally might take 20 minutes on your production table. Always test migrations against a realistic dataset before deploying. Use EXPLAIN ANALYZE on your migration SQL against production replicas.
• Mixing schema changes and data migrations: A single migration file that both alters schema and updates millions of rows can time out. Split these into separate migrations. The schema change is fast; the data update runs as a background job.

Key takeaways

• Use prisma migrate dev locally for interactive iteration, and prisma migrate deploy in CI/CD pipelines for deterministic application. Never mix them.
• Treat production database credentials with extreme paranoia. Environment-specific URLs, read-only replicas, and approval gates prevent accidental resets.
• Review generated SQL in pull requests for correctness, lock times, and data integrity. Make migration review a formal part of your code review process.
• Design migrations to be backward-compatible with your running code. Use expand-contract patterns so you can deploy without downtime.
• Test migrations against production-scale data before deploying. A safe migration locally can become a production incident on a large table.
• Automate backfill jobs separately from schema migrations. Large data updates should be chunked, rate-limited, and observable.