Grails Database Migration Gotchas

Post by Josh Reed

Before we dive into gotchas, let me start by saying that the Grails Database Migration plugin is great. It’s usually the second plugin I install on any new project, right after the Spring Security Core plugin. You’ll likely never run into the problems I describe below, but I did and learned something in the process so I figured I’d pass it along in hopes it saves someone a bit of debugging time.

Setting the Stage: BeersDB

It’s the start of Iteration 2 on our fictional BeersDB project, a simple Grails app for tracking and rating different beers. In Iteration 1 we came up with a simple domain class to model the relevant information about a Beer and some real-world data for testing the app:

class Beer {
  enum Style {
    GERMAN_ALE, GERMAN_LAGER, BRITISH_ALE,
    BRITISH_LAGER, AMERICAN_ALE, AMERICAN_LAGER,
    OTHER
  }
  String name
  Style style
}

After some testing, the client prioritized a few stories in Lean-to to make the app more useful:

All of these stories require evolving the Beer class and the underlying database schema. Since we have existing data, we’ll be using the database migration plugin to capture and manage migrations in a structured manner. At the end, we’ll package everything up for a production release.

NOTE: all of the code for this blog post is available out at GitHub in the Refactr open-source repo. I’ll only be highlighting the major changes but I’ve tagged the full code for each step if you want to explore further. The code for this initial step is tagged as initial:

$> git checkout -f initial

With all of that out of the way, let’s start tackling stories!

Story 1: Add Origin and Type

For the first story, the client wants to be able to search for beers by country of origin, e.g. Germany, and by type, e.g. Ale or Lager. This information is encapsulated in the existing Style property but not in a form that is convenient for searching. The plan of attack is to add two new properties Origin and Type and write a migration to populate them for the existing beers in the database.

The new Beer class looks much as you would expect:

class Beer {
  enum Style {
    GERMAN_ALE, GERMAN_LAGER, BRITISH_ALE,
    BRITISH_LAGER, AMERICAN_ALE, AMERICAN_LAGER,
    OTHER
  }
  enum Type { ALE, LAGER }
  enum Origin { GERMANY, BRITAIN, USA, OTHER }
  String name
  Style style
  Type type
  Origin origin
}

We’ve left the existing Style property around for the time being; it’ll get cleaned up in a later story.

For the migration, we’ll let the plugin’s grails dbm-gorm-diff script take a first pass at writing the migration. It takes care of adding the new columns for us:

databaseChangeLog = {

  changeSet(author: "josh (generated)", id: "1-1") {
    addColumn(tableName: "beer") {
      column(name: "origin", type: "varchar(255)",
        defaultValue: Beer.Origin.OTHER) {
        constraints(nullable: "false")
      }
    }
  } 

  changeSet(author: "josh (generated)", id: "1-2") {
    addColumn(tableName: "beer") {
      column(name: "type", type: "varchar(255)",
        defaultValue: Beer.Type.ALE) {
        constraints(nullable: "false")
      }
    }
  }
}

This is a good start, it adds the new columns and sets a default value so the columns can be created as not nullable. The final step is to hand-write a migration to set actual values for origin and type based on the style column of existing data:

changeSet(author: "josh (generated)", id: "1-3") {
  grailsChange {
    change {
      Beer.list().each { beer ->
        switch(beer.style) {
        case Beer.Style.GERMAN_ALE:
          beer.origin = Beer.Origin.GERMANY
          beer.type = Beer.Type.ALE
          break
        case Beer.Style.GERMAN_LAGER:
          beer.origin = Beer.Origin.GERMANY
          beer.type = Beer.Type.LAGER
          break
        case Beer.Style.BRITISH_ALE:
          beer.origin = Beer.Origin.BRITAIN
          beer.type = Beer.Type.ALE
          break
        case Beer.Style.BRITISH_LAGER:
          beer.origin = Beer.Origin.BRITAIN
          beer.type = Beer.Type.LAGER
          break
        case Beer.Style.AMERICAN_ALE:
          beer.origin = Beer.Origin.USA
          beer.type = Beer.Type.ALE
          break
        case Beer.Style.AMERICAN_LAGER:
          beer.origin = Beer.Origin.USA
          beer.type = Beer.Type.LAGER
          break
        case Beer.Style.OTHER:
          beer.origin = Beer.Origin.OTHER
          beer.type = Beer.Type.ALE
          break
        }
        beer.save(failOnError: true, flush: true)
      }
    }
  }
}

We use the migration plugin’s ability to run Groovy and Grails code to loop through each Beer domain class and set the properties accordingly. Running the migration results in what we’d expect in the database—the origin and type columns match what is in the style column:

If you’re following along at home, you can see the full set of changes and migrations with:

$> git checkout -f story1

Story 2: Clean up Style

With the new origin and type properties, the client has indicated that the style property is no longer needed. Let’s remove it up from the domain class and generate a new migration with grails dbm-gorm-diff:

databaseChangeLog = {

  changeSet(author: "josh (generated)", id: "2-1") {
    dropColumn(columnName: "STYLE", tableName: "BEER")
  }
}

The migration doesn’t require any manual modification. Running it against our database results in the style column going away:

Same deal as before, the code for this step is available with:

$> git checkout -f story2

Story 3: Add Rating

Our final change will be to add a rating property so the beers can be rated on a 0-5 scale:

class Beer {
  enum Type { ALE, LAGER }
  enum Origin { GERMANY, BRITAIN, USA, OTHER }
  String name
  Type type
  Origin origin
  int rating

  static constraints = {
    rating min: 0, max: 5
  }
}

Again the grails dbm-gorm-diff script writes the migration for us:

databaseChangeLog = {

  changeSet(author: "josh (generated)", id: "3-1") {
    addColumn(tableName: "beer") {
      column(name: "rating", type: "integer",
        defaultValue: 0) {
        constraints(nullable: "false")
      }
    }
  }
}

The database looks as we would expect with a new rating column initialized to 0 for the existing beers:

Ship It!

With that, we’re ready to push out a new release. It’s always a good practice to do a dry run of your migrations against a copy of your production database to catch any issues before the actual release. We can simulate this by checking out our original database and then running the database migration update script against it:

$> git checkout -f story3
$> git checkout initial devDb.h2.db
$> grails dbm-update

ERROR liquibase - Change Set change1.groovy::1-3 failed.
...
Caused by JdbcSQLException:
  Column "THIS_.RATING" not found;

FFFFFFFUUUUUUUUUUUUUUUU-

We ran each of the migrations without a problem so why is it blowing up now? Turns out you have to be very careful if you try to load domain classes in your migrations. The error above is in our hand-written migration to populate the values of the origin and type properties. Hibernate is expecting there to be a rating column in the database even though that column wasn’t added until a later migration. The migration has no way to know what our domain class looked like at the time the migration was written, only the current state of our code. Since the current version of Beer has a rating property, Hibernate expects it to exist in the database as well.

The solution here is to rewrite our migration in Story 1 to use SQL directly instead of relying on GORM to load domain class instances. We also have to be careful not to reference properties such as the Style enum which may change or disappear over the lifecycle of the project:

changeSet(author: "josh (generated)", id: "1-3") {
  grailsChange {
    change {
      // set origins
      sql.executeUpdate
	"UPDATE BEER SET ORIGIN = 'GERMANY'
        WHERE STYLE LIKE 'GERMAN_%'"
      sql.executeUpdate
	"UPDATE BEER SET ORIGIN = 'BRITAIN'
        WHERE STYLE LIKE 'BRITISH_%'"
      sql.executeUpdate
	"UPDATE BEER SET ORIGIN = 'USA'
        WHERE STYLE LIKE 'AMERICAN_%'"

      // set types
      sql.executeUpdate
	"UPDATE BEER SET TYPE = 'ALE'
        WHERE STYLE LIKE '%_ALE'"
      sql.executeUpdate
	"UPDATE BEER SET TYPE = 'LAGER'
        WHERE STYLE LIKE '%_LAGER'"
    }
  }
}

Conclusion

The Database Migrations plugin is a powerful tool for managing database changes in a Grails project. It requires some structure to integrate into a project but the benefits of tracking database changes alongside your code far outweigh the extra effort. Sparing and careful use of GORM methods inside your changesets will minimize potential issues down the road.

This entry was posted in Software Development and tagged , . Bookmark the permalink.

Related Posts:

8 Responses to Grails Database Migration Gotchas

  1. Thanks for the great article. Always makes you feel better when you realise that others have experienced exactly the same issues (referring to your domain class access problems).

    The way we worked around this issue was by writing a groovy script using gorm and running this in a Grails console. We then dumped the resulting data to csv. Finally, we loaded this with the loadData closure from within a change set.

    A bit long-winded, but lots of fun :-)
    Cheers,
    Marco.

  2. crazy4groovy says:

    Thanks — actually this was a great intro to the migration plugin for me, as well :)

    P.S. For clarity, it would be nice to label the filenames where you write your code.

  3. Tomas Lin says:

    Liquibase has a sql( ) command you can use in database migrations. It’s less verbose. http://fbflex.wordpress.com/2011/06/08/running-custom-sql-in-grails-database-migration-plugin-changesets/

  4. Pingback: » Blog Archive

  5. Pingback: An Army of Solipsists » Blog Archive » This Week in Grails (2012-04)

  6. Pingback: GroovyMag - the magazine for Groovy and Grails developers about Groovy programming and Grails development

  7. Pingback: Grails DB-Migration Plugin – Part 3 : More Complex Migration « will greenway

  8. Pingback: Grails DB-Migration Plugin Tutorial « will greenway

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>