Running complex migrations
I'm a little confused on how to run more complex migrations like backfilling or splitting tables.
Say for a todo app I have a
users table with columns id and todo_body. Now I want to split that table into separate users and todos tables to have a many-to-many relation.
Currently we're using knex, so in the knex migration file first I'd create the new table, then backfill existing todos into the new table, then drop the columns of the existing table.
In drizzle, it looks like I should first make the changes to the schema, then generate migrations files using drizzle-kit, then manually edit the migration files to add the backfill step.
But what I don't understand is how does drizzle track migrations and make sure that it doesn't run the backfill more than once? It seems to me like when you run migrations through the migrate() helper, it runs all the migrations every time. This is because drizzle doesn't store the state of which migrations have run in a database table like knex. So every time I run the migration helper, it would try to backfill.
This is just one example of a case requiring backfill, but another I can think of is if I need to add a new not nullable foreign key to an existing table.
What is the recommended way of running and keeping track of these migrations?11 Replies
Please correct me if I'm wrong but I believe drizzle saves what migrations have been applied in a table called
__drizzle_migrationsAt what point does drizzle create that table and save the migration into the table? I created my first migration and ran it on an empty database. The migration ran successfully, but I don't see any tables called
__drizzle_migrations
Also thanks for the speedy response!This are drizzle-kit internals, so I don't know the details. But I'm pretty sure that's how it works. What's your dialect? Maybe sqlite doesn't work like this?
@Andrew Sherman
I'm using a PostgreSQL db with the node-postgres driver
we are creating
drizzle schema and store __drizzle_migrations table there
so we are keeping track of applied migrations
We have this PR, to specify custom schema name and custom table name for migrations. But for now it's always drizzle.__drizzle_migrations
https://github.com/drizzle-team/drizzle-orm/pull/1638ah my bad didn't see that, that's perfect. Slightly related, I don't see any documentation related to migration rollbacks, could you point me to any guides on best way to handle that? Specifically in local dev it's very helpful for testing changes and then rolling back
I believe migration rollbacks are not supported; the recommended workflow, best I understand, is to test changes with
push and then create the migration once you are done
(obviously, this only works for schema changes)got it, thanks for the help!
(I feel your pain - I actually write migrations outside of drizzle entirely since I am using supabase and am hand-rolling migrations to add RLS policies and etc.)
will be improved
just noted
I second this. Current section in the docs about migration doesn't explain this at all. If the current state of affairs is that there's no way to apply rollbacks, then maybe it's worth adding that in the docs as a sidenote?