grails database migration plugin: upgrading an existing database

Today was in fact the first time I ever got into a situation where the setting dbCreate = 'update' couldn't manage to update my data base scheme after modifying some of my domain classes. This was unfortunate of course, but since I've been reading that this is bad practice anyway I decided to have a closer look at the new database migration plugin that came out at roughly the same time as grails 2.0 did. This plugin utilizes Liquibase, which has been around for a while from what I could see and seemingly does a very good job. It turns out that the database migration plugin gives you a really smooth integration of Liquibase and grails.

Coming to my actual problem:
After adding the spring-security plugin I wanted to have some light history with two of my domain classes, so I introduced the fields

• createdBy,
• lastUpdatedBy,
• created,
• lastUpdated.

The latter two are automagically populated by GORM and the first two I could populate in the controller classes using the springSecurityService. This obviously leaves existing database rows without this information and since I assigned a "not nullable" constraint, hibernate sure enough complaint when trying to change the scheme.

So I did some reading on the excellent tutorials I found to get up to speed:
http://blog.springsource.org/2011/08/17/countdown-to-grails-2-0-database-migrations/
http://fbflex.wordpress.com/2011/01/19/working-with-the-grails-database-migration-plugin/

After understanding the key concepts I went ahead and followed the instructions at the end of the second blog post (updating an existing database). I created a new environment "migrate" which I configured to use a copy of my database. My domain classes where already changed, so I just needed to compare the current database scheme with my domain classes using

grails -Dgrails.env=migrate dbm-gorm-diff --add changelog-0.2.groovy

view raw create changelog hosted with ❤ by GitHub

This yields a changelog.groovy file that translates the liquibase xml configuration into the groovy world. Since the conversion is straight-forward you can make use of the liquibase docs to modify this changelog. The original changelog corresponds exactly to what GORM would have done anyway with dbCreate="update".

You can try and run it using the dbm-update command:

grails -Dgrails.env=migrate dbm-update

view raw dbm-update.sh hosted with ❤ by GitHub

You might be happy at first seeing that everything seems to work fine, but soon you'll be frustrated because nothing actually happened. At this point you might have already asked yourself (like me) how the plugin knows which changelog you want to apply. And in fact you have to specify this in Config.groovy:

	//select migration file
	grails.plugin.databasemigration.changelogFileName = 'changelog-0.3.groovy'

view raw specify-changelog-Config.groov hosted with ❤ by GitHub

Of course running the update failed for me. I didn't do anything about the not null constraint. I solved this problem by removing the constraint from the original add column configSet and adding a new configSet add the end where I specified a defaultNullValue ( http://www.liquibase.org/manual/add_not-null_constraint ), which replaces all my trouble-causing NULL entries with a sensible value:

	changeSet(author: "mlist (generated)", id: "1340373645634-47") {
	addNotNullConstraint(columnDataType: "bigint", columnName: "createdBy", schemaName: "dbo", tableName: "slide_layout", defaultNullValue: 1)
	}

view raw addNotNullConstraint.groovy hosted with ❤ by GitHub

This worked really fine for the createdBy and lastUpdatedBy column where I only had to set an integer value. However, it took me quite some time to figure out how to set a date correctly. I read a lot about timestamp and getdate() functions and in the end the simplest solution was to just figure out the date syntax which looks like this:

	changeSet(author: "mlist (generated)", id: "1340373645634-47") {
	addNotNullConstraint(columnDataType: "datetime2", columnName: "date_created", schemaName: "dbo", tableName: "slide_layout",
	defaultNullValue: "2012-06-22 00:00:00.0000000")
	}

view raw changeSet_defaultNullValue_date.groovy hosted with ❤ by GitHub

 A few important tips for working with this plugin:

• Use grails -Dgrails.env=yourEnv to run a custom environment besides dev, test or prod.
• Use grails dbm-update-sql to see what SQL code is created.
• Be aware: The dbm-update command often fails more or less silently. Therefore: 
• Look into your database! There is a new table called DATABASECHANGELOG. 

select * from databasechangelog order by orderexecuted

view raw dbchangelog.sql hosted with ❤ by GitHub
If you compare the entries in this table you will quickly see which configSet caused the operation to fail. This is most likely due to a syntax error because problems with the database usually cause an exception.

And if you finally get to see the following line:


| Finished dbm-update

You finally did it and you can leave for the week-end.