Java Database Migrations

News: v0.9.9-m2 has been released!

A while back, I wrote to introduce the first incarnation of the Carbon Five Database Migration tools, a simple though powerful framework for applying discrete changes to a database and tracking which changes have been applied to a specific database. It was inspired by Rails’ Migration support.

We’ve made a number of changes in the v0.9.1 release. We adopted some of the improvements found in Rails 2.1 as well as feedback from our users. Here’s an overview of what’s changed:

• New create, drop, and reset goals for maven plugin. Now you can create a new database, drop an existing one, or reset an existing database by dropping it, creating a new one and then migrating it. This is tested with MySQL and PostgreSQL.
• Each applied migration is tracked in the database schema_version table (instead of just the last one). Also, when it was run and how long it took to run are now saved for each.
• Validate goal now lists which migrations are pending in addition to whether the database is up to date.
• Maven artifact ids have changed (migration -> db-migration, maven-migration-plugin -> maven-db-migration-plugin) and there’s been some restructuring in the core framework.
• Maven plugin is configured a bit differently now; environments have been removed completely since maven supports a better solution out of the box: profiles.
• Maven plugin now looks for migrations in src/main/db/migrations by default; alternate locations can be specified via the <migrationsPath/> element.
• We now recommend using timestamps for migration versions instead of the NNN format, though any numerical character sequence will work.
• Reworked the algorithm for determining which migrations to run to allow for a little more flexibility. Pending migrations aren’t determined by a single version number, they’re determined by comparing what is available to what has already been run. In conjunction with timestamp versions, developers won’t be stepping on each other’s migrations.
• New and updated google code project and documentation.

As you can imagine, some of these changes aren’t backwards compatible. While we’re in pre-release (< v1.0) mode, we feel like it's more important to make the fundamental changes to build a solid foundation than to retain complete backwards compatibility. The release notes give some guidelines for upgrading.

Here’s a quick getting started guide for the maven-db-migration-plugin:

Step 1: Configure maven in your project’s pom.xml

...
<build>
   ...
  <plugin>
    <groupId>com.carbonfive</groupId>
    <artifactId>maven-db-migration-plugin</artifactId>
    <version>RELEASE</version>
    <configuration>
      <url>jdbc:mysql://localhost/myapp_test</url>
      <username>dev</username>
      <password>dev</password>
    </configuration>
    <dependencies>
      <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
      </dependency>
    </dependencies>
  </plugin>
</build>
...
<pluginRepositories>
    <pluginRepository>
        <id>c5-public-repository</id>
        <url>http://mvn.carbonfive.com/public</url>
    </pluginRepository>
</pluginRepositories>
...

Lines 29-31 configure the database connection (see the reference for more options).
Lines 35-39 specify the required dependency on our database driver.
Lines 43-48 adds the Carbon Five maven plugin repository.

Step 2: Create a migration script

In src/main/db/migrations, create a script using the format YYYYMMDDHHMMSS.sql (e.g. 20080830174515.sql). Example:

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255),
  enabled BOOLEAN DEFAULT 'TRUE'
);

Step 3: Create the database

$ mvn db-migration:create

The supplied credentials must have the appropriate privileges, of course. If you’re not using MySQL or PostgreSQL, then do this step manually.

Step 4: Check the status of the database

$ mvn db-migration:validate

Your migration will be listed as pending and the database as not up-to-date.

Step 5: Migrate to the latest version

$ mvn db-migration:migrate

The pending migration will be applied to the database and logged in the schema_version table.

Want to learn more?

Check out the google code project page, the release notes and the sample applications.

In the near future, I’m going to look at supporting MS SQL Server and improving the SQL Script Runner. Thanks for all of the feedback and please keep it coming!

Christian