Haroon Ashraf

Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing

April 19, 2019 by

This article talks about core concepts of test-driven database development followed by creating simple SQL unit tests with tSQLt based on this approach.

The conventional SQL unit testing has been around since long while test-driven database development has not also been introduced yesterday, however, switching to this testing methodology offers pure unit testing experience with a lot more features and flexibility as compared to its counterpart.

Let us get introduced with test-driven database development first and please be prepared to switch to it if you are not already using it and you are happy to do so.

About test-driven database development

It is better to define test-driven database development first, so that it becomes easier to explore it further.

Simple Definition

The method in which unit tests drive the database development process is called test-driven database development or TDDD.

Alternative Definition

The method of creating database objects based on creating and running their unit tests first is called test-driven database development or TDDD.

TDDD: Development or testing?

I know, for the first time learners, the above definitions do not give much information about test-driven database development and on the top of that it is not still not clear whether we are talking about database development or database unit testing?

The short answer is, both!

Yes, it is basically SQL unit testing followed by database development unlike database development followed by SQL unit testing.

I may have added more confusion now. So it’s time to clear the confusion now.

Test first approach

TDDD is basically a test first approach in which database development rely on unit testing to begin and take the control.

The best way to understand this is to compare conventional unit testing with test-driven database development.

Conventional vs Test-driven SQL development

Please see a simple comparison between conventional SQL unit testing and TDDD

Conventional SQL unit testing

Test-driven database development

  1. Create database object
  2. Write database unit test to check object exists or not
  3. Run database unit test
  1. Create database unit test to check if potential object exists or not
  2. Run the unit test which will fail first
  3. Add database object
  4. Run the unit test which will pass now

A more comprehensive comparison between both methodologies is as follows:

Conventional SQL unit testing

Test-driven database development

  1. Create database object
  2. Add desired functionality to the object
  3. Create unit test to check if object functions properly
  4. Run the database unit test to see it passing or failing
  1. Create unit test to check if potential object exists or not
  2. Run unit test which fails since the object does not exist
  3. Create object and rerun the unit test which should pass now
  4. Create another unit test to check if object functions properly
  5. Run the unit test which will fail now
  6. Modify the database object to function properly and rerun the unit test which should pass now

The above points become more understandable as you move to the next sections of this article.

To know more about conventional SQL unit testing please refer to the article, “Conventional SQL Unit Testing with tSQLt in Simple Words

Benefits of test-driven database development

There are quite a number of benefits of using test-driven database development but we are only going to mention some major benefits of TDDD to highlight its importance.

Business Logic Encapsulation

One of the most outstanding benefits of test-driven database development is the encapsulation of business logic in the unit tests rather than database objects.

For example, a business requirement for end user to be able to add new book to the library system should be met by writing a unit test(s) to ensure the database object responsible for adding the new book to the library system does its job properly.

In simple words we look for the unit tests rather than database objects to see if the objects are doing their job properly or not and in this way business logic is concealed in unit tests rather than objects.

Full Test Coverage

Test-driven database development gives you full test coverage as compared to conventional database unit testing.

Full test coverage means all our unit tests are covering all the database objects which are responsible for meeting business or technical requirements.

Early Bug Detection

Test-driven database development helps in early bug detection since SQL unit tests are in the front line of writing database object code.

In other words, since all the unit tests are written first and are not considered correct until they pass so it helps in early bug detection.

Requirements Focussed Development

Another very handy benefit of test-driven database development is that, it only requires you to develop what is required by business.

Since your database objects are totally based on unit tests and your unit tests are only written to meet the business requirements so in this way un-necessary database coding can be avoided and this saves a lot of time and effort.

For example in order to meet a business requirement to add new book to the library system does not require you to provide search functionality as well since you are only concerned with what is required.

Test-driven database development

Implementing Test-driven database development

Let us now jump to implement test-driven database development with tSQLt unit testing taking into account a simple scenario.

Why tSQLt with test-driven database development?

tSQLt is one of the most advanced SQL unit testing frameworks and it supports test-driven database development by default.

tSQLt has been written in such a way that it is by design, facilitates test first approach and this is what you are going to experience in this section.

Pre-requisites

This article assumes that readers have general understanding of database unit testing and T-SQL and can comfortably write simple database scripts.

This article also assumes that readers are familiar with tSQLt unit testing framework.

Setup sample database

We are creating a sample database which consists of the following tables:

  1. Author
  2. Article

Let us create a sample database SQLDevArticlesV3 by running the following script:

As a result of running the above script you see the sample database getting created.

Sample database has been successfully setup.

Install tSQLt unit testing framework

Please download the tSQLt unit testing framework form the tSQLt.org.

Please refer to Conventional SQL Unit Testing with tSQLt in Simple Words article for better understanding of how to install the tSQLt unit testing framework if you have not installed it before.

Open tSQLt.class.sql file in SSMS (SQL Server Management Studio) and Run tSQL.class.sql script against the sample database SQLDevArticlesV3.

TSQLT unit testing framework has been successfully added to the sample database.

So, sample database has been setup and the tSQLt unit testing framework has also been added to it which means we are good to go.

Business requirement

A business requirement stating that the end user should be able to add a new article to the database has just arrived.

Create test class (ArticleTests)

In order to start writing tSQLt unit tests you have to create a suitable test class which can be easily created a by creating a schema in the database under test.

Create ArticleTests schema (test class) as follows:

Be requirements focus

The first step in test-driven database development is to just focus on meeting the requirement only by relying on unit tests more than the objects themselves.

This means we have to come up with a potential database object AddArticle.

Create SQL unit test to check object exists

Test-driven database development demands first to write a unit test to check if potential object exists or not.

Write first unit test to see object exists or not as follows:

Run unit test to check object exists

This is the tricky bit we know the object does not exist then why we are running the unit test? The answer is to comply with test-driven database development in which unit tests drive the process and they must pass to proceed further.

Run all the unit test by running all the unit tests for the test class ArticleTests as follows:

It is obvious from the test class output that the unit test to check object exists has failed:

tSQLt Unit Testing - Test to check if object exists has failed.

Create database object (ArticleTests)

Now create the database object as a stub which means just create a database object with parameters but without any functionality because we are only interested to create it at the moment.

Type the following code to create the object:

Rerun the unit test to check object exists

After creating object stub please rerun the unit test class:

tSQLt Unit Testing - Unit test to check if object exists has passed.

Your unit test has passed now, so the object which is meant to meet the requirement exists, however, please bear in mind it does not mean that the object necessarily meets the business requirement.

Create unit test to check object functions properly

In test-driven database development unit test is written first to check if object functions properly or not, which in turn triggers the database object to be developed to meet the specification only.

The recommended way to check AddArticle object functions properly is to write SQL unit test which adds new article to the database by using the potential object and then results are compared with the expected results.

Write the unit test to check object works properly as follows:

Run unit tests to check object exists and works properly

Now running all the unit tests should partially pass now because the test to check if object functions properly is going to fail.

tSQLt Unit Testing - Unit test to check AddArticle adds article to the table failed.

Refactor object and rerun unit tests

Modify AddArticle stored procedure with correct insert statement such that all the unit tests pass now.

Run the unit tests:

tSQLt Unit Testing - All the unit tests have passed now.

Congratulations, the fact that all the unit tests have passed now ensures that the object is capable of meeting the business requirement to add new article to the database now.

Summary

After going through this article and following the walkthrough you have familiarised yourself with test-driven database development methodology which is not only feature-rich, but to the point and also offers a lot of flexibility in handling business requirements ranging from simple to complex scenarios.

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, 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

400 Views