How to use fake tables in SQL unit testing?

In this article on SQL unit testing, we will talk about how to isolate SQL unit tests from dependencies and how to use fake tables in SQL unit tests, so that we will able to develop more robust and less fragile SQL unit tests.

At first, let’s make a basic definition of the SQL unit testing approach. The main purpose of the SQL unit test is to test each programmable and atomic part of the database objects (stored procedure, function and etc.) to ensure that these programmable parts work correctly. According to this definition, SQL unit testing focuses on programmable and atomic parts, for this reason, we should prevent external factors and other influences which affects the SQL unit tests behaves because dependencies may cause the SQL unit tests fails to fail. At this point, let’s try to find out the answer of “Why we struggle to isolate SQL unit test from dependencies” over a scenario.

Imagine that, we want to test a stored procedure and this stored procedure involves a table and this table data directly affects the stored procedure result set. Under these circumstances, we need to avoid a dependency of this table in the stored procedure just because our aim is to test the behavior of the stored procedure. That’s why we must create fake table and use these fake table in the SQL unit test. In this way, SQL unit test merely focuses on the programmable part and more accurate test result occurs.

Note: Before going through the details of SQL unit test faking, I want to notice that if you don’t have much experience or knowledge about SQL unit testing and tSQLt framework, I would like to suggest to take a glance on the previous articles (see the TOC at bottom) which are related to SQL unit testing.

FakeTable introduction and syntax

As we mentioned in the entrance part of the article, we must isolate unit tests from dependencies, particularly SQL unit test results might be directly affected by data which is stored in the tables. That’s why, we should isolate the SQL unit tests from external and uncontrolled table data because that might be influence the result of the SQL unit test.

The tSQLt framework includes a FakeTable stored procedure and it is the good and appropriate solution for these circumstances. FakeTable creates empty copy of the original table during the test period so that we can take control of the table during the SQL unit test. Another benefit of using FakeTable is that DML (Data Manipulation Language) operations do not affect the original table because all operation is running in a transaction block.

Faketable takes various parameters as shown in the below;

tSQLt.FakeTable

[@TableName = ] 'table name'

, [[@SchemaName = ] 'schema name'] --Deprecated: do not use, will be removed in future version

, [[@Identity = ] 'preserve identity']

, [[@ComputedColumns = ] 'preserve computed columns']

, [[@Defaults = ] 'preserve default constraints']

FakeTable usage example

Let’s make a simple demonstration to reinforce all this theoretical information. At first, we will create our sample tables which we will use in the following SQL unit test example.

DROP TABLE IF EXISTS OrderLine

DROP TABLE IF EXISTS OrderHeader

CREATE TABLE OrderHeader (OrderId INT PRIMARY KEY , OrderName VARCHAR(50))

CREATE TABLE OrderLine (OrderLineId INT PRIMARY KEY ,

OrderId INT FOREIGN KEY REFERENCES OrderHeader(OrderId)

,OrderAmnt INT,OrderNet AS (OrderAmnt*2) PERSISTED,OrderDate DATE DEFAULT GETDATE())

CREATE OR ALTER PROC GetOrderAmntYear

select YEAR(OrderDate) As OrderYear,SUM(OrderAmnt) As Amnt

from OrderLine GROUP BY

YEAR(OrderDate)

As you can see in the below illustration, OrderLine table has a foreign key constraint, so we cannot insert any induvial row to OrderLine table, unless to insert a related row to OrderHeader table.

Now we will prove this issue through the following query.

1	INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(20,'20190101')

As you can see in the above image, if we want to insert some test rows to OrderLine table, at first we have to insert referential rows to OrderHeader table otherwise we experienced a foreign key violation error message is returned.

On the other hand, GetOrderAmntYear returns a total of the OrderAmnt columns per year and we want to test this stored procedure. This stored procedure only use OrderLine table so we must break this foreign key dependency. At this point, we have to notice another important thing, in the database development environments we can’t take control under these tables’ data, because some developers or dbas data can be added or deleted data from these tables. For this reason, we cannot be sure result set of the stored procedure. This situation influences the behavior of the SQL unit test. Let’s ask the notable question, “How can we overcome this issue in the tSQLt framework?”

The tSQLt framework involves a stored procedure which name is FakeTable. Now let’s learn FakeTable usage through the following example. At first, we will create a test class and then write SQL unit test.

EXEC tSQLt.NewTestClass 'MockTableTest'

CREATE OR ALTER PROCEDURE MockTableTest.[Test_GetOrderAmntYearStoredProcedure_PerYears]

DROP TABLE IF EXISTS actual

DROP TABLE IF EXISTS expected

CREATE TABLE actual (OrderYear INT,Amnt INT)

CREATE TABLE expected (OrderYear INT,Amnt INT)

EXEC tSQLt.FakeTable 'OrderLine'

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(20,'20190101')

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(8,'20190101')

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(12,'20170101')

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(3,'20160101')

INSERT INTO actual

EXEC GetOrderAmntYear

INSERT INTO expected (OrderYear,Amnt)

VALUES(2019,28),(2017,12),(2016,3)

EXEC tSQLt.AssertEqualsTable 'expected', 'actual';

EXEC tSQLt.Run 'MockTableTest.[Test_GetOrderAmntYearStoredProcedure_PerYears]'

Now, we will tackle this SQL unit test code row by row. In the below part of the SQL unit test we create a fake copy of the OrderLine table and then we insert random test values. The crucial point is that; during the SQL unit test OrderLine table contains solely the test rows so that we can exactly know the returning result set of the GetOrderAmntYear stored procedure. This exactness allows us to make a proper comparison between actual and expected tables. On the other hand, FakeTable allows us to create a plain table so that we can deactivate the constraints in the fake table. Constraints sometime might be trouble-maker for us during the test period however we can determine and deactivate constraints with help of the FakeTable parameter.

EXEC tSQLt.FakeTable 'OrderLine'

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(20,'20190101')

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(8,'20190101')

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(12,'20170101')

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(3,'20160101')

In the following part; we insert the tested stored procedure result set to the actual table.

INSERT INTO actual

EXEC GetOrderAmntYear

In the following part; we create the expected table with values.

INSERT INTO expected (OrderYear,Amnt)

VALUES(2019,28),(2017,12),(2016,3)

This is the last and most important part of the SQL unit test because we compare expected and actual tables row by row through the AssertEqualsTable. AssertEqualsTable compares the actual and expected table’s data and then return the result of this matching.

1	EXEC tSQLt.AssertEqualsTable 'expected', 'actual';

As a result, if we require to isolate SQL unit test from tables, Faketable will be a good choice for us so that SQL unit test will be more robust and less brittle. Now, we will delete an inserted row from the actual table and then investigate the result of the unit test. In short, we will knowingly change the SQL unit test to return incorrect result.

CREATE OR ALTER PROCEDURE MockTableTest.[Test_GetOrderAmntYearStoredProcedure_PerYears]

DROP TABLE IF EXISTS actual

DROP TABLE IF EXISTS expected

CREATE TABLE actual (OrderYear INT,Amnt INT)

CREATE TABLE expected (OrderYear INT,Amnt INT)

EXEC tSQLt.FakeTable 'OrderLine'

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(20,'20190101')

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(8,'20190101')

INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(12,'20170101')

--INSERT INTO OrderLine (OrderAmnt ,OrderDate) VALUES(4545,'20160101')

INSERT INTO actual

EXEC GetOrderAmntYear

INSERT INTO expected (OrderYear,Amnt)

VALUES(2019,28),(2017,12),(2016,3)

EXEC tSQLt.AssertEqualsTable 'expected', 'actual';

EXEC tSQLt.Run 'MockTableTest.[Test_GetOrderAmntYearStoredProcedure_PerYears]'

As you can see in the above illustration, the result of the unit test returns an error and it offers comprehensive information about non-matching rows. At first, we will learn the definition of the signs;

“<” specifies that the row exists in the expected table, but it does not exist in the actual table
“>” specifies that the row exists in the actual table, but it does not exist in the expected table
“=” specifies that the row already exists in the actual and expected tables

Considering this information, we can find out that the following row does not exist in the actual table.

As a result, we developed a SQL unit test with the help of the tSQLt framework. In this example, we created a SQL unit test in order to check result of the GetOrderAmntYear stored procedure. So that if somebody will make any structural changing in this stored procedure the SQL unit test will return an error so that we can recognize the issue about it. Sometimes this issue indicates a flaw or a bug. On the other hand, this change may be related to the product and may be made in the development process routine. In that case, we have to change old SQL unit test because it is not valid to test behavior of the stored procedure or we can write a new SQL unit test.

FakeTable and computed columns

Faketable can take various parameters so that we can use it for different cases in the SQL unit test cases. Such as, if you want to preserve the computed columns in the fake table, we can set the ComputedColumns parameters as 1. Let’s make an example of it. In the following example, we will check the calculated column so that we can be sure about computed column calculation works proper.

CREATE OR ALTER PROCEDURE MockTableTest.[test_OrderLineTable_CalculatedOrderNetColumn]

DROP TABLE IF EXISTS actual

DROP TABLE IF EXISTS expected

CREATE TABLE actual (OrderNet INT)

CREATE TABLE expected (OrderNet INT)

EXEC tSQLt.FakeTable @TableName='OrderLine',@ComputedColumns=1

INSERT INTO OrderLine (OrderAmnt ) VALUES(20)

INSERT INTO OrderLine (OrderAmnt ) VALUES(8)

INSERT INTO OrderLine (OrderAmnt ) VALUES(12)

INSERT INTO actual

SELECT OrderNet FROM OrderLine

INSERT INTO expected (OrderNet)

VALUES(40) , (16) ,(24)

EXEC tSQLt.AssertEqualsTable 'expected', 'actual';

EXEC tSQLt.Run 'MockTableTest.[test_OrderLineTable_CalculatedOrderNetColumn]'

As you can see in the above image; the test completed with success.

Conclusion

In this article, we discussed the SQL unit test dependency isolation approach. Dependency isolation is very significant for the SQL unit testing because it enables to develop more effective SQL unit tests so that it increases the code quality. In this article, we specifically mentioned how to use FakeTable stored procedure in the tSQLt framework and explain it with some examples.