Esat Erkec
tSQLt framework unit test result

SQL unit testing best practices

April 8, 2019 by

SQL unit testing is a testing method which allows us to test the smallest, atomic programmable part of a database object. SQL unit testing plays a key role in the modern database development cycle because it allows us to test individual parts of the database objects work as expected. SQL unit testing adds a great worth to the database project because unit tests are more reliable then manual test methods.

I’ll now elaborate more on this idea; SQL unit testing increases confidence to the code because the developer fixes the flaws and defects of the code in initial stages of the development. The key point here is that production bug fixing is costlier than in the development phase. There is no doubt in that customers have little tolerance against errors in the applications therefore we must minimize bugs and malfunctions.

In terms of the database development, SQL unit testing is our as close to a silver bullet to minimize errors that we have available to us. In this article, you will find some suggestions for writing an effective SQL unit test and also we will make a bit of reverse engineering which means that we will examine a badly written unit test and then we will fix its errors according to the suggestions. In this way, we will clearly understand the differences between a bad and a good SQL unit test.

Note: If you don’t have a piece of basic knowledge about SQL unit testing and tSQLt framework details, see SQL unit testing with the tSQLt framework for beginners article.

Scenario

You have discovered a scalar-valued function and this function is tested by a tSQLt unit test. The following script belongs to this scalar-valued function and this function’s name is GetProductName and it returns a vegetable/fruit name in a very simple case statement. This scalar-valued function unit test was written by an unknown developer. You decided to fix this SQL unit test design and logical errors.

At first we will create the scalar-valued function through the following script.

Down below, we can find a very poorly written example of SQL unit testing which we mentioned in the previous section. In fact, this unit test case can be executed without any error; however, it does not fulfill the best suggestions according to unit test approach. Now, we will discuss details and straighten out defects of this unit test.

tSQLt framework unit test result

As you can see in the below image, the SQL unit test worked properly but it includes various problems. Let’s start to fix these issues.

Isolate the SQL unit tests from dependencies

The main purpose of SQL unit testing is to test the functional and independently programmable part; however, the tested object may have dependencies on other objects. If we face these circumstances, we must isolate the SQL unit test from other dependencies. The main reason why we do this is to avoid fragility of the unit test. Now, we will apply this idea over the example unit test. In this SQL unit test the expected value is taken from Tbl_TestName, but, this table data might be change so this changing will directly affect the result of the SQL unit test. However, this changing should not influence the GetProductName scalar-valued function unit test case. Hence, we must isolate the Tbl_TestName dependency from our SQL unit test. But how we can achieve that?

Mocking is the right answer for this issue. Mocking can be described like this; we create a fake object which simulates the behavior of the real object during the unit test so that we can isolate the dependencies of the unit test. tSQLt framework allows us to create mock tables through the tSQLt.FakeTable stored procedure. It takes a table name as a parameter, so, it creates an empty but otherwise duplicate of the original table without constraints. If we look to the working mechanism of the tSQLt.FakeTable stored procedure, it renames the original table name during the test execution and it creates a copy of the original table and test operations use this table.

In our example, we will create a fake Tbl_TestName table and then we will insert new values to this fake table so that we can compare the expected and actual value which returns from the scalar-valued function. Because during the test execution the GetProductName function will retrieve data from the fake table, in this way we can ensure the expected value and also we will prevent external impacts.

Let’s apply it to our example. As you can see in the below, we will create a fake version of the Tbl_TestName table with help of the tSQLt.FakeTable procedure and we will insert some values to it so that the function always returns max value which we inserted. Also, this insert operation does not affect the original table data.

In the Tbl_TestName, we could not find any watermelon, grape and mango record.

tSQLt faketable does not affect original table

In this section, we will fix the isolation problem of the sample SQL unit set; however, it includes other flaws. In further sections, we will continue to discuss it and fix these problems.

Avoid poor SQL unit test naming

SQL unit test names must be easily understood by being self-describing and implying intent because we must figure out some question answers without a look at the SQL unit test code. In the point of this view; good naming offers a more readable SQL unit test so that it facilitates maintenance. We can create our own naming conventions, or we can find unit test naming conventions. Here, I want to notice about that, tSQLt framework unit tests have to start with “test” prefix if you don’t do this you can’t run your unit tests. Now, we will fix the badly written example name with the following naming convention.

Prefix

object name

test description

expected result

test

GetProductName

casewhen1withfaketable

Exepected_Apple

tSQLt framework unit test result

As possible as don’t use more than one assert in the SQL unit tests

Our sample SQL unit test includes more than one assert, but this type of usage is not recommended because when the first test case fails, we don t have any idea following test case results for this reason if it is possible we have not used to multiple asserts in the SQL unit test. Now, we will prove it with a very simple example and then we will fix our example.

tSQLt unit test multiple asserts

As you can see in the image, we can’t see any information about second test case. For this reason, we need to separate multiple asserts to single ones. At first, we will apply naming conventions to our SQL unit test and then we will create separated unit tests.

Prefix

object name

test description

expected result

test

GetProductName

casewhen1withfaketable

Expected_Mango

test

GetProductName

casewhen2

Expected_Tomato

test

GetProductName

casewhen3withfaketable

Expected_Banana

test

GetProductName

casewhen4elsewithfaketable

Expected_NotFound

tSQLt seperated unit tests result

As you can see, we created four SQL unit test because GetProductName function will return four different results according to given parameter, so that we can test all behavior of the GetProductName scalar-valued function. In addition, I want to discuss one point, the tSQLt framework allows us to create individual test class so that we can gather similar SQL unit tests in the same test class. This approach takes advantage of running this unit test classes separately.

Another benefit of using single asserts in a SQL unit test is that it allows us to develop a more readable, simple and understandable SQL unit test.

Finally, we fixed various issue of a badly written SQL unit test. A well-written SQL unit test can provide the following advantages;

  • It is readable, understandable and simple
  • It is robust and does not affect from dependencies
  • It is manageable and easily redesigned

Conclusion

In this article, we discussed and learned some suggestions concerning SQL unit testing and applied these suggestions to a badly written unit test and then we fixed it. Now, let us again highlighted three main suggestions;

  • Isolate SQL unit tests from dependencies
  • Consider proper SQL unit test names
  • Avoid multiple asserts

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
2,004 Views