Refactoring databases for fun, profit and sanity
Sam Newman and Graham Tackley
DBDeploy – a new open source tool from ThoughtWorks – whilst in its current form it’s new but variations of it have been used in numerous projects for a while.
It currently supports 4 databases – Oracle, SQLServer, Hypersonic, Sybase, more coming soon including MySQL and ProgresSQL.
Incremental changes are specified, as ANSI standard SQL, in files named with numerical prefixes. DBDeploy checks which have already been applied and generates an output file containing the changes as database vendor specific SQL that have not yet been applied. This output file can then be applied, after being examined by a DBA if necessary, to the database.
DBDeploy also supports downgrades. Think carefully before using this capability (and this advice applies to any similar tool) – do you really need to be able to rollback a change. The time you really need a script to undo changes is when the easy option of doing an export before the upgrade and reimporting it if something goes wrong is not possible due to the large volume of data involved would mean it takes too long.
If you use the downgrade functionality don’t forget you need to test peforming downgrades as well as upgrades.