Want to deploy your JVM, Node.js and Go apps effortlessly to AWS? Try our service Boxfuse

Tutorial: Repeatable Migrations

This tutorial assumes you have successfully completed the First Steps: 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 repeatable migrations. It will take you through the steps on how to create and use them.

Introduction

Repeatable migrations are very useful for managing database objects whose definition can then simply be maintained in a single file in version control. Instead of being run just once, they are (re-)applied every time their checksum changes.

They are typically used for

  • (Re-)creating views/procedures/functions/packages/…
  • Bulk reference data reinserts

Reviewing the status

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

flyway-5.0.6> 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-21 18:05:10 | Success | No       |
| Versioned | 2       | Add people          | SQL  | 2017-12-21 18:05:10 | Success | No       |
+-----------+---------+---------------------+------+---------------------+---------+----------+

Creating a repeatable migration

Now let’s create a repeatable migration to manage a view of the person table. With Flyway’s default naming convention, the filename will be similar to the regular migrations, except for the V prefix which is now replaced with a R and the lack of a version.

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

CREATE OR REPLACE VIEW people AS 
    SELECT id, name FROM person;

This is now the status

flyway-5.0.6> flyway info

Database: jdbc:h2:file:./foobardb (H2 1.4)
                     
+------------+---------+---------------------+------+---------------------+---------+----------+
| Category   | Version | Description         | Type | Installed On        | State   | Undoable |
+------------+---------+---------------------+------+---------------------+---------+----------+
| Versioned  | 1       | Create person table | SQL  | 2017-12-21 18:05:10 | Success | No       |
| Versioned  | 2       | Add people          | SQL  | 2017-12-21 18:05:10 | Success | No       |
| Repeatable |         | People view         | SQL  |                     | Pending |          |
+------------+---------+---------------------+------+---------------------+---------+----------+

Note the new pending repeatable migration.

Executing the migration

It’s time to execute our new migration.

So go ahead and invoke

flyway-5.0.6> flyway migrate

This will give you the following result:

Database: jdbc:h2:file:./foobardb (H2 1.4)
Successfully validated 3 migrations (execution time 00:00.032s)
Current version of schema "PUBLIC": 2
Migrating schema "PUBLIC" with repeatable migration People view
Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.023s)

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

flyway-5.0.6> flyway info

Database: jdbc:h2:file:./foobardb (H2 1.4)
                     
+------------+---------+---------------------+------+---------------------+---------+----------+
| Category   | Version | Description         | Type | Installed On        | State   | Undoable |
+------------+---------+---------------------+------+---------------------+---------+----------+
| Versioned  | 1       | Create person table | SQL  | 2017-12-21 18:05:10 | Success | No       |
| Versioned  | 2       | Add people          | SQL  | 2017-12-21 18:05:10 | Success | No       |
| Repeatable |         | People view         | SQL  | 2017-12-21 18:08:29 | Success |          |
+------------+---------+---------------------+------+---------------------+---------+----------+

As expected we can see that the repeatable migration was applied successfully.

Modifying the migration

Now let’s see what happens when we modify our migration file in place.

Update R__People_view.sql in the /sql directory as follows:

CREATE OR REPLACE VIEW people AS 
    SELECT id, name FROM person WHERE name like 'M%';

And check the status again:

flyway-5.0.6> flyway info

Database: jdbc:h2:file:./foobardb (H2 1.4)
                     
+------------+---------+---------------------+------+---------------------+----------+----------+
| Category   | Version | Description         | Type | Installed On        | State    | Undoable |
+------------+---------+---------------------+------+---------------------+----------+----------+
| Versioned  | 1       | Create person table | SQL  | 2017-12-21 18:05:10 | Success  | No       |
| Versioned  | 2       | Add people          | SQL  | 2017-12-21 18:05:10 | Success  | No       |
| Repeatable |         | People view         | SQL  | 2017-12-21 18:08:29 | Outdated |          |
| Repeatable |         | People view         | SQL  |                     | Pending  |          |
+------------+---------+---------------------+------+---------------------+----------+----------+

Our audit trail now clearly shows that the repeatable migration that was previously applied has become outdated and is now marked as pending again, ready to be reapplied.

So let’s do exactly that:

flyway-5.0.6> flyway migrate

Database: jdbc:h2:file:./foobardb (H2 1.4)
Successfully validated 4 migrations (execution time 00:00.019s)
Current version of schema "PUBLIC": 2
Migrating schema "PUBLIC" with repeatable migration People view
Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.027s)

And the status is now

flyway-5.0.6> flyway info

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

+------------+---------+---------------------+------+---------------------+------------+----------+
| Category   | Version | Description         | Type | Installed On        | State      | Undoable |
+------------+---------+---------------------+------+---------------------+------------+----------+
| Versioned  | 1       | Create person table | SQL  | 2017-12-21 18:05:10 | Success    | No       |
| Versioned  | 2       | Add people          | SQL  | 2017-12-21 18:05:10 | Success    | No       |
| Repeatable |         | People view         | SQL  | 2017-12-21 18:08:29 | Superseded |          |
| Repeatable |         | People view         | SQL  | 2017-12-21 18:15:35 | Success    |          |
+------------+---------+---------------------+------+---------------------+------------+----------+

Our initial run has now been superseded by the one we just did. And so whenever the object you are managing (the people view in our example) needs to change, simply update the file in place and run migrate again.

Summary

In this brief tutorial we saw how to

  • create repeatable migrations
  • run and rerun repeatable migrations

Read the repeatable migration documentation