Have you ever made a change to a Prisma migration and received a warning like this when you run
The migration `20220805133548_my_migration` was modified after it was applied. ? We need to reset the PostgreSQL database "mydb" at "example.com:3033". Do you want to continue? All data will be lost.
This warning appears because Prisma tracks changes to migrations. If a migration has been applied and is later modified, Prisma will know it and assume the database is out of sync with your migration. Since it can’t know exactly what was changed and only selectively apply those changes, it opts for re-applying all of the migrations.
This is almost always what you want to happen. Changing a migration will likely cause your database to not match what is in your migrations. You probably should re-apply all your migrations at that point. However, sometimes you want to make a change to a migration file that wouldn’t cause a change to your database. For example:
IF NOT EXISTSstatement to a query. This may not change the functionality of the query. It will still e.g.
CREATEsomething like it did before. It just won’t
CREATEit if it already exists.
It should be noted that what is to follow is almost certainly a bad idea to do in production. It’s likely only going to be reasonable to do as part of your dev process. For example, if you’re working on a PR, you apply a migration, realize you want to add a comment to the migration, and then you don’t want to re-apply the migration and lose some test data you’ve set up.
So we need to somehow tell Prisma that this change is fine and it should be ignored. We can do that by creating a new checksum for the migration file and then overwriting the checksum for that migration in the database (again, not a great idea in production or if you care about your database).
You can create a new checksum for the migration file using this command:
shasum -a 256 prisma/migrations/20220510001642_my_migration/migration.sql
This will produce a value like:
You’ll want the first value, the random looking string:
Now we need to update the database with our new checksum. Use your favorite database client and look at the
_prisma_migrations table. This might be hidden in some database clients, so you’ll need to mess around with settings until it shows up. Prisma uses this table to track data about migrations. We’ll be looking at the
Find the entry in the database for your migration:
SELECT "id", "migration_name", "checksum" FROM "_prisma_migrations" WHERE "migration_name" = '20220510001642_my_migration'
It will show you something like:
id: f8b25a66-0020-4a32-9a56-7441665d9eda migration_name: 20220510001642_my_migration checksum: 6c2def14f22254ead278f805a5e63641319d546d2739de65da5fd98f615ba56b
Now update the
checksum to the new value we generated:
UPDATE "_prisma_migrations" SET "checksum" = 'fc27e97b9a61877f7f59d59a69c8c0bd2cd3271bc44b9f208800ed458d18a10b' WHERE "migration_name" = '20220510001642_my_migration'
That’s it! Prisma now thinks it has applied your migration because the checksum in the database matches the current checksum of the migration file. You should be able to run
prisma migrate again without any issues. Remember that this will not actually apply your migration again. As far as Prisma is concerned, it has already been applied.