Esat Erkec
SQL Unit testing - tSQLt framework test result

How to use fake tables in SQL unit testing?

April 22, 2019 by

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;

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.

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.

tsql unit testing - tSQLt test tables relation diagram

Now we will prove this issue through the following query.

Foreign key violation error.

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.

tsql unit testing - tSQLt framework faking tables result

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.

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.

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.

SQL Unit testing - tSQLt framework faking table error

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.

SQL Unit testing - tSQLt framework test result

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.

SQL Unit testing - Computed column and tSQLt framework

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.

Table of contents

SQL Unit Testing with SSDT (SQL Server Data Tools)
SQL unit testing with the tSQLt framework for beginners
SQL Unit testing with the tSQLt framework and SQL Server Database Project integration
SQL unit testing best practices
How to use fake tables in SQL unit testing?
How to use fake functions with SQL unit testing?
SQL Unit Testing Mocking Stored Procedures
SQL Unit Testing: Working with exceptions

Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec
Esat Erkec
168 Views