tSQLt is a powerful, open source framework for SQL Server unit testing. In this article, we will mainly focus on how to create and run SQL unit testing with help of the tSQLt framework. Before we begin to learn tSQLt framework details, let’s discuss essentials and importance of the database unit testing approach, in general. SQL unit testing is a vital and inseparable part of the modern database development approach and it makes possible to prevent errors before producing the release deployment process. Some database developers are still discussing the needs of the SQL unit testing concept; however, database unit testing is very significant to control and check the behavior of the individual parts of the database. For this reason, we cannot ignore the need to write unit test cases.
Essentially, unit tests provide code coverage over database objects (stored procedures, triggers, functions etc.) behavior so that we can automatically regression test changes. In terms of SQL unit testing, the tSQLt framework offers several advantages. Some of those are described in the following bullets with detailed descriptions.
Benefits of the tSQLt framework:
- Enables using T-SQL codes in unit tests. This is the most important advantage of tSQLt because we don’t need to learn new programing language or platform to create and run SQL unit test, such as if you want to write a new test for particular user-defined function; we just need a tSQLt installed database and SQL Server Management Studio or any query editor
- Unit tests are automatically running in the transaction log. We don’t need any data cleanup work after the unit tests because every data manipulation process rolls back after the unit test
- Allows using mocked (fake) objects. A mocked object simulates the real object’s behavior so the tested objects do not affect other dependencies and we can also create isolated SQL unit tests. For example, our aim is to write unit test for a particular stored procedure, but this stored procedure includes a user-defined function, however, we have to isolate stored procedure unit test from this user-defined function. We can achieve this idea by writing a mock function which is related to the tested stored procedure
- Completely free and open source. tSQLt is a free and open source project, so, we can use it without any charge
- It can be integrated into SSDT projects or 3rd party software. If you want to integrate tSQLt framework to Visual Studio you can find it on tSQLt Test Adapter for Visual Studio 2017. Or you can use a 3rd party product like ApexSQL Unit Test
How to install tSQLt
tSQLt installation is very easy. At first, we will download the zipped file from tsqlt.org in the download section. Then we need to enable CLR (SQL Common Language Runtime) in the SQL server instance because tSQLt requires this option. Run the following script to enable CLR functionally of the SQL Server afterwards we ensure to running vales (run_value) of this option.
|
1 2 3 4 |
EXEC sp_configure 'clr enabled', 1; RECONFIGURE; GO EXEC sp_configure 'clr enabled' |
We must enable TRUSTWORTHY property of the database in which we want to install tSQLt framework.
Note: In the following demonstrations, we will use WideWorldImporters sample database.
|
1 2 3 |
USE WideWorldImporters GO ALTER DATABASE WideWorldImporters SET TRUSTWORTHY ON; |
In the second step of the installation process, we will install tSQLt framework into WideWorldImporters database. Therefore, we need to execute tSQLt.class sql query file which is placed in the downloaded file.
We will open tSQLt.class file in the SQL Server Management Studio and then execute it. If your installation succeeds, you will see the version of the tSQLt and thanks message.
In here, I want to add a notice about the tSQLt framework and Azure SQL database collaboration. The main question about this issue is how to install the tSQLt framework to Azure SQL. If we want to install and work tSQLt framework on Azure SQL database we don’t need to enable CLR and TRUSTWORTHY options, we can simply execute tSQLt.class file in the Azure SQL database so that we can use tSQLt framework on Azure SQL. The result set of the following query shows the tSQLt framework objects list which are installed to Azure SQL database also Dbversion column indicates version of the Azure SQL database.
|
1 2 3 4 |
SELECT @@VERSION,name FROM sys.objects sysobj where schema_id = ( select sch.schema_id from sys.schemas sch where name='tSQLt' ) order by sysobj.name |
Our first SQL unit test through tSQLt
The tSQLt framework offers several different test methods to us. However, at first, we need to create a test class, because the test class collect the test cases under this class. The below script creates a new test class whose name is DemoUnitTestClass
|
1 2 3 |
USE WideWorldImporters GO EXEC tSQLt.NewTestClass 'DemoUnitTestClass'; |
When we create a new test class, in the back of the scene tSQLt creates a schema and adds extended property with value so that tSQLt framework can easily figure out this schema is created for test class. The following query proof this point.
|
1 2 3 4 |
select SCHEMA_NAME,objtype,name,value from INFORMATION_SCHEMA.SCHEMATA SC CROSS APPLY fn_listextendedproperty (NULL, 'schema', NULL, NULL, NULL, NULL, NULL) OL WHERE OL.objname=sc.SCHEMA_NAME COLLATE Latin1_General_CI_AI and SCHEMA_NAME = 'DemoUnitTestClass' |
And also we can find out this value in the extended event properties of the test class (schema).
- Open the Security->Schemas node under the database folders.
- Right click into DemoUnitTestClass and navigate to Properties
- Select the Extended Properties page.
In addition, if you want to drop the created test class, we can use the following stored procedure. It takes a test class name as a parameter.
|
1 |
EXEC tSQLt.DropClass 'DemoUnitTestClass' |
If you run the DropClass stored procedure, it does not only drop the test class (schema), but it also removes whole test objects which are related to this class. Another consideration about recreating test class with the same name. In this case, tSQLt framework acts as similar to DropClass procedure. In the first step it removes whole test objects that are related to test class and then creates an empty test class with the same name.
Unit test methods of the tSQLt framework
The tSQLt framework offers a bunch of test methods for SQL unit testing operations so that we can use this method under different circumstances.
- tSQLt.AsserEquals
- tSQLr.AssertEqualsTable
- tSQLt.AssertEmptyTable
- tSQLt.AssertEqualsString
- tSQLt.AssertEqualsTableSchema
- tSQLt.AssertLike
- tSQLt.AssertNotEquals
- AssertObjectDoesNotExist
- AssertObjectExists
- AssertResultSetsHaveSameMetaData
- Fail
Let’s learn some details of this method and reinforce with examples.
tSQLt.AsserEquals: This method allows us to compare the expected and actual values and it takes three input parameters;
@expected: This parameter specifies expected value namely the result of the test value compared with this value.
@actual: This parameter specifies the result of the test.
@message: If the unit test returns fail, we can customize the error message with the help of the parameter.
Now, we will create a very basic unit test through the tSQLt framework. In the following unit test scenario, we will create a user-defined function which will calculate the tax value of the given parameter afterwards. We will write a unit test case according to this user function. As we have already noticed about that, the main idea of the unit testing is checking behavior of individual, programmable and smallest (atomic) part of the database object.
|
1 2 3 4 5 6 7 8 |
CREATE OR ALTER FUNCTION CalculateTaxAmount(@amt MONEY) RETURNS MONEY AS BEGIN RETURN (@amt /100)*18 END; GO select dbo.CalculateTaxAmount(100) AS TaxAmount |
As you can see in the above illustration when we send 100 as a parameter into CalculateTaxAmount function and it returns 18. Now, we will write a test case to check this user function behavior.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
EXEC tSQLt.NewTestClass 'DemoUnitTestClass'; GO CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount] AS BEGIN DECLARE @TestedAmount as money = 100 DECLARE @expected as money = 18 DECLARE @actual AS money SET @actual = dbo.CalculateTaxAmount(100) EXEC tSQLt.AssertEquals @expected , @actual END |
In order to run the test case, we can use tSQLt.Run method. It can take test class name as a parameter so that it can run all the unit tests which are related to this test class or it takes test case name and then it executes particularly.
|
1 |
tSQLt.Run 'DemoUnitTestClass.[test tax amount]' |
|
1 |
tSQLt.Run 'DemoUnitTestClass' |
As you can see in the above images, our test is successful and acceptable. Now, we will change the expected result in the test case and then re-run the unit test.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount] AS BEGIN DECLARE @TestedAmount as money = 100 DECLARE @expected as money = 20 DECLARE @actual AS money SET @actual = dbo.CalculateTaxAmount(100) EXEC tSQLt.AssertEquals @expected , @actual END GO tSQLt.Run 'DemoUnitTestClass.[test tax amount]' |
When we read the messages about the unit test result, it is obviously explaining everything about the unit test issue. In the first line, we can find out the main problem of the unit test. It clarifies that the expected and actual values are different.
I want to mention about unit test case naming conventions. The test’s case names must begin with “test” and if we don’t do this tSQLt framework, we cannot find and execute it. As we noticed about the message parameter of the tSQLt.AsserEquals method. Now, we will implement this test message to our unit test.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount] AS BEGIN DECLARE @TestedAmount as money = 100 DECLARE @expected as money = 20 DECLARE @actual AS money DECLARE @Message AS VARCHAR(500)='Wrong tax amount' SET @actual = dbo.CalculateTaxAmount(100) EXEC tSQLt.AssertEquals @expected , @actual ,@Message END GO tSQLt.Run 'DemoUnitTestClass.[test tax amount]' |
So far, we mentioned about installation and essentials of the tSQLt framework, but in the real world, unit test scenarios are much complex than these examples. For this reason, we must understand Arrange, Act and Assert pattern well according to SQL unit testing. This pattern suggests separating unit tests into three parts so that we can write more clear and readable tests.
Arrange: In this part, we can declare the variables, or we can define preconditions and inputs.
Act: In this part, we can execute the code that is under the test and then capture the result of the executed code.
Assert: In this part, we compare the expected and actual value.
Now, we will specify this part for our previous unit test example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount] AS BEGIN ----------------------Arrange----------------------------- DECLARE @TestedAmount as money = 100 --- DECLARE @expected as money = 20 --- DECLARE @actual AS money --- DECLARE @Message AS VARCHAR(500)='Wrong tax amount' --- ---------------------------------------------------------- ----------------------Act--------------------------------- SET @actual = dbo.CalculateTaxAmount(100) --- ---------------------------------------------------------- ----------------------Assert------------------------------ EXEC tSQLt.AssertEquals @expected , @actual ,@Message --- ---------------------------------------------------------- END |
Conclusion
In this article, we mentioned about the importance of the SQL unit testing and how to act this approach through the tSQLt framework. First of all, we need to well understand to the main logic of the tSQLt framework essentials so that we can develop more complex test cases.
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