QuickQuestion: Can I mock a table in another DB using tSQLt?

QuickQuestion series is a series of short posts in which I answer database related questions asked by my colleagues, friends, and co-workers, mainly application developers.

Today’s question:

Can I mock a table in another database using tSQLt?

Nowadays, with the microservices pattern for application development, it becomes more and more common that our product has more than one database. It may happen (especially during the transition from monolith database to the single database per microservice) that objects from one database have to access objects in another one. That makes testing harder, but as you will see, it is not impossible.

What is tSQLt?

For those of you who don’t know what is tSQLt, I hurry to explain. It is a free and opensource framework for writing database unit tests. It is written in T-SQL and C# .NET (CLR). It provides a lot of different assertions that make testing relatively easy and allows you to get detailed results. However, its main and biggest advantage is the ability to mock database objects. tSQLt framework is available on GitHub.

What is mocking?

Mocking is primarily used in unit testing. It is rather unseen in databases but is very popular in other programming languages like Java, C#, C++…

An object under test may have dependencies on other (complex) objects. To isolate the behavior of the object you want to test, you want to replace the other objects by mocks that simulate the behavior of the real objects. This is very useful if the real objects are impractical to incorporate into the unit test. In short, mocking is creating objects that simulate the behavior of real objects.

How to mock a table with tSQLt?

The framework provides tSQLt.FakeTable Stored Procedure that can be used to mock tables.

tSQLt.FakeTable [@TableName = ] 'table name'
                , [[@SchemaName = ] 'schema name']
                , [[@Identity = ] 'preserve identity']
                , [[@ComputedColumns = ] 'preserve computed columns']
                , [[@Defaults = ] 'preserve default constraints']

Usage is very simple, the only required parameter is @TableName. It’s the name of the table for which we want to create a fake table. Parameter value should contain both the schema name and the table name (parameter @SchemaName is deprecated).

Example:

USE [LocalDB];
GO

BEGIN TRAN
    SELECT * FROM dbo.Orders;
    EXEC tsqlt.FakeTable 'dbo.Orders';
    SELECT * FROM dbo.Orders;
ROLLBACK TRAN

SELECT * FROM dbo.Orders;

As you can see it works like a charm. After execution of tsqlt.FakeTable we got an empty table that is ready to be used in our Unit Tests. After transaction rollback, everything is as it was at the beginning. However, if you try to mock a table in another database it will not work… Let’s have a look.

USE [LocalDB];
GO

BEGIN TRAN
    SELECT * FROM RemoteDB.dbo.Customers;
    EXEC tsqlt.FakeTable 'RemoteDB.dbo.Customers';
    SELECT * FROM RemoteDB.dbo.Customers;
ROLLBACK TRAN

SELECT * FROM RemoteDB.dbo.Customers;

Unfortunately, the table was not mocked. SELECT statement returned data from the original table. So… Does it mean it is not possible?

Solution

Don’t worry. It is possible! After few attempts and time spent on debugging of tSQLt internal mechanics, a working solution has been discovered. Here it is…

USE [LocalDB];
GO

BEGIN TRAN
    SELECT * FROM RemoteDB.dbo.Customers;
    EXEC RemoteDB.tsqlt.FakeTable 'dbo.Customers';
    SELECT * FROM RemoteDB.dbo.Customers;
ROLLBACK TRAN

SELECT * FROM RemoteDB.dbo.Customers;

Instead of providing object from another database as a parameter for tsqlt.FakeTable stored procedure we call now tsqlt.FakeTable explicitly in this second database. And here is the result… ta-da!

You’re welcome 🙂

-Marek

Share it: