Troubleshooting

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:

• Production troubleshooting
• Patching / hotfixing production databases

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.

• 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.

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.

Schema drift can occur if:

• The database schema was changed without using migrations - for example, by using prisma db push or 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.

If you made manual changes to the database that you do not want to keep, or can replicate in the Prisma schema:

1. Reset your database:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate reset

   pnpm prisma migrate reset

   yarn prisma migrate reset

   npx prisma migrate reset

   2. Replicate the changes in the Prisma schema and generate a new migration:

2. Replicate the changes in the Prisma schema and generate a new migration:

   • Tab 1
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev

   pnpm prisma migrate dev

   yarn prisma migrate dev

   npx prisma migrate dev

If you made manual changes to the database that you want to keep, you can:

1. Introspect the database:

   • Tab 1
   • pnpm
   • yarn
   • npm

   bunx prisma db pull

   pnpm prisma db pull

   yarn prisma db pull

   npx prisma db pull

   Prisma will update your schema with the changes made directly in the database.

2. Generate a new migration to include the introspected changes in your migration history:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev --name introspected_change

   pnpm prisma migrate dev --name introspected_change

   yarn prisma migrate dev --name introspected_change

   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.

   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.

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.

The most direct way to handle a failed migration in a development 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.sql file 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):

  1. Delete the migration.sql file.

  2. Modify the schema - for example, add a default value to the mandatory field.

  3. 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

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 error

Error querying the database: db error: ERROR: prepared statement "s0" already exists

See Prisma Migrate and PgBouncer workaround for further information and a workaround.