Making unit tests in SQL Server a first class citizen with Red Gate’s SQL Test

Who here doesn’t write enough unit tests? I mean other than me? Somehow no matter how good my test coverage gets I always fell like there are some bits missing. Partly this is because unit testing practices tends to be one of those religious debates and you if you listen to enough people, it’s easy to convince yourself you’re doing it wrong.

One area that’s always been a little tricky is testing anything with a database dependency. In part, this is because those tests often end up being dependent on the data itself which, of course, can be highly volatile. But it’s also philosophically challenged in that if a unit test is to assess a discrete unit of code on the application tier then it probably shouldn’t have dependencies on the data tier.

The problem, of course, is that we’re still writing a lot of logic in the data tier. Fancy ORMs are fantastic and I advocate using them to the full extent that is practical, but there are still plenty of use cases for writing business logic in the database and you really want that to be testable. Plus of course there’s this whole other alternate universe of people who work entirely in database and don’t have access to the testing tools many of us regularly use within Visual Studio; let’s call them the “second class unit test citizens”.

This is where Red Gate’s SQL Test comes in. It’s entirely self-contained within SSMS and all it does is tests business logic in the database, just like those first class cousins in Visual Studio. And it totally rocks.

Use case

This is not one of those airy-fairy hypothetical situations; I really needed to move some logic into the DB so I could run queries on ASafaWeb independently of the app tier. What I wanted to do was write a little function to ascertain whether an entry in the log suggested someone had run a scan against an ASP.NET website or not. I already have this logic over in my web app and it looks like this:

public static bool? IsSiteAspNet(string server, IEnumerable<string> 
xPoweredBy, string xAspNetVersion, string xAspNetMvcVersion, string 
aspNetVersion)
{
  // No information at all should (hopefully) mean the site is configured to 
  keep headers silent. Could go either way so we'll return null.
  if ((string.IsNullOrEmpty(server) || server.Contains("nginx"))
    && (xPoweredBy == null || !xPoweredBy.Any())
    && string.IsNullOrEmpty(xAspNetVersion)
    && string.IsNullOrEmpty(xAspNetMvcVersion)
    && string.IsNullOrEmpty(aspNetVersion))
  {
    return null;
  }
return

(!string.IsNullOrEmpty(server) && (server.Contains("Microsoft-IIS")))

|| (xPoweredBy != null && xPoweredBy.Any(x => x == "ASP.NET"))

|| !string.IsNullOrEmpty(xAspNetVersion)

|| !string.IsNullOrEmpty(xAspNetMvcVersion)

|| !string.IsNullOrEmpty(aspNetVersion);

}

This is simply looking at the response headers and making a guesstimation as to whether the site is ASP.NET or not. No info (or a server running nginx which supports multiple frameworks) means I have no idea and returns null (the first set of conditions) whereas anything whatsoever that suggests either IIS or ASP.NET is present returns true (the second set of conditions). There are then a bunch of tests written around this in a Visual Studio test project.

Installing and configuring SQL Test

The first step here is to go and get your hands on Red Gate’s SQL Test. It’s in preview 2, it’s presently free, just do it! Once it’s installed and we fire up SSMS, new things start appearing:

We’ll take the example database simply because it’s a good reference point of how to do things properly:

And here we go, the test runner is loaded up with the sample database:

I won’t delve into what’s going on here, run it up yourself and take a look inside if you’re interested. Instead, I want to add my ASafaWeb database and actually start testing my TSQL. When I add the database, I get a nice clear dialogue explaining what’s going to happen:

Now this probably won’t be for everyone; I can image some people not being overjoyed with a bunch of new procedures, functions and other objects appearing in their pristine database. But of course you can easily exclude those from deployment when publishing via SQL Compare as I’ve been known to do.

One last thing – if we take the default and include SQL Cop static analysis tests, we can validate a few good DB design principles. Sounds worthwhile:

Run that, here’s what we’ve now got:

Which then loads the DB up in the test runner:

As you can see, we’ve only got SQL Cop tests there for now but let’s give them a run anyway:

Uh oh, does this mean my SQL is no good? Am I destined for eternal DB damnation? Fortunately, if we take a look at the messages from the test runner it turns out that none of this is my fault:

It turns out that Microsoft is getting brought to task by using EXEC instead of sp_executesql in a couple of their membership provider stored procedures which is apparently a bad thing. Then we’ve got ELMAH getting caught using SET ROWCOUNT which is apparently set to be deprecated (nearly five years on and still working fine in SQL08…)

Anyway, none of this is my code and I’m not about to go changing generated code from other parties. Let’s get onto the interesting bit.

Writing tests

The first really basic test I want is effectively the first condition I was testing for in that C# code from earlier on; no headers at all should return null because we have nothing to either confirm or deny the presence of ASP.NET or IIS. Let’s write that up in the “New Test” dialogue:

Working on the precedents in the sample database, I’m going to create a dedicated test class called “AspNetSiteTests”. Doing this creates a schema by the same name and once the test is created, we’re back into good old TSQL with a ready test template:

--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
ALTER PROCEDURE [AspNetSiteTests].[test no headers returns null]
AS
BEGIN
  --Assemble
  --  This section is for code that sets up the environment. It often
  --  contains calls to methods such as tSQLt.FakeTable and 
      tSQLt.SpyProcedure
  --  along with INSERTs of relevant data.
  --  For more information, see http://tsqlt.org/user-guide/isolating-
      dependencies/
  
  --Act
  --  Execute the code under test like a stored procedure, function or view
  --  and capture the results in variables or tables.
  
  --Assert
  --  Compare the expected and actual values, or call tSQLt.Fail in an IF 
      statement.  
  --  Available Asserts: tSQLt.AssertEquals, tSQLt.AssertEqualsString, 
      tSQLt.AssertEqualsTable
  --  For a complete list, see: http://tsqlt.org/user-guide/assertions/
  EXEC tSQLt.Fail 'TODO:Implement this test.'
  
END;

This should look pretty familiar to those who’ve spent a bit of time writing tests before; we’ve got the classic arrange (or “assemble” in this case), act, assert pattern and of course it’s set to fail because it’s not yet doing anything. Of course before it can do anything we need something to run it against so let’s just create a new scalar value function that does nothing more than declares the correct parameters and returns null.

ALTER FUNCTION [dbo].[IsAspNetWebsite]
(
    @Server nvarchar(250),
    @XAspNetVersion nvarchar(50),
    @XAspNetMvcVersion nvarchar(50),
    @XPoweredBy nvarchar(300)
)
RETURNS bit
AS
BEGIN
  EXEC tSQLt.Fail
  RETURN NULL
END

I’m not convinced that calling tSQLt.Fail is really the smartest thing to do here, but obviously some sort of exception needs to be thrown (thinking NotImplementedException in .NET), and you can’t RAISEERROR in a function. Anyway, it’s only temporary so that we can get our tests written and frankly I’m more interested in making things work than getting sucked into that unit testing religious debate I alluded to earlier. Here’s the first simple test I’ll write to make sure that if everything is null then the result is also null:

ALTER PROCEDURE [AspNetSiteTests].[test no headers returns null]
AS
BEGIN

  --Assemble
  DECLARE @Server NVARCHAR(250) = NULL;
  DECLARE @XAspNetVersion nvarchar(50) = NULL;
  DECLARE @XAspNetMvcVersion nvarchar(50) = NULL;
  DECLARE @XPoweredBy nvarchar(300) = NULL;
  
  --Act
  DECLARE @IsAspNetSite BIT;
  SET @IsAspNetSite = dbo.IsAspNetWebsite(@Server, @XAspNetVersion, 
      @XAspNetMvcVersion, @XPoweredBy);
  
  --Assert
  EXEC tSQLt.AssertEquals NULL, @IsAspNetSite;
  
END;

Now we can go ahead and execute this in the test runner:

Excellent, this is exactly the sort of workflow we’d expect when writing tests first back in Visual Studio. Let’s write the rest of them before actually implementing the function:

Now we can get on with actually implementing the method and going through the whole red, green, refactor cycle. As I began implementing the function, I’d jump over to the test runner, give it a whirl and watch the tests gradually begin to pass:

And of course there’s some verbosity as to why things are going wrong once you drill down into the SQL Test messages:

And that’s really it – keep implementing tests and making things green just like you would back in good old Visual Studio.

But wait – there’s more!

Whilst I didn’t need to delve into this area just to write tests against a simple function, SQL Test has excellent support for dealing with the volatility of data. Let me explain; there are many programmatic uses of TSQL which have a dependency of the state of data. For example, a stored procedure runs against a table and makes calculations based on the data which is in there.

The challenge here is similar in some ways to what we’ve faced for many years when writing unit tests in Visual Studio in that when tests become dependent on the state of the data you run the real risk of the data not always being in that expected state. You also end up having to clean up after yourself and trying to get all the data into a nice predictable state before running any more tests. It’s a minefield.

The way SQL Test handles this is to enable the creation of fake tables which implement all the attributes of the real thing and allow you to mess around with it to your heart’s desire without actually modifying any persistent data. You can then assert that the fake table matches a pre-defined expected state. The best way to get your head around this is to take a look at Red Gate’s intro video where Grant Fritchey walks you through it (fake tables come in around the 5 minute mark):

Another worthy resource is the Simple-Talk article on Using SQL Test Database Unit Testing with TeamCity Continuous Integration. I’ve been a fan of integrating Red Gate tools with TeamCity in the past so this really aligns to my way of thinking.

Summary

Like all the other Red Gate bits, what I like about SQL Test is that it’s extremely practical and integrates cleanly into the way you work when building databases; it’s right up there in SSMS and is very easily accessible. There are no smoke and mirrors or magic going on in the background, it’s very easy to see exactly how the thing is put together and what it’s doing under the covers.

SQL Test makes sense for all the same reasons that unit testing your Visual Studio projects does; having that certainty that your code is doing what you intended both today and in the future is absolutely invaluable. Plus you can drop it all into SQL Source Control and share the tests with your team then exclude when generating change scripts and publishing via SQL Compare. Nice.

I’m not quite sure of Red Gate’s future roadmap for the product, but especially while it’s free I say go in and get your hands on it. I had everything up and running with my own tests in literally about 20 minutes – have a go!