Esat Erkec
SQL Unit testing - - tSQLt extended properties

SQL unit testing with the tSQLt framework for beginners

March 18, 2019 by

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.

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.

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.

SQL Unit testing - 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.

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

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.

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.

SQL Unit testing - - tSQLt extended properties

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.

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.

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.

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.

SQL Unit testing - - tSQLt test results

SQL Unit testing - - tSQLt test results

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.

SQL Unit testing - - tSQLt test results

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.

SQL Unit testing - - tSQLt test results

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.

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

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?

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
Testing

About 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

436 Views