Skip to main content

Patching & hotfixing

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.

warning

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

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:

  1. Replicate the change you made in production in the schema - for example, add an @@index to a particular model.

  2. 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):

    npx prisma migrate dev --name retroactively-add-index
    Show CLI results
    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

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

    prisma migrate resolve --applied "20201127134938-retroactively-add-index"

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

  4. Repeat the previous step for other databases that were patched - for example, if you applied the patch to a staging database.

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

Note: The migration will not be applied to databases where it has been marked as already applied by the prisma migrate resolve command.

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

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

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
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 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 ./prisma/migrations \
        --shadow-database-url $SHADOW_DATABASE_URL \
        --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. Note that because we're using --to-migrations, the command requires a shadow database.

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

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

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

info

This does not apply from version 3.12.0 upwards.

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.