Prisma Migrations Guide - migrate dev/deploy, Shadow DB, and CI

Status: Active
Last Updated: 2026-08-26
Category: Databases - Operations
Prerequisites: prisma-connections, schema-overview
Time: 2 hours
Tags: prisma, migrations, postgres, ci, shadow-database, drift

Summary

The complete Prisma Migrate workflow for fogserv.cloud: migrate dev for local iteration, migrate deploy for production, how the shadow database works, how to detect and resolve drift, baselining an existing database, and wiring the whole thing into Woodpecker CI.

🎯 What You'll Learn

By the end of this article, you'll be able to:

• ✅ Run a safe migrate dev → commit → migrate deploy workflow
• ✅ Explain what the shadow database does and configure it
• ✅ Detect and resolve schema drift without data loss
• ✅ Baseline a pre-existing database into migration history
• ✅ Add migration checks to a CI pipeline

---

Context / Why This Matters

Prisma is our ORM layer (prisma-connections), and the CRM/CMS schema (schema-overview) has 21 models with real cascade rules. Ad-hoc changes (db push) are fine for throwaway prototypes but destroy reproducibility: production must be rebuilt from Git plus ordered migrations, or it isn't rebuildable at all. Migrations are the bridge between the code in Forgejo and the state of the database.

Implementation / Core Content

The Two Commands That Matter

# Development ONLY: creates/edits migrations, applies them,
# regenerates client, may reset the DB (destructive!)
bunx prisma migrate dev --name add_campaign_indexes

# Production / CI ONLY: applies pending migrations in order,
# never resets, never edits, fails safely on conflicts
bunx prisma migrate deploy

Rule: whoever runs migrate dev owns the migration files until they're committed. Never hand-edit applied migrations; to fix a bad one, create a new migration that corrects it.

The Shadow Database

migrate dev needs a scratch database (the shadow DB) to:

1. Replay your full existing migration history from zero.
2. Apply your current schema changes on top.
3. Generate SQL and detect drift between history and reality.

Configuration:

# .env — only needed when the DB user can't CREATE DATABASE
SHADOW_DATABASE_URL=postgresql://prisma:...@localhost:5432/shadow_db

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

With local Docker Postgres (see docker-basics) the default superuser can create the shadow DB automatically — no config needed. On managed providers where CREATE DATABASE is restricted, create one shadow database per developer and point shadowDatabaseUrl at it.

Drift: When History and Reality Disagree

Drift exists when the actual database no longer matches replaying the migration folder. Common causes:

• Someone ran db push against an environment managed by migrations.
• A hotfix was applied by hand in prod.
• Migration files were edited after being applied.

Detect it:

# Compares DB against migration history; exits non-zero on drift
bunx prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-schema-datamodel ./prisma/schema.prisma \
  --shadow-database-url "$SHADOW_DATABASE_URL" \
  --exit-code

Resolve it deliberately, not by reflexively running migrate dev --force:

# Option A: adopt current DB state as new truth (keeps data)
bunx prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-url "$DATABASE_URL" \
  --script > prisma/migrations/20260826_resolve_drift/migration.sql

# Option B: dev-only, disposable data → reset cleanly
bunx prisma migrate reset

Commit the resolution like any other migration so every environment converges again.

Baselining an Existing Database

If prod predates Prisma Migrate (or was built entirely with db push):

# 1. Initialize history without touching the DB
bunx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > 0_init/migration.sql

# 2. Move the folder into prisma/migrations/0_init/

# 3. Mark it as already-applied everywhere
bunx prisma migrate resolve --applied 0_init

# From then on, normal migrate dev / deploy flow works.

Baselining writes nothing to the database — it just seeds history so future diffs start clean.

Production Workflow

1. Merge PR containing prisma/migrations/* to main (Forgejo).
2. Deploy step runs bunx prisma migrate deploy before starting the new app version.
3. App starts; old app version should tolerate both old and new schema during the window (expand-then-contract pattern).

Expand-then-contract for risky changes: add column (migration 1) → deploy code that writes both columns (release N+1) → backfill → drop old column (migration 2). Avoids downtime without lock-heavy ALTERs.

CI Integration

Minimal check in Woodpecker:

steps:
  migration-check:
    image: oven/bun:1
    environment:
      DATABASE_URL: postgresql://ci:ci@database:5432/ci
    commands:
      - bun install --frozen-lockfile
      - bunx prisma migrate deploy   # must apply cleanly on fresh DB
      - bunx prisma validate
services:
  database:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: ci
      POSTGRES_PASSWORD: ci
      POSTGRES_DB: ci

This catches broken migration chains (missing dependencies, non-replayable SQL) before they reach prod.

Practical Examples

Example 1: Adding a field end-to-end

dotenvx run -- bunx prisma migrate dev --name post_add_reading_time
# Creates prisma/migrations/20260826120000_post_add_reading_time/{migration.sql,migration_lock.toml}
git add prisma && git commit -m "schema: add Post.readingTime"

Generated SQL:

ALTER TABLE "Post" ADD COLUMN "readingTime" INTEGER NOT NULL DEFAULT 0;

Note the explicit default — required because the table has rows; otherwise the migration would fail on prod data.

Example 2: Verifying prod is up to date

bunx prisma migrate status
# Output:
# 3 migrations found in prisma/migrations
# Following migrations have been applied: ...
# Database schema is up to date!

Common Pitfalls & Troubleshooting

Problem	Cause	Fix
P3006: migration failed in shadow DB	Non-replayable SQL (e.g., references env-specific state)	Rewrite migration to be deterministic; test on fresh DB
P3019: destructive change blocked	Dropping/renaming column with data	Confirm intent with --accept-data-loss, or stage via expand-then-contract
P3020: automatic migration reset offered	History doesn't match DB	Resolve drift explicitly (diff + new migration), don't blindly accept
Shadow DB errors on managed hosting	User lacks CREATE DATABASE	Provide dedicated shadowDatabaseUrl
Two devs created conflicting migrations	Parallel branches off same base	Rebase, delete the losing duplicate migration, re-run migrate dev
Prod never got new migration	Forgot migrate deploy in release script	Add it as mandatory pre-start step in the deploy pipeline

Next Steps / Ops Actions

• Ensure pooling is configured before scaling connections: prisma-connections and postgres-tuning-basics.
• Add the CI job above next to lint/type-check in the pipeline (cicd-concepts).
• Schedule backups that include the migration history directory: backup-recovery-drill.
• If any environment still uses db push, convert it with baselining this week.

Sources & Related

External references consulted:

• https://www.prisma.io/docs/orm/prisma-client/running-prisma-migrate
• https://www.prisma.io/docs/orm/prisma-migrate/workflows/baselining
• https://www.prisma.io/docs/orm/prisma-migrate/understanding-prisma-migrate/shadow-database

Related knowledge-base articles:

• prisma-connections
• schema-overview
• ../research/prisma.md
• woodpecker-first-pipeline

Change Log

2026-08-26

• Initial creation covering dev/deploy workflow, shadow DB, drift resolution, baselining, and CI checks.