Database Migration in Grails

So what is database migration?

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

DbMigrate

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.

Liquibase

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

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 (/.grails/scriptCache) and rebuild the entire grails project before running it (I had to do that every time before running grails create-migration). Autobase doesn’t ship with db-diff tool like liquibase so we have to create our own migration scripts. Even though it might sound bad but having little cool groovy dsl is nice and easy.

//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 :)

Is anyone out there using GWT?

I would be surprised if the answer is yes except you are working for Google. For our new project we had a dream. A dream to create a complete testable web based business application for our customer. Now don’t get me wrong, it is possible to test drive standard web apps but it's hard. We have to have some unit testing tool for JavaScript and then something like Selenium or Watir for the actual integration test. It's a mess.

Now when you take a first look at GWT, it’s like a promise land. You get an integrated development environment to build web application. An environment where your Java code compiles to JavaScript, you get all IDE goodness and above all write unit tests for your GUI code. And it’s been out for few years now. What could go wrong if we try it out for our new project? At least that’s what we thought…

Installation

Most of our developers use Mac book pro as their development environment. Now GWT has some issues running with Java 6 because of 64-bit mode, we had to use Java 5. Now after Snow Leopard update we had to re-install Java 5 just for GWT. This is entirely Apple’s fault but we had this initial hiccup to make up and running for our developers.

Creating Project

This one was really annoying for us. Now in GWT, project could be created in 2 ways, using the command line webappCreator or using eclipse plug-in. We kind of needed both. We needed eclipse because we like to use as our development environment and command line scripts for hooking into our CI builds. If you create project-using eclipse, you cannot use it from command line. Now if you create your project using webappCreator (command line) then it will work from command line but eclipse will not recognize it as GWT project. Come on, this is something really basic. How would a tool like GWT miss something simple like this? But webappCreator does create a .classpath and .project file so that you could import the project to eclipse but that is not good enough. We could have still lived with that but the project that got created had an absolute path dependency specific to the environment. Ok let’s look at this in perspective of a team environment. If you create project and check all the files in your source control, no one else will be able to checkout and run the project as is. They have to go and edit both project and launch configuration files. A big failure for us.

Testing

Being part of an agile team we like to test drive all our design and production code. In fact that was one of the reasons why we choose GWT. To write a unit test we had to use GWTTestCase, which is a wrapper over JUnit. But unfortunately it wraps JUnit 3 not the 4, which is bummer. Every GWT test we created we had to create a launch file at the same time so that we could run it form eclipse and CI box. And did I mention that this launch files will again have an absolute path to the environment it was created. We did tried to use relative path but never worked for us. So again if you are in a team environment how do you scale this? And did I mention that GWT tests ran really slow in hosted mode. A huge failure for us.

Using 3rd path libraries

Could anyone expect a decent size Java project without any 3^rd party library? To our surprise GWT doesn’t allow any 3^rd party jar, it has its own special requirements. First we had to add 3^rd party jars as module and it needs to have source and class files in same location. Now if you want to use apache commons for example you cannot. How many open source projects and tools do you know that follows this special requirement, only very handful of them? I do understand why this requirement is there but at the same time not having access to helpful libraries could slow your team velocity and throughput. Another failure for us.

So what happened next?

Within the second week of the project we scraped GWT and decided to go back to html/JavaScript. Even though we are facing the same testability challenges but our productivity went up. I am sure there are possible workarounds for all the problems I am mentioned up here. And I would like to hear them but if barrier of entry into GWT world is so high, I don’t see anyone using GWT in decent size project.

Tracking change in xp project?

The project I am in right now is a fixed bid xp project. Now I am sure this might raise few eyebrows but that's how it is in most of the enterprise environment these days. They want to be agile/xp but is not ready to change the way they write contracts with their vendors. There are few better alternatives out there but I am not sure whether that's a starting point for an organization to be agile?

So given the suituation our team is in right now, we are all puzzled with one question, how to track change?

This is really hard because I see my customer/product owner changing his mind every hour.

We don't want to stop that because it is adding more value to the product by making it better. The more customer is able to see the evolving product, more it is helping him to make the right choices. That's why upfront planning doesn't work to begin with. But at the same time we don't want to be in a position where we don't get our product done before a big trade show. We are doing the prioritization of the stories but I am not sure whether that's enough. I guess it helps to know that if you make this change than something else will not get done because other two ends of our pyramid is fixed, time and budget. But I am not sure what is the simple solution to this problem? Maybe build trust....

Why Scripting languages Matter

As far as I can tell, the way they taught me to program in college was all wrong. You should figure out programs as you're writing them, just as writers and painters and architects do. Realizing this has real implications for software design. It means that a programming language should, above all, be malleable. A programming language is for thinking of programs, not for expressing programs you've already thought of. It should be a pencil, not a pen. Static typing would be a fine idea if people actually did write programs the way they taught me to in college. But that's not how any of the hackers I know write programs. We need a language that lets us scribble and smudge and smear, not a language where you have to sit with a teacup of types balanced on your knee and make polite conversation with a strict old aunt of a compiler

Hackers and Painters

Quality Assurance versus Quality Control

From Jason Yip blog

Quality Assurance (QA): The design and implementation of processes (roles + activities) to ensure quality in a system/product/service.

Quality Control (QC): The design and implementation of processes (roles + activities) to verify the quality of a system/product/service.

QA = prevent defects; QC = detect defects

Testing File upload with Watir

For uploading file from watir test we have to do something like following

@browser.file_field(:name, "fileUploadFieldName").set "path.to.file"

But when I was running my test(on windows) I was getting the following error

Watir::Exception::WatirException, "The AutoIt dll must be correctly registered for this feature to work properly"

Apparently what this means is you have to install AutoIt and once you do that everything works as you expected. I kind of discovered it in a hard way but maybe some else will find this little information useful.