How To Mock Database Objects For (Almost) Isolated Unit Testing In SSDT Database Projects

While writing this Stored Procedure I wanted to properly unit test in SSDT, I faced this annoying little issue:

  • My Stored Procedure, let’s call it MainProcedure, had to call another Stored Procedure, let’s call it SubProcedure.
  • The SubProcedure had some dodgy and time-consuming side-effects that I didn’t want to see happen during a Unit Test. Plus, the SubProcedure was not the one being tested anyway.
  • Considering this, I wanted to isolate the code in MainProcedure using a Dependency Injection / Inversion Of Control pattern somehow.
  • Unfortunately, this is not supported out-of-the-box in SSDT, plus to be fair, IoC is not something the T-SQL language lends itself to easily at all: You can’t just pass a type-safe reference to a Stored Procedure in your parameters.

After some head scratching and searching the web for what wasn’t there, I arrived at a couple of options:

  • Don’t do anything and live with it. It’s always an option.
  • Mimic an IoC pattern by passing in a name for a dependent stored procedure… And then end up writing loads of dynamic SQL, thereby undermining the point of using SSDT in the first pace.
  • Drop the SubProcedure before running the test, create a fake and then issue the original CREATE statement again… And end up with loads of code copied all over the place… And what if wanted to mock tables?
  • Or swap the underlying SubProcedure before the test with a stub/fake and then swap it back after completion. Definitely cheating… Definitely Not IoC… But I wondered if it worked…

So let’s see what happened.

Main Procedure

First of all, I created some sample code to represent the MainProcedure.
Notice how this sample is calling the SubProcedure by passing through its received parameters and its return value. This will help us evaluate whether the correct code is being called later on.

CREATE PROCEDURE [dbo].[MainProcedure]
	@Param1 INT,
	@Param2 INT
AS

PRINT 'Running MainProcedure...';

PRINT 'Calling SubProcedure...';

DECLARE @ReturnValue INT;
EXECUTE @ReturnValue = dbo.SubProcedure
	@Param1 = @Param1,
	@Param2 = @Param2;

PRINT 'Done Calling SubProcedure...';

PRINT 'Done Running MainProcedure...';

RETURN @ReturnValue;

Sub Procedure

Then I created some sample code for the SubProcedure.
The WAITFOR was just to make it really obvious the sluggish procedure was being called, you can take it out if you wish.

CREATE PROCEDURE [dbo].[SubProcedure]
	@Param1 INT,
	@Param2 INT
AS

	PRINT 'Running SubProcedure...';

	/* do something really heavy here */
	WAITFOR DELAY '00:00:05';
	/* done */

	PRINT 'Done Running SubProcedure...';

RETURN 0

Test

Now it was the time to see if I could swap the SubProcedure with a mock.
I created an SSDT Unit Test for the MainProcedure, and then went on to write some testing code.

Below is what I put on the Test code block, to start with.
I also added a Scalar Value Condition to the Test, to verify that the @ResultValue would return the value 30. I would then make the Mock SubProcedure return this as opposed to the simple zero of the original code. This would provide evidence that the mock code was being called.

SET XACT_ABORT ON
BEGIN TRANSACTION

BEGIN TRY

	-- ARRANGE
	DECLARE @Param1 INT = 10;
	DECLARE @Param2 INT = 20;
	DECLARE @ResultValue INT;

	-- ACT
	EXECUTE @ResultValue = dbo.MainProcedure
		@Param1 = @Param1,
		@Param2 = @Param2;

	-- ASSERT
	SELECT @ResultValue;

END TRY
BEGIN CATCH

	ROLLBACK;
	THROW;

END CATCH;

Pre-Test

So now it was time for some good old cheating.
I stuffed the code below in the Pre-Test phase of this Unit Test.
This creates a Mock at run time, just like you would do in plain .NET Unit Tests, and then swaps it with the original code, whilst backing it up too.
Notice we have to resort to using Dynamic SQL here (sigh…) because we can’t include multiple clean batches in a SQL Server Unit Test. (e.g. no GO for you)

SET XACT_ABORT ON
BEGIN TRANSACTION

BEGIN TRY

	-- create a mock object
	EXECUTE sp_executesql N'
	CREATE PROCEDURE dbo.SubProcedure_Mock
		@Param1 INT,
		@Param2 INT
	AS
		PRINT ''Mock SubProcedure Called With { @Param1 = '' + CAST(@Param1 AS VARCHAR(10)) + '', @Param2 = '' + CAST(@Param2 AS VARCHAR(10)) + '' }'';
		RETURN @Param1 + @Param2;
	';

	-- swap out the original object
	EXECUTE sp_rename 'SubProcedure', 'SubProcedure_Original';

	-- swap int the mock object
	EXECUTE sp_rename 'SubProcedure_Mock', 'SubProcedure';

END TRY
BEGIN CATCH

	ROLLBACK;
	THROW;

END CATCH

COMMIT;

Post-Test

To undo the blatant cheating of the Pre-Test phase, I also added the code below to the Post-Test phase.
This swaps in the original code and then drops the mocked procedure that was created earlier.
I couldn’t help but to keep recalling a known issue with the Post-Test phase though: it wouldn’t run if the Unit Test code failed in the first place.
But I would get back to that in a minute.

SET XACT_ABORT ON
BEGIN TRANSACTION

BEGIN TRY

	-- swap out the mock object
	EXECUTE sp_rename 'SubProcedure', 'SubProcedure_Mock';

	-- swap in the original object
	EXECUTE sp_rename 'SubProcedure_Original', 'SubProcedure';

	-- remove the mock object
	EXECUTE sp_executesql N'DROP PROCEDURE dbo.SubProcedure_Mock';

END TRY
BEGIN CATCH

	ROLLBACK;
	THROW;

END CATCH;

COMMIT;

Results

This actually worked better than expected.
When I ran the MainProcedure on its own in SSMS, like so:

-- ARRANGE
DECLARE @Param1 INT = 10;
DECLARE @Param2 INT = 20;
DECLARE @ResultValue INT;

-- ACT
EXECUTE @ResultValue = dbo.MainProcedure
	@Param1 = @Param1,
	@Param2 = @Param2;

I got the results I was expecting:

Running MainProcedure...
Calling SubProcedure...
Running SubProcedure...
Done Running SubProcedure...
Done Calling SubProcedure...
Done Running MainProcedure...

However, when running the all the test phases together (by copy/pasting from the Unit Test Editor), these were the results:

Running MainProcedure...
Calling SubProcedure...
Mock SubProcedure Called With { @Param1 = 10, @Param2 = 20 }
Done Calling SubProcedure...
Done Running MainProcedure...

It was working! This unclean cheating was actually working!
Running the Test itself in Visual Studio also proved to work, and with Scalar Value Condition being satisfied too.

Test Cleanup

Now there was just one issue to resolve. It is a fact that the in the Post-Test will only run if your test does not throw errors or fails execution otherwise. I tested this to be true by changing the code of MainProcedure to THROW a fake error:

CREATE PROCEDURE [dbo].[MainProcedure]
	@Param1 INT,
	@Param2 INT
AS

PRINT 'Running MainProcedure...';

PRINT 'Calling SubProcedure...';

DECLARE @ReturnValue INT;
EXECUTE @ReturnValue = dbo.SubProcedure
	@Param1 = @Param1,
	@Param2 = @Param2;

THROW 50000, 'Something went wrong...', 1;

PRINT 'Done Calling SubProcedure...';

PRINT 'Done Running MainProcedure...';

RETURN @ReturnValue;

Running the Unit Test now left me with a left-over SubProcedure_Original object in the database, also causing subsequent test runs to fail.
However, I didn’t want to bring the Pre-Test and Post-Test code together to the Test block, as these phases are meant to be run under different user credentials.
Fortunately, this is exactly what the Test Cleanup phase is for.
This phase will always run after all the Unit Tests in a given file, regardless if they fail or not. That’s the point of it: To clean up the mess!

So I added the code below to the Test Cleanup phase:

SET XACT_ABORT ON
BEGIN TRANSACTION

BEGIN TRY

	-- check if the original objects exists as a backup
	IF OBJECT_ID('SubProcedure_Original') IS NOT NULL
	BEGIN

		-- check if the current object is present and drop it
		IF OBJECT_ID('SubProcedure') IS NOT NULL
		BEGIN
			EXECUTE sp_executesql N'DROP PROCEDURE SubProcedure';
		END

		-- swap in the original object
		EXECUTE sp_rename 'SubProcedure_Original', 'SubProcedure';

	END

END TRY
BEGIN CATCH

	ROLLBACK;
	THROW;

END CATCH;

COMMIT;

And voilĂ ! Everything now ran perfectly!

Thoughts

SSDT Unit Testing is getting there. It doesn’t yet go head-to-head with the full functionality of tSQLt, like its dependency isolation capabilities (which, technically, are glorified cheating mechanisms), but it’s definitely getting there, and I hope some easier mocking abilities will get added soon.

However, it looks likely that we’ll never see true IoC development in T-SQL unless some major restructuring is done to the language itself. And why break something that works? That doesn’t mean we can’t get stuff done though.

How To Generate MERGE statements with SQLCMD

While it is usually a good idea to keep away from the injectionable fun of dynamic SQL, there are some cases where it does help you out a bit.
One such example is generating code in your SSDT post deployment scripts.

Configuring the bit of code below and including it in your SSDT post deployment script will allow you to pull in data automatically from another database on the same server (say a common data harness for the team) right after deploying your SSDT project onto a new or existing personal development database. The use of a fully configured MERGE statement means the entire table is synced, considering INSERTS, UPDATES, DELETES and even proper handling of NULL columns. And because the code is generated dynamically, it will be tolerant of design changes in the underlying tables.

The code is also SQLCMD friendly, feel free to adjust the variable names to whatever is better fit to your own project.

:setvar SourceDatabaseName MySourceDatabase
:setvar SourceSchemaName dbo
:setvar SourceTableName MySourceTable

:setvar TargetDatabaseName MyTargetDatabase
:setvar TargetSchemaName dbo
:setvar TargetTableName MyTargetTable

:setvar KeyColumn DATE_KEY

DECLARE @NL NCHAR(2) = NCHAR(13) + NCHAR(10);
DECLARE @TB NCHAR(1) = NCHAR(9);
DECLARE @TB2 NCHAR(2) = REPLICATE(@TB, 2);
DECLARE @TB3 NCHAR(3) = REPLICATE(@TB, 3);

DECLARE @SQL NVARCHAR(MAX) = '';

/* declare the source block */
SET @SQL += 'WITH Source AS' + @NL + '(' + @NL + @TB + 'SELECT';

/* list the columns from the target table */
SELECT
	@SQL += @NL + @TB2 + QUOTENAME(C.name) + ','
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);

/* add the rest */
SET @SQL += @NL + @TB + 'FROM' + @NL + @TB2 + '[$(SourceDatabaseName)].[$(SourceSchemaName)].[$(SourceTableName)]' + @NL + ')';

/* add the merge */
SET @SQL +=
	@NL + 'MERGE INTO [$(TargetDatabaseName)].[$(TargetSchemaName)].[$(TargetTableName)] AS T' +
	@NL + 'USING Source AS S' +
	@NL + 'ON S.[$(KeyColumn)] = T.[$(KeyColumn)]' +
	@NL + 'WHEN NOT MATCHED BY TARGET THEN' +
	@NL + 'INSERT' +
	@NL + '(';

/* list the columns to be inserted into */
SELECT
	@SQL += @NL + @TB + QUOTENAME(C.name) + ','
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);

/* close and add the values section */
SET @SQL +=
	@NL + ')' +
	@NL + 'VALUES' +
	@NL + '(';

/* list the columns to be sourced */
SELECT
	@SQL += @NL + @TB + QUOTENAME(C.name) + ','
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);

/* close and add the intermission */
SET @SQL +=
	@NL + ')' +
	@NL + 'WHEN NOT MATCHED BY SOURCE THEN' +
	@NL + @TB + 'DELETE' +
	@NL + 'WHEN MATCHED AND NOT' +
	@NL + '(';

/* list the columns to be compared */
SELECT
	@SQL +=
		@NL + @TB + '(' +
		@NL + @TB2 + 'T.' + QUOTENAME(C.name) + ' IS NULL AND S.' + QUOTENAME(c.name) + ' IS NULL OR' +
		@NL + @TB2 + '(T.' + QUOTENAME(C.name) + ' IS NOT NULL AND S.' + QUOTENAME(c.name) + ' IS NOT NULL AND T.' + QUOTENAME(C.name) + ' = S.' + QUOTENAME(C.name) + ')' +
		@NL + @TB + ')' +
		@NL + @TB + 'AND'
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 6);

/* close and add the update */
SET @SQL +=
	@NL + ')' +
	@NL + 'THEN' +
	@NL + @TB + 'UPDATE' +
	@NL + @TB2 + 'SET';

SELECT
	@SQL +=
		@NL + @TB3 + QUOTENAME(C.name) + ' = S.' + QUOTENAME(C.name) + ','
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
	AND C.name <> '$(KeyColumn)'
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);

/* done, let's not forget to close */
SET @SQL += @NL + ';'

/* change this into an sp_executesql once you're feeling brave */
SELECT @SQL;

Some upgrades can definitely be added to this code, namely:

  • Supporting multiple keys for comparison (or using the entire set of columns if the table has no keys).
  • Being able to fetch data from another server (perhaps through a linked server or OPENQUERY).
  • Wrapping this into a cleaner Stored Procedure. Considering this relies on SQLCMD, that might be a good challenge.

If you do end up adding this stuff, please comment below and I’ll add in those changes.

How To Create A Numbers Table In SSDT

Do you need to quickly add a numbers table to your SSDT Database Project? Here’s how.

Add a new table object to your database project, like so:

CREATE TABLE [dbo].[Numbers]
(
	Number INT NOT NULL,
	CONSTRAINT PK_Numbers 
		PRIMARY KEY CLUSTERED (Number)
		WITH (FILLFACTOR = 100, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF, DATA_COMPRESSION = ROW)
)

Note the details: FILLFACTOR = 100: This table will be effectively read-only, so we can make use of all the pages to minimize reads. ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF: With a read-only table there is no need for the overhead of row and page level locking, so we can evade these altogether. Queries will then escalate to table locking without additional help. DATA_COMPRESSION = ROW: This helps a bit in minimizing the footprint of the table on disk. ROW level is used instead of PAGE due to all the values in a page being unique, so little benefit to be had from dictionary compression algorithms.

Create a SQLCMD variable in your project named $(NumbersTableRowCount), or something else of your choosing, and assign it the count of numbers you want your table to have. Be sensible with this, you can always double up the rows in your queries. I tend to use 1M in order to make spoofing data easier, but this will be overkill for most systems.

Add the code below to your Post-Deployment Script:

PRINT 'Populating The Numbers Table';
GO
DECLARE @Count INT = CAST('$(NumbersTableRowCount)' AS INT);
IF (SELECT COUNT(*) FROM [dbo].[Numbers]) <> @Count
BEGIN
	WITH Numbers AS
	(
		SELECT
			1 AS Number
		WHERE
			1 < @Count
		UNION ALL
		SELECT
			Number + 1 AS Number
		FROM
			Numbers
		WHERE
			Number < @Count
	)
	MERGE INTO [dbo].[Numbers] AS T 
	USING Numbers AS S
	ON S.Number = T.Number
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (Number)
		VALUES (Number)
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE
	OPTION
		(MAXRECURSION 0)
	;
END
GO
ALTER INDEX PK_Numbers ON [dbo].[Numbers] REBUILD;
GO

Granted, the use of MERGE and a Recursive CTE there might be far from being the most efficient way to populate the table, but they do demonstrate two important principles in a database project.

  • No dependencies (if possible). Using a CTE means we’re not depending on other tables that might or might not be there in the future (like the commonly used spt_values), so the code becomes more portable between different versions of SQL Server.

  • Using a MERGE statement instead of an assortment of other statements abides by the principle of declarative programming. The [Numbers] CTE block defines “how” you want your table contents to look like, and then the MERGE merely enforces this against whatever is the current status of the database.

And you’re good to go! A Numbers Table is especially useful for running algorithms or spoofing data, like so:

WITH Source AS
(
	SELECT
		ROW_KEY = N.Number,
		ROW_NAME = 'Spoofed Row ' + CAST(N.Number AS VARCHAR(100))
	FROM
		dbo.Numbers AS N
	WHERE
		N.Number < 100000
)
MERGE INTO dbo.TARGET_TABLE AS T
USING Source AS S
	ON S.ROW_KEY = T.ROW_KEY
WHEN NOT MATCHED BY TARGET THEN
	INSERT (ROW_KEY, ROW_NAME)
	VALUES (ROW_KEY, ROW_NAME)
;