This article is about basics of conventional SQL unit testing concepts and its implementation through tSQLt, a highly acclaimed SQL unit testing framework because of being written in T-SQL and its built-in design support for SQL SQL unit testing needs ranging from simple to complex scenarios.
This article also highlights the importance of understanding the core concepts of conventional database unit tests before you start writing and running these unit tests with tSQLt.
In this article the readers are going to be familiarised with tSQLt to write basic database unit tests chasing a simple business requirement with respect to conventional database development.
Conventional SQL Unit Testing Basics
Please remember that database development is linked with SQL unit testing whether we adopt conventional SQL unit testing style or not.
Let us now go through basics of conventional SQL unit testing.
Simple definition
A conventional SQL unit testing is a method of unit testing database objects after they are created.
Alternative definition
The database (objects) development followed by their unit testing is termed as conventional SQL unit testing.
About role playing database objects
Database objects referenced in both versions of the definition are mainly role playing database objects.
The database objects such as SQL view, stored procedure, function etc. created to provide certain functionality can be referred to as role playing database objects, a term I am coining for the first time (but not to be confused with role playing dimensions in business intelligence solutions) to clarify the purpose of these objects in SQL unit testing through tSQLt.
Role playing database objects vs. Requirements
So, we know about role playing database objects now let us find out how they are linked with business requirements.
Obviously, the standard goal of any database development project is to meet business requirements unless otherwise specified and the role playing database objects help the developers to map the business requirements.
In other words, the database role playing objects are designed keeping in mind business requirements so if they are functioning properly it can be said that they are meeting the business requirement and the best way to ensure this is to unit test them using tSQLt.
Conventional SQL unit testing steps
The main steps generally considered in conventional database development and unit testing are as follows:
- Receive and review the business requirements
- Map business requirements to code (database objects)
- Create database objects to meet the business requirements
- Create (first time) or modify (if unit tests already exist) database unit tests to check if objects are functioning properly or not
- Run database unit tests and go to step 1 if unit tests pass or step 4 if unit tests fail
Please remember that to keep things simple we are not going into the details of whether choosing database objects to map the business requirements is the best option or not.
Pre-Requisites
Let us go through pre-requisites of this article.
Database concepts and T-SQL familiarity
The article assumes that the readers have basic know how of T-SQL and database development concepts.
Please refer to tSQLt – A Forgotten Treasure in Database Unit Testing for further information about basics of tSQLt.
About sample database
In order to understand the tSQLt implementation it is better to have a sample database in hand so that we can write tSQLt unit tests against it.
I have purposely chosen to prepare a two table sample database with only primary key constraints in place to encourage the readers to focus solely on SQL unit testing rather than trying to resolve the complexity of the database being unit tested.
The sample database consists of the following two tables:
- Author
- Article
Setup sample database
Let us first create a sample database called SQLDevArticlesV2 by running the following SQL script:
|
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 39 |
-- 1 Create SQLDevArticlesV2 database CREATE DATABASE SQLDevArticlesV2; GO USE SQLDevArticlesV2; GO -- 2 Create author table CREATE TABLE [dbo].[Author] ( [AuthorId] INT IDENTITY (1, 1) NOT NULL, [Name] VARCHAR (40) NOT NULL, [RegistrationDate] DATETIME2 (7) NULL ); -- 3 Create article tables CREATE TABLE [dbo].[Article] ( [ArticleId] INT IDENTITY (1, 1) NOT NULL, [Title] VARCHAR (300) NOT NULL, [Published_Date] DATETIME2 (7) NOT NULL ); -- 4 Populate author table SET IDENTITY_INSERT [dbo].[Author] ON INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (1, N'Asif', N'2018-01-01 00:00:00') INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (2, N'Peter', N'2018-02-01 00:00:00') INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (3, N'Sarah', N'2018-03-02 00:00:00') INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (4, N'Adil', N'2018-04-02 00:00:00') INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (5, N'Sam', N'2019-01-01 00:00:00') SET IDENTITY_INSERT [dbo].[Author] OFF -- 5 Populate article table SET IDENTITY_INSERT [dbo].[Article] ON INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (1, N'Fundamentals of Database Programming', N'2018-01-02 00:00:00') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (2, N'Advanced Database Programming', N'2018-01-03 00:00:00') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (3, N'Understanding SQL Stored Procedures ', N'2018-02-02 00:00:00') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (4, N'Database Design Concepts', N'2018-03-02 00:00:00') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (5, N'Power BI Desktop Fundamentals', N'2019-01-02 00:00:00') SET IDENTITY_INSERT [dbo].[Article] OFF; GO |
The sample database is now ready to be unit tested as per requirements.
Please remember that in real world scenario sample database is going to be replaced with your development database.
tSQLt Setup
Next big thing, once you are familiarised with basics of conventional SQL unit testing and sample database is setup, is to understand how to setup tSQLt framework to get ready to unit test your database.
How tSQLt is installed?
tSQLt is installed in the form of running its script against the desired database which in turn creates tSQLt objects in the desired database.
tSQLt download and extract
Download tSQLt from the official website and then extract the downloaded zip folder
Run tSQLt script
Open tSQLt.class.sql file in SSMS (SQL Server Management Studio) and Run tSQL.class.sql script against the sample database SQLDevArticlesV2:
Refresh database
Right Click Databases and then click Refresh and then click Tables (to expand) under SQLDevArticles database to see tSQLt has been installed successfully:
Our sample database is ready to be unit tested after the successful installation of tSQLt framework.
Creating and running database unit test
In real world scenarios business requirements drive the database development and testing process.
Business requirement
Let us assume that you have received the following business requirement:
“The end user must be able to add new author to the (database) system”
Conventional database object development
In order to meet the business requirement we must focus on the best suited database object which is capable of meeting the requirement in the best possible way.
Creating database object (stored procedure)
A stored procedure called AddAuthor has been chosen to be created to meet the business requirement.
Create the stored procedure as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Creating database object (stored procedure) AddAuthor to add new author to the author table CREATE PROCEDURE AddAuthor(@Name VARCHAR(40),@RegistrationDate DATETIME2) AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.Author (Name, RegistrationDate) VALUES (@Name,@RegistrationDate) END GO |
Understanding unit testing architecture in tSQLt
Before you start creating database unit tests with tSQLt it is important to understand how unit tests architecture works with respect to tSQLt.
Database unit testing with tSQLt is primarily based on the following things:
- All the database unit tests are grouped into classes
- Each class is represented by creating a database schema
- Creating a database unit test is same as creating a stored procedure within a test class
- A Database unit test follows AAA principle (Arrange, Act and Assert) where expected results are compared with actual results in the end
- Running a tSQLt unit test is simply running a SQL stored procedure
Dry run all unit tests
Let us learn the first thing to do once tSQLt is installed successfully and that is to run all the unit tests.
You can run all the unit tests written in tSQLt by running the following script against the sample database:
|
1 2 |
-- Test run all the unit tests EXEC tSQLt.RunAll |
Since there are no unit tests written yet, so no results to see, however, it confirms that tSQLt framework is readily accepting unit tests.
Creating database unit test class
Now that we have created a stored procedure AddAuthor to add new author to the database table, it is time to create unit test to check if the object is working properly or not.
The first thing in this regard is to create database unit test class.
Create a test class called AuthorTests by creating a schema in the sample database (SQLDevArticlesV2) as follows:
|
1 2 3 4 |
CREATE SCHEMA [AuthorTests] Authorization dbo GO EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='AuthorTests' |
Creating unit test to add author
Create a unit test to check if AddAuthor stored procedure is working properly or not.
Please remember that the stored procedure to be unit tested is responsible to meet business requirement, so its unit test when successful confirms that the object under test works properly and therefore it meets the requirement.
Creating the database unit test for a procedure which adds new author to the table is done by keeping the following things in mind:
-
Mocking original table:
Create a blank copy of the table Author which is done by using FakeTable function provided by tSQLt (so that we can ensure that there is no data present before the unit test)
-
Creating and populating expected table:
Create an expected table similar to original Author table and manually insert a record into it
-
Running stored procedure to populate original mocked table:
Add the same record by using AddAuthor stored procedure this time which is going to populate Author table
-
Create actual table out of original table:
Create an actual table out of Author table which contains data as a result of running AddAuthor procedure
-
Compare actual table with expected table:
Compare actual table with expected table and if the result is same then the test has passed else troubleshoot the unit test to make it work for you
The code is as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE PROCEDURE [AuthorTests].[test to check AddAuthor adds author to the table] AS -- Assemble EXEC tSQLt.FakeTable @TableName='dbo.Author',@Identity=1 -- Fake Customer table Create TABLE [AuthorTests].[Expected] -- Create expected table ( [AuthorId] INT NOT NULL, [Name] VARCHAR(40) NOT NULL, [RegistrationDate] DATETIME2 NOT NULL ) INSERT INTO AuthorTests.Expected -- Insert data into exepcted table (AuthorId,Name,RegistrationDate) VALUES (1,'Naveed','01 Jun 2018') -- Act EXEC dbo.AddAuthor 'Naveed','01 Jun 2018' -- Run AddAuthor procedure which adds new author to Author table SELECT * INTO AuthorTests.Actual FROM dbo.Author -- Put the records from Author table into Actual table -- Assert (compare expected table with actual table results) EXEC tSQLt.AssertEqualsTable @Expected='AuthorTests.Expected',@Actual='AuthorTests.Actual' |
Running the unit test
Run the unit test to see the results:
|
1 2 |
-- Run all tSQLt database unit tests EXEC tSQLt.RunAll |
Congratulations! The database unit test has passed so you are good to go.
After going through this article you are not only just familiar with conventional SQL unit testing concepts but can also create simple unit tests to check if your database objects are functioning properly.
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
- How to create and query the Python PostgreSQL database - August 15, 2024
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023