Migration troubleshooting in production

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

This guide does not apply for MongoDB.
Instead of migrate deploy, db push is used for MongoDB.

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"

Fixing failed migrations with migrate diff and db execute

To help with fixing a failed migration, Prisma provides the following commands for creating and executing a migration file:

  • prisma migrate diff which diffs two database schema sources to create a migration taking one to the state of the second. You can output either a summary of the difference or a sql script. The script can be output into a file via > file_name.sql or be piped to the db execute --stdin command.
  • prisma db execute which applies a SQL script to the database without interacting with the Prisma migrations table.

These commands are available in Preview in versions 3.9.0 and later (with the --preview-feature CLI flag), and generally available in versions 3.13.0 and later.

This section gives an example scenario of a failed migration, and explains how to use migrate diff and db execute to fix it.

Example of a failed migration

Imagine that you have the following User model in your schema, in both your local development environment and your production environment:

schema.prisma
1model User {
2 id Int @id
3 name String
4}

At this point, your schemas are in sync, but the data in the two environments is different.

You then decide to make changes to your data model, adding another Post model and making the name field on User unique:

schema.prisma
1model User {
2 id Int @id
3 name String @unique
4 email String?
5}
6
7model Post {
8 id Int @id
9 title String
10}

You create a migration called 'Unique' with the command prisma migrate dev -n Unique which is saved in your local migrations history. Applying the migration succeeds in your dev environment and now it is time to release to production.

Unfortunately this migration can only be partially executed. Creating the Post model and adding the email column succeeds, but making the name field unique fails with the following error:

ERROR 1062 (23000): Duplicate entry 'paul' for key 'User_name_key'

This is because there is non-unique data in your production database (e.g. two users with the same name).

You now need to recover manually from the partially executed migration. Until you recover from the failed state, further migrations using prisma migrate deploy are impossible.

At this point there are two options, depending on what you decide to do with the non-unique data:

  • You realize that non-unique data is valid and you cannot move forward with your current development work. You want to roll back the complete migration. To do this, see Moving backwards and reverting all changes
  • The existence of non-unique data in your database is unintentional and you want to fix that. After fixing, you want to go ahead with the rest of the migration. To do this, see Moving forwards and applying missing changes

Moving backwards and reverting all changes

In this case, you need to create a migration that takes your production database to the state of your data model before the last migration.

  • First you need your migration history at the time before the failed migration. You can either get this from your git history, or locally delete the folder of the last failed migration in your migration history.

  • You now want to take your production environment from its current failed state back to the state specified in your local migrations history:

    • Run the following prisma migrate diff command:

      npx prisma migrate diff --from-url "$DATABASE_URL_PROD" --to-migrations ./migrations --script > backward.sql**

      This will create a SQL script file containing all changes necessary to take your production environment from its current failed state to the target state defined by your migrations history.

    • Run the following prisma db execute command:

      npx prisma db execute --url "$DATABASE_URL_PROD" --file backward.sql

      This applies the changes in the SQL script against the target database without interacting with the migrations table.

    • Run the following prisma migrate resolve command:

      npx prisma migrate resolve --rolled-back Unique

      This will mark the failed migration called 'Unique' in the migrations table on your production environment as rolled back.

Your local migration history now yields the same result as the state your production database is in. You can now modify the datamodel again to create a migration that suits your new understanding of the feature you're working on (with non-unique names).

Moving forwards and applying missing changes

In this case, you need to fix the non-unique data and then go ahead with the rest of the migration as planned:

  • The error message from trying to deploy the migration to production already told you there was duplicate data in the column name. You need to either alter or delete the offending rows.

  • Continue applying the rest of the failed migration to get to the data model defined in your prisma.schema:

    • Run the following prisma migrate diff command:

      npx prisma migrate diff --from-url "$DATABASE_URL_PROD" --to-schema-datamodel schema.prisma --script > forward.sql

      This will create a SQL script file containing all changes necessary to take your production environment from its current failed state to the target state defined in your prisma.schemafile.

    • Run the following prisma db execute command:

      npx prisma db execute --url "$DATABASE_URL_PROD" --file forward.sql

      This applies the changes in the SQL script against the target database without interacting with the migrations table.

    • Run the following prisma migrate resolve command:

      npx prisma migrate resolve --applied Unique

      This will mark the failed migration called 'Unique' in the migrations table on your production environment as applied.

Your local migration history now yields the same result as the state your production environment is in. You can now continue using the already known migrate dev /migrate deploy workflow.

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