Reset migrations for production
Hello!
During development I have been using migrations to sync changes to our staging environment. Before going live on production I messed up and forgot to reset all migrations and start from scratch. What we have now is a mess with 38 migration files. I have manually made edits in some of the migrations files (I know..) and staging won't accept migrations due to conflicts. I have been forcing changes with push for some time.
Now we have our prod live since a week with not much, but some data that I want to keep. Before it goes too far I want a clean slate on migrations with the current state as a baseline.
Is there a good way to achieve this? I've gpt'd it a bit but since I'm not super confident in this space I'd rather get some human input.
Thanks!
4 Replies
There are tools that you can use to diff 2 databases. So you can setup a new db, run all your migrations on it and then diff it with your production. Once you manually change your production db schema to match your new db, you can just copy the rows from the new db's drizzle_migration table into your production db after truncating that table in production.
If you're trying to squash all of your 38 migrations down into a single migration file, you can just delete your drizzle folder and regenerate a single initial migration. Then add your modifications back into that file and then you can follow the steps above to fix production.
Also, instead of appending custom SQL to a generated migration, Drizzle would rather that you create a custom, blank migration file and put your custom SQL in there - https://orm.drizzle.team/docs/kit-custom-migrations
Drizzle ORM - Custom migrations
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Thanks for your input! What I ended up doing was this.
- Move all old migrations to new
migrations_legacy folder.
- Generate new baseline 000 migration
- Copy all migrations in drizzle schema in the database to a backup table.
- Truncate the real table.
- Create a new temp db and run baseline migration against it to get hash.
- Insert first ID 1 row to prod db drizzle migration table with hash from previous step.
I will do this moving forward@Bagofskates I may switch to doing that going forward too. I didn't even know about that until you asked this question! What had done instead was to create a folder where I store files like this
objects/rev_log_triggers/0000_init.sql with just the code to create triggers and then I had Claude create a custom bin script to append those files to the matching migration. With my method, the migration files are huge because I'm appending all sorts of functions and triggers so I think I will switch too.