Unit testing: The tSQLt framework and the execution of a test!
In the previous article, the first one of this series, I described what the unit testing is and why it is important also in the database development using T-SQL language for SQL Server and Azure SQL Database.
This is the second article of the series, I will describe how to install the tSQLt framework, I will make a brief introduction to SQL Test, the Red-Gate tool that simplifies the management and execution of tests in SQL Server. At the end I will describe how the execution of a test case takes place. Let’s start!
How to install tSQLt framework
The installation of the tSQLt framework is very simple, point your browser here and you will find the Quick Start page in which you can download the zip file that contains the sql scripts for the installation (the last version of tSQLt is V1.0.7597.5637 and it is contained in the tSQLt_V1.0.7597.5637.zip file).
tSQLt requires that SQL CRL is enabled in the SQL Server instance used for development and for running tests. To enable SQL CLR, you can run the commands contained in the SetClrEnabled.sql inside the zip file. Since I would not have unit tests in production environment, this does not introduce a requirement for the production server.
Inside the zip file you will also find the Example.sql that allows you to create the tSQLt_Example database. tSQLt sorts out tests within a dedicated schema. A good practice is creating a tSQLt “class” for each entity you want to test separately. Inside the tSQLt_Example database you will find the AcceleratorTests schema that contains individual tests by way of stored procedures! The name of each stored procedure have to start with the keyword “test”, this naming convention tells to tSQLt that the stored procedure is a test case. Tests can be performed in two ways:
- Execution of all tests within the database
- Execution of a specific test or a specific test class
If you want to run all tests in the database, you have to use the tSQLt.RunAll stored procedure. If you want to run a specific test you have to use the tSQLt.RunTest stored procedure and if you want to run a specific test class you have to use the tSQLt.RunTestClass. Let’s try to run all tests in the tSQLt_Example database, we will get the following output in which we observe that all tests are executed successfully except this one: [AcceleratorTests]. [Test ready for experimentation if 2 particles].
If you want to obtain information about the tSQLt version, you can use the tSQLt.Info function that returns an output similar to the one shown below.
Because tSQLt resides within the database, different databases on the same Server can run different versions of tSQLt; anyway it is advised to make a single version of tSQLt as a standard to avoid different framework features in different databases.
Red-Gate SQL Test
When the installation of tSQLt has been completed, you can test your T-SQL code at any time, obviously, after writing the test cases. Unfortunately, writing unit tests is seen as a waste of time and the uncertainty of how to write them and where to start can represent a barrier in the development of tests.
Red-Gate SQL Test provides a nice user interface and some facilities for developing unit tests in T-SQL language. SQL Test is integrated into SQL Server Management Studio and facilitates the management, execution and monitoring of tests results. SQL Test provides the ability to create a new test, it will automatically place the keyword “test” at the beginning of the name of the stored procedure so that it is recognized by tSQLt as a test procedure. I think that this tool is also useful as a visual reminder for a SQL Developer to remind him/her that building unit tests is a part of the development work.
Creating a new test class is very simple as shown in the following picture.
Red-Gate SQL Test is independent from tSQLt framework installed in the database and as a consequence it is not even dependent from a specific version of tSQLt. You can try SQL Test by downloading it from the red-gate portal at this link.
How does a test run with tSQLt
It’s time to describe the internal structure of a test. You will have already guessed that each test is implemented by a stored procedure that must contain three parts also known as Arrange, Act and Assert (remember the three “A”).
The scenario of our test must be placed in the Arrange section. Typical activities performed in this section are the preparation of data on which the test will run and the isolation of the code from any external dependencies (related Tables, Triggers, Stored Procedures, etc.). In this section the expected result of the test will be built. The complexity of the Arrange section is proportional to the complexity of the test itself.
In the Act section, the system under test (SUT) will be executed and the output has been acquired as a result.
In the Assert section the expected result will be compared with the obtained one, the test will fail or will have a positive outcome according to this comparison. The test will be considered successful if the expected output will be the same as the one obtained by running the system under test (SUT).
When we run a test through tSQLt, the framework traces the running test and starts a dedicated transaction. The configuration procedure for the test class will be executed (if existing) and afterward the test will be executed. At the end of the test, the tSQLt framework will rollback the dedicated transaction that will take the database to the state it was before the starting of the test. The results will be stored in the tSQLt.TestResult table. When we run all tests of a class the process just described will be iterated for every test in the class.
The order in which the tests are performed must be irrelevant, the database will be restored to its initial state before starting the next test. Running all tests in the database we will see the process iteration for all test classes.
In the next article I will describe how to write our first unit test! In the meantime, enjoy the tSQLt framework and Red-Gate SQL Test!
