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.

2 comments:

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

    ReplyDelete
  2. Thanks for the post, I am techno savvy. I believe you hit the nail right on the head.
    I am highly impressed with your blog. It is very nicely explained.
    Your article adds best knowledge to our Java Online Training from India. or learn thru Java Online Training from India Students.

    ReplyDelete