Rolling Back Migrations with Flyway

1. Introduction

In this short tutorial, we’ll explore a couple of ways to rollback a migration with Flyway.

2. Simulate Rollback with a Migration

In this section, we’ll rollback our database using a standard migration file.

In our examples, we’ll use the command-line version of Flyway. However, the core principles are equally applicable to the other formats, such as the core API, Maven plugin, etc.

2.1. Create Migration

First, let’s add a new book table to our database. In order to do this, we’ll create a migration file called V1_0__create_book_table.sql:

create table book (
  id numeric,
  title varchar(128),
  author varchar(256),
  constraint pk_book primary key (id)
);

Secondly, let’s apply the migration:

./flyway migrate

2.2. Simulate Rollback

Then, at some point, say we need to reverse the last migration.

In order to restore the database to before the book table was created, let’s create migration called V2_0__drop_table_book.sql:

drop table book;

Next, let’s apply the migration:

./flyway migrate

Finally, we can check the history of all the migrations using:

./flyway info

which gives us the following output:

+-----------+---------+-------------------+------+---------------------+---------+
| Category  | Version | Description       | Type | Installed On        | State   |
+-----------+---------+-------------------+------+---------------------+---------+
| Versioned | 1.0     | create book table | SQL  | 2020-08-29 16:07:43 | Success |
| Versioned | 2.0     | drop table book   | SQL  | 2020-08-29 16:08:15 | Success |
+-----------+---------+-------------------+------+---------------------+---------+

Notice that our second migration ran successfully.

As far as Flyway is concerned, the second migration file is just another standard migration. The actual restoring of the database to the previous version is done entirely through SQL. For example, in our case, the SQL of dropping the table is the opposite of the first migration, which creates the table.

Using this method, the audit trail doesn’t show us that the second migration is related to the first, as they have different version numbers. In order to get such an audit trail, we need to use Flyway Undo.

3. Using Flyway Undo

Firstly, it’s important to note that Flyway Undo is a commercial feature of Flyway and isn’t available in the Community Edition. Therefore, we’ll need either the Pro Edition or Enterprise Edition in order to use this feature.

3.1. Create Migration Files

First, let’s create a migration file called V1_0__create_book_table.sql:

create table book (
  id numeric,
  title varchar(128),
  author varchar(256),
  constraint pk_book primary key (id)
);

Secondly, let’s create the corresponding undo migration file U1_0__create_book_table.sql:

drop table book;

In our undo migration, notice how the filename-prefix is ‘U’ compared with the normal migration prefix of ‘V’. Also, in our undo migration files, we write the SQL that reverses the changes of the corresponding migration file. In our case, we’re dropping the table that’s created by the normal migration.

3.2. Apply Migrations

Next, let’s check the current state of the migrations:

./flyway -pro info

This gives us the following output:

+-----------+---------+-------------------+------+--------------+---------+----------+
| Category  | Version | Description       | Type | Installed On | State   | Undoable |
+-----------+---------+-------------------+------+--------------+---------+----------+
| Versioned | 1.0     | create book table | SQL  |              | Pending | Yes      |
+-----------+---------+-------------------+------+--------------+---------+----------+

Notice the last column, Undoable, which indicates Flyway has detected an undo migration file that accompanies our normal migration file.

Next, let’s apply our migrations:

./flyway migrate

When it completes, our migrations are complete, and our schema has a new book table:

                List of relations
 Schema |         Name          | Type  |  Owner   
--------+-----------------------+-------+----------
 public | book                  | table | baeldung
 public | flyway_schema_history | table | baeldung
(2 rows)

3.3. Rollback the Last Migration

Finally, let’s undo the last migration using the command line:

./flyway -pro undo

After the command has run successfully, we can check the status of the migrations again:

./flyway -pro info

which gives us the following output:

+-----------+---------+-------------------+----------+---------------------+---------+----------+
| Category  | Version | Description       | Type     | Installed On        | State   | Undoable |
+-----------+---------+-------------------+----------+---------------------+---------+----------+
| Versioned | 1.0     | create book table | SQL      | 2020-08-22 15:48:00 | Undone  |          |
| Undo      | 1.0     | create book table | UNDO_SQL | 2020-08-22 15:49:47 | Success |          |
| Versioned | 1.0     | create book table | SQL      |                     | Pending | Yes      |
+-----------+---------+-------------------+----------+---------------------+---------+----------+

Notice how the undo has been successful, and the first migration is back to pending. Also, in contrast to the first method, the audit trail clearly shows the migrations that were rolled back.

Although Flyway Undo can be useful, it assumes that the whole migration has succeeded. For example, it may not work as expected if a migration fails partway through.

4. Conclusion

In this short tutorial, we looked at restoring our database using a standard migration. We also looked at the official way of rolling back migrations using Flyway Undo.