Database Downgrade

[flyway]

Original author: wolfgang...@gmail.com (September 20, 2012 07:06:48)

I am new to flyway and currently evaluating how/if we could use flyway in our project.

What I really miss, is the possibility to downgrade a database to a previous version. Migrating to the newest version is fine when you start from scratch and when you have no (production) data in a database. But in the case you have to step back you still have to do everything manually.

In my opinion, this would not very hard to implement. You just have to provide some additiona scripts with a __dowgrade suffix that revert a certain version and do the things you did from the other direction.

At the moment, this is the main issue regarding use/don't use flyway. Stepping back seems to be still clumsy and when it is necessary to do a downgrade manually, the question arises to do the migration manually also to be consistent.

Original issue: http://code.google.com/p/flyway/issues/detail?id=334

[flyway]

From axel.fontaine.business@gmail.com on September 20, 2012 07:19:30
Hi Wolfgang,

What real world use case do you have for downgrades? Please describe so I can better understand your situation.

Please note that rolling back a failed migration is not a valid case, as I described here: http://code.google.com/p/flyway/wiki/FAQ#What_about_downgrade_scripts/downward_migrations?

Thanks
Axel

[flyway]

From wolfgang...@gmail.com on September 20, 2012 07:45:49
Hi Axel,

the situation is as follows:

As long as you we in development, there is usually no need to do so. But when we go to production we are usually installing the new version, holding back the previous version and just switching from the old to the new version whith the ability to switch back to the previos version in case of unforeseen problems.
What we can't change so easy is the database, so if we would have database changes, the changes are applied to the production database and would have to be reverted in the case of problems. This is not only the case in my current company. I also had other companies where it was the case, that every release was delivered with migration AND revert scripts and I think this is definitively the right approach - you have to think about the emergency case and be prepared. So it would be very nice to have also an easy approach to step back to the previous version :-)

That I have to care about my migration scripts myself is clear, but that's not the point here (and I think, when my migration script fails, I shouldn't expect my revert script to succeed :-))

Greetings,

Wolfgang

[flyway]

From axel.fontaine.business@gmail.com on September 20, 2012 08:12:25
Hi Wolfgang,

thanks for your infos. How do you handle drops? Or information destruction in general? And if you make home-made backups, when do you get rid of them? And how well are they tested?

There is another approach which I find vastly superior: backwards-compatible upgrades. This way both new and old code can work with the DB at the same time. And if the new code doesn't work, just revert to the old one. There is strictly no impact. Yes, it takes a bit more discipline and might require compatibility views or triggers or leaving older columns around until the old software has been decommisioned. But in my opinion, it's well worth.

Also, if you really want downgrade scripts, Flyway doesn't stop you from writing them. You could then just execute them separately if you need to.

Cheers
Axel

[flyway]

From wolfgang...@gmail.com on September 20, 2012 12:13:33
Hi Axel,

for me, it seem a little bit as if you missed my point... :-) When you change a database, there are a lot fo things to think about, regardless if you migrate to a new version or go back to an old and there definitely be a lot of strategies for all different types of database changes, but this has nothing to do with flyway per se - I can do all that things manually.

From my point of view, the question is, how can I use flyway and should I use it? Flyway seems to be great in one direction, but when I need to go back one step, I still have to do that manually, so why then use flyway at all? Don't get me wrong here, we might use it even if it's not perfect for us but I think, it might be in your primary interest to implement the possibility to move in both directions because otherwise you might loose a lot of possible users...

To point it again, the question is: Why to learn a new tool that does only support me partially - wouldn't it be easier/better to stick at the current approach that might be harder, but is at least consistent either way you migrate to a new version or go back to a previous state?

Greetings,

Wolfgang

[flyway]

From axel.fontaine.business@gmail.com on September 20, 2012 13:03:16
Hi Wolfgang,

no worries, I did understand your point. There are however a number of use cases which for me seem to invalidate this approach. Maybe you can help me understand how this is not the case.

How do you rollback drops?
Or information destruction in general (delete, update, ...)?
And if you make home-made backups (separate copy of a table for the rollback script), when do you get rid of them?
Do you make a backup of the backup?

Cheers
Axel

[flyway]

From wolfgang...@gmail.com on September 20, 2012 21:55:44
Hi Axel,

I hope that I understand your point - you mean there are situations where it is not possible to roll back in a completely consistent way, mainly when you drop 'living' data that is not just static and can't be easily converted from one direction to the other because it is modified in the meantime.
You are right, this is sometimes a problem and I know about cases where the backup strategy was to call the customers, ask them for the appropriate information and update it manually (thank god I know no case where this was really necessary).
In my opinion such a case is really rare. The more common strategies where not to delete the table/column immediately, provide some default values or filling strategy or leave the field blank.

So if I got you right, you think that reverting a version brings up additional problems. This is not true. What you are doing, is you bring a database from one state to another. The direction you are doing this doesn't matter. It's just a transition. And when you are not a human and just a machine, there are no directions back and forth...
What you are asking me is how we handle a revert. But this is not a problem of flyway, this is a problem I have and when I make a mistake in a revert script, then that's my problem. When the revert doesn't leave the database in the exactly same state as before the migration, that's my problem (and in most cases this is just tolerable).
What I would like to have is just the possibility to transform a database also back to a certain state in the same way I do the migration to a new version :-)

Regarding the backups of a table for a revert: I would get rid after I reverted a version (so when I migrate to a new version, I would first do the backups, and when I revert to a previous version I would delete the backups at the end)

Greetings,

Wolfgang

[sharplet]

+1

[pkcinna]

A standard way to downgrade should be supported even if there are many cases where you can't really undo a migration in that the state of db will not be same as before the migration. The issue is there ARE many scenarios where you can so why not provide that option? Perhaps it can be something else besides a downgrade to imply it is not truely a guaranteed rollback to a prior version. We have to have rollback scripts for every migration in our current projects in order for the DBA to approve them. If there is no feasible rollback for something like a DML script updating a bunch of rows where the prior value is not known, then the rollback can be marked so a warning is produced saying it is not a 100% revert. So if we use Flyway, we would have to grow our own folder conventions and procedures which is enough to discourage us from adopting it.

[ramfjord]

Indeed Axel, it sounds like you're arguing that because there are use cases where down migrations fail, it's never helpful.

Also, when developing migrations in flyway I often found myself managing the schema_versions table and running down migrations manually. Now I've resorted to calling the migration scripts with \i in psql within an uncommited tranasction to test.

[cunhaax]

This feature request doesn't make sense and might cause more problems.
I like the "backwards-compatible upgrades", it makes total sense!

Let me give you an example:
Supose that you have your production or testing DB at version V15 and at some point you "downgrade" it to version V14 (providing a script eg V15_to_V14.sql).

Now suppose you need to install (lets suppose you want to stay at V14) your project in a new PRD server, or a new testing server, or that you have a new developer in the team and he needs to create his DB.

What scripts are going to run on this new install?
Are you going to run ONLY the "upgrade" scripts? Do you have any guarantee that NOT running V15_to_V14.sql will be exactly the same (I would not trust this - you could have several downgrade scripts that were not run)?

I'm not even considering to always run all the "upgrade" scripts and then the "downgrade", this is a complete mess (it looks almost the same as the backwards-compatible upgrades, but with some problems). And how would you fix your V15? would you modify the script? This is a shoot in the foot, forget it.

Flyway as it is now lets you re-create your DB ALWAYS with the EXACT same steps, don't change this.
"backwards-compatible upgrades" is the way to go!

[cvmocanu]

I'll add my thoughts on this, since my team would have been benefited by an automated downgrade quite often. Too many times we had to downgrade manually and that's way too error prone.

The need to downgrade

1. This is very useful during development. Many times I see that my migrations are not entirely correct (for example I forgot a column) and need to manually downgrade. This wastes development time. I could add another script for such cases, but I prefer to keep the scripts focused because it makes code easier to review and it's also easier to create a downgrade script.
2. Another case in development is when working on multiple feature branches. When you need to switch to branch B, you need to downgrade the new stuff from branch A, to be able to apply cleanly the upgrades on branch B. I'm sure there are more cases that I'm missing.
3. This is also useful in production. Sometimes deploying a new version needs to be rolled back because a critical code bug slipped through the acceptance testing. We have a big database that takes too much time to completely restore from backups. We cannot afford to have the production down for so long, so when things go wrong we end up having to manually downgrade. We can, of course, prepare such downgrade scripts regardless of Flyway support, but having them under Flyway forces a certain consistency (e.g. file names conventions), that is very desirable. Plus, having a nice "downgrade" command that accepts an old version makes downgrading faster. Because it's not manual, it's also a lot safer.

Not everything can be downgraded

This is true in the general case. But in the real world, we have about one such case in 100. The 99 downgrade scripts would have proved quite useful. I bet that people who use Flyway in production can testify to similar percentages - there are very few cases in the real world where an upgrade script cannot be rolled-back/downgraded.

Testing downgrade scripts

It's true that the upgrade scripts will be tested a lot more than the downgrade scripts. But even here Flyway can provides some support. You can have a "test-downgrades" command that applies all the downgrade scripts down to a given version and then re-apply the upgrade scripts to the version from which we downgraded. After this, the developer can just run automated and manual tests to check if everything is still fine. Presumably this can be done just prior to merging the feature branch into the CI branch.

@cunhaax
You said: "Flyway as it is now lets you re-create your DB ALWAYS with the EXACT same steps, don't change this."
This is only true in theory. In practice, people have to downgrade (see reasons above).
If tool support is missing this will be done manually, usually under stress and time constraints to get the production back to a running state.
This is much worse than having scripts prepared in advance, under no kind stress.

So Axel, what do you think? Is there any chance of seeing something like this soon? :) Things look a bit different in real-world usage, and this feature would prove very useful.
In any case, thanks for a very useful tool.

[ayratn]

We use flyway a lot in our company and I'd like to vote +1 for "downgrade" feature.

Though downgrades is mostly used during developing phase (switching between branches or for wrong migrations - the same as cvmocanu wrote about) the need to do it manually now is not ideal way and at least causes developers to ask questions like "why wasn't it implemented in flyway yet"

Thank you

[sfmontyo]

I'd like to add another voice to the request for rollback.

This would be extremely useful for our company as well and is what is preventing our adoption of it here. You may want to "argue" that our use case isn't valid, but that isn't helpful. Further, the use case for us isn't developer convenience, rather to support production.

We have a similar use case as mentioned above. When rolling out to production, we'll do the following:

1. bring the site offline
2. rollout the new software and database changes.
3. validate services . if all is good, bring up the site.
4. if not, then we'll need to restore the old services.

Restoring the old services means that we may have to restore the old database schema. For destructive forward changes such as dropping a column, we can write our migrations to save that column's data off to the side in case we need to rollback (if the data is worthy of being retained). We do this instead of doing a general full backup of the database during this window in the advent of a need to rollback the migration. Our database is too large for the window to do a full backup and do all the other checks.

The point is, rollback is needed not because there is an issue with the migrations themselves, but because of an operational need. Something not due to the DB or sometimes even the software itself.

The above is incomplete in all the reasons as to why we need to roll the schema back and it is certainly not meant to be an airtight argument for why we need it. For instance, someone might read this and rather than attempting to understand the use case, might instead choose to argue its validity. Such a person might state that one doesn't need to "rollback the schema" when switching back to a previous version of a service. The truth is that many times you can make the schema backwards compat with an older version, but sometimes you can't.

This isn't about arguing whether rollbacks can fail - of course they can - so can rolling forward. That's not the point. We want to avoid manual steps during production roll out and need to handle both satisfactory rollouts and emergency rollbacks in the case something major prevents the rollout.

[magmax]

@sfmontyo You have described exactly my case.

+1

[HabibMAALEM]

@flyway if we have an upgrade scripts, we must have downgrade ones
+1

[pkcinna]

Several years later and I'm at a new job and have same situation again... cannot adopt flyway without structured downgrades or rollbacks. Is this a scenario justifying a fork in the project?

[ghost]

Another use-case that needs support for a downgrade feature is in development environments where a single developer may be working on different features, with each feature being on a different Git feature branch. If one of those feature branches involves adding a field to a table in their local database, then once that migration is applied to their local DB they can't work on any other feature branches until that migration is applied in all branches (since the other branches wouldn't have the new migration, but the local database would, flyway would throw an error). Now there is obviously the issue of drops and/or deletes to deal with, but there are ways of handling that (many other DB migration tools for other environments, like Ruby on Rails, already do). Of course there is the counter-argument that support for downgrading migrations causes more problems than it solves, and in some cases that is true. However, a developer who has to work on multiple feature branches should not have to drop their entire database schema and let flyway recreate it every time they want to switch feature branches. Many times developers enter in data while they are working on a feature for testing and/or debugging purposes, and they shouldn't have to always lose all of that data when switching branches. Now obviously the developer is just going to have to deal with having to re-enter some data when switching branches (for whatever field was added/deleted), but they shouldn't have to re-enter data that wasn't affected by the database migration. Flyway should support downgrade scripts, as this is a valid and common use case (and many other ones were mentioned on this thread as well) that requires downgrade script support.

@flyway

[cvmocanu]

Any news on this?

Changing branches is also a big reason why I need rollbacks. Especially since we are using a feature-branch + rebase workflow.
We are currently writing the rollback scripts anyway to support this workflow, and it would be nice to have a tool executing them instead of the error-prone manual process.

To support this, it would be nice for Flyway to rollback and migrate automatically, like this:
Let's say we have 2 branches:

• A with versions 1, 2, 3
• B with versions 1, 2, 3' (version 3, but with different content), 4' (version 4, but with different content).
  When switching branches from branch A to branch B, Flyway could:
• see that 1 and 2 are common versions
• rollback 3
• apply (migrate) 3' and 4'
  This would make working with feature branches a breeze.
  We are currently doing something like that manually (we make a rollback script for every migration script and we apply the rollback scripts manually).

This should be an option so we can activate it in development only.

Don't get me wrong. Flyway is a wonderful tool, and I push for its usage in all the projects I can. But without rollback it feels half implemented.

[jstncno]

+1 on support for rollbacks/downgrades

[mcekovic]

+1, especially as the need for downgrade would increase if Condensed Migrations are supported (#470). Testing Condensed Migrations would be much easier if downgrade is supported.
Flyway people, I am sorry, but database migrations of complex database schema is not a simple thing, so the tool for automating it cannot be THAT simple :) Btw., I greatly appreciate current simplicity of Flyway, but IMHO, it is more simpler then it should be (i.e. lacking support for downgrades and condensed migrations which are essential for DevOps needs for continuous delivery of complex schema).

[rwblackburn]

+1, I also have encountered issues with this feature not being available.

I've actually always created an downgrade script for all my migration scripts which return the DB to its previous state. I have never encountered an issue where a downgrade script could not be created (though, in some cases, a little forethought is required).

A downgrade is needed in a number of cases. Just as a few examples, if v10 is our live version, and we have migrated v11 & v12 to our test server - but then find a production bug which needs to be addressed right away (v11&v12 will not be ready for months). We need to downgrade our test server back to v10 in order for the code to be compatible with the bug-fix code and be tested. Or if I have v12 on our test server, and I create v13. It works for me locally, but fails for some external reason when v13 is migrated to the test server. This shouldn't ever happen, but it can. Once I have a possible fix to v13, it would be nice to be able to downgrade to v12 locally, then re-migrate v13 to insure it still works as expected locally before having the script re-attempted on the test server.

All of this could be done by doing a clean & migrate, but this might be a very slow process.

[xseneque]

+1 ; in the real world there are many cases where this is very useful even if in theory this is not possible all the time. Just make is as "use at your own risk" feature.

this could be quite simple (by having using a naming convention for example extention=.reverse.sql or something)

I'm trying to make it work by using alternative scripts location and schema_version table, but I'm a bit stuck with running scripts in the reverse order. Is that possible? If so there could be a workaround without any dev required.

[michzimny]

+1

We also see the lack of downgrade possibility, escpecially on development. I was little suprised, when I realized that the leading database migration framework for Java does not support downgrades, when frameworks for other programming languages support it for years (e.g. for Python).

[alexbalonperin]

+1
I think that the Active Record migration tool could be a good model to follow for down migrations. It is not perfect but quite powerful nevertheless. In Active Record migrations, you write the up and down migrations in the same file. However, most of the time, the tool can figure out the down migration automatically.

The documentation is a good start to understand how it works and maybe port it to Flyway:
http://edgeguides.rubyonrails.org/active_record_migrations.html

[mcekovic]

+1 for downgrade, but -1 for using same files for migration and downgrade. It may be fine for RoR Active Record migrations, but not for general purpose DB upgrade tool like Flyway. You would need some DSL to embed SQLs in it to support the same files, but much better is to avoid it and have plain SQL in the migration scripts and differentiate downgrade through file or directory names, like Flyway is doing now for migration ordering.

[alexbalonperin]

@mcekovic it's true that having to deal with a specific DSL is not ideal. I like your idea of having a different folder or filename pattern but it might be difficult to see the connection between a migration and its reverse especially in the case of a separate folder.

Another options could be to do like the Play evolutions which are plain SQL with a part for Ups and a part for Downs (Separated by special comments): https://www.playframework.com/documentation/2.5.x/Evolutions.

[bodinger]

+1 for the downgrade feature. I have seen various projects facing the same problem ever and ever again. And some times there were solutions that were extremely helpful during development and production as it has already been pointed out.
Any argumentation that only direction upwards is a valid migration is not valid in my opinion. When you are working distributed over several teams with several issues to deal with that require migrations that can only be applied after a certain development phase you will have to rollback to a status of the database where the development of all issues has begun. and after a merge you are able to do that properly.
And i also have seen production deployments that have failed and needed a rollback. We were able to do that by simply using ActiveRecord and/or Sequel (ruby) migrations. This was our life saver.
Currently i am evaluating flyway and i can not really understand why such an easy to use tool does not offer this possibility.
I think all the problems that come up with a downwards direction migration are problems of the developers that use that tool. Not of the tool itself. So probably you need a bit more trust in the people that use the software. usually good developers (at least most of them) know what they are doing. And they also know when it comes to a migration mechanism that things are there to be taken care of.

So i vote strongly for introducing also a downward migration functionality in flyway because it simply matches the current needs of any developer and/or company that wants to work professionally.

My suggestion:

• File names should be kept as they are (by config).
• But introduce another shortcut like for repeatable migrations (R)
• suggestion for shortcut of downwards migraton: (D)

So you would have like:

• V1_5__some_up_migration.sql
• V1_5_D__some_down_migration.sql

The only thing flyway needs to do is to take care of the versioning table. All the other things are developer problems.

[elliotchance]

I am having trouble understanding why Flyway has such a hostility towards rolling back migrations*.

It's not an acceptable solution to say "just snapshot your production database and go back to that if something terrible goes wrong". What about all the data created since then? What if we already designed the application to be forward compatible? Let the application owner make these decisions and the software support them however it can.

It's true, it's uncommon and unwise to rollback migrations on a production database. But it's extremely common to do so during testing and development. Especially when dealing with multiple branches.

I understand Flyways reasoning for promoting it as a bad idea. However, any reasoning you can think off is immediately dismissed by developers who understand those issues and risks and have successfully used rollback migrations in other platforms.

* https://flywaydb.org/documentation/faq#downgrade

[mcekovic]

@elliotchance Cannot agree more!

[asciiu]

Yes, I need this during development!

[mcekovic]

Yes, database downgrade would be a feature mostly used in development environments (or Integration, QA, UAT, Performance testing environments), and probably very rarely in production, so argument about 'not suited for production' does not hold.

But, on the other hand, why not in production? Some database downgrades can be dangerous or hard or time consuming to be applied in production (like downgrade for remove column migration, where old column with data needs to be restored), but some database downgrades are fully safe (like rename column or table).
Whether to apply database downgrade or to do a restore from backup or from some other mechanism, should be judged case-by-case, depending on what database migration consisted of.

Ether case, Database Downgrade paired with Condensed Migrations are a must have features for serious database Continuous Delivery process on mission-critical multi-million record RDBMSes.

[DGoAdorsys]

+1 for the downgrade feature. Please do not mix up rollback of failing migration and downgrade use case. They are completely different!!!

I must admit, that this is the only one issue, that I miss in flyway.
Actually I do have downgrade scripts in the old way and they are used widely
My use case is following:
We have a really big team with two parallel staging processes (one for actual production version and one for next version in development), 4 staging environments for each (develop, integ, qa, prod). For each environment there is a database with dozens of data (yes, in develop also). The db restore procedure takes about 5 hours to complete, so it's not possible to perform backup/restore for downgrade reasons. On each stage database is updated/downgraded with automated scripts. And with every release on every stage it may happen that for some reasons we need to work with previous version of system, so the downgrades do happen!

For each version our developers always prepare two scripts: one for upgrade and one for downgrade. Yes, we're aware that complete downgrade of data is not always possible but this is not the issue of tool, but issue of data backups. And this happens really rare, not more that 1 of 10 upgrades.
Now we use flyway to migrate to future revisions and manual sql scripts with plain old command line tool to downgrade the database schema. And this is horrible.

My proposal for this feature implementation would be the following:

Configuration

1. Without passing configuration parameter flyway.downgrades=true the complete feature is off, so ones who don't need it won't change anything.
2. Allow to configure downgrade scripts prefix/suffix, i.e.

# update scripts, configuration as usual
flyway.sqlMigrationPrefix=patch.
flyway.sqlMigrationSeparator=_
flyway.sqlMigrationSuffix=.upgrade.sql

# downgrade scripts, additional configuration
flyway.downgradeSqlMigrationPrefix=patch.
flyway.downgradeSqlMigrationSeparator=_
flyway.downgradeSqlMigrationSuffix=.downgrade.sql

Default values could be: V1__description.sql -> R1__description.sql

Usage

The usage without option flyway.downgrades=true is the same.
Usage with option flyway.downgrades=true (as any option this could be in configuration file, maven configuration and commandline):

1. Info, Clean, Baseline, Repair
   No behaviour changes needeed
2. Validation
   mvn flyway:validate -Dflyway.downgrades=true
   This checks that for every migration script found there exists a corresponding correct downgrade script. This command validates downgrade scripts as usual.
3. Migration
   3.1. Simple migration
   mvn flyway:migrate -Dflyway.downgrades=true
   Peforms migration as usual. No changes to behaviour
   3.2. Migration to concrete version
   mvn flyway:migrate -Dflyway.downgrades=true -Dflyway.target=5
   If the database version is lower than 5, this performs normal migration. If the database version is higher than 5, this invokes the downgrade scripts to be run at reverse order (according to information of order in schema_version table). For example, if database version is 7, this command will run downgrade script R7__description.sql and after that R6__description.sql. Of course, these two versions will be removed from schema_version table (another option, instead of "Success" to store status "Downgrade success", but I personally see this useless.
   If out-of-order flag is set, downgrades could be omitted (execution could be interrupted with message).

That's it, from my point of view there is not a lot of work to be done, to make many DB Administrators happy.

[boberlla]

Forward and reverse database migrations have been in every software product I have developed. It is essential for a number of use cases.

1. In production customers are reassured by the fact that they can roll back a software upgrade and a database modification. It is not difficult to make new software understand an old schema, but you need a time machine to get old software to understand a new schema. Rollbacks are rare, but required.
2. Rollbacks are great for testing, especially on huge clusters where it is not possible to back up the database. Apply a forward migration, test, roll back. Change the migration, test...
3. Working on git feature branches that require different database schemas.

This is pretty much what everyone else said. I am considering using flyway. I need rollbacks.

[cvmocanu]

This is great news.
Thanks a lot, @axelfontaine !

[mcekovic]

Thank you, great news!
Now only condensed migrations are missing, but having downgrade is a huge step forward!

[cowwoc]

@axelfontaine Can you please clarify how undo actually works for SQL migrations? I mean, what SQL statements get executed? How does Flyway know what to do? I didn't find this documented anywhere. Thanks in advance.

[axelfontaine]

@cowwoc I have now added a new section to the docs for this: https://flywaydb.org/documentation/migration/#undo-migrations

[cowwoc]

@axelfontaine Interesting. How does one associate an UNDO migration to an SQL migration file? I assume these sit alongside the versioned SQL migrations with a different filename prefix?

[axelfontaine]

@cowwoc I have now clarified this both in the docs (https://flywaydb.org/documentation/migrations#naming) and a new tutorial (https://flywaydb.org/getstarted/undo).

See also https://flywaydb.org/documentation/commandline/migrate#undoSqlMigrationPrefix

[cowwoc]

@axelfontaine Looks good to me. Thank you!