Test-First development tool for SQL Server 2005?

Question

For several years I have been using a testing tool called qmTest that allows me to do test-driven database development for some Firebird databases. I write a test for a new feature (table, trigger, stored procedure, etc.) until it fails, then modify the database until the test passes. If necessary, I do more work on the test until it fails again, then modify the database until the test passes. Once the test for the feature is complete and passes 100% of the time, I save it in a suite of other tests for the database. Before moving on to another test or a deployment, I run all the tests as a suite to make sure nothing is broken. Tests can have dependencies on other tests, and the results are recorded and displayed in a browser.

Nothing new here, I am sure.

Our shop is aiming toward standardizing on MSSQLServer and I want to use the same procedure for developing our databases. Does anyone know of tools that allow or encourage this kind of development? I believe the Team System does, but we do not own that at this point, and probably will not for some time.

I am not opposed to scripting, but would welcome a more graphical environment.

Any suggestions?

Answer 1

Team System is probably the best-known solution, but you could also try TSQLUnit (SourceForge).

I haven't used it myself, but this article does a decent job of introducing it.

Answer 2

Checkout http://www.sqlservercentral.com/articles/Testing/66553/ and http://www.sqlservercentral.com/articles/Database+Design/66845/

This is a fairly crude article about doing everything within T-SQL.

Have you thought about using NHibernate and using TestDriven or similar just for the tests?

Answer 3

On projects where I didn't have access to team system for db pro's, I have used sql scripts combined with msbuild and the sdc tasks library for msbuild (http://www.codeplex.com/sdctasks). The msbuild script calls on an sdc task to run my sql scripts in a particular order (e.g. create db, create tables etc...) and on a particular connection string. The scripts always check if an object exists and do teardown first and build it back up.

The sql and msbuild scripts I place in a regular visual studio database project (which does nothing special, so you could choose to use a simple empty project), so everything is source-controlled.

with such a set of scripts , you can setup a new database for each test run. You can then use insert scripts to populate it with data and run unit tests against it.

These scripts are also useful for setting up databases from scratch in different environments (DEV/TST/QUA/...)

Answer 4

I was able to adequately apply a test driven development style against SQL Server databases using TSQLUnit. I followed the same flow as you described with writing a unit test sproc first that fails and then making the changes necessary for the test to pass. Over time, I also built up a suite of tests when executed validated that nothing broke while making any new changes.

There was some tough spots (including extreme difficulties in writing tests for existing sprocs) but it worked especially for schema changes. However, I would recommend looking at T.S.T. the T-SQL Test Too1 which unlike TSQLUnit (I had to roll my own) has built-in support for assertions.