Test Driven Development (TDD) for Databases – Choosing a framework

by data-centric on October 8, 2011

I’ve been a fan of test-driven development (TDD) for a long time. My primary skill set revolves around database design and development but I like to think that, whilst not a .Net expert, I have pretty good C# skills for a SQL bod. So I have had the opportunity to follow proper TDD methodolgies on several .Net projects and this in turn has led to a deeper understanding of a number of design patterns that you kinda have to use if your going to do TDD properly and which concidentally make for better, more robust code in production as well..

So it has always frustrated me that it is not so easy to implement TDD effectively for T-SQL. Granted there a number database testing tools and frameworks out there including DBFit, DBUnit, NDBUnit, TSQLUnit and SQLUnit to name just a few that I’ve come across. DBUnit and NDBUnit aren’t unit testing frameworks as such, they are more about getting the database into a known data state to facillitate unit testing. Let’s look at a few of the testing tools in a little more detail…

DBFit uses the tried and tested FIT/Fitnesse framework which I have seen used very effectively for testing UI calls for example. However, whilst DBFit does allow the effect of stored procedure calls to be tested, which is better than nothing, I think it operates at too high a level to be really classed as a unit testing framework for databases. In fairness to the developers, FIT was originally designed to allow users to write acceptance tests and its HTML table-based format for specifying inputs and expected outputs works very well for non-developers and so does lend itself somewhat to writing database tests.

TSQLUnit has been around for years, I remember playing with it back in the days of SQL Server v7. Unfortunately, developmeent seemed to stop for a number of years although I noticed recently some renewed activity on SourceForge with v0.91 being made available for release in April this year. TSQLUnit certainly supports proper unit testing although the need to learn yet another language (python) to run the tests has always put me off doing anything more than playing with this framework.

Visual Studio Team System Database Edition a.k.a. DbPro, or DataDude to its friends, has been around since VS2005. At first there was a big price difference between regular Visual Studio which had no support for database testing and the Team System editions. As new VS versions have been released so the price differential has become less significant. One of the advantages of using Visual Studio for database development is that my database project(s) can be part of the same solution as the data access, business logic and UI layers of the application. What I like about this tool is that it allows me as a SQL Developer to write unit tests that can be rerun at the same time and in the same manner as test suites belonging to the other (.Net) parts of the application – without writing a line of .Net code. This is great for continuous build scenarios. What I’ve struggled with is actually writing tests in this environment – lots and lots of dynamic SQL and the assertions, especially for evaluating sets of data were especially cumbersome. Having said that, I’ve not played with the VS2010 database testing framework at all so it may have moved on considerably. Over a year ago, I started writing a few articles on writing unit tests using DBPro but lost interest – partly because writing the tests just seemed like such hard work – plus in hindsight, my approach was wrong.

tSQLt is a newer, SQL-based framework that I am growing to really enjoy working with. Although the framework itself makes use of CLR, all the tests are written in T-SQL. I think this is important because whilst some SQL Developers may be comfortable in multiple languages, others may not. I also think it’s just easier if you can write your tests and your code side by side in the same environment. These are some of the things I like about tSQLt:

  • The ability to easily mock a table (or “fake” in tSQLt) so I can completely isololate my tests from the rest of the database without the need to set up reference data in umpteen tables. I think that this is a massive time saver and so is a huge win for tSQLt.
  • The ability to easily compare the contents of two tables. The syntax of tSQLt.assertEqualsTable 'expectedTableName', 'actualTableName' will be very familiar to OO developers accustomed to NUnit and the like and is readily grasped by someone only familiar with T-SQL. Another big tick in the box.
  • The ability to effectively mock stored procedures. In the compiled-code unit testing world, I want to be able to test what a method does and thereafter, just mock the results of calls to that method without having to retest the underlying method again using something like Rhino Mocks. The benefits of this are less obvious until you really start getting in to testing your database code and this feature deserves a little more explanation. You write a sproc called myWorker that accepts a dozen or so input parameters, does something wizzy with these internally then writes the results to three different tables. You write one or more tests to check that myWorker does everyting it should. Next, you want to write a sproc called hisManager that also takes some inputs, does something with them then passes them in a call to myWorker. Without tSQLt’s ability to mock stored procedures, the only way you can test what hisManager does is by effectively retesting myWorker all over again and checking what myWorker inserted to those tables. tSQLt lets you write a proper, discrete unit test for myManager by just testing that myWorker gets called and is passed the correct parameters. This type of testing is taken for granted in the compiled code world but is now possible for T-SQL.
  • The choice of output format for test results including XML means that your tests can be integrated with CruiseControl in a continuous build environment.

I think that tSQLt is the most exciting framework out there at the moment and might just be the tool that gets test-driven development more widely adopted by the SQL development community.

I’m not claiming particular expertise in any of these frameworks, so my recommendation is based on dipping into each of these at different times over the past few years rather having worked in any real depth with any one framework.  Having said that, I have been doing quite a lot with tSQLt recently – especially as a precursor to some refactoring of a couple of critical pieces of functionality on one of my current projects.  I honestly believe that tSQLt offers the biggest bang for your buck right now – especially because it’s zero bucks.  But actually, now I have an understanding of some of this framework’s unique features I would happily pay for this tool. I don’t know either Sebastian Meine or Dennis Lloyd the guys who wrote this but is plain to me that these guys have a deep understanding of both the philosophy of test-driven development and how such methods might be best utilised by database developers

If you want some other opinions, George Mastros has some positive things to say about his first experiences with tSQLt and DBA Dave also writes very favourably about tSQLt on his sql-ution blog

Previous post:

Next post: