Thursday 30 January 2014

tSQLt adds Function Mocking to the unit testing armoury

The latest release of tSQLt (version V1.0.5137.39257) was published last week, and you can download this version in the usual place. If you're using SQL Test with tSQLt, you can upgrade SQL Test to the latest tSQLt release by following these instructions.

(Note, if you've not come across tSQLt before, it's a database unit testing framework written in T-SQL, and there's a great article explaining why you might want to use it here. Red Gate have written SQL Test to help integrate the power of tSQLt with the SQL Server Management Studio environment. This article assumes you're familiar with tSQLt, but if not I'd strongly encourage you to look at it for unit testing your databases.)

I am pleased to see that this release adds function mocking, as well as a simpler way to rename classes than the previous work around, and checking for a specific error number to tSQLt.ExpectException.

Let's look at that function mocking in a bit more detail. Consider a function which should add two numbers together. We would want to unit test it, and we can do that in the normal way, but a test on a stored procedure which needs to isolate from that function needs to call our new tSQLt method tSQLt.FakeFunction.

CREATE FUNCTION dbo.AddTogether (@a INT, @b INT)



dbo.AddTogether(1,2) AS SumOfNumbers

Now, we can see there is a bug in the function above (and if we run the Stored procedure, we will get a value of 2 returned), but let's ignore that function and proceed to test our stored procedure. Remember, because we are isolating from our dependencies, we don't expect out stored procedure to fail its unit tests.

The way that FakeFunction works is that you need to supply it with a stub function to use in place of your function to be isolated. I suggest you put them in the test class. So let's create a simple function that returns a static value - 3.

EXEC tSQLt.NewTestClass @ClassName = N'MathsTests' -- nvarchar(max)

CREATE FUNCTION MathsTests.Fake (@a INT, @b INT


Now we are ready to create our test:

CREATE PROC MathsTests.[test I get a value of 3 returned when I add 1 and 2] 
EXEC tSQLt.FakeFunction @FunctionName = N'dbo.Addtogether', -- nvarchar(max)
            @FakeFunctionName = N'MathsTests.Fake' -- nvarchar(max)

CREATE TABLE MathsTests.Expected (SumOfNumbers INT)
CREATE TABLE MathsTests.Actual (SumOfNumbers INT)
INSERT MathsTests.Expected (SumOfNumbers)

INSERT MathsTests.Actual
EXEC tSQLt.ResultSetFilter 1,'exec dbo.Maths'
EXEC tSQLt.AssertEqualsTable @Actual = 'MathsTests.Actual'
                                               @Expected = 'MathsTests.Expected'

If we run this test, we get a successful test, because the stored procedure under test returns a row with a value of 3, the expectation, as our code under test (the stored procedure) is isolated from the function which has the bug in it. Of course, you would want to ensure that any module from which you isolate is properly tested, so that you catch issues such as this, but this somewhat contrived example allows you to see how the true cause of the failure can then be found more easily.

The ability to fake functions in this way is a great addition to the unit test writer's armoury.


  1. What in the name of god does this mean?

    1. Hi there,

      If there's a specific concept you're struggling with please do let me know, but in general this update to the tSQLt database unit testing framework means that we can now isolate functions from our code under test as we would stored procedures when unit testing database code. If you've not used tSQLt previously and this is the first time you've come across it, the open-source Unit Test framework tSQLt is a great way of writing unit tests in the same language as the one being tested, i.e. T-SQL.

      I'd suggest this article by Greg Lucas as a good place to start if you're just starting to look at unit testing databases, or if you have more time, my Pluralsight course.

      Hope that helps, but do come back to me if you've a more specific question.


  2. I love reading through a post that can make
    people think. Also, thank you for allowing me to comment!