Migrations Rollback

[rafaell-lycan]

Hi #drizzle-team & community folks 👋

I'd like to suggest something that I believe more people might be looking for. Is there any plan for rollback migrations?

My day-to-day workflow

Because Drizzle currently doesn't support a way to rollback migrations I'm using a custom solution (hopefully temporary) with migrate with a specific Dockerfile configured and a more painful way to configure/deploy to run our migrations, which might be slightly painful sometimes:

• Create a new migration using Drizzle;
• Include the suffix *.up.sql the migration script;
• Create a new file with the same name and change the suffix to *.down.sql and manually add a rollback;
• Run migrations on a custom Dockerfile + run.sh script that doesn't look as pretty as I would like; 😅

I was wondering if on next releases, there will be any sort migration rollback like migrate does.

1481574547_create_users_table.up.sql
1481574547_create_users_table.down.sql

This would benefit many developers and wouldn't be a breaking change by keeping everything else working.

Possible solution:

• Implement up/down SQL scripts on a new major release with the same drizzle-kit generate command;
• When running migrations it would use the same command and ignoring scripts with suffix *.down.sql;
• When running a undo/rollback with drizzle-kit it cheks for the migrations table and identifying the latest migration ID to match with a <migration_name>.down.sql script on the folder to be executed otherwise exit;
• This would open a possibility for Drizzle Kit adopt include the new drizzle-kit rollback:*/drizzle-kit undo:* command;

The future

Using Drizzle is being a breazy so far (coming from Knex) and I wanna see what things going far beyond the rest of the community.

Let's see how the community will (or not) react to this idea (calling for help) and also the core team.

[devanfarrell]

I would love a migrate down option as well. My current approach is:

• create a sql dump of our staging database
• delete my local database
• recreate the local database
• load the dump into the new local database
• rinse and repeat until the migration is correct

[capaj]

you're using mysql, right? No-transaction migrations are definitely a pain.

[devanfarrell]

Postgres personally but it's probably the same.

[capaj]

oh I thought you're repeating your migrations because they fail and your DB is in inconsistent state, but you're probably just iterating on your migration SQL script so youre migrations actually run OK from SQL standpoint.
On postgres if they fail on something like a missing foreign key whole thing reverts back, so you wouldn't need to manually fix anything.

[akhanalcs]

Coming from .NET background where we have amazing ORM like Entity Framework, I'm horrified to learn that JS ecosystem is awful in this regard.

[pppdns]

Coming from .NET background where we have amazing ORM like Entity Framework, I'm horrified to learn that JS ecosystem is awful in this regard.

It really is. Entity Framework sets the bar very high, though. I tried all the major ORM-s and DB tools in the JS ecosystem and there's no solution which checks all the boxes. Drizzle may become the one in a couple years

[ivawzh]

If implementing an automated rollback script will take a big effort, I'd like to have a way to opt in for a manual rollback script for the time waiting for the feature.

Being able to review and commit a rollback script like 1481574547_create_users_table.down.sql in a PR before we merge and deploy the migrations, that will give us much more confidence on dealing with production incidents with an option of rolling back.

[afogel]

I can't echo this request enough as an important aspect to mitigate unforeseen deployment issues.

I recognize that the drizzle team (as per @AndriiSherman 's comments in the discord) suggest that we leverage the push API for development.

However, sometimes a mismatch between what the data layer supports and the application layer requests after a migration runs successfully and is deployed (and this mismatch isn't caught in QA). Having a down migration would be incredibly helpful in that circumstance.

The drizzle toolkit is an awesome and incredibly sharp tool, but it would be nice if we had some method to sheath those tools at times.

Thank you!

[kkoyot]

I second that - not implementing migrations rollback/resetting is a huge omission. It's an awful pain to correct your migrations when developing schema change in a single PR doing changes on the way, since you have to delete or merge the migration files, modify the journal, revert the DB state manually and whatnot.

[rizen]

Can I pay Drizzle Team to add this?

[Ehtisham-Ayaan]

That was the same thought as i had. As rails has already this from years.

[darrenbarklie]

Also commenting to request this feature. Don't make me go back to TypeORM, please...

[nvegater]

Please add a rollback option for migrations :) would be awesome.

[lucasltv]

Don't have it yet? Wow... it's so essential!

[toshmukhamedov]

agree

[MChangFoundry]

Yeah this would be huge

[JasBogans]

[dragonfleas]

Your software doesn't have the rollback migrations feature being discussed here, why even mention it?

[FR073N]

Needing this feature so badly

[suzubki]

Hope it releases soon

[Leo-Henrique]

Just need this feature to use in production :(

[StuartMorris0]

Can we get any update on this from the drizzle team? I've posted the same in Discord, but haven't received a response. Be good to know if this is being worked on or not.

Not, is fine, we'd just need to work on some approach that builds the reverse migrations using something else, but I'd be keen to know if it's something in the pipeline just to avoid recreating the wheel here. Please and thanks 🙏

[afogel]

I made a feature request #2352 just in case

[kirkas]

+1 on this, this is the only thing preventing us from using Drizzle for production as migration rollback can be crucial when deploying.

[Twiddlle]

rollback pleaseeeee...

[alif-devcon]

Any updates @AlexBlokh?

[JosepAlacid]

Any good news about this one?

[predragnikolic]

Hey folks! Since 62+ people are getting notified every time someone says that they'd love to have this feature
Can we please stop spamming inboxes with messages with "+1" or "any updates"? (Also there is a subscribe button if you want to get notified)
Thanks!

[bezxc]

Any updates guys? 👉👈

[matthewblewitt]

Absolutely love drizzle, but my team won't adopt it prod without this feature 🙏

[rpl-matt-demler]

@matthewblewitt yea I've unsubscribed and no longer trying to get updates on this feature. I know it pisses some people off because they are subscribed to the topic... but they can equally just unsubscribe if they don't want the notifications 😄
Kysely was mentioned by another commenter. We've gone with that and it's amazing so far. It's not an ORM per se, more an abstraction layer over SQL, but has all the expected features of up & down migrations. Not the forum to sell it... but has those features that are critical to a lot of teams 👍

[szamanr]

we're subscribed because we want to know when this is implemented or there is a status update. not to get notified every week about another user requesting this. you can use reactions for that.

[rpl-matt-demler]

It's a fair comment. I guess the "+1" and the other comments are really just a more visible signal from the community that this is needed? Adding reactions doesn't seem to have the same impact ☺️

[nnti3n]

I'll pay for this feature

[capaj]

how much?
Also how would you like to implement it?
Fork drizzle-kit?
Or have some alternative CLI tool which just calls drizzle-kit and adds rollback?
If you would be ok having this in a standalone CLI I can take a look.

[itschip]

Please, this is a fundamental part of any migration solution. Not having the possibility to rollback, creates a massive headache if one person messes up one migration for whatever reason.

[hahaha-helpme]

28K stars and still no roll back 😮

[hahaha-helpme]

Will have more stars than prisma at the end of the year probably

[lshegay]

@hahaha-helpme your nickname is literally me after i found out that drizzle doesn't have a rollback feature.

[Nishchit14]

It's been two years, why there is no response to this most asked feature?

And also curious to know that how people are managing rollback today?

[waynesbrain]

I just posted my plan here and received approval from our AI overlords that the logic is sound - #2352 (comment)

[rpl-matt-demler]

https://kysely.dev/

[kdawgwilk]

The strategy that feels like the best path forward would be a phased approach i would think

1. Implement generating up/down migration files, with down migration files empty with a comment indicating that its up to the user to write their own down migration if they want one
2. Add CLI commands to rollback that runs the down migration files
3. Start supporting a few types of operations that get auto generated in the down migration file and leave comments for unsupported operations that the user needs to implement themselves
4. Slowly get feature parity with up migrations operations
5. Profit?

[fccoelho7]

Something really cool is coming
https://github.com/drepkovsky/drizzle-migrations

[8907234]

I forked @drepkovsky's package to add a drizzle-migrations down-one command for rolling back one migration at a time rather than in batches.

https://www.npmjs.com/package/@aegon_targaryen/drizzle-migrations

[Nishchit14]

I have read this blog and looks promising, Anyone has tried this manual approach?

https://app.studyraid.com/en/read/11288/352162/running-and-reverting-migrations

[atomicpages]

Drizzle CLI has migrating tracking and checking?

• Checking
• Migrating tracking

[Nishchit14]

Migration is not a problem. The rollback is the main issue. There is no clear confident way to rollback migration. which is highly requried for team environemnt where multiple people are making schema changes.

[8907234]

Migration is not a problem. The rollback is the main issue. There is no clear confident way to rollback migration. which is highly requried for team environemnt where multiple people are making schema changes.

You can use this package if you want to be able to do drizzle down-one to roll back the most recent migration.

https://www.npmjs.com/package/@aegon_targaryen/drizzle-migrations

[Nishchit14]

Thanks but I expect it as a core feature. Not a community package.

[TArch64]

Atlas has integration with drizzle so probably you can achieve down migration using it. it can generate migrations based on a drizzle schema so it should work very well but I haven’t tried it out yet

https://atlasgo.io/guides/orms/drizzle/existing-project

[joelparkerhenderson]

Atlas and Drizzle interfere with each other, in my experience, and force complexity when trying to use them together.

Atlas is also very expensive-- for my needs-- with Atlas Pro costing approximately $10,000/year for 100 developers.

[TArch64]

Well for simplier cases it might work. I use it for a golang ORM to have versioned migrations so i think you can do the same to delegate db migrations from drizzle cli to atlas. Basically the tool is free to use

[Jac0xb]

Atlas and Drizzle interfere with each other, in my experience, and force complexity when trying to use them together.

Atlas is also very expensive-- for my needs-- with Atlas Pro costing approximately $10,000/year for 100 developers.

bro $10,000/year for 100 developers 😂😂😂, what company you work at where you can afford to pay 100 engineers but cant buy a 10k annual saas.

[waynesbrain]

@Nishchit14 I am using the manual method and I just put my rollback script in this comment - #2352 (comment)

[broisnischal]

[fanckush]

in the future you can just click on the subscribe button in the right side

[ainsly]

I have a concern that I can't seem to find anyone address yet - which kind of ties into the issue with the migrations being .sql files - but the migration files are being passed by dir/glob, and the schema file is being referenced via path.
I've used bundlers for backend code for years now and this never goes down smoothly from what I've experienced.

Simple fix for the dir glob would be to expose index.ts and has a list of exports export * from 'mig1.sql'; etc.. etc and build an array from those exports -- which also makes the pitfall clear -- the file is .sql not .ts ... so that's not currently possible.

And whilst the schema could definitely be passed as it currently is (for use in ts-node / tsx / etc. or even NodeJS itself now) - passing the file directly would ensure it's bundled correctly would be a most welcome addition.

My specific use case is that I don't tend to yolo migrations by putting sql files in a dir, (maybe using tsc to preserve dir structure), and then 'pushing' those migrations to dev / staging / live or whatever env. Rather I usually create a little CLI app that connects to the DB [which is compiled & bundled] (from local machine / GH actions / on-prem server - wherever) and then trigger the migrations using exposed methods there - managed to implement everything most ORMs have, transactions (each/all) included.
The current .sql method just doesn't make that possible at all.

Those packages above are a step in the right direction for sure. It's what I've been doing, just wrapping the up/down sql in class methods. Yet something doesn't sit right with me either.. since drizzle knows which dialect its using, instead of raw sql outputs it could either use it's own drizzle client to execute those queries in a more structured way.
Sometimes in a migration no only do I need to update data-structures, but also need to insert/modify/remove data before and/or after - which the logic is usually within the up/down queries in a migration class file, and whilst it COULD be done in pure SQL, if that was an option, none of us would be using ORMs in the first place. (Especially when a lot of those data modifications as part of the migrations require external packages custom logic / js / ts which are just totally incompatible with TS..)

Can't wait until drizzle releases 1st class support for it, and hope they cover all bases. Personally never seen a migrations system without up/down/revert/dry-run etc. But would love to see a divergence (or at least extra option) to use an array of migrations and a schema file directly instead of paths which cause all kinds of havoc.
Currently using a local nx-plugin to generate the migrations, which does me the courtesy of placing the new migration within the encompassing 'migrations' array - so no extra setup is needed at all.

I know the drizzle team have a lot on their hands, so no rush, since we all seem to have worked around it in our own ways, but 1st class support would be ideal 🙏 keep it up guys. And let me know if you need a hand with anything

[hi-reeve]

been 2 years since this issue now, think they ignored it 😅

[ainsly]

been 2 years since this issue now, think they ignored it 😅

Potentially. However they are also focused on many other things (not just regarding drizzle), so I wouldn't conclusively say it's not on the roadmap.
Although judging from this thread it seems to be a pain point for a lot of people, so I get the frustration.

It's quite complex too from a devops standpoint - especially in distributed systems. A lot of the time the question 'when and where' do you run the migrations. For zero-downtime systems it's harder, although with things like Neon's branched DBs, maybe less so - but I'd say it's very niche at this point anyway. Even kubernetes / podman/docker clusters.. GH actions / admin triggered migrations usually the best bet.

Anyways, if I ever get enough free time I could potentially publish an Nx plugin for it, including initialization, and all the migration features drizzle has plus what I mentioned and what's been mentioned above. Some of my monorepos have multiple dbs for different domains so their configs / schemas / migrations / seeds are stored in different places (and also compiled before release), so there was a clear and relatively simple way to solve it in userland.
Sure people have made plugins for drizzle for their monorepo of choice (or standalone repo at that) - but do you ever find those to be of any decent quality? 😂

Only limitation of my solution is that it's within Nx ecosystem..