disclaimer: newbie here 1st time ever ran into this problem anyone reading this pls suggest some better methods
If you’ve been working with Prisma and PostgreSQL, you might run into a situation where your database schema and Prisma migration history are out of sync. This often happens when:
- You make direct manual SQL changes instead of using Prisma migrations.
- You create an enum and use it in the same migration (which PostgreSQL doesn’t allow).
- You delete or modify migration files after they’ve been applied.
- Prisma tries to reset the database because it detects drift.
- Instead of resetting your database and losing data, I’ll walk you through how to sync your Prisma migrations with your actual PostgreSQL schema safely.
What is Schema Drift in Prisma?
Prisma tracks all applied migrations in a special table called _prisma_migrations. When you make direct SQL changes, Prisma isn’t aware of them, so it assumes your database is different from what it expects. This is called schema drift.
To check if your database is out of sync, run:
npx prisma migrate status
If you see something like "The database schema is not in sync with the migrations history", that means there’s a drift.
Step 1: Check Your Current Database Schema
First, you need to verify what your database schema looks like compared to your Prisma schema. Run:
npx prisma db pull
This command updates your schema.prisma file to reflect the current state of your PostgreSQL database. If there are changes that Prisma wasn’t tracking, you’ll see them here.
Now, compare your prisma/migrations folder with your schema.prisma. If you see differences, that means Prisma thinks some migrations haven’t been applied, or the database was changed manually.
Step 2: Identify and Remove Problematic Migrations
f Prisma thinks a migration is missing but you already applied the changes manually, you need to remove that migration from the _prisma_migrations table so Prisma stops trying to reapply it.
To list all applied migrations:
psql your_database -c "SELECT * FROM _prisma_migrations;"
Find the problematic migration name and delete it from the history.
To remove a specific migration:
DELETE FROM _prisma_migrations WHERE migration_name = '20240228000100_add_enum_and_table';
⚠️ Warning: Be very careful when deleting migrations! Always take a backup before making changes.
Step 3: Create and Apply a New Migration Without Resetting
Now that you’ve removed the problematic migration from Prisma’s history, you need to generate a new migration file that matches the current schema.
1️⃣ Run this command to create a new migration without applying it:
npx prisma migrate dev --create-only --name sync_schema
This generates a migration file inside prisma/migrations/, but it won’t run it yet.
2️⃣ Manually edit the migration file to make sure it accurately reflects your current schema.
3️⃣ Mark this migration as applied so Prisma won’t try to reapply changes:
npx prisma migrate resolve --applied "sync_schema"
Now, Prisma knows this migration is already applied and won’t try to reset your database.
Step 4: Verify That Everything is in Sync
Finally, check that Prisma recognizes your schema as up-to-date:
npx prisma migrate status
If everything is correct, you should see:
pgsql
Database schema is up to date!
To be extra sure, run:
npx prisma db pull
This ensures your schema.prisma is fully synced with the live database.
Best Practices to Avoid Schema Drift in Prisma
✅ Always use Prisma migrations instead of direct SQL changes.
✅ If you must make manual changes, immediately run prisma db pull to sync.
✅ Never modify an already applied migration file. Instead, create a new migration.
✅ Regularly check npx prisma migrate status to catch drift early.
✅ Use npx prisma migrate resolve instead of forcing resets to preserve data.
Top comments (1)
Haunted by the past? Create a new narrative.