JAVADatabase migration with Flyway

Geir Ole Garaas Stevning

ons. 18. april 20186 minutter lesetid

During my years working with software development I've probably only scratched the surface of topics regarding code, architecture, patterns and best practices. One thing I always thought about was how we handled changes to our database schema. This was often done in the same way regardless of which firm I worked for or which customer I worked with.

Doing changes to a database table would normally include writing a bit of SQL, store the script somewhere, either on the file system or version control, and then send it off to the DBA for execution. Sometimes I would run it myself, given that it was stated in the release document for the service I was installing or updating.

For simple things like adding a new column this seemed like quite a waste of time. Not to mention the worries of remembering to execute manual tasks. And what if you're setting up a new environment, do you have to run all the scripts with database changes in a sequence? There had to be an alternative, and as it happens; there is.

Introducing Flyway

In my small search for a system I stumbled upon two; Flyway and Liquibase.

In the end I landed on Flyway, mainly due to how easy it was to configure in my case. I also didn't want to add more complexity with using XML to configure the migrations which was what Liquibase supported. Flyway's SQL support seemed more suitable for my case. But if you plan on supporting or changing database vendor, I would have another look at Liquibase.

So, what is Flyway? Flyway is an open source database migration tool. It's easy to use and has a lot of plugins for convenience. SQL is used to write migrations, while the Java API can help you with the more complex transformations and recalculations of data. Flyway keeps a schema history table for status of the migration tasks.

A typical example of use is to have the initial database schema in a file: V1.0__Initial_version.sql. Additional changes to the schema would be noted in files such as: V1.1__Added_some_column_to_tableA.sql and V1.2__Changed_columnXYZ_to_BIGINT.sql. When the application loads, Flyway looks at the noted history and applies the scripts in order.

Flyway is vast and has several different features not covered by this post. What if you want to undo a migration? That's supported, it's included in the following commands: Migrate, Clean, Info, Validate, Undo, Baseline and Repair.

Practical Example

In my practical example I will make use of Spring Boot(spring-boot-starter-parent) with the following dependencies: spring-boot-starter-data-jpa, spring-boot-starter-web, flyway-core and h2. The code is published here

First attempt

An entity named Team and a repository named TeamRepository will define the relationship with the database.

@Entity
public class Team {
    @Id
    private Long id;

    private String name;

    // Getters/Setters
}

public interface TeamRepository extends CrudRepository<Team, Long> {
}

In the application.properties file we will let hibernate validate the schema without making changes by setting: spring.jpa.hibernate.ddl-auto=validate.

We start the Spring Boot application from the following main-method.

@SpringBootApplication
public class FlywayExampleApplication {
    
    public static void main(String[] args) {
        SpringApplication.run(FlywayExampleApplication.class, args);
    }
}

Uh-oh

The log following the execution of FlywayExampleApplication is grim reading: 2018-04-16 20:23:16.992 ERROR 5868 --- [ main] o.s.boot.SpringApplication : Application run failed

Looking further in the logs we find a stack trace where the cause is: java.lang.IllegalStateException: Cannot find migrations location in: [classpath:db/migration] (please add migrations or check your Flyway configuration)

Okay, let's add some migrations! Under the default directory src/main/db/migrations we create a simple file called V1.0__init.sql:

CREATE TABLE TEAM (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  name varchar(255) not null
);

When running the application once more we can clearly see that the migrations have been run:

2018-04-16 20:30:05.665  INFO 16424 --- [  main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:h2:mem:testdb (H2 1.4)
2018-04-16 20:30:05.840  INFO 16424 --- [  main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.033s)
2018-04-16 20:30:05.867  INFO 16424 --- [  main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: "PUBLIC"."flyway_schema_history"
2018-04-16 20:30:05.912  INFO 16424 --- [  main] o.f.core.internal.command.DbMigrate      : Current version of schema "PUBLIC": << Empty Schema >>
2018-04-16 20:30:05.913  INFO 16424 --- [  main] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 1.0 - init database
2018-04-16 20:30:05.932  INFO 16424 --- [  main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.075s)

Great, now what?

So, the application started successfully and everything is good. Time to destroy that illusion.

In Team.java we add a new field, which by default maps to a column in our table. Let's call the field yearFounded. Restarting the application will once again make the validation fail, and hibernate tells us what's wrong: Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing column [founded_year] in table [team]. We need to update our table in order to match our entity.

Migrate!

To fix this we'll add another file: V1.1__Add_yearFounded_column_to_team.sql

ALTER TABLE TEAM
ADD year_founded BIGINT NOT NULL;

During application startup we can see that Flyway applies our migration:

2018-04-16 20:41:39.847  INFO 22352 --- [  main] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 1.1 - Add foundedYear column to team

And the application is up and running!

Running the query: select "version", "description" from "flyway_schema_history" we can clearly see that Flyway has applied the migrations we supplied under db/migration.

Conclusion

Adding additional migrations and advanced cases will now be, if not trivial, easier. By using Flyway integrated in our application we solved some of the difficulties I've had throughout my career. Some of the issues covered include:

Maintain and version control our migrations
Let a tool apply the database migrations
Find issues early in the development cycle

As with everything else, it's not the perfect tool/system, but for most cases it gets the job done in a peaceful manner. The best way is to find out for yourself and make up your own mind on what you and your team prefer. It's better than waking up after doing a release wondering if you actually ran the database clean-up script you were supposed to.

References

Flyway documentation
Example code