New! – Spin up instant copies of your dev databases in the cloud for free Learn more

Flyway Documentation

Poll

Tutorial: Undo Migrations

Flyway Teams

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

Setting up the database

This tutorial picks up from where the First Steps: Command-line tutorial left off.

To get started quickly without having to run through that tutorial first, we will create a new Spawn data container with the migrations from that tutorial already applied:

> spawnctl create data-container \
  --image postgres:flyway-getting-started-complete \
  --name flyway-undo \
  --lifetime 24h

The flyway-getting-started-complete image is available for other database engines besides postgres. Use:

$ spawnctl get data-images --public | grep flyway-getting-started-complete

to find them.

Configure Flyway by editing ./flyway.conf with your Spawn data container connection details, like this:

flyway.url=jdbc:postgresql://instances.spawn.cc:<Port>/foobardb
flyway.user=<User>
flyway.password=<Password>

Create a ./sql directory and create the two migration scripts that have already been applied. The first file should be called V1__Create_person_table.sql:

create table PERSON (
    ID int not null,
    NAME varchar(100) not null
);

and the second one called V2__Add_people.sql:

insert into PERSON (ID, NAME) values (1, 'Axel');
insert into PERSON (ID, NAME) values (2, 'Mr. Foo');
insert into PERSON (ID, NAME) values (3, 'Ms. Bar');

The database is now ready to go.

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 First Steps: Command-line, you can now execute

flyway-8.0.0-beta1> flyway info

This should give you the following status:

Database: jdbc:postgresql://instances.spawn.cc:31585/ (PostgreSQL 11.0)

+-----------+---------+---------------------+------+---------------------+---------+----------+
| 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-8.0.0-beta1> flyway info

Database: Database: jdbc:postgresql://instances.spawn.cc:31585/ (PostgreSQL 11.0)

+-----------+---------+---------------------+------+---------------------+---------+----------+
| 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 undoable.

Undoing the last migration

By default, undo undoes the last applied versioned migration.

So go ahead and invoke

flyway-8.0.0-beta1> flyway undo

This will give you the following result:

Database: Database: jdbc:postgresql://instances.spawn.cc:31585/ (PostgreSQL 11.0)
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-8.0.0-beta1> flyway info

Database: Database: jdbc:postgresql://instances.spawn.cc:31585/ (PostgreSQL 11.0)

+-----------+---------+---------------------+----------+---------------------+---------+----------+
| 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-8.0.0-beta1> flyway migrate

Database: Database: jdbc:postgresql://instances.spawn.cc:31585/ (PostgreSQL 11.0)
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-8.0.0-beta1> flyway info

Database: Database: jdbc:postgresql://instances.spawn.cc:31585/ (PostgreSQL 11.0)

+-----------+---------+---------------------+----------+---------------------+---------+----------+
| 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

Read the undo migration documentation