Working with the Grails Database Migration Plugin

I’ve recently spent some time exploring the new database migration plugin in Grails. Here are a few notes that I hope will help you out if you’re looking into using this plugin.

Using the plugin – first steps.

Install the plugin

 grails install-plugin database-migration 

Note: there is a showstopper bug in 0.1. that won’t allow you to deploy your war file. We’ve been using the 0.2. plugin from git as an in-place plugin.

If I have an existing database or dbCreate = ‘create’ set in Datasources.groovy, I call

 grails dbm-create-changelog changelog.groovy 

At this point, I would remove my dbCreate = ‘create’ call from my development database to make sure that subsequent calls don’t overwrite the generated database.

I then call:

 grails dbm-changelog-sync 

to tell the plugin that my database is up-to-date. This marks all changesets as applied.

Q; Why not use GORM diff and changeset?

One of the best features of the plugin to me is the new dbm-gorm-diff mechanism. This allows you to create a diff of your database against your GORM domain model. We found that in the 0.1. version, however, db-gorm-diff generated a lot of junk indices, inconsistent precision indices, and would often generate an incorrect or unusable migration file. This will definitively improve in future versions but we found it just too unstable to use now.

A configuration that works for us.

In a nutshell, our method involves keeping a database created via dbm-update and constantly doing a diff against the database generated via dbCreate.

To get the right changesets, we use the database diff mechanism provided by the plugin. This is very similar to the liquibase mechanism described here by Jakob Kultzer, but made easier due to the Database Migration plugin’s support for different environments.

To start, our Datasources.groovy file looks like this:

development{
 dataSource {
 driverClassName = "com.mysql.jdbc.Driver"
 url = "jdbc:mysql://localhost:3306/db1?autoReconnect=true"
 username = "root"
 password = ""
 }
}

dbdiff {
 dataSource {
 driverClassName = "com.mysql.jdbc.Driver"
 url = "jdbc:mysql://localhost:3306/dbdiff?autoReconnect=true"
 username = "root"
 password = ""
 dbCreate = "create-drop"
}
}

How to use:

Setup.

First, we create two empty databases in MySQL, dbdiff and db1. db1 will be our development database and dbdiff will be the database we get via dbCreate = ‘create’.

To generate the initial database, we simply call:

grails dbm-update

this will update the database to the latest version.

Managing Code Changes.

Whenever we make a change to a domain class, we call the following commands.

 grails dbm-update 

This syncs our dev database to the latest version.

 grails -Dgrails.env=dbdiff dbm-diff development added-last-updated-constraints-to-user.groovy --add 

This generates a file called added-last-updated-constraints-to-user.groovy and adds it to the changelog.groovy file we generated at the beginning. For each change, you want to use a new descriptive name to the migration.

Since the dbdiff environment is set to create-drop, every time this script is ran a new database will be created with all the correct keys, indices and sqlType mappings via GORM.

At this point, I usually open the migration file for inspection and remove unneeded or junk changesets.

 grails dbm-update 

I usually run this script again to update my development database and test that the migration works.

Check in your migration file to subversion, git or your favorite repository.

Re-generating checksums

What do you do when you run into an invalid checksum exception using the Database Migration plugin?

If you end up editing some of your changesets with the database migration plugin and try to apply them to your database, you will be greeted by an exception telling you that the changesets don’t match.

To overcome this problem, the plugin provides a convenience method to reset all your checksums. Simply invoke

 grails dbm-clear-checksums 

This took us a while to figure out so writing it down here to document it.

Migrating old databases.

How do you deal with a database that hasn’t been changelogged and you would wish to bring up to date?

We use the following steps.

1. Make a backup or ten of your old database.
2. Add a new environment for your old database. Let’s say dbToUpdate.
3. Make sure your current development database is up-to-date with your changelogs and call

   grails dev dbm-diff dbToUpdate newChangelog.groovy

   This will create a file called newChangelog.groovy which contains the differences between your new database and the one that you haven’t migrated yet.

4. Tell the plugin to use the newly generated changeset file by setting this in config.groovy

   grails.plugin.databasemigration.changelogFileName = newChangelog.groovy

5. Update the old database

   grails -Dgrails.env=dbToUpdate dbm-update

   Your previous database is now up to date with your current one.

6. Remove the config parameter
7. Drop your DATABASECHANGELOG and DATABASECHANGELOGLOCK tables in the old DB.
8. Mark all the existing changesets as used via

   grails -Dgrails.env=dbToUpdate dbm-changelog-sync

Your old database is now up to date.

I hope this is useful. The database migration plugin is definitively a groovier, more elegant plugin than the previous liquibase or autobase offerings, and I can’t wait to have nice easy migrations built into Grails.