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.

On Distributed Computing