Featured Image with Sidebar

How to Use Java-based Migrations and Callbacks to Implement Complex Database Migrations With Flyway

By Thorben Janssen


Flyway’s SQL-script based database migration is more than powerful enough for most use cases. But sometimes, you need to take it a step further to adapt your existing data to the new database schema. E.g., you might need to extract data from blobs or read JSON documents so that you can fill the newly added database columns. In these cases, Flyway’s Java migration and callback methods provide an easy and powerful way to implement the necessary migration logic.

Let’s implement a Java migration step first. You can use it in the same way as the SQL migration steps I showed you in the previous post of this series.

Implement Complex Migrations in Java

Similar to the SQL migration steps, Flyway automatically finds and executes the required migration classes. You just have to implement the JdbcMigration interface, add your class to the package defined in Flyway’s location property and use a class name that follows Flyway’s naming schema V<VERSION>__DESCRIPTION.java.

Here’s an example of a simple migration class which updates the database to version 2.0. The goal of this migration is to store the author of the book in a separate table. This requires the following operations:

  • Create a new author table and sequence
  • Read all records from the book table and get the id of the book and the name of the author
  • Persist each author as a new record in the author table
  • Set the id of the author as the foreign key in the book table
public class V2__extract_author implements JdbcMigration {

	@Override
	public void migrate(Connection connection) throws Exception {
		// create author table
		Statement st = connection.createStatement();
		st.execute("CREATE TABLE author(id bigint NOT NULL, firstname character varying(255), lastname character varying(255), CONSTRAINT author_pkey PRIMARY KEY (id));");
		st.execute("CREATE SEQUENCE author_seq");
		
		// add fk_author to book table
		st.execute("ALTER TABLE book ADD COLUMN fk_author bigint REFERENCES author (id);");

		// migrate author information
		final PreparedStatement psAuthor = connection.prepareStatement("INSERT INTO author (id, firstname, lastname) VALUES (?, ?, ?)");
		final PreparedStatement psBook = connection.prepareStatement("UPDATE book SET fk_author = ? WHERE id = ?;");
		
		ResultSet rs = st.executeQuery("select id, author from book");
		Statement idSt = connection.createStatement();
		while( rs.next() )
		{
			// get data from book table
			Long bookId = rs.getLong("id");
			String author = rs.getString("author");
			String[] name = author.split(",");
			
			// get author id from sequence
			ResultSet authorIdRs = idSt.executeQuery("select nextval('author_seq');");
			authorIdRs.next();
			Long authorId = authorIdRs.getLong(1);
			
			// write new author
			psAuthor.setLong(1, authorId);
			psAuthor.setString(2, name[1]);
			psAuthor.setString(3, name[0]);
			psAuthor.execute();
			
			// update book
			psBook.setLong(1, authorId);
			psBook.setLong(2, bookId);
			psBook.execute();
		}
		rs.close();
		psAuthor.close();
		
		// add fk_author to book table
		st.execute("ALTER TABLE book DROP COLUMN author;");
		
		st.close();
	}

}

As you can see, this requires almost no Flyway-specific code. You just need to implement the migrate method of the JdbcMigration interface. Within this method, you can use the provided Connection object to implement the migration.

When you now run your migration, Flyway will detect the current database version, scan for all SQL and Java migration steps and execute the required ones.

12:07:04,867  INFO VersionPrinter:44 - Flyway 4.2.0 by Boxfuse
12:07:04,927  INFO DbSupportFactory:44 - Database: jdbc:postgresql://localhost:5432/test_flyway (PostgreSQL 9.4)
12:07:05,009  INFO DbValidate:44 - Successfully validated 2 migrations (execution time 00:00.037s)
12:07:05,029  INFO MetaDataTableImpl:44 - Creating Metadata table: "public"."schema_version"
12:07:05,087  INFO DbMigrate:44 - Current version of schema "public": << Empty Schema >>
12:07:05,092  INFO DbMigrate:44 - Migrating schema "public" to version 1 - create database
12:07:05,127  INFO DbMigrate:44 - Migrating schema "public" to version 2 - extract author
12:07:05,159  INFO DbMigrate:44 - Successfully applied 2 migrations to schema "public" (execution time 00:00.132s).

After Flyway successfully executed a migration step, it adds a record to the schema_version table.

As you have seen, a Java migration step is used in the same way as an SQL script and fully integrates into your migration process. So, when you’re in the situation that you can’t describe the required migration in SQL, you just need to implement the JdbcMigration interface and follow Flyway’s naming convention.

Use Callbacks for Repetitive Tasks

Another useful feature for complex migration scenarios is Flyway’s callback mechanism. It allows you to execute an SQL script or a Java class when one of the following lifecycle events gets triggered within Flyway:

  • beforeMigrate
  • beforeEachMigrate
  • afterEachMigrate
  • afterMigrate
  • beforeClean
  • afterClean
  • beforeInfo
  • afterInfo
  • beforeValidate
  • afterValidate
  • beforeBaseline
  • afterBaseline
  • beforeRepair
  • afterRepair

SQL Callbacks

The implementation of an SQL callback is straightforward. You just need to add an SQL script, with the name of the lifecycle trigger you want to use, to your migration directory. The migration directory is either the sql folder of the Flyway command line client or the src/main/resources/db/migration folder of your Java application.

So, if you want to execute a SQL script after Flyway migrated your database, you just need to put all SQL statements into a file with the name afterMigrate.sql and copy it to the sql or src/main/resources/db/migration folder.

Java Callbacks

If your callback operation is too complex for an SQL script, you can also implement it in Java. That requires 2 steps. You need a class that implements the FlywayCallback interface and register it in your Flyway instance.

If you need access to the Flyway configuration, you should also implement the ConfigurationAware interface. It defines a method which Flyway will call with the configuration object so that you can store it in an internal property and use it in your callback implementation.

Another class you should know is the BaseFlywayCallback class. It implements the FlywayCallback and the ConfigurationAware interface and provides empty implementations for all methods. So, you just need to override the callback methods you want to implement.

I do that in the following example to implement an afterMigrate callback which adds an example record to the book and the author table, if the database is empty.

public class FillDatabaseAfterMigrate extends BaseFlywayCallback {

	Logger log = Logger.getLogger(FillDatabaseAfterMigrate.class.getSimpleName());
	
	@Override
	public void afterMigrate(Connection connection) {
		log.info("afterMigrate");
		Statement st;
		try {
			st = connection.createStatement();
			ResultSet rs = st.executeQuery("SELECT count(id) FROM book");
			rs.next();
			if (rs.getInt(1) == 0) {
				st.execute("INSERT INTO author (id, firstname, lastname) VALUES ((SELECT nextval('author_seq')), 'Thorben', 'Janssen');");
				st.execute("INSERT INTO book (id, publishingdate, title, fk_author, price) VALUES ((SELECT nextval('book_seq')), '2017-04-04', 'Hibernate Tips - More than 70 solutions to common Hibernate problems', 1, 9.99);");
				log.info("Database was empty. Added example data.");
			} else {
				log.info("Database contains books. No example data needed.");
				return;
			}
		} catch (SQLException e) {
			throw new MigrationException(e);
		}		
	}

	public class MigrationException extends RuntimeException {

		public MigrationException(Throwable cause) {
			super(cause);
		}	
	}
}

OK, almost done. You now just need to register the callback implementation in your Flyway instance. You do that by calling the setCallbacks method of the Flyway class with one or more FlywayCallback implementations.

Flyway flyway = new Flyway();
flyway.setDataSource("jdbc:postgresql://localhost:5432/test_flyway", "postgres", "postgres");
flyway.setCallbacks(new FillDatabaseAfterMigrate());
flyway.migrate();

That’s all you need to do. When you now start your application and trigger the database migration, Flyway will call your callback implementation when the defined lifecycle event occurs.

The following log output shows that Flyway called our callback implementation after it completed the migration. The callback implementation then initialized the empty database with 2 example records.

16:57:57,009  INFO VersionPrinter:44 - Flyway 4.2.0 by Boxfuse
16:57:57,071  INFO DbSupportFactory:44 - Database: jdbc:postgresql://localhost:5432/test_flyway (PostgreSQL 9.4)
16:57:57,162  INFO DbValidate:44 - Successfully validated 2 migrations (execution time 00:00.043s)
16:57:57,192  INFO MetaDataTableImpl:44 - Creating Metadata table: "public"."schema_version"
16:57:57,254  INFO DbMigrate:44 - Current version of schema "public": << Empty Schema >>
16:57:57,259  INFO DbMigrate:44 - Migrating schema "public" to version 1 - create database
16:57:57,297  INFO DbMigrate:44 - Migrating schema "public" to version 2 - extract author
16:57:57,336  INFO DbMigrate:44 - Successfully applied 2 migrations to schema "public" (execution time 00:00.147s).
16:57:57,339  INFO FillDatabaseAfterMigrate:17 - afterMigrate
16:57:57,345  INFO FillDatabaseAfterMigrate:26 - Database was empty. Added example data.

Summary

I showed you in the previous post of this series that Flyway provides an easy but powerful approach to implement a version-based migration process. You just need to provide a script with the required SQL statements to update your database structure and migrate your data.

In my experience, you should be able to implement almost all migrations within these SQL scripts. But as you’ve seen in this post, you’re not limited to that approach. If you need more flexibility to perform complex migration operations, you can implement them in Java.

And for all repetitive tasks, like recompilation of stored procedures, database initialization with sample data or dynamic creation of database triggers, you can implement lifecycle callbacks in SQL scripts or Java classes.

The combination of all these tools provides you with a powerful tool to implement a version-based database migration approach.

About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}