How to reconcile the migration history after applying a hotfix or patch to a production environment.

Patching or hotfixing a database involves making an often time critical change directly in production. For example, you might add an index directly to a production database to resolve an issue with a slow-running query.

Patching the production database directly results in schema drift: your database schema has 'drifted away' from the source of truth, and is out of sync with your migration history. You can use the prisma migrate resolve command to reconcile your migration history without having to remove and re-apply the hotfix with prisma migrate deploy.

Reconciling your migration history with a patch or hotfix

The following scenario assumes that you made a manual change in production and want to propagate that change to your migration history and other databases.

To reconcile your migration history and database schema in production:

• Replicate the change you made in production in the schema - for example, add an @@index to a particular model.
• Generate a new migration and take note of the full migration name, including a timestamp, which is written to the CLI:(20210316150542_retroactively_add_index):

migrations/
└─ 20210316150542_retroactively_add_index/
└─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (2.19.0-dev.29) to .\node_modules\@prisma\client in 190ms

• Push the migration to production without running migrate deploy. Instead, mark the migration created in the previous step as 'already applied' so that Prisma Migrate does not attempt to apply your hotfix a second time:

This command adds the migration to the migration history table without running the actual SQL.

• Repeat the previous step for other databases that were patched - for example, if you applied the patch to a staging database.
• Propagate the migration to other databases that were not patched - for example, by committing the migration to source control and allowing your CI/CD pipeline to apply it to all databases. The migration will not be applied to databases where it has been marked as already applied by the prisma migrate resolve command.

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:

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

• 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 tables)

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.

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

• Re-deploy the migration:

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.

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

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

To help with fixing a failed migration, Prisma ORM 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.

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

model User {
  id   Int    @id
  name String
}

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

model User {
  id    Int     @id
  name  String  @unique
  email String?
}

model Post {
  id    Int    @id
  title String
}

You create a migration called 'Unique' with the command npx 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:

    # Prisma 6
    npx prisma migrate diff \
      --from-url "$DATABASE_URL_PROD" \
      --to-migrations ./prisma/migrations \
      --shadow-database-url $SHADOW_DATABASE_URL \
      --script > backward.sql
    
    # Prisma v7 (with production config)
    npx prisma migrate diff \
      --from-config-datasource \
      --to-migrations ./prisma/migrations \
      --config prisma.config.prod.ts \
      --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:

    # Prisma 6
    npx prisma db execute --url "$DATABASE_URL_PROD" --file backward.sql
    
    # Prisma v7 (with production config)
    npx prisma db execute --config prisma.config.prod.ts --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:

    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 schema.prisma file
• Run the following prisma migrate diff command

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 schema.prisma file.

• Run the following prisma db execute command:

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:

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.

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.