Support rollback/undo migrations

[sucaba]

From the readme/Rollback section:

refinery's design is based on flyway and so, shares its perspective on undo/rollback migrations. To undo/rollback a migration, you have to generate a new one and write specifically what you want to undo.

However according to this, undo is actually supported by Flyway.

Personally, I don't see how it is possible to switch between different branches of my project and work with the same database if refinery does not allow you to rollback changes.

My proposal is it to:

• add migrations prefixed with "U" as in Flyway
• add "refinery migrate undo" command

[jxs]

Hi! thanks for your interest, when I started refinery flyway did not offer undo migrations, even if they do now their opinion on the subject is the same , I share the same view, I think they are counter productive.

[sucaba]

Prompt answer, thanks. I assume you had multiple discussions around this topic but, no offense, I'd still like to clarify your/Flyway point of view.

Flyway solution to avoid undo migrations has two parts: 1.

maintain backwards compatibility between the DB and all versions of the code currently deployed in production

Which does not help in case when I'm developing a migration and want to apply/rollback changes many times.

2.

This should be complemented with a proper, well tested, backup and restore strategy

Nice, however it brings additional complexity to the project ecosystem and, in fact, is required when project uses refinery. That may be an overhead for smaller projects.

[jxs]

Prompt answer, thanks. I assume you had multiple discussions around this topic but, no offense, I'd still like to clarify your/Flyway point of view.

No worries, this discussion hasn't been had here on github before, so it's nice that its also here now.

The gist is this:

Undo migrations assume the whole migration succeeded and should now be undone. This does not help with failed versioned migrations on databases without DDL transactions. Why? A migration can fail at any point. If you have 10 statements, it is possible for the 1st, the 5th, the 7th or the 10th to fail. There is simply no way to know in advance. In contrast, undo migrations are written to undo an entire versioned migration and will not help under such conditions.

overall I don't think the concept makes sense, imho if one wants to undo a change they should just write the part of it they want to undo on a new migration and keep it going forward. It's easier to understand less bug prone, and less code to be maintained

[sparky8251]

For me, the primary benefit of an undo/rollback is for development. It's a huge pain to have to apply a newly made "undo" migration or just to drop stuff manually if I find I need some minor change a user will never see mid development

Maybe having some method of wiping clean everything it adds in via migrations and then a seeding a fresh application of migrations with development data instead? That way I can do something like, tweak a fields nullability, defaultness, uniqness, spelling, etc until I'm happy with both the schema AND code quickly and efficiently, vs just suffering my way through the manual rollbacks and reapplications that should only exist in the development cycle.

I myself don't want to support rolling back come a cut release for the reasons you describe, but I still need a quick way to undo and redo a migration if I miss a letter in a word... (This is likely more a refinery-cli thing I would say though)

[jaythomas]

Definitely more as a development utility, I find it useful to undo a migration when switching branches and code reviewing something, then having the ability to quickly revert back to master's schema version.

[jayvdb]

I've been building a evaluation matrix of migration management tools for a new product, and nearly all the tools we're considering have some mechanism for reverting changes to assist with development. This is a core feature for large development teams which will have lots of proposed schema changes being developed simultaneously.

https://github.com/prisma/prisma is the only widely used tool that I've found so far which doesn't have explicit "undo" support, but it has a separate workflow for prototyping schema changes - see https://www.prisma.io/docs/guides/database/prototyping-schema-db-push