Managing Database Schema With Flyway

What is Flyway?

“Version control for your database so you can migrate it with ease and confidence”

Flyway allows you to specify a series of migrations (versions) for your database schema and data that can be used to keep the database in-line with the application version. It supports a long list of databases including, but not limited to, MySQL, H2, PostgreSQL and Google Cloud SQL

How does it work?

Flyway works by allowing the user to define migrations, either SQL or Java. Each migration describes how to migrate the database from one version to the next. When run, to migrate a database Flyway compares the available migrations against those already applied to the database and applies any missing ones to reach the required version. Migrations are detected by file scanning a migrations directory, where order is determined by the file name which must follow the Flyway naming convention

Consider a simple example add storing documents in a database.

public class Document {
    private int id;
    private String name;
    private Date created;
    private String contents;
}

We create an intial migration to create the table for documents in the database.

v1_0__create_document_table.sql

CREATE TABLE document(
  id int NOT NULL,
  contents text,
  created timestamp without time zone NOT NULL,
  name character varying(255) NOT NULL,
)

In version 2.0 of the application we decide to add a lastModified and a version to the Document object

public class Document
{
    private int id;
    private String name;
    private int version;
    private Date created;
    private Date lastModified;
    private String contents;
}

Create a migration to alter the table to add these new columns.

v2_0__add_version_to_document.sql

ALTER TABLE document ADD COLUMN version int NOT NULL default 0;
ALTER TABLE document ADD COLUMN lastModified timestamp without time zone;

When Flyway migrate is run against a existing v1.0 database it would see that the v2.0 schema is not applied and run this migration to add the version and lastModified columns.

SQL migrations can be easily created by hand, or by using the contents of a pgdump or similar tool as no special formatting of the SQL is required.

More details on how Flyway works can be found in the Getting Started guide

What else can it do?

In addition to migrating a database to a specific version it can also be used for other operations

  • clean – remove any schema, restoring the database to a clean state
  • info – provide a report on the database, which migrations have been applied, which ones are available and not yet applied
  • validate – validate that the migrations applied to the database match the available ones
  • baseline – set the baseline version of a database, introduce Flyway to an existing database already at an existing version
  • repair – repair Flyway’s metadata, tidy up failed migrations on non-transactional databases

Running Flyway

Flyway can be run various ways, from a command line application, from various build tool plugins or embedded in the application to run at start up.

Using Flyway in Gradle

A gradle plugin for flyway is available and provides a serious of new tasks to migrate, clean etc. Ideal for ensuring that test database schema and data is in-line with the current application for integration tests and for enabling developers to share schema changes among teams.

Adding Flyway is as simple as adding the plugin to build.gradle

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath "org.flywaydb:flyway-gradle-plugin:3.2.1"
    }
}

apply plugin: 'org.flywaydb.flyway'

And then configuring Flyway, this can be done either in flyway configuration in the build script or through gradle properties. By default Flyway will expect the migrations to be in a folder called db/migration in the classpath but this can be configured.

Migrate, clean etc can then be used through the gradle command

gradle flywayMigrate

or from within the script, for example to clean and migrate a test database for integration testing

test.doFirst {
    flywayClean
    flywayMigrate
}

For more information see the Gradle plugin documentation

A similar plugin is available for Maven, Ant and SBT, more information in the Flyway documentation

Embedding Flyway in an application

Taking things a step further, Flyway can also be integrated into the application to provide automatic schema and data migration on startup before the rest of the application starts, ensuring the schema is always in sync with the application. Although this can be done programmatically using the Flyway class and configuring and calling migrate the easiest way is to using Spring to configure and run Flyway.

First include the Flyway Java API library as a dependency

compile "org.flywaydb:flyway-core:3.2.1"

Then define the flyway bean and ensure that the rest of the application starts after the flyway bean

<bean id="flyway" class="org.flywaydb.core.Flyway" init-method="migrate">
<property name="dataSource" ref="..."/>
...
</bean>

<!-- The rest of the application (incl. Hibernate) -->
<bean id="sessionFactory" class="..." depends-on="flyway">
...
</bean>

This is even easier with Spring Boot where all that is required is to add the Flyway dependency and Spring Boot will automatically create the Flyway bean and run it against the primary @DataSource

In all cases, the migrations should be located on the classpath in db/migration by default but this can be configured.

Java migrations

While SQL migrations can cover most simple data migrations, sometimes a more complex data migration is needed that would be better achieved in Java code. Flyway supports this by automatically scanning for classes in the migrations location, the same as sql files. Data migrations can be grouped with required schema changes by ensuring the version numbers are contiguous.

In our Document example, v3.0 extracts some meta information from the String contents of the document into a separate column. In this case the SQL migration would be

v3_0_0__add_wc_column.sql

ALTER TABLE document ADD COLUMN word_count int;

Now we write a Java migration that can calculate the word count for each document in the system. The first step is to determine which of interface to implement, JdbcMigration or SpringJdbcMigration. The first provides your class with a JdbcConnection while the second is only available for Spring projects and provides a JdbcTemplate. For our example we will use the SpringJdbcMigration and implement the migrate() method

v3_0_1__extract_wc_data.java

package db.migration; // must be in this package or the
                      // location must be changed in
                      // Flyway properties to match the package

import org.flywaydb.core.api.migration.spring.SpringJdbcMigration;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * Example of a Spring Jdbc migration.
 */
public class v3_0_1__extract_wc_data implements SpringJdbcMigration
{
    public void migrate(JdbcTemplate jdbcTemplate) throws Exception
    {
        List<Map<String, Object>> results = jdbcTemplate.queryForList("SELECT id,contents FROM document");

        for (Map<String, Object> result : results)
        {
            jdbcTemplate.update("UPDATE document SET word_count=? WHERE id=?", calculateWC(result.get("contents)), result.get("id"))
        }
    }
}

Now when v3.0 runs for the first time Flyway will apply the schema changes in one transaction, then in a separate transaction apply the data migration to initialise the word count for each document in the system.

 Further Reading

 



State of the Guild Address

Couple of weeks ago World of Matticus had a post urging guild leaders to do a “State of the Guild” address. While our guild isn’t what you might consider well organised or structured I still thought it a good idea to stick up a post recapping some of the high points of Wrath of the Lich King and reaffirming where we are going in the new expansion.

The Cataclysm will be with us soon, bringing with it lots of changes. We’ll have new villains to defeat, new lands to explore, new professions to train and new races to meet. There will be new raids, the new flexible lockout system, guild achievements, levels and reputation. A big long list of NEW.

Before we look forward, lets take a moment to think back on our time together in Northrend. Wrath was a big step for us as a guild, taking the decision to do our own regular guild raids. These have been the source of many a frustrated night, but we’ve had a lot of good times along the way. Who amongst us will forget the pidgeon and his box? I think we made the right decision to spend those hours together on Vent and Mumble and I hope you all join me in thanking Kihara for wielding the whip on the hard nights in Ulduar, Colliseum and Icecrown. We’ve also had a lot of other guild members step up to plate and run events for everyone to come and enjoy: Bluriel, Remyngton and Nardash are just a few of the people who have been herding you cats in the never ending adventure of keeping Azeroth safe.

Raiding though, has never, and should never, be the focus of Kindred Spirits. First and foremost we’re a community, a circle of friends, a gathering of kindred (and often crazy) spirits in the game. We’ve made many new friends along the way this year and invited them into our community with open arms. To me this is more important than any of our raiding achievements, that we continue to be open and welcoming to new people, equally as insane as the rest of us. I love that our guild has heart, that we’re always there to help out others, no matter the distance or job. Just last night one of our oldest members didn’t even hesitate to travel all the way to Gadgetzan to help our newest member. I believe it’s this community spirit that keeps our guild going when other guilds fall by the wayside. There aren’t many other guilds as old as ours and that’s something we should all be proud of, oldies and newbies alike, we’re all Spirits in the end.

So what about that big, scary, new future looming at us from the 7th December? Bring it on I say! I’m looking forward to the new friends we’ll meet in the coming months, the old friends that might reappear, and to continuing on this journey with all you crazy folk. I can’t wait to see what new and interesting ways my fellow Spirits come up with to make life interesting and fun as we work out how to get from Darnassus to Gadgetzan with a giant chasm in the way now.

On the raid front, I hope we continue to be a guild that raids, rather than a raiding guild. The changes Blizzard have brought into the raiding system seem to suite our style perfectly, and I look forward to seeing us rack up the guild experience and achievements as we battle our way through whatever Deathwing has to throw at us.

But before that happens we have something even bigger in my calendar, KSFest 2010. A chance for some of us to catch up, and for some of us to meet for the first time. I know it will go with a real Spirits bang, with more beers than you can count, some of the silliest conversations ever voiced and Issy giving us all a lift home after. May our livers forgive us.

I’ll stop being all emo at you now and let you get back to fishing up coins in Dalaran.

Sondahl, Boom Boom Chicken
aka that poor shlub who gets the bank items in their mail when someone gets hacked

The original post was crafted before we kicked the Lich King’s but otherwise that would have featured :)