Database Upgrade Process
Wednesday, November 22nd, 2006There has been some discussion on the web recently regarding the best strategy to keep a database schema updated. Generally my team uses a simple procedure of checking in a SQL script for EVERY database change. The file is labeled with a release number and run manually when a release happens, or a developer wants to get up to speed.
Patrick Lightbody (of OpenSymphony fame) starts the discussion on how to formalize this process into a solid “enterprise” framework. This is expanded with some other suggestions here on Infoq.com.
Another interesting process for those of you using Hibernate is to create HBM diff files. So in addition to your full set of HBM files, for each release you create a set of files that just describe the new fields in your objects. Then with a simple java updater run the SchemaUpdate Class against your set of hibernate mappings to synchronize your database schema.
Some caveats on letting Hibernate handle this for you is that it can only make additions to your database (since your HBM files can only describe those) not deletions. Also a big hole in this strategy is what about data migration?
Patrick maintains that you should create your upgrade process as a separate application with no dependencies on your ORM framework. Just plain JDBC calls to migrate data and upgrade schema. Sounds good.