DB Migration issues when syncing Prod and Dev DB
To keep my local dev DB sane I created a shell script to dump my prod (deployed) DB to a local file, drop my local database and restore the dump locally.
When I then run wasp clean and try to start the server, it prompts me that the migrations do not match and I need to wipe the Database. After that I rerun my sync script to have some Data which works fine, but the next time I need to run wasp clean, I need to repeat the process. It's not a Shopstopper nor Dealbreaker. Just wanted to point it out and ask how you detect the migration changes?
I tried to prevent this by excluding the migrations table from the remote dump and do a local dump first and after dropping the local db and restoring the remote dump I also restore the local migrations table hoping to prevent the issue, which sadly did not work.
I hope I managed to describe the "issue" well enough.
Do you Guys have any Insights why this might be happening or how to prevent it?
8 Replies
Hi @Gwaggli. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:
----------------------
You've described a common challenge when syncing production and development databases in Wasp projects, especially when using database dumps and restores. Here’s what’s happening and why you’re seeing migration mismatch issues:
How Wasp Detects Migration Changes
Wasp uses a combination of mechanisms to track and ensure that your database schema, migrations, and Prisma client are in sync:
- Checksum File: Wasp maintains a checksum file (e.g.,
db/.schema.prisma.wasp-migrate-checksum) that records the state of the schema at the last successful migration. When you run wasp db migrate-dev, this checksum is updated. If this file is missing (which can happen after wasp clean or a fresh checkout), Wasp may warn you that migrations are out of sync, even if the database schema itself is correct. This can lead to false positives, but it’s a safety mechanism to ensure you don’t miss required migrations or schema updates. The checksum approach is intentionally conservative: it may warn you unnecessarily, but it won’t miss real issues Warn user if Prisma needs to be re-generated Consider using prisma migrate status or prisma db pull to improve DB migration warnings.
- Prisma Migrations Table: Prisma (and thus Wasp) uses the _prisma_migrations table to track which migrations have been applied to the database. If you restore a production dump that doesn’t include this table, or if the table’s contents don’t match the local migrations directory, Wasp/Prisma will detect a mismatch and may prompt you to wipe the database or reapply migrations.
Why Your Approach Isn’t Working
- Restoring Only the Migrations Table: Simply restoring the local _prisma_migrations table after importing a production dump doesn’t guarantee consistency. The actual database schema (tables, columns, etc.) may not match what the migrations table says has been applied, especially if the production schema has diverged from your local migrations or if the dump/restore process introduces differences. Prisma checks both the migrations table and the actual schema, so any mismatch triggers warnings or errors Consider using prisma migrate status or prisma db pull to improve DB migration warnings.
How to Prevent or Mitigate This
- Keep Migrations in Sync: The most reliable way is to ensure that your migrations directory and the _prisma_migrations table are always in sync with the actual schema. If you restore a production dump, you should also restore the corresponding _prisma_migrations table from production, not from your local environment.
- Avoid Manual Schema Changes: Try not to make manual changes to the database schema outside of migrations, as this can cause drift that Wasp/Prisma will detect.
- Accept the Warning as a Safety Net: The warning you see after wasp clean is a conservative check to prevent accidental schema drift. While it can be annoying, it’s there to ensure your local environment matches your migrations and schema.
- Alternative Approaches: Some discussions suggest using prisma migrate status or prisma migrate diff to check for drift between the schema, migrations, and database, but Wasp currently relies primarily on the checksum and migration table approach Consider using prisma migrate status or prisma db pull to improve DB migration warnings.
Summary
What you’re experiencing is a known side effect of how Wasp and Prisma track migrations and schema state. The safest workflow is to always keep your migrations directory, the _prisma_migrations table, and your schema in sync, and to accept the occasional need to reset or reapply migrations as a trade-off for safety. There isn’t a perfect workaround in the current Wasp tooling, but your approach is reasonable and the warnings are expected given the checks in place.
If you need more granular control, you might consider scripting the restoration of both the schema and the migrations table from production, but always be cautious to avoid schema drift.
If you have further questions or want to discuss improvements, the Wasp team is aware of these challenges and open to feedback Warn user if Prisma needs to be re-generated Consider using prisma migrate status or prisma db pull to improve DB migration warnings.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @Gwaggli, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:
Perhaps try again in 15mins?@kapa.ai The Remote Schema and the Local Schema are in Sync and I don't have manually edited either of them. Any other ideas?
Let me check the knowledge base for you again. One moment! :books:
----------------------
If your remote and local schemas are in sync and you haven't manually edited either, but Wasp still prompts you about migration mismatches after running
If you are certain that your schemas and migrations are in sync, these warnings can be safely ignored after you run
wasp clean, the likely cause is how Wasp tracks migration state:
- Checksum File Missing: After running wasp clean, the checksum file (e.g., db/.schema.prisma.wasp-migrate-checksum) that Wasp uses to track the last successful migration may be deleted. Without this file, Wasp cannot confirm that the migrations and schema are in sync, so it conservatively warns you to run migrations—even if the actual database schema matches your migrations. This is a known behavior and can result in "false positive" warnings, but it is designed to prevent accidental schema drift Warn user if Prisma needs to be re-generated.
- _prisma_migrations Table: Even if you restore the _prisma_migrations table, if the checksum file is missing or out of sync, Wasp may still prompt you. The system is designed to err on the side of caution, as discussed in the Wasp team's internal discussions Consider using prisma migrate status or prisma db pull to improve DB migration warnings.
In summary:If you are certain that your schemas and migrations are in sync, these warnings can be safely ignored after you run
wasp db migrate-dev once to re-establish the checksum. This is a limitation of the current migration tracking approach in Wasp, and not a sign of actual schema drift.
If you continue to see issues after running wasp db migrate-dev and confirming schema/migration sync, it may be worth raising a bug or feature request with the Wasp team, as this is an area they are actively discussing and improving Warn user if Prisma needs to be re-generated Consider using prisma migrate status or prisma db pull to improve DB migration warnings.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @Gwaggli, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:
Perhaps try again in 15mins?@kapa.ai I understand that the prompt pops up conservatively. But If I just wasp clean my project without syncing my Databases, this prompt does not pop up. So it must have to do something with the sync and is not only about clearing the checksum file. Also soley prompting to run the migrations would be fine, but it requires me to wipe all Data to run the migrations. (its not able re-run the migrations without wiping all data)
Let me check the knowledge base for you again. One moment! :books:
----------------------
You're correct—according to the knowledge sources, the warning and the requirement to wipe the database are not triggered solely by running
- The prompt and the requirement to wipe the database are triggered by a mismatch between the local migrations directory and the
wasp clean and losing the checksum file. The issue arises specifically when the state of the local database (including the _prisma_migrations table) does not match what Wasp expects based on the migrations directory and the schema.
From the discussions, Wasp uses a combination of the checksum file and the state of the _prisma_migrations table to determine if the database is in sync with the migrations and schema. If you restore your production database locally, the _prisma_migrations table from production may not match your local migrations directory, even if the schema itself is in sync. This mismatch causes Wasp/Prisma to detect a conflict, and in such cases, Prisma requires you to wipe the database before you can proceed with migrations. This is a safety feature to prevent applying migrations to a database that may already have some or all of those changes, which could lead to data corruption or inconsistencies Consider using prisma migrate status or prisma db pull to improve DB migration warnings.
The knowledge sources also confirm that simply losing the checksum file (e.g., via wasp clean) does not always trigger the prompt. The prompt appears when there is a detected mismatch between the migrations directory and the _prisma_migrations table in the database, which is more likely to happen after syncing your database from production, as the migration history may not align perfectly with your local migrations Warn user if Prisma needs to be re-generated.
In summary:- The prompt and the requirement to wipe the database are triggered by a mismatch between the local migrations directory and the
_prisma_migrations table in the database, not just by the absence of the checksum file.
- This mismatch is more likely after syncing your database from production, as the migration history may differ.
- Wasp/Prisma does not support re-running migrations on a database that already has a conflicting migration history; it requires a wipe to ensure consistency.
The knowledge sources do not provide a workaround for this limitation, and it is a known behavior of how Prisma (and thus Wasp) handles migrations and schema state.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @Gwaggli, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:
Perhaps try again in 15mins?@kapa.ai That's what i thought and is the reason why I also dump the local migrations table before syncing and then re-applying this dump to make sure, my local migrations table does not change between syncs.
Let me check the knowledge base for you again. One moment! :books:
----------------------
Your approach—dumping and restoring the local
Even if you restore the local
_prisma_migrations table after syncing the production database—is logical and should, in theory, keep your local migration history aligned with your local migrations directory. However, according to the knowledge sources, this method does not always prevent Wasp/Prisma from detecting a mismatch and requiring a database wipe.
The reason is that Prisma (and thus Wasp) checks for consistency between:
- The migrations in your local migrations directory,
- The contents of the _prisma_migrations table in your database,
- And the actual schema of the database.
If any of these are out of sync—even slightly—Prisma may refuse to proceed with migrations and require a wipe. The sources specifically mention that restoring the migrations table alone is not always sufficient, because the actual schema in the database must also match what the migrations table and migration files describe. If there is any drift (for example, if the production schema was changed outside of migrations, or if the migrations table and schema are not perfectly aligned), Prisma will detect this and block further migrations until the database is wiped Consider using prisma migrate status or prisma db pull to improve DB migration warnings.
In summary:Even if you restore the local
_prisma_migrations table after syncing, Wasp/Prisma may still require a wipe if it detects any inconsistency between the migrations directory, the migrations table, and the actual schema. This is a limitation of the current migration system, and the knowledge sources do not provide a workaround for this scenario.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @Gwaggli, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:
Perhaps try again in 15mins?Maybe someone from the Wasp Team or the Community has some more insights?