Java Database Migrations at Carbon Five Community

News: v0.9.3 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

Share:

14 Responses to “Java Database Migrations”

Feed for this Entry Trackback Address

1 Lance
September 4, 2008 at 9:34 am

Nice work! Thanks for setting this up as a google code project. As an MS SQL Server user this will really help as I can now submit issues and fixes when and if I run into issues with MS SQL. Thanks!
2 christian
September 4, 2008 at 2:37 pm

Lance,

I did some testing against MS SQL Server and found one issue which has been resolved in 0.9.2-SNAPSHOT. So, I’ve successfully run all of the functionality against SQL Server 2000. If you’re up for it, switch to that SNAPSHOT and give it a try. Let me know how it goes. -Christian
3 Jim Alateras
October 15, 2008 at 2:20 am

I just tested against hsql 1.8 but i am having a problem created the schema version table

Caused by: java.sql.SQLException: Unexpected token: UNIQUE in statement [create table schema_version (version varchar(32) not null u
nique]

I believe that HSQL v1.9 will rectify this problem but there is no release date as yet. Am i able to use my own definition for the schema version table

cheers
4 christian
October 15, 2008 at 8:19 am

Jim, it depends. If you’re using the migration manager directly in your code, then you can plug-in in a new VersionStrategy, with a custom implementation of enabledVersioning(). If you’re using the maven plugin, then there isn’t really an easy hook to do what you need to do.

What is the correct DDL SQL for creating the version table for HSQL?

Another alternative is to switch to H2Database. It’s written by the same team as Hypersonic and is actively developed, unlike HSQL.

I’m midway through a refactor which will allow for better handling of dbms-specific features. When it’s done, it will be easier to handle these cases more gracefully than the framework allows for now.
5 Jim Alateras
October 15, 2008 at 3:06 pm

Thanks Christian

HSQL just doesn’t support the ‘unique’ constraint in the create table construct. I am using MM directly in my code.

I will also look at H2Database

cheers
6 Jim Alateras
October 18, 2008 at 1:04 pm

Chrstian.,

Do you have an rough idea when you will be doen with the refactor
7 christian
October 19, 2008 at 9:44 am

Jim: this time, I do. I’m wrapping up a few things today and hope to release another 0.9.3-SNAPSHOT this afternoon. If no issues surface with the SNAPSHOT, I’ll release 0.9.3 Monday or Tuesday. Keep an eye on the release notes (http://code.google.com/p/c5-db-migration/wiki/ReleaseNotes) to see what’s changed and when.
8 Jim Alateras
October 19, 2008 at 9:10 pm

Christian,

Very timely indeed. Thank you very much.
9 Jim Alateras
October 24, 2008 at 3:55 pm

Christian,

Just picked up 0.9.3 and i can get it to run against the hsql databse. Much appreciate for the quick turn around. Now i have a question around managing the migration across a number of different databases. For instance the sql that hibenrate spits out for hsql is different to that for mysql.

Is there a mechanism to handle this at the moment?

cheers
10 christian
October 28, 2008 at 9:20 am

Jim: I’ve been meaning to add the recommended approach to the wiki, but haven’t yet had time. Here’s a quick description…

Using maven profiles, you can create a profile for each database supported. In each profile, you can set a property called “dbtype”. Then, in you db-migration configuration, you can override the migrations path like this: file:src/main/resources/db/${dbtype}/migrations/*

I’ll add a more complete example to the documentation one of these days. Good luck!
11 Jim Alateras
November 2, 2008 at 5:25 pm

Cool. I’ll give a burl
12 Jim Alateras
November 4, 2008 at 11:42 pm

Christian,

Works like a charm. I actually ended up extended ResourceMigrationResolver and used the hibernate dialect, which i already had to set the migrationsLocations
13 Maxim Porges
March 27, 2009 at 8:24 am

Hi,

This is a great looking plugin - we’ve just hooked it in to our CI process and are expecting good things.

The plugin looks fully featured with the exception of rolling back to prior versions a la the way it’s done in Rails. Was this a deliberate step to enforce process, or just a feature that has not been implemented yet?

Thanks,

- max
14 christian
March 27, 2009 at 9:07 am

@Maxim: leaving rollback out was a deliberate decision. Not every migration has an easy-to-write corresponding rollback (e.g. destructive changes). And in my personal experience, I’ve never really needed to rollback a migration in development. We develop our migration against a unit test database that’s easy to reset. Once a migration is satisfactory, it’s run against a local development database, then checked into VCS for the rest of the team. If we need to undo something in a previous migration, we simply do it in a new migration. We’ve had scenarios where migration N adds a column and the migration N + 2 (or something) removes it. That works fine.

Have you found a strong case for rolling back in your experience?