Other Than Think

Sunday, December 16, 2012

MyBatis Migrations with Grails

I currently prefer using MyBatis Migrations for substantial Grails apps. In the world of Grails, this is an unconventional choice, so it wasn't made lightly. But after having used it for a while on a moderately large app, it still feels like the right choice for us.

Background

As you probably know, the official Grails database migration plugin is based off of liquibase. I've tried using it a couple times in the past on other projects, including non-Grails apps, but it wasn't ever a good fit from a technical or stylistic point of view. When it was made the official migration style of Grails, I gave it another shot, but too many things still got in the way compared with MyBatis Migrations.

What pushed me away from Liquibase and the database-migrations plugin

Like all software abstractions, there are leaks and bugs. e.g. Do a search for your favorite database plus the word "liquibase issue". The point isn't to criticize the software or its authors - they're master craftsmen. The point is that you can eliminate those issues if you eliminate the abstraction itself.

In my project's culture, the DBAs want to review and apply the database changes in the form of SQL - not in the form of XML. This is a fair requirement in my opinion. You can use the dbm-update-sql command to generate SQL scripts, but this is problematic for two reasons. First, you can only run the command for a target environment that hasn't been migrated yet. Usually this is the opposite of what I want to do...I want to create a script for migrations that I've already created and tested. Second, DBAs may want to tweak the resulting SQL, but that means the production environment is different than the test and development environments.

I like the potential of being able to use groovy for migrations, but there are gotchas in this approach that I also experienced. Again, I can avoid this issue entirely if I treat the database as a separately managed entity, rather than try to treat it like an extension of a programming language.

The liquibase and groovy DSL is not portable from a development sense. e.g. You can create manual SQL in the migration like this sql("update foo set bar = true")but you can't easily copy/paste that to and from your favorite SQL development IDE during your development experimentations.

Along with avoiding the deficiencies mentioned above, here are some more reasons why it works well for us.:

It's simpler. There are 9 available commands in MyBatis Migrations, compared with 31 in the database-migrations plugin. You're writing straight-up SQL using a simple framework.

Basically, it gives us the full control of our database design and migrations that we need without getting in the way.

How We Use MyBatis Migrations

A note that's not specific to MyBatis Migrations or Liquibase...I only apply "real" migrations to a project after it's settled down. i.e. I work with the H2 in-memory database with create-drop for as long as I can when I start a new project. I don't care what the database looks like during the the early stages of development, and auto-creating it gives monster productivity gains. When data needs to start sticking around (e.g. in the user acceptance test environment), then I'll turn off automated migrations and use a more manual approach.

The db folder is in source control with the rest of the app. In Bootstrap.groovy, we go down to the create_changelog script and back up to the top for our automated test environments (test, ci, and functional). e.g.:

We deploy the migrations by packaging the db folder with each release. We then have the choice of either automating the migrations to run when the app is deployed, or running the migrations ahead of time (when it's a backwards-compatible db change).

tl;dr - Using MyBatis Migrations works great for a grails app when you're comfortable being in full control of all the database changes.