Wednesday, August 02, 2006

Continuous Integration of database scripts

I've recently got Cruise Control working on our development server to get latest from source safe, build our .NET assemblies and execute all unit tests. I'm extremely pleased with this and has really proved it's worth recently to show complete visibility to the team of failed builds. Any broken builds which do occur get turned around very quickly; I would strongly encourage anyone who hasn't already integrated cruise control into their build process to do so now.

I have also recently taken this further by integrating database script changes into the process. In source safe we keep all the creation scripts for tables / stored procedures / UDFs e.t.c. When any changes are detected on the VSS database the scripts are applied using a few nant tasks and then the unit tests are re executed. This has quickly caught a few issues recently when stored procedures weren't in sync with the .NET code e.g. wrong data types or too many parameters in the DB compared to the client code.

Like most development teams we have to maintain a few versions of our application and upgrade databases from old version to new versions. The latest database script are rerunnable against old versions of the schema i.e. it will add columns / stored procedures if their not already there, Ideally I would like to execute the database scripts against a number of different versions of the database and then execute the unit tests against the newly updated database to ensure the tests still passed. This would find issues for clients upgrading very quickly. I'd be interested in hearing from anyone else who has tried to automatically integrate database changes within their continous integration process.

3 comments:

Anonymous said...

Matt - I know this post if a bti old, but relevant to something I'm doing. I've been using CC.Net to do application builds and unit tests for a few years now. Now, I'm looking at taking it that step further and integrating my database scripts. Our environment performs a weekly refresh of our database schema from our production environment. I need to then apply any script changes to this older version of the database. Any thoughts you could throw out that could help in my planning of this process in using CI?

Sam said...

I am not sure if this helps but I have been using (N)ANT to build database scripts from SCM branches for some years.

It is pretty straight forward to produce a consolidated file with the table/procedure/UDF etc... changes. I go further and produce independent scripts for Production and UAT so that I can apply specific changes for our test environment.

What I would like to do next is expand the build process to include database unit test cases.

Christophe Fondacci said...

Hi,

I suggest you have a look at the open source neXtep designer project. It is a database development IDE initiative aiming at automating the integration and deployment phazes of a database application.

From the latest release, neXtep offers a command line tool allowing you to drive the build of your database from simple command line calls. You could therefore easily wire this with your integration server.

We need feedbacks, use cases and suggestions about this new feature. That would be great if you could have a look and tell me what you think.

Here is the link to the project :
http://www.nextep-softwares.com

Kind regards,
Christophe