Managing database schema changes between multiple environments.
In most of the agile projects database schema evolves along with the code. Its follows the same incremental change code goes through, we add a table, rename column and so on (Evolutionary database design). Database by itself doesn’t have any versioning information. One of the great thing about versioning code is if the current version doesn’t work the way we want we can always rollback to the prior version. But what about the database schema changes? Having a tool automate and manage database migration helps teams to address that problem.
If you have worked with grails then you would know that we don’t necessary deal with database when working with grails domain objects. Instead of creating new table we create new domain object, we don't add column instead we add a new property to an existing domain object. But we still need to deal with database migration issues when make incremental releases. We cannot create/recreate database every time we make release. We have to have a mechanism to version and keep track of all the scheme changes. As of writing grails as 3 viable possible tools to manage database migration and in this blog entry I will discuss about my little playing with them
Well I didn’t find any descend documentation about how to use dbmigrate with grails. When I installed the plugin it don’t work and gave me a file not found exception. After going into the plugin source code I figured that it is looking for the resource inside my grails project not in the location where plugin is actually install {dbmigratePluginDir}. And on top of that asking to create version table (db_version) manually is not very developer friendly. Let me know if anyone has used dbmigrate and had better experience than me.
This is a very matured database migration tool but unfortunately has some gotcha’s when it comes to use it with grails. If you haven’t thought about database migration from beginning of the project it’s becomes harder to start with one. When you will run the liquibase plugin for the first time it will show the entire database as change because none of the changes are logged with liquibase. So to register all the changes with liquibase we had to recreate the database schema. Now if that is not an option, taking backup and reloading the data back again is possibly the only way. Well now is the fun part. My spike involved adding a property to an existing domain object and then removing it again, simple isn’t? We test-drive almost all our changes. So in any typical scenario our test database will change before any other environment. So one of the expectations we had liquibase is to detect that change and propagate that to dev and QA environment.
Liquibase plugin does ship with a db-diff tool but unfortunately it compares between dev and test environment. I think I need to explain that little bit more. So according to Liquibase db-diff tool dev is more current than test schema. What that means is if we add a property to a domain object(that means a new column is added in test schema) liquibase will think that the column needs to be dropped from test schema and will generate a drop column change log, weird right? So I had to change the plugin source.
In this case I changed DbDiff.groovy to swap dev and test database between source and target and it worked. But when we tried dropping a property for some reason it got the schema name wrong in it. I had to modify the generated change log. But overall it's a working solution if you are ready to deal with xml.
Autobase is the new and shinny database migration tool for grails. It is actually build over liquibase and has a nice little DSL in groovy. If you have an experience with rails migration you will find this very closely related. The biggest problem with autobase is to make to run successfully over and over again. When I tried to run it first time I got all sort of class not found exception. It turns out that autobase plugin relies on some groovy source that needs to be compiled before using it. And it doesn’t happen automatically. So I basically had to clean the scriptCache (
//Dropping column
changeSet(id:'DropColumnAddedForTest', author:'Nilanjan') {
dropColumn(tableName:'company', columnName:'added_for_test')
}
Looking at all the things I went through so far I like Autobase better than any other database migration tool. Who doesn’t like DSL these days :)
Posts
