Sometimes when you upgrade your application the new version does not work correctly, and you need to downgrade. When you are unlucky you did some changes in your database that cannot be undone. In that case you need to restore the database from a backup, which is usually quite cumbersome and time-consuming. How nice would it be if you could just run an undo script which is much more light weight. This blog describes how you can do that using Flyway.

Steps to follow

  1. Create a downgrade script for every upgrade script.

  2. Make both scripts idempotent, by using IF EXISTS constructs. This makes your scripts more robust and testable.

  3. Do not add irreversible changes to the upgrade script. So do not use statements like delete or drop. Instead, use rename, and do the actual deletes and drops some version later.

Here is an example for MariaDB:

V1.7__rename_table_city_and_person_columns.sql:

ALTER TABLE IF EXISTS city RENAME TO deprecated_city;

ALTER TABLE
    person RENAME COLUMN IF EXISTS name TO deprecated_name,
    person ADD COLUMN IF NOT EXISTS first_name VARCHAR(255),
    person ADD COLUMN IF NOT EXISTS last_name VARCHAR(255);

UPDATE person SET first_name = deprecated_name;

U1.7__rename_table_city_and_person_columns.sql:

ALTER TABLE IF EXISTS deprecated_city RENAME TO city;

ALTER TABLE person RENAME COLUMN IF EXISTS deprecated_name TO name;
--  No need to drop added columns, they are harmless.

And then a few releases later:

V1.11__drop_deprecated_table_and_column.sql:

DROP TABLE IF EXISTS deprecated_city;

ALTER TABLE person DROP COLUMN IF EXISTS deprecated_name;

And now there is no undo script, because this cannot be undone. However, since this is a few releases later you should have noticed any issues with the missing (because they were renamed) table and column in earlier releases already.

NB When you run the undo script manually, you will have to update the flyway history table manually as well. See also Flyway in the Flyway in the command-line.

shadow-left