Analytics

Sunday, August 28, 2011

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

Remove the dbCreate attribute from DataSource.groovy. Hibernate will no longer handle db changes.

Create a baseline changeset.

grails dbm-generate-gorm-changelog changelog.groovy

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

grails dbm-changelog-sync

Make Domain Change

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

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

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.

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.

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:

Technology

Pages

About Me

My name is Nirav Assar and I am a Java consultant in the DFW area. My specialties and skills include Groovy/Grails, JEE Development, Object Oriented Design, Agile Methodologies and industry best practices. I have worked in a variety of industries building systems some of which include defense, government, retail and financial.