Patching / hotfixing a database in production

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:

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

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