Using DBDeploy.NET

To get dbdeploy.net up and running on a project is relatively straightforward.

First thing, if you haven't already downloaded dbdeploy.net, get it from here
I try to keep unnecessary files from being committed to the source control repository, so extract the downloaded zip somewhere (like your desktop) and we'll pick and choose what is needed from there to add to our project.

I tend to follow a project structure similiar to this:


So the first step is to copy the dbdeploy.net\bin folder from the desktop into a tools\dbdeploy folder:

Next we'll want to setup the location we keep all our db related scripts.
In the working directory, add a db folder and below that also add a deltas folder. For dbdeploy.net to work, your database requires a schema version table, so from the dbdeploy.net\scripts folder from the desktop, select the appropriate sql based on the database being used and copy it to your db folder: I also usually keep the database creation script at this location:

Finally, its time to modify our nant build file to include tasks for the db related work.
(Note that i'm omitting some things to reduce the noise, so if something is unfamiliar let me know)

Use of the sql task from nantcontrib is required so ensure you have that available in your tools and import it at a global level in your build file as so:

<loadtasks assembly=""tools\nant\contrib\NAnt.Contrib.Tasks.dll"/>

Add a folder to hold the automated builds upgraded script.

<target name="init" description="initial compilation setup">
<mkdir dir="${build.dir}"/>
<mkdir dir="${build.dir}\db"/>
</target>


Then a task for creating the database:

<target name="db.create" depends="init">
<echo message="create database" />
<sql connstring="${connection.string}"
source="db\createDatabase.sql"
output="${build.dir}\db\createDatabase.out.txt"
delimiter="GO" delimstyle="Line" print="true" batch="false" />

<echo message="create db deploy change log table" />
<sql connstring="${connection.string}"
source="db\createSchemaVersionTable.mssql.sql"
output="${build.dir}\db\createSchemaVersionTable.out.txt"
delimiter="GO" delimstyle="Line" print="true" batch="false" />

<call target="db.upgrade" />
</target>

And finally, the task for upgrading the database:

<target name="db.upgrade" depends="init">
<loadtasks assembly="tools\dbdeploy\Net.Sf.Dbdeploy.dll" />
<echo message="upgrading database to latest version" />

<!-- creates the script -->
<dbdeploy dbType="mssql"
dir="db\deltas"
dbConnection="${connection.string}"
outputFile="${build.dir}\db\db-upgrade.sql"
undoOutputFile="${build.dir}\db\db-upgrade-undo.sql" />

<!-- applies the script -->
<sql connstring="${connection.string}"
source="${build.dir}\db\db-upgrade.sql"
output="${build.dir}\db\db-upgrade.out.txt"
delimiter="GO" delimstyle="Line" print="true" batch="false" />
</target>


I also usually add a task to handle undoing the changes.

<target name="db.upgrade.undo">
<sql connstring="${connection.string}"
source="${build.dir}\db\db-upgrade-undo.sql"
output="${build.dir}\db\db-upgrade-undo.out.txt"
delimiter="GO" delimstyle="Line" print="true" batch="false" />
</target>


Thats it. Infrastructure done.

Assuming your existing build all target is something like this:
<target name="all" depends="clean, init, compile, test, dist" >

To make that recreate the database on each build:
<target name="all" depends="clean, init, compile, db.create, test, dist" >

Or if you only want to upgrade on each build:
<target name="all" depends="clean, init, compile, db.upgrade, test, dist" >

We're now set up to follow a more evolutionary database design, with each additional database change needed being added as a file in the db\delta folder. Remember, the only rule is to follow a naming convention around these scripts. Each file needs to begin with a number, one higher than the previous one.



NOTE: for a good post on using the java version of dbdeploy see Pramod Sadalage blog here