Database Migration in Grails

This blog covers the Grails Database Migration Plugin, the official plugin created by Spring Source and based on the popular Liquibase framework. Examples will demonstrate how database migrations can be controlled, managed, and executed. 

Database migrations are an important facet of web development. Preserving existing data while seamlessly adding new functionality and tables is critical when making incremental database changes to production applications. Without a tool to manage database migrations, teams rely on manual sql, error prone communication processes, and costly risk management to implement solutions.

What is a Database Migration

First let's define database migration. Simply put, database migrations are changes to a database that is already running in production, and the customer wants to retain the data for future releases. If this is not the case, then it is sufficient to not really consider it a database migration, and thus, you can rely on GORMs dbCreate configuration.

GORM dbCreate has Problems

If you haven't already realized it, hibernate's dbCreate feature has its problems. It does not support database migrations robustly. Some basic database changes work, such as adding a completely new table or adding new columns, but that is it. Briefly, here are some problems with dbCreate. I will leave in depth research up to the reader:

• Does not delete columns
• No conversion of datatypes
• No data migration
• Cannot change a constraint on a column
• Cannot add a column with not-null constraint on existing table
• Rows read and updated will not be able to update ever again

Why use a tool for data migrations?

Manually managing database migration is a flat out nightmare. Believe me, I have lived it and I realize it fully now that a tool is being used to manage migrations. Coordination between developers becomes huge overhead, as well as the communication between the developers and the DBA. Manually generating sql is a pain, mired in the fact that you are often creating the same type of sql over and over again. In addition, some of the sql will become messy and cryptic and thus hard to understand over time. Then you have the burden of ordering separate sql files for execution, or the alternate solution of creating some very large sql file that is impossible to understand.

The Case for a Controlled Database Migration plan

Adopting a tool adds structure and process to managing database changes. Teams can rely on a tool for communication of changes, rather then email. We also need to treat databases with version control. This is the same respect we give to code, and databases deserve it as well. Most importantly, database migrations enable agility and meeting customer needs, and we want to perform them well.

Examples

In this blog, we will run through three sample migrations. The sample domain objects used are Person and Address. We will start off with a Person object and eventually end up with a Person object that has many Addresses.

• Migration 1 - make column nullable
• Migration 2 – add address columns
• Migration 3 – Add Address Table  

Installation

Let's assume we have a grails app up and running. To install the plugin, execute

grails install-plugin database-migration 

Let's also assume we have a basic Person class with a few attributes. The app is already in production and has data in it. We now would like to migrate.

Migration 1 - make column nullable

The change we want to make is to make age nullable. But before we make the change, we need to do some initialization for liquibase.

Initialization Step

1. Remove the dbCreate attribute from DataSource.groovy. Hibernate will no longer handle db changes.
2. Create a baseline changeset.

1. grails dbm-generate-gorm-changelog changelog.groovy

3. Establish the baseline in persistence. This will store the version history of the database in a table. This is a one time setup.

1. grails dbm-changelog-sync

Make Domain Change

Now we can proceed with domain object changes. The Person class should be edited to become this:

package dbmigration.poc
class Person {
    String name
    Integer age
    static constraints = {
      name()
      age(nullable: true)      
    }
} 

The next step is to create a changeset file and append it to changelog.groovy. The '--add' parameter appends an include statement into the changelog.groovy

grails dbm-gorm-diff change-age-constraint-to-nullable.groovy --add

Configure Automigration on startup

Up to this point, we have only created DSL files with migration scripts. We have not actually done any migrations. We have the option to execute data migration on startup. This is very convenient as no DBA has to be involved when deploying war. The plugin will automatically execute the migration script. In order to do so, we must configure these properties in Config.groovy

grails.plugin.databasemigration.updateOnStart = true
grails.plugin.databasemigration.updateOnStartFileNames = ['changelog.groovy‘]

Run the war and you will see that the data table for the Person.age column become nullable.

Migration 2 - add address columns

The second example for migration demonstrates adding columns to a single table. Let's assume the Person needs address fields. We will simply add them to the current table. The naive assumption is that a Person will only have address. This obviously is not true in most circumstances, but it sets us up nicely for a more complicated third migration.

Make Domain Change

Edit the Person class to become this:

package dbmigration.poc
class Person {
    String name
    Integer age
    String streetName
    String city
    String zipCode
    static constraints = {
      name()
      age(nullable: true)
      streetName(nullable:true)
      city(nullable:true)
      zipCode(nullable:true)
    }
} 

Create a changeset file:

grails dbm-gorm-diff add-address-fields-to-person.groovy --add

Now you can deploy the war again.

Migration 3 - add Address table

To demonstrate a slightly more complicated scenario, let's assume we need to expand the domain to allow a Person to have multiple Addresses. Thus, Person to Address will have a one to many relationship.

The domain classes:

package dbmigration.poc
class Person {
    static hasMany = [addresses: Address]
    String name
    Integer age
    static constraints = {
      name()
      age(nullable: true)
    }
}

package dbmigration.poc
class Address {
    Person person 
    String streetName
    String city
    String zipCode
    static belongsTo = [person: Person]
    static constraints = {
      streetName(nullable:true)
      city(nullable:true)
      zipCode(nullable:true)
    }
}

Generate the changset:

grails dbm-gorm-diff create-address-table.groovy –add

Consider Migration of Data

Now we must consider the data that remains in the Person table. It must be transferred to the Address table. Liquibase cannot predict the intent of data migration. Liquibase has no way knowing the semantic meaning of data, or where you intend to migrate it. This type of information has to be provided by the developer. Thus, right after the creation of the address table in the create-address-table.groovy script, we have to add this:

changeSet(author: "a488338 (generated)", id: "migrate-person-data") {
        sql("""insert into address (id, version, person_id, street_name, city, zip_code)
              select hibernate_sequence.NEXTVAL, 0, id, street_name, city, zip_code from person""")
            }

Notice we used the sequence value for id and also added the version value to the sql.

Steps Illustrated

In summary we can illustrate the steps a developer must do in order to conduct migration with the plugin.

The plugin will become part of Grails 2.0, according to the roadmap.