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

Tutorial: Java-based Migrations

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

Introduction

Java-based migrations are a great fit for all changes that can not easily be expressed using SQL.

These would typically be things like

  • BLOB & CLOB changes
  • Advanced bulk data changes (Recalculations, advanced format changes, …)

Reviewing the status

After having completed the First Steps: Maven, you can now execute

bar> mvn flyway:info

This should give you the following status:

[INFO] Database: jdbc:h2:file:./target/foobar (H2 1.4)
[INFO]
+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1       | Create person table | SQL  | 2017-12-22 15:26:39 | Success |
| Versioned | 2       | Add people          | SQL  | 2017-12-22 15:28:17 | Success |
+-----------+---------+---------------------+------+---------------------+---------+

Creating a Java-based migrations

Now let’s create a Java-based migration to anonymize the data in the person table.

Start by

  • adding the flyway-core dependency to our pom.xml
  • configuring the Java compiler for Java 8
  • configuring Flyway to scan the Java classpath for migrations
<project xmlns="...">
    ...
    <dependencies>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>5.0.7</version>
        </dependency>
        ...
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.7.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>5.0.7</version>
                <configuration>
                    <url>jdbc:h2:file:./target/foobar</url>
                    <user>sa</user>
                    <locations>
                        <location>classpath:db/migration</location>
                    </locations>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>com.h2database</groupId>
                        <artifactId>h2</artifactId>
                        <version>1.4.191</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>
</project>

Now create the migration directory src/main/java/db/migration.

Followed by a first migration called src/main/java/db/migration/V3__Anonymize.java:

package db.migration;

import org.flywaydb.core.api.migration.jdbc.JdbcMigration;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class V3__Anonymize implements JdbcMigration {
    public void migrate(Connection connection) throws Exception {
        try (Statement select = connection.createStatement()) {
            try (ResultSet rows = select.executeQuery("SELECT id FROM person ORDER BY id")) {
                while (rows.next()) {
                    int id = rows.getInt(1);
                    String anonymizedName = "Anonymous" + id;
                    try (Statement update = connection.createStatement()) {
                        update.execute("UPDATE person SET name='" + anonymizedName + "' WHERE id=" + id);
                    }
                }
            }
        }
    }
}

Finally compile the project using

bar> mvn compile

This is now the status

bar> mvn flyway:info

[INFO] Database: jdbc:h2:file:./target/foobar (H2 1.4)
[INFO]
+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1       | Create person table | SQL  | 2017-12-22 15:26:39 | Success |
| Versioned | 2       | Add people          | SQL  | 2017-12-22 15:28:17 | Success |
| Versioned | 3       | Anonymize           | JDBC |                     | Pending |
+-----------+---------+---------------------+------+---------------------+---------+

Note the new pending migration of type JDBC.

Executing the migration

It’s time to execute our new migration.

So go ahead and invoke

bar> mvn flyway:migrate

This will give you the following result:

[INFO] Database: jdbc:h2:file:./target/foobar (H2 1.4)
[INFO] Successfully validated 3 migrations (execution time 00:00.022s)
[INFO] Current version of schema "PUBLIC": 2
[INFO] Migrating schema "PUBLIC" to version 3 - Anonymize
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.011s)

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

bar> mvn flyway:info

[INFO] Database: jdbc:h2:file:./target/foobar (H2 1.4)
[INFO]
+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1       | Create person table | SQL  | 2017-12-22 15:26:39 | Success |
| Versioned | 2       | Add people          | SQL  | 2017-12-22 15:28:17 | Success |
| Versioned | 3       | Anonymize           | JDBC | 2017-12-22 16:03:37 | Success |
+-----------+---------+---------------------+------+---------------------+---------+

As expected we can see that the Java-based migration was applied successfully.

Summary

In this brief tutorial we saw how to

  • create Java-based migrations
  • configure Flyway to load and run them

Read the Java-based migration documentation