Migration troubleshooting in production

The following guide describes issues relating to Prisma Migrate that can occur in production, and how to resolve them.

Failed migration

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.

There are two ways to deal with failed migrations in a production environment:

  • Roll back, optionally fix issues, and re-deploy
  • Manually complete the migration steps and resolve the migration

Option 1: Mark the migration as rolled back and re-deploy

The following example demonstrates how to roll back a migration, optionally make changes to fix the issue, and re-deploy:

  1. Mark the migration as rolled back - this updates the migration record in the _prisma_migrations table to register it as rolled back, allowing it to be applied again:

    $prisma migrate resolve --rolled-back "20201127134938_added_bio_index"
  2. If the migration was partially run, you can either:

    • Modify the migration to check if a step was already completed (for example: CREATE TABLE ... IF NOT EXISTS) OR
    • Manually revert the steps that were completed (for example, delete created databases)

    If you modify the migration, make sure you copy it back to source control to ensure that state of your production database is reflected exactly in development.

  3. Fix the root cause of the failed migration, if relevant - for example, if the migration failed due to an issue with the SQL script itself. Make sure that you copy any changed migrations back to source control.

  4. Re-deploy the migration:

    $prisma migrate deploy

Option 2: Manually complete migration and resolve as applied

The following example demonstrates how to manually complete the steps of a migration and mark that migration as applied.

  1. Manually complete the migration steps on the production database. Make sure that any manual steps exactly match the steps in the migration file, and copy any changes back to source control.

  2. Resolve the migration as applied - this tells Prisma Migrate to consider the migration successfully applied:

    $prisma migrate resolve --applied "20201127134938_my_migration"

Migration history conflicts

prisma migrate deploy issues a warning if an already applied migration has been edited - however, it does not stop the migration process. To remove the warnings, restore the original migration from source control.

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 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. Follow GitHub issue #6485 for updates.

Edit this page on GitHub