Introducing Java DB Migrations

UPDATE: A new version of the Java DB Migrations framework has been release, check this post for details and the project documentation.</p

Here at Carbon Five we have the luxury of working on many projects, so anything we can do to make things easier will pay off in multiplicity across new projects. One of the things that we have to deal with on every project is maintaining a database schema over time. We’ve had a manual process of capturing changes in incremental db patch scripts for a while, but it was error prone and sometimes neglected. We’ve been doing more Ruby on Rails work and found Rails Migrations easy to work with and a real time saver. We wanted something that would make our lives easier when working on Java projects in the same way Migrations improve Rails development. With that manifest in mind, Alon and I collaborated on a simple Java database migration framework.

During development, it’s a big deal because each engineer has two instances of the database, one for unit tests and another for running the application. We need an easy way to create a new, up-to-date database and update existing databases. Once a project has launched, it’s a big deal because we need a way to migrate a database teeming with important production data to the latest version without losing critical information.

High Level Requirements

• Initiate a migration from the command-line as a Maven plugin
• Programmatically migrate a database during application startup
• Convention over Configuration
• Initially support migrations written in SQL

At a high level, the migration process looks like this:

1. Query the database (table db_version) to find the current version.
2. Determine the latest database schema version available.
3. If the database is out of date, run each migration in order in its own transaction, updating the db_version for each migration.

We’d identified two usage patterns, the first is more akin to the Rails Migration model in that you explicitly migrate the database via the command line. The second is automatic migration when an application starts up, before Hibernate initializes or any other data access takes place. I’ll
discuss each in turn.

Migrating using Maven

This functionality is easy to enable in a mavenized project. First you add the Carbon Five public plugin repository:

pom.xml

...
<pluginRepositories>
    <pluginRepository>
        <id>c5-public-repository</id>
        <url>http://mvn.carbonfive.com/public</url>
    </pluginRepository>
</pluginRepositories>
...

And then you configure the migration plugin:

pom.xml

...
<plugin>
    <groupId>com.carbonfive</groupId>
    <artifactId>maven-migration-plugin</artifactId>
    <version>0.9-SNAPSHOT</version>
    <configuration>
        <defaultEnvironment>test</defaultEnvironment>
        <environments>
            <environment>
                <name>default</name>
                <driver>com.mysql.jdbc.Driver</driver>
                <username>dev</username>
                <password>dev</password>
            </environment>
            <environment>
                <name>test</name>
                <url>jdbc:mysql://localhost/myapp_test</url>
            </environment>
            <environment>
                <name>development</name>
                <url>jdbc:mysql://localhost/myapp_development</url>
            </environment>
        </environments>
    </configuration>
    <dependencies>
        <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.5</version>
        </dependency>
    </dependencies>
</plugin>
...

You’ll notice that we’ve got 2 environments configured. You can have as many as you need and you can specify which you want to migrate on the command line. If none are specified the default environment will be migrated. In this example we’re specifying the dependency on our JDBC driver so that the plugin has access to the code it needs to connect the database.

Lastly, you drop in your migration scripts into the src/main/resources/db/migrations directory, naming them using the pattern NNN_description.sql, where NNN is three digits indicating the script sequence. Some examples might be:

• 001_create_users_table.sql
• 002_add_default_users.sql
• 003_add_lastvisit_column.sql

The description is optional and isn’t used for anything, it’s just there so that other developers can get an idea of what a script does without having to open it.

From the command line, you can run the migration plugin like this:

$ mvn migration:migrate

Note that he database must exist for the migrations to take place as we do not create missing databases (yet).

I’ve created a simple, complete sample that shows off this functionality, it’s on the C5 public subversion repository here. Check it out and then read the readme.txt at the top of the project.

Migrating from your Application

The other usage scenario is to auto-migrate during application startup. At the core of the framework, there’s an interface called MigrationManager which has two implementations: DataSourceMigrationManager and DriverManagerMigrationManager. Migration happens right after a datasource (of the javax.sql variety) is created.

Migrating from your application is as easy as instantiating one of these early in the startup cycle and invoking the migrate() method, something like this:

MigrationManager migrationManager = new
    DriverManagerMigrationManager(“com.mysql.jdbc.Driver”,
    “jdbc:mysql://localhost/myapp_test”, “dev”, “dev”);
migrationManager.migrate();

Of course this needs to happen before anything else in the application uses the database; we want to database to be updated completely before it’s used.

Spring is part of our standard development stack on our Java projects, and it’s easy to enforce these dependencies in Spring configuration. First we define a data source for the application:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.h2.Driver"/>
    <property name="url" value="jdbc:h2:file:~/.h2/migration_sample2_test"/>
    <property name="username" value="dev"/>
    <property name="password" value="dev"/>
</bean>

And now we declare our MigrationManager instance. Note the ‘init-method’ attribute:

<bean id="migrationManager"
    class="com.carbonfive.db.migration.DataSourceMigrationManager"
    init-method="migrate">
    <constructor-arg ref="dataSource"/>
</bean>

And then you define something that’s going to use the defined DataSource. Note the ‘depends-on’ attribute:

<bean id="userService"
    class="com.carbonfive.migration.sample2.UserService"
    depends-on="migrationManager">
    <constructor-arg ref="dataSource"/>
</bean>

This is obviously a little contrived for the sake of example, but you get the point. In a typical application the thing that would depend on the datasource is a Hibernate SessionFactory.

You can visit the source code for this example on the C5 public subversion repository here.

Best Practices

Here are a few of the things we’ve learned along the way:

• Start using migrations early. Definitely start by time there’s more than one person on a project. I usually start off letting hibernate generate my schema while I’m experimenting with things, but as soon as I’m really working on features I’ll switch over to migrations.
• All database changes are captured as a new migration.
• Migration scripts cannot be changed once anyone has run them and make further changes in a new migrations.

Source Code Access

The Carbon Five db-support project which contains all of this migration goodness is available on the C5 public subversion repository at https://svn.carbonfive.com/public/carbonfive/db-support/trunk. It’s a maven project and should compile and pass its tests out of the box. I encourage you to look through the code and check out the tests.

Future

If you look through the code you’ll see some of what’s in store for this project. We’ve got initial support for writing migrations in Groovy and JRuby and we’re thinking about added Java support as well. We’re looking for feedback to drive the future direction of the project, so feel free to write us and let us know what you think.