Tutorial: Undo Migrations

Flyway Teams

This tutorial assumes you have successfully completed the Quickstart: Command-line tutorial If you have not done so, please do so first. This tutorial picks up where that one left off.

This brief tutorial will teach how to use undo migrations. It will take you through the steps on how to create and use them.

Introduction

Undo migrations are the opposite of regular versioned migrations. An undo migration is responsible for undoing the effects of the versioned migration with the same version. Undo migrations are optional and not required to run regular versioned migrations.

Reviewing the status

After having completed the Quickstart: Command-line, you can now execute

flyway-11.1.0> flyway info

This should give you the following status:

Database: jdbc:h2:file:./foobardb (H2 1.4)

+-----------+---------+---------------------+------+---------------------+---------+----------+
| Category  | Version | Description         | Type | Installed On        | State   | Undoable |
+-----------+---------+---------------------+------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL  | 2017-12-17 19:57:28 | Success | No       |
| Versioned | 2       | Add people          | SQL  | 2017-12-17 20:01:13 | Success | No       |
+-----------+---------+---------------------+------+---------------------+---------+----------+

Creating the undo migrations

Now let's create undo migrations for these two applied versioned migrations. With Flyway's default naming convention, the filenames will be identical to the regular migrations, except for the V prefix which is now replaced with a U.

So go ahead and create U2__Add_people.sql in the /sql directory:

DELETE FROM PERSON;

And add a U1__Create_person_table.sql as well:

DROP TABLE PERSON;

This is now the status

flyway-11.1.0> flyway info

Database: Database: jdbc:h2:file:./foobardb (H2 1.4)

+-----------+---------+---------------------+------+---------------------+---------+----------+
| Category  | Version | Description         | Type | Installed On        | State   | Undoable |
+-----------+---------+---------------------+------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL  | 2017-12-17 19:57:28 | Success | Yes      |
| Versioned | 2       | Add people          | SQL  | 2017-12-17 20:01:13 | Success | Yes      |
+-----------+---------+---------------------+------+---------------------+---------+----------+

Note that both migrations have now been marked as being undo-able.

Undoing the last migration

By default, undo undoes the last applied versioned migration.

So go ahead and invoke

flyway-11.1.0> flyway undo

This will give you the following result:

Database: Database: jdbc:h2:file:./foobardb (H2 1.4)
Current version of schema "PUBLIC": 2
Undoing migration of schema "PUBLIC" to version 2 - Add people
Successfully undid 1 migration to schema "PUBLIC" (execution time 00:00.030s)

And you can check that this is indeed the new status:

flyway-11.1.0> flyway info

Database: Database: jdbc:h2:file:./foobardb (H2 1.4)

+-----------+---------+---------------------+----------+---------------------+---------+----------+
| Category  | Version | Description         | Type     | Installed On        | State   | Undoable |
+-----------+---------+---------------------+----------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL      | 2017-12-17 19:57:28 | Success | Yes      |
| Versioned | 2       | Add people          | SQL      | 2017-12-17 20:01:13 | Undone  |          |
| Undo      | 2       | Add people          | UNDO_SQL | 2017-12-17 22:45:56 | Success |          |
| Versioned | 2       | Add people          | SQL      |                     | Pending | Yes      |
+-----------+---------+---------------------+----------+---------------------+---------+----------+

Our audit trail now clearly shows that version 2 was first applied, then undone and is now pending again.

We can now safely reapply it with

flyway-11.1.0> flyway migrate

Database: Database: jdbc:h2:file:./foobardb (H2 1.4)
Successfully validated 5 migrations (execution time 00:00.020s)
Current version of schema "PUBLIC": 1
Migrating schema "PUBLIC" to version 2 - Add people
Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.017s)

And the status is now

flyway-11.1.0> flyway info

Database: Database: jdbc:h2:file:./foobardb (H2 1.4)

+-----------+---------+---------------------+----------+---------------------+---------+----------+
| Category  | Version | Description         | Type     | Installed On        | State   | Undoable |
+-----------+---------+---------------------+----------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL      | 2017-12-17 19:57:28 | Success | Yes      |
| Versioned | 2       | Add people          | SQL      | 2017-12-17 20:01:13 | Undone  |          |
| Undo      | 2       | Add people          | UNDO_SQL | 2017-12-17 22:45:56 | Success |          |
| Versioned | 2       | Add people          | SQL      | 2017-12-17 22:50:49 | Success | Yes      |
+-----------+---------+---------------------+----------+---------------------+---------+----------+

Summary

In this brief tutorial we saw how to

create undo migrations
undo and redo existing migrations

Redgate Flyway

Tutorial - Undo Migrations