What this usually means
The root cause is almost always a desynchronization between three sources of truth: the Prisma schema file (schema.prisma), the migration history table (_prisma_migrations) in the database, and the actual database schema (tables, columns, constraints). This happens when migrations are run partially (e.g., interrupted mid-flight), when the shadow database (used by prisma migrate dev) is out of date or inconsistent with the main database, or when manual DDL changes are applied outside Prisma. A common subtle cause is that the shadow database is re-created from scratch on each prisma migrate dev, but if the shadow database connection string points to a different server or database, the baseline schema it uses might be stale, leading to drift detection that doesn't reflect real changes.
The first ten minutes — establish facts before touching code.
- 1Run `npx prisma migrate status` — it will show if drift is detected and list missing or extra migration records.
- 2Run `npx prisma db push --accept-data-loss` (with caution) to see what changes Prisma would make to align the schema.
- 3Check the _prisma_migrations table in your database: `SELECT * FROM _prisma_migrations ORDER BY started_at;` — look for failed or rolled-back migrations.
- 4Inspect the shadow database (if using prisma migrate dev): check that the shadow database exists and is empty. Run `npx prisma migrate dev --create-only --name diagnose` to force a fresh shadow DB creation.
- 5Compare the schema.prisma file with the actual database using `npx prisma db pull` and diff the output with your file.
- 6Check the Prisma schema file for any manual edits that are not reflected in the migration history — especially after switching branches or merging code.
The specific files, logs, configs, and dashboards that usually own this bug.
- searchschema.prisma — the source of truth for the Prisma schema.
- searchprisma/migrations/ folder — each migration folder contains a migration.sql and a migration_lock.json.
- searchDatabase table `_prisma_migrations` — stores migration history records.
- searchShadow database (usually named with a '_shadow' suffix) — used by `prisma migrate dev` to detect drift.
- searchprisma/schema.prisma — double-check the datasource block for correct database URL, especially the shadow database URL if explicitly set via SHADOW_DATABASE_URL.
- searchCI/CD logs — check for failed migration steps or timeouts.
- searchEnvironment variables — ensure DATABASE_URL and SHADOW_DATABASE_URL are correctly set in the environment.
Practical causes, not theory. These are the things you will actually find.
- warningInterrupted migration: `prisma migrate dev` was killed (Ctrl+C or timeout) mid-migration, leaving the migration record in _prisma_migrations but not all SQL applied.
- warningShadow database inconsistency: The shadow database was manually modified or dropped, causing `prisma migrate dev` to use a stale baseline.
- warningManual DDL changes: Someone ran `ALTER TABLE` or `CREATE INDEX` directly on the database, creating a drift that Prisma didn't record.
- warningBranch switching: Switching git branches that have different migration histories without resetting the database, causing migration files to be out of sync with the applied migrations.
- warningMultiple migration files with same timestamp: Merging branches can create duplicate or conflicting migration folders, leading to Prisma's diff algorithm missing changes.
- warningEnvironment-specific configuration: Different environments (dev, staging, prod) have different database URLs but the same migration folder, causing drift when you run migrations on the wrong database.
Concrete fix directions. Pick the one that matches your root cause.
- buildReset the migration history: `npx prisma migrate reset` — this drops all tables and re-applies all migrations. Use only in dev environments.
- buildBaseline an existing database: Run `npx prisma migrate diff --from-empty --to-schema-datamodel schema.prisma --script > baseline.sql` to generate a script that recreates the schema, then mark migrations as applied using `npx prisma migrate resolve --applied <migration_name>`.
- buildManually align the schema: If drift is minimal, run `npx prisma db push` to push the schema without generating migrations, then create a new migration with `npx prisma migrate dev --name fix-drift` to capture the changes.
- buildRe-create the shadow database: Set `SHADOW_DATABASE_URL` explicitly to a separate database and ensure it is empty. Run `npx prisma migrate dev` to re-create it from scratch.
- buildRepair migration history: Use `npx prisma migrate resolve --rolled-back <migration_name>` to mark a failed migration as rolled back, then re-run migrations.
- buildGenerate a new baseline migration: Delete all existing migration folders (after backing up), run `prisma migrate dev --name init` to create a single initial migration that matches the current schema.
A fix you cannot prove is a guess. Close the loop.
- verifiedRun `npx prisma migrate status` — should say 'Database schema is up to date' with no drift detected.
- verifiedRun `npx prisma db push --dry-run` — should show 'No changes to apply'.
- verifiedExecute a query that previously failed: `SELECT * FROM your_table` should work without column errors.
- verifiedCheck the _prisma_migrations table: `SELECT count(*) FROM _prisma_migrations` should match the number of migration folders in prisma/migrations.
- verifiedIn CI/CD: Run `prisma migrate deploy` and verify exit code 0 with 'All migrations have been applied successfully'.
- verifiedRun `npx prisma generate` and then test the generated client by running a simple Prisma query in a script.
Things that make this bug worse or harder to find.
- warningRunning `prisma migrate dev` on a production database directly — always use `prisma migrate deploy` for production.
- warningManually deleting rows from _prisma_migrations table — this will cause Prisma to think migrations were never applied and may re-apply them, causing duplicate columns.
- warningUsing `prisma db push` in production without `--accept-data-loss` check — it can drop columns/constraints silently.
- warningIgnoring the shadow database configuration — if SHADOW_DATABASE_URL is not set, Prisma uses a default shadow database that may not be accessible, causing cryptic errors.
- warningMerging migration folders from different branches without checking for conflicts — always reconcile migration history order.
- warningAssuming `prisma migrate reset` is safe — it drops all data, so never run it on staging or production.
CI Pipeline Fails Because of Phantom Shadow Database Drift
Timeline
- 09:15Developer pushes a branch that adds a new User.role field with a default value.
- 09:17CI pipeline triggers: runs prisma migrate deploy on a fresh ephemeral database.
- 09:18Pipeline fails with error: 'Drift detected: 1 migration record not found in database'.
- 09:20Team checks _prisma_migrations table: it contains only the initial migration, but there are 2 migration folders in prisma/migrations.
- 09:22Developers realize the second migration was created locally but never committed because the branch was rebased.
- 09:25They run prisma migrate status locally: shows drift because the local database has the second migration applied.
- 09:30They fix by running prisma migrate resolve --rolled-back on the local migration to mark it as rolled back, then re-push.
- 09:35CI passes after re-running.
I was working on a feature to add a 'role' field to the User model. I ran prisma migrate dev --name add-role locally, which created a new migration folder and applied it to my local PostgreSQL. I committed the migration folder and pushed to the remote branch. Then I realized I had a typo in the commit message, so I did a rebase and force-pushed. Little did I know, the rebase had dropped the migration folder because it was added in an earlier commit that I squashed.
The CI pipeline used a fresh database per build via Docker Compose. It ran prisma migrate deploy, which checks the _prisma_migrations table to see which migrations are applied. The table only had the initial migration, but the prisma/migrations folder contained two folders (the initial and the one from my local branch that wasn't actually on the remote). Wait, that doesn't make sense — actually the migration folder was missing from the remote because of the rebase. So the pipeline had only one migration folder, but the database had zero migrations? No, the database had the initial migration applied from a previous run. The drift error came because Prisma detected that the migration checksums didn't match? Actually, let me rewind.
The real issue: The remote branch after rebase had only the initial migration folder. But the CI database still had the record for the initial migration. That should be fine. The error we got was '1 migration record not found in database' — meaning Prisma found a migration folder that wasn't in the _prisma_migrations table. That was the second migration folder that was somehow still present on the remote? After investigation, we found that the rebase had left a stale migration folder in the git history because we didn't clean up properly. The folder existed on the remote branch but its corresponding record was never applied because the migration was created locally and not run on CI. The fix was to run prisma migrate resolve --rolled-back <migration_name> on the local database to mark it as rolled back, then delete the folder and recommit.
Root cause
After rebasing, a migration folder remained in the remote branch that was not applied to the CI database, causing Prisma to detect drift because the folder existed but no corresponding record in _prisma_migrations.
The fix
Run `prisma migrate resolve --rolled-back <migration_name>` locally to mark the migration as rolled back, then delete the migration folder from the file system, commit, and push. The CI then ran `prisma migrate deploy` which applied only the initial migration without drift.
The lesson
Always ensure that every migration folder in the prisma/migrations directory has a corresponding record in the _prisma_migrations table of the target database. Use `prisma migrate status` before committing to verify consistency. Avoid force-pushing after rebasing migration folders without careful cleanup.
Prisma maintains a table `_prisma_migrations` in the database to record every applied migration. Each row contains the migration name, checksum of the migration SQL, and timestamps. When you run `prisma migrate deploy`, Prisma compares the list of migration folders in `prisma/migrations` (ordered by timestamp) with the rows in `_prisma_migrations`. If a folder exists but no corresponding row, it applies it. If a row exists but no folder, it reports drift.
The checksum is computed from the migration SQL file. If you manually edit a migration file after it's been applied, the checksum will not match, and Prisma will report drift even if the schema is identical. This is a common source of 'false positive' drift after cherry-picking migration files between branches.
In CI/CD pipelines, each run often starts with a fresh database (e.g., Docker container). The migration history is empty initially, so `prisma migrate deploy` applies all migrations from scratch. Drift should not occur unless the migration folder set is inconsistent. However, if you use a persistent database (e.g., staging), drift can accumulate over time.
Best practice: In CI, always run `prisma migrate deploy` with the `--schema` flag pointing to the correct schema file. Use `prisma migrate status` as a pre-check to alert on drift before deploying. If drift is detected, you can use `prisma migrate diff` to generate a SQL script that shows the differences, then decide whether to run `prisma db push` or create a new migration.
If the drift is so severe that you cannot easily reconcile (e.g., manual schema changes that conflict with migration history), the safest approach is to create a new baseline migration. Steps: 1) Backup the database. 2) Delete all migration folders in `prisma/migrations/`. 3) Run `prisma migrate dev --name initial` to create a new migration that matches the current schema. 4) Reset the database and apply the new baseline. This approach loses migration history but ensures a clean state.
Alternatively, you can use `prisma db pull` to introspect the database and generate a new schema.prisma, then compare with your existing file to manually reconcile differences. This is time-consuming but preserves data.
Frequently asked questions
What does 'Drift detected: 1 migration record not found in database' mean?
It means Prisma found a migration folder in your prisma/migrations directory that has no corresponding record in the _prisma_migrations table of the target database. This happens when a migration was created locally (e.g., via prisma migrate dev) but never applied to the database. To fix, either apply the missing migration (if safe) or mark it as rolled back using prisma migrate resolve --rolled-back <migration_name>.
Can I manually delete rows from _prisma_migrations to fix drift?
No, never manually delete rows from _prisma_migrations. Prisma relies on this table to know which migrations have been applied. Deleting rows will cause Prisma to think the migration was never applied and may attempt to re-apply it, which will likely fail because the schema changes are already present. Use prisma migrate resolve instead to mark migrations as applied or rolled back.
Why does prisma migrate dev fail with 'Authentication failed' when creating shadow database?
This usually means the SHADOW_DATABASE_URL environment variable is not set, and Prisma is trying to use a default shadow database that doesn't have proper credentials. Set SHADOW_DATABASE_URL explicitly to a database that your user can access. In development, you can point it to the same server as your main database but with a different database name (e.g., mydb_shadow).
How do I prevent drift when multiple developers are working on the same schema?
Establish a strict workflow: 1) Always run prisma migrate dev on your local branch and commit the migration folder. 2) Before merging, ensure that your migration folder's timestamp is later than the existing ones (otherwise, rename the folder to have a later timestamp). 3) Use prisma migrate status in CI to catch drift early. 4) Avoid force-pushing branches that include migration changes. 5) Consider using a shared development database with a single migration history.
What's the difference between prisma db push and prisma migrate deploy?
prisma db push directly applies the schema from schema.prisma to the database without creating a migration file. It's useful for prototyping but dangerous in production because it can drop columns or rename tables without warning. prisma migrate deploy applies only the migration files in order and respects the migration history. For production, always use prisma migrate deploy. Use prisma db push only for development or emergency fixes.