Database Migration Problem
Hello!
I finally found the error that confused me a lot at the beginning of wasp.
Assume we have a model
Now we change this in dev to
In dev we will get a message to confirm this change and it will migrate. I noticed when I run my github action to prod
(similar to this) https://github.com/wasp-lang/deploy-action
There will be no migration in our database in prod, this will create major problems that I can't fix! Somewhere it says there are no migrations needed and the result is that our schema.prisma has the change, but the migration.sql wasn't run.
This migration was one that needed a manual user input in dev (just to confirm), what happens with manual user input in prod?
5 Replies
Hi @VS. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:
----------------------
Your observation is correct and highlights an important aspect of database migrations in production environments, especially when using Wasp and Prisma. Let me explain what's happening and how to address this issue.
In development, when you make changes to your Prisma schema (like renaming a field), Wasp uses Prisma's
migrate dev command, which is interactive and prompts for confirmation on potentially destructive changes. This is why you see the confirmation message in your local environment.
However, in production, Wasp uses Prisma's migrate deploy command, which is designed for non-interactive environments like CI/CD pipelines. This command only applies pending migrations and does not generate new ones or prompt for user input.
The issue you're encountering is related to how Wasp handles migrations in production deployments. According to the Github issue about running prisma migrate deploy, Wasp currently runs migrations as part of the deployed Docker container, which is not the recommended approach.
To address this problem and ensure your production database is properly migrated, you should consider the following steps:
1. Always create and commit migration files in your development environment before deploying to production. Run wasp db migrate-dev locally, which will generate the necessary migration files.
2. Commit these migration files to your version control system.
3. Ensure your deployment process includes running migrations before starting the application.
For the specific case of manual user input required for migrations, you need to handle this carefully in production:
1. In development, when you make a change that requires manual confirmation (like renaming a field), carefully review the proposed migration.
2. If the migration is safe to apply, confirm it and commit the resulting migration files.
3. For production, you may need to modify your deployment process to run migrations separately, allowing for manual intervention if necessary.
The Wasp team is aware of this issue and is working on improving the migration process for production environments. As mentioned in the Github issue about migration improvements, they are considering options like offering a separate command for production migrations or integrating migrations into the CI/CD process.
In the meantime, to ensure your production database stays in sync with your schema changes, always generate and commit migration files in development, and consider running migrations as a separate step in your deployment process, rather than relying solely on the automatic migration in the Docker container.
Remember, database migrations in production should be handled with care, and it's always a good practice to have a rollback plan and to test migrations thoroughly before applying them to production databases.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @VS, 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?aha ok this is a big gotcha!!! I guess I need some help still making these migration to prod. I had to delete the entire prod database (glad I hadn't gone live!)
@kapa.ai what are the migrations that would be ok to move to prod? Only adding new optional columns?
Let me check the knowledge base for you again. One moment! :books:
----------------------
Based on the information provided in the knowledge sources, I don't have specific details about which migrations are considered safe to move to production in Wasp. However, I can provide some general best practices for database migrations in production environments.
Generally, migrations that are considered safer to apply in production include:
1. Adding new optional columns (as you mentioned)
2. Adding new tables
3. Adding indexes (though be cautious with large tables as this can be time-consuming)
However, it's important to note that even these "safer" migrations should be thoroughly tested before applying them to production.
One relevant piece of information from the knowledge sources is a comment about forward-only migrations. As mentioned in the Github issue about running
prisma migrate deploy:
"I have a coworker that has drilled into me the value of forward-only migrations (no renaming, no dropped columns). By doing this, you can migrate before deployment, and you dont worry about the application going down. Once you deploy, your two apps can exist simultaneously without any problems. Over time, when you are confident the column is 'dead', you can drop it, also without any problems."
This approach suggests that it's safer to add new structures (columns, tables) rather than modifying or removing existing ones. This allows for a smoother transition between versions of your application.
Remember, regardless of the type of migration, it's crucial to:
1. Always test migrations thoroughly in a staging environment that mirrors production.
2. Have a rollback plan in case something goes wrong.
3. Consider the impact on application performance and downtime.
4. Back up your production database before applying any migrations.
For Wasp-specific best practices on database migrations in production, I would recommend reaching out to the Wasp community or consulting the official Wasp documentation for the most up-to-date and accurate information.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @VS, 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?This is a tricky one, production DB migrations are hard to get right irregardless of Wasp or Prisma.
You are basically trying to change the wheels on a car while it's still driving.
For example, if you are looking to drop a column that's used currently. Ideally, you'd first change your code not to use that column, make sure everything is okay and then drop the column.
Renaming a column is also tough, first you need to modify your code to write to two places (oldName, newName) and then when you transfer all the code to use the newName, drop the oldName.
Locally - it's okay if you destroy your DB to create a new aggressive migration - but then it'll probably fail when you try to apply some aggressive migration directly in production. You'll get some errors in the
_prisma_migrations table and then you'll manually connect to the production DB, modify the database manually and then ... delete the failed migration entry in the Prisma table and you can retry applying the migration.
What I'm trying to say - Google for Prisma tips, general DB migration tips since this is quite a common and a hard problem in web dev 🙂Definitely! You're right on all counts. For me, I assumed this manual approvement would happen automatically on prod -- this was a false assumption! I'll stick to just adding optional columns.