Blog

At 2Paths we’ve got some pretty good processes in place: we practice agile software development and scrum, have all our projects set up in Continuous Integration. We try to do test-driven development where at all possible. One area that has slipped through the cracks though is database change management. What company hasn’t run into the problem of ensuring all database environments in a project (dev, staging, production, test) are in sync and in source control? Without this, it becomes an onerous if not impossible task to rollback a set of databases to a known state, or to recreate one from scratch to a known state.

Enter LiquiBase, an open source database-agnostic tool for tracking, managing and applying database changes. We recently worked on a small Grails project and decided to give this tool a try and I was duly impressed.

LiquiBase functionality is built around a main changelog.xml file containing changesets representing incremental database changes to be applied to a database. LiquiBase manages which changesets have been run through a DATABASECHANGELOG table it creates in each database.

We used the grails plugin which gave us most of the full LiquiBase functionality, albeit a little less than mature. There were a couple of gotchas and bugs with the plugin, but nothing we couldn’t work around.

LiquiBase buys us the ability to store database change in source control, easily sync databases in multi-environments in an automated and controlled fashion, tag database states upon iteration-end releases, auto-generate rollback sql to tagged states, diff databases, and much more.

LiquiBase-ifying Your Application

With your database in a known state, you can use the grails plugin to create your changelog.xml. First you need to install the grails LiquiBase plugin:
grails install-plugin liquibase
Once installed simply run this from the root of your grails app:
grails generate-changelog grails-app/changelog.xml

This will generate the changelog.xml from your development database (specified in DataSource.groovy) and write it to the path specified in the command. grails-app/changelog.xml is the default path where the grails plugin will be expecting the changelog to be. If you need to read from a different database environment like staging for example, just add the -Dgrails.env=staging option to the command.

To propagate this changelog to other databases (say, test), run
grails -Dgrails.env=test migrate. This runs all the sql necessary to upate the database to match the changelog. Any new changes from here on will be appended as new changesets, and can be migrated with the same command as above. You can also use the migrate-sql command instead if want to generate the sql and run it yourself. Most LiquiBase commands come in tuples: one to generate the sql for you and one to just run the sql directly against your database.

If you’re integrating LiquiBase mid-project and already have all your databases set up, you’ll need to run sql against them to update the LiquiBase DATABASECHANGELOG table to show all the changes as run. First, make sure that the databases are all in sync. To do this, you can use the handy LiquiBase diff tool. Unfortunately, the grails plugin for this diff tool is not very robust and will only diff your development database against your test database – the plugin has those two environments hard-coded. You can either mess with your DataSource.groovy db environments to do the diff setting the two dbs in question to dev and test temporarily, or install LiquiBase itself and run the diff passing the db parameters to the diff command. Using the grails plugin you would run:
grails db-diff which will spit to the screen any differences as changesets to be applied. Strangely, there is no documentation for this command in the LiquiBase Grails plugin page.

Once your database are in sync, run
grails changelog-sync-sql with the appropriate -Dgrails.env switch to generate the sql to update the DATABASECHANGELOG table, then run the sql in your database.

LiquiBase and Continuous Integration

We use Hudson at 2Paths for our CI, and have added the simple grails -Dgrails.env=test migrate command as part of our build process to migrate the test database upon every checkin. This ensures that the test db is always the most up-to-date.

Rolling LiquiBase into our Dev Process

We’ve adopted on a trial basis the following process for database change management as part of our agile software development, taking into consideration Grails development (which uses hibernate) which can auto-generates schemas based on domain objects:

1. Generate changelog from initial schema and commit to svn
2. Rollout schema to other databases by migrating from changelog
1. if the schema already exists and it needs “LiquiBase-ifying”, generate changelog-sync-sql and run it
3. Add every schema change via new changeset from hereon in by generating changelogs via grails db-diff
1. add / change domain objects in project
2. set hiberante ddl mode to update
3. start app and grails will automatically update the db
4. generate new changelogs using db-diff (against the test db)
5. append changelogs to changelog.xml
6. run grails changelog-sync-sql to generate the sql that will mark all these new changes as ran on your db, then run the sql
7. checkin changes. this will be applied to test
4. provide explicit rollback sql for any custom sql
5. tag each iteration end with a “tagDatabase” changelog in the changelog.xml

Gotchas

We ran into some gotchas with LiquiBase. One of the first things we started doing before fully understanding how LiquiBase worked was to update existing changesets when changing the schema. LiquiBase generates checksums for each changeset to ensure they don’t change, and altering existing changelogs will cause future migrations to fail. Even though LiquiBase gives you some tools to get around this, it’s generally a better practice to just add a new changeset for every database change. There is a good blog on the LiquiBase site explaining how to deal with changing changesets.

Another gotcha is that the rollback-sql command won’t magically generate rollback scripts for hand-coded sql (obviously!) You must to generate your own rollback sql for these custom sql tags, otherwise not only will you not get rollback sql for those particular changesets, but the rollback functionality won’t spit out sql for any other changeset either until you do.

Tagging was a little fussy as well. The command line grails tag can only tag a given state once – future tags will overwrite the earlier ones unless you’re at a different changeset. I found it better to add a tag changeset explicitly in the changelog.xml. The Grails plugin for tagging also seems to be problematic with spaces in the tag name. We decided to just use underscores as a convention.

We also ran across some broken functionality:

• The grails generate-changelog command generates some extraneous information that breaks sql for data type numeric(19,0) if that type has auto-increment on. It generates this in the changelog:
  <column autoIncrement=”true” name=”id” type=”numeric()(19,0)”> (notice the extra empty brackets). We needed to manually remove these to get it to work.
• The grails rollback-to-date-sql command writes to a file with the datestamp instead of to console like the other rollbacks do, even though the docs say it writes to STDOUT (rollback-count-sql also has issues, but rollback-sql for tags works just fine). The code for these plugin commands doesn’t seem to do the right thing with the args. Time permitting, we may provide a patch for this.
• db-doc generation doesn’t work with large amount of columns in a table because it uses the table description including column names for the html file name. Too many db columns make the file names too long.

Moving Forward

Although there’s a bit of grumbling amongst the developers here that using LiquiBase with the above process is a little onerous, it buys us a lot more certainty for knowing what state our databases are in, making sure changes are in source control, and knowing what updates have or haven’t been run. It’s a whole lot easier than hand-coding a bunch of sql, and easily accommodates the possibility of migrating to other DBMS’s in the future without having to re-write DBMS-specific sql. There’s even talk of trying to go a step or two further to automate even more of the process so a developer doesn’t have to think about generating changesets upon changing of a grails domain model. We’ll see how that goes.

Tags: database, Grails, LiquiBase

This entry was posted on Tuesday, December 23rd, 2008 at 14:46:27 and is filed under Blog, Development, Technology, Under the hood, Utilities. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.