16 October 2008

risible-db: database migrations in java

Jean-Philippe Hallot, and I, are pleased to offer the world risible-db, a new, free, really simple, 100% java open-source utility library for managing database schema migrations in a manner not wholly unlike rails migrations. The class of interest is (somewhat unimaginatively) called Migrations, with a convenience subclass called WebInfMigrations that does a lot of the convention-over-configuration thing for you. Use as follows:

in your Spring applicationContext.xml config
<bean id="migrations" class="risible.db.WebInfMigrations" init-method="start">
  <property name="dataSource" ref="myDataSource"/>
</bean>
You can of course auto-wire the datasource. There is no other property to set. When Spring calls the init-method, start(), risible-db will look in a directory called migrations directly under your application's WEB-INF directory, for *.sql files, order them lexicographically, and execute any that have not already been executed against the current datasource. Here's an example:
/WEB-INF/migrations
001-add-widgets-table.sql
002-add-users-table.sql
003-add-permissions.sql
003-add-widget-tags.sql
004-rename-widgets-to-woggles.sql
select * from migrations;
+---------------------------+
| name                      |
+---------------------------+
| 001-add-widgets-table.sql |
| 002-add-users-table.sql   |
| 003-add-widget-tags.sql   |
+---------------------------+
In this situation, risible-db migrations will execute 003-add-permissions.sql, and 004-rename-widgets-to-woggles.sql, because they are not already installed in the migrations table. Unlike Rails, risible-db considers the whole name of the migration, not just the number. So you can have multiple migrations with the same number; in fact risible doesn't care about the numbers at all, but it's a convenient way for you to keep your migrations in order. If you prefer A-add-widgets.sql, B-add-users.sql, etc, that's ok too. We found that the strict numbering approach called for a lot of number-management overhead when working in a distributed team - when the half of the team on this continent wanted to add a migration at the same time as the other half on another continent, we needed a way to ensure we wouldn't both use the same number, while simultaneously guaranteeing that the order of migrations respected schema dependencies. Each .sql file is a plain text file containing one or more sql commands, separated by semicolons. No comments, please. Notes:
  • Support for Oracle is coming in the next few days - right now it works only for MySQL
  • Respect and acknowledgements to Pascal Pratmarty who was the first to say, "well we could do that in java, too!"
  • future enhancements include down migrations like rails, conditional migrations (eg data migrations only to run in a test environment), other ways to express migrations (java code, for example), and SQL comments. Please comment if there's something on this list (or not) that you can't live without.
  • download here
  • Don't search for documentation. This is it.
  • It's LGPL licensed
Please let us know how it works for you, we'll be happy to hear your feedback.

1 comment:

  1. I just started writing something similar, so I don't need to anymore :-). Thanks, I think I will try it soon.

    ReplyDelete