Getting started with dbdeploy
dbdeploy is a Database Change Management tool. It’s for developers or DBAs who want to evolve their database design - or refactor their database - in a simple, controlled, flexible and frequent manner.
The recurring problem with database development is that at some point you’ll need to upgrade an existing database and preserve its content. In development environments it’s often possible (even desirable) to blow away the database and rebuild from scratch as often as the code is rebuilt but this approach cannot be taken forward into more controlled environments such as QA, UAT and Production.
Drawing from our experiences, we’ve found that one of the easiest ways to allow people to change the database is by using version-controlled SQL delta scripts. We’ve also found it beneficial to ensure that the scripts used to build development environments are the exact same used in QA, UAT and production. Maintaining and making use of these deltas can quickly become a significant overhead - dbdeploy aims to address this.
dbdeploy requires java 1.5 or higher.
The example, for simplicity, uses a local file version of hsqldb that is included in the distribution. Many other databases are supported by dbdeploy including Oracle, MySql and Microsoft SQL Server.
This makes sure the example always starts with a clean database by deleting and recreating the directory.
dbdeploy uses a table in your database called changelog to track which delta scripts have been successfully applied. This target runs the script provided in the distribution to create this table. You will need to do this by hand on any database you want to start using dbdeploy.
This is dbdeploy actually doing its work. It:
The bit of ant that made this happen was:
<taskdef name="dbdeploy" classname="com.dbdeploy.AntTarget" classpathref="dbdeploy.classpath"/>
Take a look at the source scripts to see what was included in the scripts.
In versions of dbdeploy prior to 3.0M2, you had to write out a generated script file to a file and then execute that with the database vendor's tool or by using ant's sql task. You don't need to do this any more, though if you need to do this see GeneratingAndCustomisingScripts. By default dbdeploy will split your files on ";" to work out which separate jdbc statements to execute; you can use the delimiter and delimitertype parameters to change this - they work just like they do for the ant sql task.
(For those who used earlier versions, if you don't specify an output file, dbdeploy will apply the changes for you directly. Note that when dbdeploy applies changes, it uses standard sql and jdbc so you do not need to specify a database syntax.)
Run just dbdeploy again, without clearing down the database:
$ ant update-database
dbdeploy detects that scripts 1 and 2 have already been applied, so generates a empty script.
Create a file 003_more_data.sql with the following content:
INSERT INTO Test VALUES (8);
$ ant update-database
As you see, just the new change script is applied.