labs

DB migrations in a Spring project

Having done a lot of work in Ruby on Rails over the years we at Pivotal Labs have become a little spoiled by some tools available to us. One of those tools is the ubiquitous database migration tool. When combined with Active Record’s DB abstraction we can very quickly write DB independent migrations to go along with our features. I love this podcast that goes over a little of the history of DB migrations.

Moving to the Spring and Java world has prompted me to try and re-create a similar experience to that in Ruby on Rails. The features we were looking for:

  1. Running migrations out of order
  2. Timestamped migration files

After checking out a few alternatives flyway seems to offer a good set of features. With some tweaks to the default configuration and the addition of timestamped filenames I felt right at home.
This post is only going to deal with migrating a single test DB. I’ll follow up this post with a post describing managing migrations for multiple DBs.

To add flyway to the project:

<build>
  <plugins>
    <plugin>
      <groupId>com.googlecode.flyway</groupId>
      <artifactId>flyway-maven-plugin</artifactId>
      <version>2.3</version>
      <configuration>
        <user>pivotal</user>
        <password>""</password>
        <url>jdbc:postgresql://localhost:5432/spring_dev</url>
        <outOfOrder>true</outOfOrder>
      </configuration>
    </plugin>
  </plugins>
</build>

Options
The outOfOrder configuration option requires a little more explanation. One of the important parts of our agile development process is each developer managing their own development and test DB locally.

<outOfOrder>true</outOfOrder>

We test and implement features including any DB schema changes using small migration files. These changes are committed and pushed to git. Other developers then pull the changes and run any associated migrations. When a deploy is made to another environment (e.g. staging, production) then all of the new migrations are run in order and the new associated code deployed to run with the newly migrated DB schema.
The default behavior of flyway is not to run old migrations. This clearly won’t work for us as we’d miss migrations locally when we pull changes from other developers if we’d also created and run a migration. The outOfOrder configuration allows old migrations to be run.

Timestamped filenames
The suggested flyway naming scheme for migrations is to use V1__create_table.sql. We’re used to the Rails way of a timestamp being included in the file name. That way we don’t get file name clashes. After a quick scout around I found a github project with similar goals to ours. It seemed like a good starting point rather than writing our own. The project isn’t pushed to a maven repository so we built it locally and included it in our project.


git clone https://github.com/YiyuJia/flywayFile
cd flywayFile/maven-flyway-generator-plugin
mvn install

Back to our project to add the jar file


mvn install:install-file -Dfile=../flywayFile/maven-flyway-generator-plugin/target/maven-flywayFile-plugin-2.0.jar -DpomFile=../flywayFile/maven-flyway-generator-plugin/pom.xml

Add a little configuration to our pom.xml

<properties>
  <flyway.SQL.directory>./src/main/resources/db/migration</flyway.SQL.directory>
</properties>

This allows us to create migration files much like we’d get using the rails generator

mvn org.idatamining:maven-flywayFile-plugin:2.0:generate -DmyFilename=create_account_table

This creates a migration file in the directory specified in the flyway.SQL.directory.
After adding some SQL to the migration file the migration can be run using

mvn compile flyway:migrate

Rollbacks
Flyway doesn’t support rollback migrations. This is going to get a little controversial; I don’t think they matter. I’ve yet to rollback a migration on any DB schema other than development or test. That means, for me, they are simply a convenience when developing the migrations locally. Should a migration be written incorrectly and need to be changed (before being pushed to source control and shared) then having rollback could prove convenient. Should the migration fail, from say a duplicate table error then the schema can easily be dropped and recreated from scratch.

Full source code for the project including some migrations here on Github.

Related Articles