Unit Testing Databases with Visual Studio Part 1

by data-centric on November 14, 2010

I’m a big fan of the benefits of test-driven development (TDD) and similar methods. Having a suite of unit tests that can be easily or automatically re-run gives me greater confidence in the quality of the software I produce and means that I spend less time debugging both during initial development and during any subsequent maintenance phase. The challenge for me has always been how to intelligently utilise automated unit testing in the database arena. By intelligently, I mean that the amount of time I spend writing tests and, especially for databases, setup and tear down code, should not outweigh the amount of time I can reasonably expect to save in later troubleshooting and debugging. If it takes longer to write the setup and tests than it does to fix any bugs later, there is much less value for the business in using TDD.

Aside from the simple fact that T-SQL is, primarily a declarative language with its attendant restrictions on reusability and inheritance, the general lack of any good frameworks for database unit testing makes implementing any form of TDD a challenge. For me, in this context the definition of “good” includes “easy to adopt”. If you can code in C#, then using the current incarnation of NUnit to write tests involves a smaller learning curve than if say, the only testing frameworks for .NET were written SmallTalk. In which case, I think that a lot less people would be producing quality, testable code in the .Net world today.

It’s true that there are a range of database testing tools and frameworks out there including DBFit, DBUnit, NDBUnit, TSQLUnit, SQLUnit and TSqlTest to name just a few that I’ve come across. Some of my experiences with these should probably be the topics of other posts in the future. The fact that, to my knowledge, none of these have become widely adopted on the scale of tools like NUnit or MBUnit suggests something, they may not be that easy to adopt. For the most part they all require a steep learning curve and possibly learning a completely new language or paradigm or they’re not really frameworks in the truest sense at all, And that’s before you start actually writing tests and producing better code.  It also tells us what we knew already, that unit testing databases is hard, especially to do intelligently (see my earlier definition). There is also a newer, SQL-based framework called tSQLt which I think shows great potential and about which I will be writing more the future.

Then along came Visual Studio 2005 Team System for Database Professionals – with a big price tag, followed by the equally pricey 2008 version.  There was a big difference in price between Visual Studio Professional and the Team System versions, especially when, at first, if you wanted Team System Developer Edition and Database Edition you had to pay twice – a situation rectified when Microsoft started offering both under the same license.  This made it expensive to adopt rather than hard but just as much of a barrier in some teams.  And now, with Visual Studio 2010 Premium, there is an even smaller price differential to prevent you getting the right tools for the job.

So, given all this, and based on my own experiences, I thought I would write a few beginners “how to” posts to help people get started using Visual Studio for database test-driven development. In using Visual Studio, I am not suggesting that Microsoft has it completely right but the Visual Studio approach does allow dedicated database developers to start doing TDD without having to learn any new languages and working with tools that they are already broadly familiar with.

Actually, this introduction has gone on longer than I thought so you will have to wait until Part 2 to start learning how to do test-driven development.  What I will finish by saying is that there are as many diametrically opposed views on how to implement TDD for databases and what to test as there are religious wars over whether the commas separating a vertical listing of columns in a SELECT statement should be leading or trailing. For the record, I’m a “leading” man.

There are some people who think that all the database testing should be handled by the data access layer, I’ve done this and it works really well, if you’re familiar with .NET or whatever the middle tier is written in.  To me this is a bit of a cop out, and besides this is integration testing, not unit testing. There are others who think that there is no point testing whether tables or constraints exist as these would be picked up when the release scripts are run or when  subsequently testing stored procedures.  I think that this is also just a way of shifting the responsibility elsewhere.  In some shops, there may be no stored procedures or views etc as all data access is done via ORM tools and/or SQL built on the fly so does the database developer ensure that what they are releasing meets the required quality standards.

Personally, I like to try and use the same approach to writing tests for the database as I do in .NET.  I try and test everything at the lowest possible denominator that still allows me to deliver value.  Also, the lower the level that I start writing tests, the easier it is to write tests higher up the food chain because I already know that the things they depend on do actuall work as they should.  This series of articles will reflect my approach and will hopefully help database developers who’ve never done TDD to gain a better understanding of how, and more importantly why we might want to start writing all this extra code.

In the next part we will work through setting up a database test project and then writing and passing our first test.


Previous post:

Next post: