The last couple of weeks I have been researching on a better way to manage our application database changes. We have a small size team (8-9 persons) working on the application and we have a hard time managing the database changes. The way we handle the changes currently is not the most elegant approach but is followed in many team environments.
In the current system we store the database changes as sql statements in source control categorized by the changed component such as tables, sequences, indexes etc. So we basically have a "db" folder under source control containg separate folders for various DB components such as tables, sequences, indexes etc. For each new DB component a new sql file is created and is stored in the corresponding db component folder and changes to existing DB components are added to corresponding DB component creation script.
Here are some of the issues with our current approach:
1) Hard to compare db changes between build versions
2) No elegant way to release db changes to production/staging/other team member dev boxes. In order to push the db changes to production system @ build time, a separate changes.sql file is created and run at release time. Sometimes for developers to catch up with current db state, they need to go through a maze of sql files and manually determine the changes that need to be synched.
3) Hard to switch database to a previous release version. Database changes must be correctly segregated to accomplish the above scenario.
Looking around in search of an elegant approach I stumbled across this concept of agile databases. Of many things that constitute the core principals of agile databases, incremental change and easy change management is one of them. So I found this tool called migratDB that can be used to manage db changes.
MigrateDB is a simple xml based solution that applies all the changes defined by you in the XML file. For each sql change, you provide a pre-condition or test condition for the execution of the sql and the SQL is executed when the condition is met. The tool provides command line support and also gives ANT integration.
Here are some of the salient features (taken from migratDB):
1) Allows construction of a database at a particular version
2) Allows migration from an existing database to a later version
3) Human readable format for releases
4) Release ‘action’ available on multiple environments (i.e., various operating systems) allowing development on a different platform than production
5) Provides complete history of changes for each database object
6) The source code can be branched and merged
7) Allows multiple developers to work with/on the same database source code, at the same time
8) Supports an ‘automated build’ / ‘continuous integration’ enviroment
Here's the setup I played around with:
Sample changes.xml file:
The above changes.xml file is stored in source control and can be used to detect database changes between versions. A custom XML parser can be built to track changes for a particular db object.
For databases that do not support data dictionary, an alternative approach called Generic Migrations can be used:
Sample ant target: