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, …)

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 migratedb-core dependency to our pom.xml
  • configuring the Java compiler for Java 11
  • configuring MigrateDB to scan the Java classpath for migrations
<project xmlns="...">
    ...
    <dependencies>
        <dependency>
            <groupId>de.unentscheidbar</groupId>
            <artifactId>migratedb-core</artifactId>
            <version>1.4.1</version>
        </dependency>
        ...
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.7.0</version>
                <configuration>
                    <source>11</source>
                    <target>11</target>
                </configuration>
            </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 migratedb.1.core.api.migration.BaseJavaMigration;
import migratedb.1.core.api.migration.Context;

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

public class V3__Anonymize extends BaseJavaMigration {
    public void migrate(Context context) throws Exception {
        try (Statement select = context.getConnection().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 = context.getConnection().createStatement()) {
                        update.execute("UPDATE person SET name='" + anonymizedName + "' WHERE id=" + id);
                    }
                }
            }
        }
    }
}

Finally compile the project using

bar> mvn compile

The new status is:

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

[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 MigrateDB to load and run them

Read the Java-based migration documentation ➡️