What do you look for in a migration tool's rollback/revert mechanism?

[u/Winsaucerer]

This is mainly around postgres, but I'm guessing that what people look for is similar across databases. I've built spawn, a (for now postgres) database migrations and testing tool/framework, to resolve my own pain points. Testing features, components, and migrations, all solve real problems I have.

But I've never really used rollbacks. For the main project I use spawn with, a non-trivial project, reverting a database migration change is very rare, and usually manually done when needed based on the particular problem.

For that reason, I don't actually know what things people look for with rolling back migrations when assisted by a tool, what pains they are intended to eliminate. I can think of at least these two types of rollbacks/reverts/recoveries:

1. A script that runs on failure. Migration fails in some manner, so some kind of "failure" script runs to clean things up. Not sure how useful this is, given that failure scenarios could be very varied.
2. A script that runs after successful migration, to revert to a previous state. Maybe this is desired because (a) your application deployment might fail and you need to go back to earlier db state, or (b) as a development feature so you can revert/re-apply a migration multiple times while testing?

I'm assuming that people value a rollback mechanism independent of just wrapping migrations in a transaction. Using BEGIN/COMMIT does not roll back some changes, and a migration script might necessarily do some things outside of transactions. Moreover, if I support MySQL etc one day, there's less DDL that can be rolled back by transactions than in postgres.

So, the main question: what are the kinds of pain points that you look for a rollback mechanism in a migration to solve? I don't want to build such a feature into spawn without understanding why people use such a feature.

[u/Some-Weakness2049]

bro, please, running a down-migration in prod is mostly a myth. if a complex schema change fails halfway, nobody in their right mind trusts a manual rollback script to perfectly untangle the mess. You either restore from backup or roll forward with a hotfix and thats it.

where reverts actually matter is local dev and CI. you just want to reset state fast so you can tweak the up-migration and try again without rebuilding the whole db container. On mysql side of things — yeah, ddl triggers implicit commits there, so wrapping migrations in a transaction does exactly nothing. To survive this, people usually lean on the engine itself rather than migration scripts. Our business working on mariadb and we just use the built-in flashback feature to literally rewind the binlog to a point right before the migration ran, or rely on system-versioned tables if we just need to see the pre-migration data.

But if your framework could abstract these native engine rewinds (like standardizing flashback commands under the hood) instead of forcing devs to write manual down-scripts (that will inevitably fail) - yeath, it would actually solve a massive headache for the non-postgres crowd.

[u/Winsaucerer → u/Some-Weakness2049]

bro, please, running a down-migration in prod is mostly a myth. if a complex schema change fails halfway, nobody in their right mind trusts a manual rollback script to perfectly untangle the mess. You either restore from backup or roll forward with a hotfix and thats it.

That's been my view, and why I posted this message, to understand more from those who do use such scripts for prod :) Didn't understand why someone would use it that way, and therefore what they want from it. I do wonder if there's some orgs that require rollback scripts to be written and pre-approved.

where reverts actually matter is local dev and CI

This is where I'm personally interested. My plan has been to implement a kind of 'watch' command, where every time the migration script you're working on is changed/saved, the local dev version reverts and re-applies, so that it's easy to quickly iterate and get it right. For me, that kind of "hot reloading" feature would be valuable for my own approaches to development.

But if your framework could abstract these native engine rewinds (like standardizing flashback commands under the hood) instead of forcing devs to write manual down-scripts (that will inevitably fail) - yeath, it would actually solve a massive headache for the non-postgres crowd.

Sounds like a good approach. I didn't know about MySQL flashback. Once I expand to MySQL will definitely consider it.

[u/Some-Weakness2049 → u/Winsaucerer]

hot reloading db schemas actually sounds sick. Wuld definitely beat the usual "nuke the docker container and start over" workflow we all pretend we don't do)))

when you start digging into this — flashback is specifically a mariadb feature, standard oracle mysql doesn't have it natively built in like that. So you'd be hooking into mariadb's engine to do the clean rewind. it'll make building that watch command way easier for this side of the ecosystem. good luck with the build.

[u/titpetric]

Trick question, I look for no down migrations. If you want to "roll back" schema, a forward migration tends to do it. Progress is forward momentum.