You Write Your SQL Unit Tests in SQL?

One of the questions that came up after my ACCU London talk back in January (xUnit Style Database Unit Testing) also came up last week after my ACCU Conference talk (Using xUnit As a Swiss Army Testing Toolkit):-

Why do you write your database unit tests in SQL?

My immediate and somewhat flippant response is:-

Why wouldn’t you write your SQL tests in SQL? You wouldn’t write your Python unit tests in C++ would you?

Now, if you swap the words Python and C++ around in that sentence it begins to sound more plausible. In fact it’s an idea that I’ve looked into in the past, but not so much at the unit test level, more at the component/integration level. Kevlin Henney, in his ACCU London talk last year, pointed out that you could use NUnit[*] to unit test C++ code via the magic of C++/CLI so using a different language to express your tests is by no means wrong but it comes with another cost…

Portability (People Skills)

Steve Love did a talk at the ACCU Conference back in 2009 about portability. Of course what most people think of when you say portability is about the source code and how it works across multiple platforms (e.g. Windows/Unix) or across multiple toolchains (e.g. Visual C++/GCC). But there is another aspect to it and that is portability across people – can you hire another person to do the same job?

I’ve worked in small teams where everyone has to do everything and also in larger teams where people specialise in a particular language or technology. It is hard enough to find good people as it is, adding another orthogonal requirement to the role only makes the search that much more difficult. OK, so many experienced T-SQL developers will probably also have some knowledge of C# and the Cool Kids will happily remind you how we should all be Polyglot Programmers and be able to work with multiple languages, but the organisations I work in don’t get to attract the superstar programmers. Sometimes you’re ‘given’ members of staff from another team, presumably because they can save money by avoiding firing one and hiring another in the mistaken belief that you will turn them into a superstar programmer.

Isolation (Tooling)

Even so, just because they might know C# doesn’t meant they feel comfortable using, say, Visual Studio as their main development tool. Our automated test runner currently uses SQLCMD and we (can) write our SQL code and tests using just SQL Server Management Studio (SSMS). This means that not only are the tests expressed in their language of choice, but the tooling is also the one they are likely most comfortable with. I’ve not tried SQL debugging with SSMS but I would have thought that it is far simpler if you’re not trying to debug across a high technology stack (it feels akin to debugging across RPC calls).

That last point sums up my approach to unit testing, which is that it is all about isolation. That doesn’t just apply at the code level – at the thing under test – but also to the toolchain that sits around it. That doesn’t mean that you should only use primitive tools like Notepad & SQLCMD to develop your code, but that you try and avoid having too many other layers (e.g. C#, ADO.NET) ‘above’ the code in your test scaffolding as it makes it unnecessarily complicated and can make it harder to do things like debugging.

The Right Tool For the Job

Deciding what the right tool is for any job is getting harder every day because new tools are springing up all the time that bring the best bits from other tools together to solve some other hybrid problem. I’m definitely sold on the idea of writing SQL unit tests in SQL as the language provides enough features to allow you to write fairly clear tests, but our Heat Robinson style test runner is clearly begging to be replaced by something better. I also need to look again at how Visual Studio studio approaches the problem because they have an army of developers [hopefully] much cleverer than me that should be able to balance these requirements. I also promised myself I’d got back and look at how TSQLUNIT was coming along as that is also more mature than our framework.

We never set out to write a SQL unit test framework, we just did what felt natural to bootstrap ourselves and that’s the way it ended up. Looking back I still think it feels the right way to go.

[*] Whereas other languages seem to have a few key unit test frameworks, C++ is blessed with a bucket load. At the ACCU Conference last week someone asked for a show of hands of those people that had written a unit test framework and there were quite a few hands. I’ve said before that I believe it’s somewhat of a Rite of Passage and writing a unit test framework seems to be common part of that voyage of discovery.