Haroon Ashraf
sql developer unit testing - Installing tSQLt framework by running its script against the sample database

Conventional SQL Unit Testing with tSQLt in Simple Words

March 28, 2019 by

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:

  1. Receive and review the business requirements
  2. Map business requirements to code (database objects)
  3. Create database objects to meet the business requirements
  4. Create (first time) or modify (if unit tests already exist) database unit tests to check if objects are functioning properly or not
  5. 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:

  1. Author
  2. Article

Setup sample database

Let us first create a sample database called SQLDevArticlesV2 by running the following SQL script:

The sample database is now ready to be unit tested as per requirements.

SQL developer unit testing - Installed sample database named SQLDevArticlesV2

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:

sql developer unit testing - Installing tSQLt framework by running its script against the sample database

Download tSQLt script

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:

sql developer unit testing - TSQLT 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:

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:

  1. All the database unit tests are grouped into classes
  2. Each class is represented by creating a database schema
  3. Creating a database unit test is same as creating a stored procedure within a test class
  4. A Database unit test follows AAA principle (Arrange, Act and Assert) where expected results are compared with actual results in the end
  5. 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:

SQL developer unit testing - Running all tSQLt database unit tests

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:

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:

  1. 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)

  2. Creating and populating expected table:

    Create an expected table similar to original Author table and manually insert a record into it

  3. 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

  4. 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

  5. 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:

Running the unit test

Run the unit test to see the results:

SQL developer unit testing - Database unit test has passed

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

tSQLt – A Forgotten Treasure in Database Unit Testing
Conventional SQL Unit Testing with tSQLt in Simple Words
Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
10 Most Common SQL Unit Testing Mistakes
Why you should cleverly name Database Objects for SQL Unit Testing
Three Standard SQL Unit Tests you can write against any Stored Procedure
Creating SQL Unit Testing Utility Procedures with tSQLt

Haroon Ashraf

Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

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
Haroon Ashraf
SQL unit testing, T-SQL, Testing

About Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.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

529 Views