This article is an overview of tSQLt, which is one of the best SQL unit testing frameworks, due to number of reasons, including the ease of writing unit tests in the same language (T-SQL) in which the database objects themselves are written.
Most of this article is knowledge based and has been written keeping in mind the first time tSQLt and SQL unit testing learners.
This article also highlights the very compatible nature of tSQLt with core concepts of SQL unit testing there by making it best fit for the purpose along with the things which are hard to notice in the fast paced SQL unit testing world but serve as driving force to build state of the art database unit tests.
SQL unit testing Core Concepts and tSQLt
It is very unlikely for an experienced database developer or database tester to be unaware of the core concepts of SQL unit testing, but it is worth reviewing them (if you are already familiar) especially when they are discussed in relation to tSQLt.
What is SQL unit testing?
SQL unit testing as the name suggests is testing individual units of the database by writing unit tests which are also called test cases and tSQLt makes this task easy and instant.
What is a Database Unit?
A database unit is any database object such as view or stored procedure which contributes partially or fully to meet business requirement directly or indirectly and tSQLt is capable of giving full coverage to all the database objects in a database.
When do you Create Database Unit Tests?
Database unit tests are created right from the database development phase alongside creating database objects and tSQLt makes it easy to do that and lets you create these unit tests instantly.
Should you Create Unit Tests Before or After Database Objects?
In conventional database development pattern, you create database objects first and then you write database unit tests for those objects, however, this is not always the case.
You can create database unit tests before or after creating a database object and tSQLt supports both testing patterns (which are discussed in detail in the upcoming articles of this series).
Who Writes Database Unit Tests?
Mostly, database developers who create database objects also create database unit tests and learning tSQLt to write database unit tests is easier than other SQL unit testing frameworks.
Who Runs Database Unit Tests?
Database Unit Tests are generally run by the development team members who write them in first place followed by test team as a part of manual or automated process during the build and release time while further details of these processes are beyond the scope of this article.
Understanding Special Features of tSQLt
We are purely going to focus on what makes tSQLt one of the best SQL unit testing frameworks.
Let us rebuild tSQLt by putting all its best things together one by one to understand its distinctive features assuming you are tasked to build tSQLt framework.
First, visualise the features in the illustration below:
No Context Switching From T-SQL
If you have to build an all-purpose SQL unit testing framework you would like it to be in the same language in which database development is taking place which is T-SQL.
This is exactly what tSQLt is offering to its users as writing database unit tests in tSQLt is same as writing database objects in T-SQL.
In other words there is no context switching from T-SQL to any other language or tool to write unit tests so it does not require additional learning skills and it is easy and quick to write database unit tests.
Database Unit Tests Grouping
Your proposed SQL unit testing framework must be able to arrange unit tests in groups so that you can run unit tests on these groups independently and on demand without needing to run all the unit tests.
tSQLt lets you arrange your unit tests into different groups and then individual group unit tests can be run independently or on demand which is such a relief when it comes to narrowing the scope of unit testing to test particular business requirement.
For example you can put all the unit tests related to a business requirement into one group such as AuthorsReportTests group contains all the unit tests related to Authors Report.
In case of troubleshooting Authors Report you can only run AuthorsReportTests as a first step to check the unit tests are all fine or not.
True Isolation of Objects under Test
Another important factor in building a unit testing framework is to make sure that it provides isolation of the objects under test which means it should be able to focus on a single database object there by isolating it from any other dependent objects.
Isolating object under test from other dependent objects is very distinctive feature of tSQLt.
For example if you are unit testing a stored procedure called AddAuthor() which adds a new author to the database table but also internally calls another stored procedure LogUser() which stores the information of the database user who is performing this action of adding new author then tSQLt isolates AddAuthor() completely from LogUser() stored procedure in such a way that it feels LogUser() is not there anymore and the only object under test is AddAuthor().
This does however require you to write another unit test for LogUser() this time isolating it from AddAuthor().
Tests Run in Transactions
You would like to see our proposed SQL unit testing framework to run unit tests in transactions so that they can be run in parallel and in a consistent manner.
tSQLt by default supports running unit tests in transactions which also mean no need to clean up test objects afterwards since they are going to be automatically cleaned.
Database Continuous Integration (CI) Support
A good SQL unit testing framework is the one which can be integrated with other integration tools such Azure DevOps and fully supports Database Lifecycle Management (DLM) so you want to see this integration capability in your proposed unit testing framework.
The good news is that tSQLt provides the integration support for Database Continuous Integration and Deployment through DevOps for databases.
In simple words the unit tests written in tSQLt can be fully automated in such a way that any changes to the database deployed to target environment automatically run the associated unit tests.
Advanced SQL unit testing Support
One of the most wanted features in a SQL unit testing framework is to provide advanced SQL unit testing support and this is what you would like to see in your proposed testing framework.
tSQLt also provides advanced SQL unit testing support so this means cross-database objects (database objects from different databases) can be unit tested as well.
Pricing Model, Customisation and Competitors
As you have just become familiar with the special features of tSQLt it is worth to see pricing model and competitors of tSQLt.
tSQLt Pricing Model
The pricing model of tSQLt is cost free model which means it is free to use personally and commercially because tSQLt is an open source project capable of serving the testing needs ranging from individuals to corporates.
Ready for the Contribution / Customisation
Since tSQLt is an open source project, its code is easily available for further contribution or customisation, however, this does require certain level of expertise in this area though.
3rd Party Testing Tools vs tSQLt
One would simply argue that what is the point of using tSQLt when third-party unit testing tools are there.
Yes, this is true the database testing tools are available in the market today by famous vendors who have made SQL unit testing very easy and intuitive along with fancy GUI support.
However, do you know that the underlying unit testing framework behind almost all the famous third-party database testing tools is tSQLt.
Yes, all the market leading SQL unit testing tools are based on tSQLt which shows the power, flexibility and coverage of tSQLt.
Philosophy of Creating and Running Unit Tests in tSQLt
Let us look at the some of the core SQL unit testing concepts and their conceptual implementation to get better understanding of SQL unit testing.
Understanding Database Unit Test Anatomy (AAA Rule)
A database unit test is based on AAA rule which consists of the following steps:
- Arrange: This is where you arrange database objects taking part in the test along with creating expected objects and mocking actual tables
- ACT: This is where you run the object (stored procedure or view) under test and put the results into mocked actual table
- Assert: Assert is the final step which compares expected results with actual results and on the bases of comparison a test is passed or failed
AAA Simple Example
Let us understand how unit test anatomy revolves around Arrange, Act and Assert with an example:
Preconditions:
You are unit testing a SQL view named Authors which shows records of all the authors stored in Author table.
To better understand this let us do some coding and create a very simple Author table as follows:
|
1 2 3 4 5 6 |
CREATE TABLE [dbo].[Author] ( [AuthorId] INT NOT NULL, [Name] VARCHAR(40) NOT NULL, ); |
Next create Authors view:
|
1 2 |
CREATE VIEW [dbo].[Authors] AS SELECT [AuthorId], [Name] FROM [Author] |
Arrange
In the first step we create expected object and populate it with expected value.
In order to unit test Authors view we first need to create an expected table Authors_Expected to hold the expected results:
|
1 2 3 4 5 |
CREATE TABLE [dbo].[Author_Expected] ( [AuthorId] INT NOT NULL, [Name] VARCHAR(40) NOT NULL ) |
Next insert one author record into the expected table:
|
1 |
INSERT INTO [dbo].[Author_Expected] ([AuthorId], [Name]) VALUES (1, N'Asif') |
Insert the same record into original table:
|
1 |
INSERT INTO [dbo].[Author] ([AuthorId], [Name]) VALUES (1, N'Asif') |
Act
In the next step we have to test run the object under test which is Authors view and put the result into a new Author_Actual table as follows:
|
1 2 |
-- Run object under test (Authors view) and put results into a new table Author_Actual SELECT * INTO Author_Actual FROM Authors |
Assert
In the final stage actual results are compared with expected results and in our case we will compare Author_Actual table with Author_Expected table.
|
1 2 3 4 5 6 7 8 9 |
-- See if there are any records in expected table but not in actual table SELECT * FROM Author_Expected EXCEPT SELECT * FROM Author_Actual -- See if there are any records in actual table but not in expected table SELECT * FROM Author_Actual EXCEPT SELECT * FROM Author_Expected |
If no records are found that means both expected and actual results match so test has passed.
Keeping this simple example in mind tSQLt lets you apply these concepts with ease and flexibility by hiding complexities and isolating objects under test in such a way that unit testing a database object becomes easy, interesting and flexible.
Congratulations! You have familiarised yourself with some of the core concepts of SQL unit testing along with some key facts of tSQLt which is going to help you in writing database unit tests with tSQLt in the upcoming articles of this series.
Table of contents
He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.
He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.
View all posts by Haroon Ashraf
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023
- Common use cases of SQL SELECT Distinct - February 2, 2023