65

MyBatis migrations splits each SQL file into two sections:

  1. One for migrating forward one version
  2. One for migrating back one version

How does one roll back versions using Flyway?

Martin Schröder
  • 4,176
  • 7
  • 47
  • 81
Gili
  • 86,244
  • 97
  • 390
  • 689

3 Answers3

86

While Flyway supports rollbacks (as a commercial-only feature) its use is discouraged:

https://flywaydb.org/documentation/command/undo

While the idea of undo migrations is nice, unfortunately it sometimes breaks down in practice. As soon as you have destructive changes (drop, delete, truncate, …), you start getting into trouble. And even if you don’t, you end up creating home-made alternatives for restoring backups, which need to be properly tested as well.

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.

An alternative approach which we find preferable is to maintain backwards compatibility between the DB and all versions of the code currently deployed in production. This way a failed migration is not a disaster. The old version of the application is still compatible with the DB, so you can simply roll back the application code, investigate, and take corrective measures.

This should be complemented with a proper, well tested, backup and restore strategy. It is independent of the database structure, and once it is tested and proven to work, no migration script can break it. For optimal performance, and if your infrastructure supports this, we recommend using the snapshot technology of your underlying storage solution. Especially for larger data volumes, this can be several orders of magnitude faster than traditional backups and restores.

Community
  • 1
  • 1
Gili
  • 86,244
  • 97
  • 390
  • 689
  • 6
    Your best option is of course to use a database that properly supports DDL transactions, such as PostgreSQL. That way, the database will take care of cleaning up the mess for you. – Axel Fontaine Feb 10 '11 at 20:47
  • 16
    You misunderstood the point of my question. I'm not saying I'd like to roll-back in the middle of a transaction in case of a failure. I'm saying a customer has version 4 of the database and I want to roll him back to version 2. – Gili Feb 12 '11 at 17:56
  • 1
    What would be the real life usecase where one would like to roll back multiple successful migrations on a production system? – Axel Fontaine Feb 13 '11 at 13:37
  • 1
    I was thinking of rolling back a developer database to an older version a customer has. Though obviously using a backup/restore is a better approach (assuming the DB isn't too large). – Gili Feb 14 '11 at 03:57
  • 7
    Another usecase would be to rollback the (life)system when a new feature makes trouble – Karussell Feb 19 '12 at 21:36
  • 4
    We are using liquibase and postgresql for years and never had to rollback one single migration. We are going to switch to flyway as we can put our migration scripts in different modules. Flyway just scans the classpath for migration scripts. That's fine. – Janning Vygen Aug 22 '12 at 15:33
  • Please add the key points to your post so that when the link breaks this answer is still useful. Posts here need to stand on their own, with links as references to supplemental information. – Kevin Nov 06 '12 at 23:44
  • 1
    @Kevin: Done. I copied the FAQ section into the answer. – Gili Nov 07 '12 at 00:21
  • 31
    Another usecase is when working on a branch which is ahead some migrations. Then I want to work on other branch. Rollbacking those migrations is a must, otherwise the code would break. – mirelon Mar 07 '14 at 10:04
  • 2
    @Gili The better solution to the problem of a developer deploying an old version of the database is to deploy it from scratch. Drop the developer's copy and deploy from nothing. If you have a good CI set up (and the whole *point* of technologies like Flyway is to help get your database into one), then checking out the code for version 2 and running the deploy scripts is trivial. Making a fix off of version 2 probably requires branching off that code anyway. – jpmc26 Apr 29 '15 at 18:52
  • @mirelon I have my PostgreSQL database in a Docker container, and in order to rollback, I just delete the container and start a new one based on a base image which contains an a-little-bit-older version of the database + some test data. This new database container then gets rolled forwards by my application, to whichever branch I have checked out. — After switching to Flyway + Docker, I've never felt the need for any down migrations :-) – KajMagnus Sep 19 '15 at 14:15
  • 6
    I liked Flyway for migrations, but I find the arguments for not implementing rollbacks pretty lame.. As stated before by @mirelon there are real use cases where rollbacks are a must-have.. – Joaquín L. Robles Apr 07 '17 at 15:27
  • 1
    @JoaquínL.Robles I don't think it's a "must have" when jpmc26 answered mirelon's comment with an alternative. Deploy an empty database from scratch and run all the forward migrations from zero up to your desired stopping point. This is at least as effective as using backward migrations because it's a lot easier to guarantee its reproduceability. – Kev Jun 19 '17 at 20:31
  • 2
    I've been using Rails for years and have found the ability to rollback migrations to be extremely helpful - in development. Especially during the process of writing migrations, where you might find that you've forgotten a few key changes, it's much cleaner to rollback, modify the migration, and rerun it. Sure, rollback shouldn't be used in production, but in development it's a big time saver and results in fewer, cleaner migrations. – mockaroodev Sep 27 '17 at 14:37
  • @mockaroodev You can configure Flyway to automatically clean the database on validation errors (e.g. when you update an existing migration file). Yes, you lose the data, but in development this is rarely an issue. – Gili Sep 28 '17 at 15:51
  • This answer is no longer technically accurate. Flyway 5.0 added support for rollbacks, but it's a commercial only feature. See post below. – user2248785 Jun 04 '19 at 16:50
  • another reason rollbacks are finicky is views/stored procs/etc. If you define them in migrations, you need to copy paste them in every up and down section of every migration that modifies them. If instead you define them in a separate file, now any dependent tables that have migrations can't be rolled back. – Hudon Mar 02 '23 at 22:09
11

This is supported since Flyway 5.0. Sadly it's a commercial only feature though.

https://flywaydb.org/documentation/command/undo

user2248785
  • 385
  • 3
  • 12
  • 2
    Given that the caveats mentioned at https://stackoverflow.com/a/4959332/14731 still apply this doesn't get you very far. Still, it's nice to know this was added. Maybe they'll find a way to remove some of these limitations in the future. – Gili Dec 13 '17 at 10:29
  • Those caveats were revised in the latest version. See the relevant [commit](https://github.com/flyway/flywaydb.org/commit/25e1f01ca6effb1c508bd98c5e6a0fe4cef0172a#diff-83eadbf6287db7b4b054d30b5dae6394). – user2248785 Dec 19 '17 at 19:43
0

I assume you need a rollback strategy, when e.g. a partner fails at production stage and his deployment is essential for your release.

You could name your flyway SQL scripts like these:
V< YourReleaseNumber >.000_< description >.sql

Now you can leave
V< YourReleaseNumber >.998_rollback.sql for rollback
and make V< YourReleaseNumber >.999_reenroll.sql to reenroll.

In your CI/CD Environment you need 2 more Jobs (manually triggered) after your deployment job. One for rollback, which runs the rollback process including flyway migrate. Other for reenroll.
You just have to care for the target configuration in flyway.
For your deployment job your target should be < YourReleaseNumber >.997
For your rollback job < YourReleaseNumber >.998

When you start a new release, make sure you won't run the rollback/reenroll script of the old release.

As said before a well tested, backup and restore strategy is the recommended solution.

(sry for bad english)

ndueck
  • 713
  • 1
  • 8
  • 27