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.

10 thoughts on “Working with the Grails Database Migration Plugin

  1. Pingback: Working with the Grails Database Migration Plugin

  2. Brigette

    Thanks for this post Tomas – I’d been working through many of the same issues, particulary with the dbm-gorm-diff. There are also issues if you have a custom hibernate dialect using dbm-gorm-diff.
    I think as a first cut we’ll start using the same method you have outlined.

    Reply
  3. Brigette

    ps – thanks for point out the “showstopper” bug – I hadn’t found that one, so I’m branched until I get the 0.2 version.

    thanks again for this post.

    Reply
  4. Dean Moses

    Tomas — thanks for laying it out so clearly. It was a big help.

    You might want to clarify for us Grails newbies that you must remove the dbCreate statement entirely, not just change the ‘create’ to ‘update’. Otherwise Grails will attempt to add any changes it deems safe, and the migration plugin will fail because those tables/fields are already added.

    Reply
  5. Pingback: Grails Database Migration Plugin | Margots Kapacs Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s