Migration troubleshooting in development
This guide describes how to resolve issues with Prisma Migrate in a development environment, which often involves resetting your database. For production-focused troubleshooting, see:
This guide does not apply for MongoDB.
db push is used for MongoDB.
Handling migration history conflicts
A migration history conflict occurs when there are discrepancies between the migrations folder in the file system and the
_prisma_migrations table in the database.
Causes of migration history conflict in a development environment
- A migration that has already been applied is later modified
- A migration that has already been applied is missing from the file system
In a development environment, switching between feature branches can result in a history conflict because the
_prisma_migrations table contains migrations from
branch-1, and switching to
branch-2 might cause some of those migrations to disappear.
Note: You should never purposefully delete or edit a migration, as this might result in discrepancies between development and production.
Fixing a migration history conflict in a development environment
If Prisma Migrate detects a migration history conflict when you run
prisma migrate dev, the CLI will ask to reset the database and reapply the migration history.
Database schema drift occurs when your database schema is out of sync with your migration history - the database schema has 'drifted away' from the source of truth.
Causes of schema drift in a development environment
Schema drift can occur if:
- The database schema was changed without using migrations - for example, by using
prisma db pushor manually changing the database schema.
Note: The shadow database is required to detect schema drift, and can therefore only be done in a development environment.
Fixing schema drift in a development environment
If you made manual changes to the database that you do not want to keep, or can easily replicate in the Prisma schema:
Reset your database:$npx prisma migrate reset
Replicate the changes in the Prisma schema and generate a new migration:$npx prisma migrate dev
If you made manual changes to the database that you want to keep, you can:
Introspect the database:$npx prisma db pull
Prisma will update your schema with the changes made directly in the database.
Generate a new migration to include the introspected changes in your migration history:$npx prisma migrate dev --name introspected_change
Prisma Migrate will prompt you to reset, then applies all existing migrations and a new migration based on the introspected changes. Your database and migration history are now in sync, including your manual changes.
Causes of failed migrations in a development environment
A migration might fail if:
- You modify a migration before running it and introduce a syntax error
- You add a mandatory (
NOT NULL) column to a table that already has data
- The migration process stopped unexpectedly
- The database shut down in the middle of the migration process
Each migration in the
_prisma_migrations table has a
logs column that stores the error.
Fixing failed migrations in a development environment
The easiest way to handle a failed migration in a developer environment is to address the root cause and reset the database. For example:
If you introduced a SQL syntax error by manually editing the database, update the
migration.sqlfile that failed and reset the database:$prisma migrate reset
If you introduced a change in the Prisma schema that cannot be applied to a database with data (for example, a mandatory column in a table with data):
Modify the schema - for example, add a default value to the mandatory field.
Migrate:$prisma migrate dev
Prisma Migrate will prompt you to reset the database and re-apply all migrations.
If something interrupted the migration process, reset the database:$prisma migrate reset
Prisma Migrate and PgBouncer
You might see the following error if you attempt to run Prisma Migrate commands in an environment that uses PgBouncer for connection pooling:
Error: undefined: Database errorError querying the database: db error: ERROR: prepared statement "s0" already exists
See Prisma Migrate and PgBouncer workaround for further information and a workaround. Follow GitHub issue #6485 for updates.