Wednesday, December 16, 2009

Schema Migration

iBatis3.0 introduces Schema Migration as the database change management tool. It is very impressive to say the least. Coming just out of a project where LiquiBase was used, I can say - this is what I wanted.

To start with, iBATIS Schema Migration is crisp and compact. A few commands, a small configuration file to set the environment thats all a developer needs to struggle with - if still one feels that way. commands like migration up or migration down is in no way a struggle to me. The user guide is only 13 pages but complete. You don't really need anything more to start working with it.

Well, but what about the syntax of the script file? They are actual database script files with .sql extension. The only tool specific syntax here is --//@UNDO

That is just a demarcation between two sets of scripts in a single file. Anything above you write is to upgrade the database and below is to downgrade (or rollback) the database. That's it. Even the --//@UNDO is auto generated! Compare this with the .xml files known as change logs one needs to create with Liquibase.

I also liked the way to start on an existing database. Bootstrap. ./scripts folder has a bootstrap.sql where you put all your existing DDLs or a complete export of the existing database. That is the starting point- which is not even part of migration. With this common starting point, any environment you go in - you can bootstrap that database to a common known status.

Then there is this 'status' command which gives current state of the database - most importantly applied and pending migration scripts. When your team member creates a migration script after yours but migrates to the database before you did, you get the status of your script reported as 'orphaned pending'. And you also can not migrate up your script in a normal course. The choice for you is to migrate down your team member's script and migrate up both so the order is maintained. The most important thing is - the order of script is enforced by the tool. The lack of tool support for ordering was the greatest reason of LiquiBase failure in our case. I can not resist to quote below the excerpt from the user guide I liked the most-

"iBATIS Migrations simply allows this situation to occur, but makes it very obvious that it has happened. Then the teams can review the situation, downgrade their schemas and re‐run the migrations in order, and re‐assess the situation. It allows teams to work autonomously, while encouraging communication, team work and good source control practices."
Finally, it knows that in practice the development team will not have direct access to production. To handover all migration scripts, the tool offers 'migrate script' command, using it you can generate do / undo scripts. Handover those to DBAs and relax- your migration headaches will be much less!!!

I am convinced. Try it yourself.

1. iBATIS3 Schema Migration User Guide