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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DROP TABLE IF EXISTS Tbl_TestName CREATE TABLE Tbl_TestName (Id INT , DefName VARCHAR(100)) INSERT INTO Tbl_TestName VALUES (1,'Apple') , (2,'Avocado'), (3 , 'Carrot') GO CREATE OR ALTER FUNCTION GetProductName (@Nm INT) RETURNS VARCHAR(100) BEGIN DECLARE @RetVal AS VARCHAR(100) SELECT @RetVal = CASE @Nm WHEN 1 THEN (SELECT TOP 1 DefName FROM Tbl_TestName ORDER BY Id DESC) WHEN 2 THEN 'Tomato' WHEN 3 THEN 'Banana' ELSE 'Not Found' END RETURN @RetVal END |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
EXEC tSQLt.NewTestClass 'Func_ProductTest' GO CREATE OR ALTER PROCEDURE Func_ProductTest.[Test product function] AS DECLARE @Expected AS VARCHAR(100)='Carrot' DECLARE @Actual AS VARCHAR(100) SELECT @Actual = dbo.GetProductName(1) EXEC tSQLt.AssertEquals @Expected,@Actual SET @Expected='Tomato' SELECT @Actual = dbo.GetProductName(2) EXEC tSQLt.AssertEquals @Expected,@Actual SET @Expected='Banana' SELECT @Actual = dbo.GetProductName(3) EXEC tSQLt.AssertEquals @Expected,@Actual SET @Expected='Not Found' SELECT @Actual = dbo.GetProductName(4) EXEC tSQLt.AssertEquals @Expected,@Actual GO EXEC tSQLt.Run 'Func_ProductTest.[Test product function]' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR ALTER PROCEDURE Func_ProductTest.[Test product function] AS DECLARE @Expected AS VARCHAR(100)='Mango' EXEC tSQLt.FakeTable 'Tbl_TestName' INSERT INTO Tbl_TestName VALUES (1,'Watermelon') INSERT INTO Tbl_TestName VALUES (2,'Grape') INSERT INTO Tbl_TestName VALUES (3,'Mango') DECLARE @Actual AS VARCHAR(100) SELECT @Actual = dbo.GetProductName(1) GO EXEC tSQLt.Run 'Func_ProductTest.[Test product function]' |
In the Tbl_TestName, we could not find any watermelon, grape and mango record.
|
1 |
SELECT * FROM Tbl_TestName |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhen1withfaketable Expected_Mango] AS DECLARE @Expected AS VARCHAR(100)='Mango' EXEC tSQLt.FakeTable 'Tbl_TestName' INSERT INTO Tbl_TestName VALUES (1,'Watermelon') INSERT INTO Tbl_TestName VALUES (2,'Grape') INSERT INTO Tbl_TestName VALUES (3,'Mango') DECLARE @Actual AS VARCHAR(100) SELECT @Actual = dbo.GetProductName(1) EXEC tSQLt.AssertEquals @Expected,@Actual GO EXEC tSQLt.Run 'Func_ProductTest.[test GetProductName casewhen1withfaketable Expected_Mango]' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
EXEC tSQLt.NewTestClass 'Multiple_Assert' GO CREATE OR ALTER PROCEDURE Multiple_Assert.[test multiple assert test] AS DECLARE @Expected AS INT = 1 DECLARE @Actual AS INT = 2 EXEC tSQLt.AssertEquals @expected ,@actual DECLARE @ExpectedTest2 AS VARCHAR(10) = 'A' DECLARE @ActualTest2 AS VARCHAR(10) = 'Z' EXEC tSQLt.AssertEquals @ExpectedTest2 ,@ActualTest2 GO EXEC tSQLt.run 'Multiple_Assert.[test multiple assert test]' |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
EXEC tsqlt.NewTestClass 'Func_ProductTest' GO CREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhen1withfaketable Expected_Mango] AS DECLARE @Expected AS VARCHAR(100)='Mango' DECLARE @Actual AS VARCHAR(100) EXEC tSQLt.FakeTable 'Tbl_TestName' INSERT INTO Tbl_TestName VALUES (1,'Watermelon') INSERT INTO Tbl_TestName VALUES (2,'Grape') INSERT INTO Tbl_TestName VALUES (3,'Mango') SELECT @Actual = dbo.GetProductName(1) EXEC tSQLt.AssertEquals @Expected,@Actual GO CREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhen2 Expected_Tomato] AS DECLARE @Expected AS VARCHAR(100)='Tomato' DECLARE @Actual AS VARCHAR(100) SELECT @Actual = dbo.GetProductName(2) EXEC tSQLt.AssertEquals @Expected,@Actual GO CREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhen3 Expected_Banana] AS DECLARE @Expected AS VARCHAR(100)='Banana' DECLARE @Actual AS VARCHAR(100) SELECT @Actual = dbo.GetProductName(3) EXEC tSQLt.AssertEquals @Expected,@Actual GO CREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhenelse Expected_NotFound] AS DECLARE @Expected AS VARCHAR(100)='Not Found' DECLARE @Actual AS VARCHAR(100) SELECT @Actual = dbo.GetProductName(4) EXEC tSQLt.AssertEquals @Expected,@Actual GO EXEC tSQLt.Run 'Func_ProductTest' |
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
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023