After running a database migration locally, all my database deleted - how to prevent in prod?
I did a necessary migration where I converted a property of an entity to be unique. The migration all appeared to go smoothly, but after testing I realised it had actually deleted all the data locally, so the database is empty aside from the tables I already set up. I want to be able to do this in production, but maintain my existing data. What's the best way?
23 Replies
Hi @CamBlackwood. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:
----------------------
Before running a migration in production, it's crucial to take some precautions to avoid data loss:
1. Backup your production database: Before migrating your production (deployed) app, it's advised to back up your production database in case something goes wrong. This way, you can restore your database to its previous state if the migration doesn't go as expected.
2. Test in a staging environment: Besides testing it in development, it's good to test the migration in a staging environment if you have one. This allows you to catch any issues before they affect your production data.
3. Check your migration scripts: Ensure that your migration scripts are not deleting data unintentionally. If you're using Prisma, you can use the
@map directive to change a field name in the database without affecting the existing data.
4. Run the data migration functions on the production database: You can do this by calling the API endpoints you defined in the previous step, just like you did locally. You can call the endpoint by visiting the URL in your browser, or by using a tool like curl or Postman.
Here is an example of how you can call an API endpoint:
After running the migration, your deployed app should be working normally now, with the new auth system.
Remember, the time between deploying the new code to production and migrating the production database data should be as short as possible to minimize any potential downtime or disruption to your users.
For more detailed instructions, you can refer to the Wasp migration guide (https://wasp-lang.dev/docs/migrate-from-0-11-to-0-12#1-migrate-to-the-new-auth-system).
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @CamBlackwood, 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?Could you describe the change in the schema you did? Before/After
Also, did Prisma warn you about anything 🙂
There might be a hint in the latest migration file in the
migrations dir@miho It's related to the database of influencers I have, I changed it so that the username for each platform must be unique across that database (so there's no duplicate accounts, basically). I'll dig out the migrations file now
That's the migration file
It seems to me that Prisma dumped your dev DB to make sure the migration doesn't fail. It quite normal in dev for this to happen since Prisma considers the dev DB as a disposable DB that can be reset.
In dev we are running
prisma migrate dev and this is the expected behaviour.
But in the production Dockerfile for the server, we are running the prisma migrate deploy command which will not dump your DB as far as I am aware. This is a production command which is more careful and don't dump
Docs: https://www.prisma.io/docs/orm/prisma-client/deployment/deploy-database-changes-with-prisma-migrateDeploying database changes with Prisma Migrate | Prisma Documentation
Learn how to deploy database changes with Prisma Migrate.
The best thing to do - create a different app with a string field. Deploy it. Add the unique constraint and deploy again to see what happens 😄
I'll do that quickly just to check the theory.
cool, thanks @miho 🙂 I'll grab a backup for the prod database as well using pg_dump as well just in case and will test it
Nice, you do that and let me know what happens 🙂
On Fly, I even think they do volume snapshots for you aka you have your DB backed up. But do the
pg_dump backup as well to be extra safe.@miho just gave deploying it a try and seems to have caused some db issues (same thing I had the other day, it seems disconnected). I'll try and wind it back using the snapshot I took
this was in the logs
It means you have duplicate values in the table under the key you wanted to make unique 😄
Let's debug this together, this happens 🙂
Ah that makes a lot of sense thank you
Migration name:
20240917170817_
Migrations table _prisma_migrations
1. Connect to the DB fly postgres connect -a <db-app-name>
2. You'll need to clean the table with the duplicates first. UnverifiedCreator -> tiktokUsername. Find the duplicates, delete them: https://www.atlassian.com/data/sql/how-to-find-duplicate-values-in-a-sql-table
3. Next, you'll need to delete the failed migration attempt from _prisma_migrations table: DELETE FROM "_prisma_migrations" WHERE migration_name='<migration-name>';
4. You'll need to restart the app one more time: fly apps restart -a <server-app-name>Atlassian
How to Find Duplicate Values in a SQL Table | Atlassian
Efficiently uncover, resolve, and eliminate duplicate values in your SQL tables with our easy, step-by-step guide! Learn to clean your data efficiently.
Thanks mate I’ll get cracking on that now!
Let me know if something is unclear 😄 let's teach Kapa together for the future 😂
When you connect with
psql using the fly postgres connect command, use \l for the list of DBs and \c <db-name> to connect to your DB.@miho I don't seem to be getting anything back when I try and query the database via the CLI. I know I'm connected (I get a list of databases), but even when I run SELECT * FROM UnverifiedCreator nothing comes back
You need to put
; at the end of the query for it to execute 😄is it obvious that I'm more of a frontend developer 😂
thanks
Don't worry, it's not really that intuitive - it bites now and then, that same thing 😄
oh interesting, it does run now but it says there is no UnverifiedCreator table. Maybe it wiped somehow, should I try and reset it from that snapshot?
also, the client and server now are seem to be totally disconnected again which would maybe point to the db being corrupted
1. Verify you are connected to the correct DB (on the left it should say your DB name)
2. Try seeing all the tables with
\dtah I see what was happening, it was converting it to all lowercase when I enter the command - see pic
just fixed the query, working on removing the duplicates atm
cool, deleted all the duplicates, deleted all migrations within past 2 hours and restarted the server - do I need to redeploy?
It should be fine 😄 the server has the latest code and it will apply the migrations on restart 🙂