Haroon Ashraf
Test-driven database development vs test-driven database hotfix development

Test-driven database hotfix development (TDHD) with SQL unit test based framework (tSQLt)

August 24, 2020 by

This article talks about test-driven database hotfix development using a very productive database testing framework called tSQLt applying the same SQL unit test based approach.

In this article, we will focus on a test-first approach to build simple SQL database hotfixes provided they are T-SQL compliant and have no other dependencies.

This article also highlights the importance of creating SQL unit test using tSQLt for the purpose of hotfix testing.

About test-driven database hotfix development

It is easier (if not essential) to understand test-driven database hotfix development if you are already familiar with test-driven database development concepts or have read and implemented the examples presented in the following articles:

  1. Fundamentals of test-driven database development
  2. The concept of test-driven data warehouse development

Let us now take a look at the potential definitions of test-driven database hotfix development inspired by test-driven database development:

Definition 1

It is a test first approach to build and test simple SQL database hotfixes before they get deployed to run in Production environments.

Definition 2

It is a method of developing a simple database hotfix by testing it first.

Definition 3

It is a simple SQL hotfix development strategy where testing the hotfix helps in building it successfully.

Test-driven database development vs test-driven database hotfix development

At first glance, if you are not familiar with test-driven database development it apparently makes no sense that how a test can help us to build something.

However, the truth is that the SQL unit test is shaping your logic to build that object in the test-driven database paradigm (model) thereby encouraging you to keep on modifying your object until the test which satisfies the requirements starts giving you the green signal.

The only difference in the case of test-driven database hotfix development is that the database object in this particular case is a script or even a procedure that is solely responsible to fix a database related performance, consistency or business (requirements) issue.

Test-driven database development vs test-driven database hotfix development

Test-driven database hotfix development implementation

Let us now implement this approach based on a real-time scenario where you have been instructed by the business to apply a database hotfix.

However, before you make a start you must have a database which is an essential element in understanding the whole scenario.

Set up a sample database (SQLDevArticlesTDHD)

Please create a sample SQL database called SQLDevArticlesTDHD by running the following T-SQL script against the master database:

View articles before the hotfix

We can quickly take a look at the only table of the database before we receive business requirements to apply a database fix as follows:

The output is as follows:

View articles before applying hotfix

Hotfix requirement

Please assume the following hotfix is desired by the business:

The Title column of the table Article must mention the name of the author in the following format:

<title> by <author>

For example, the article “Learn Data Structures with Examples” written by Sarah must be shown as “Learn Data Structures with Examples by Sarah” in the Title column of the table in the Production database.

This hotfix looks like a piece of cake if we have a handful of records but what if there are thousands and thousands of records and applying the hotfix incorrectly or partially is not going to help.

So, you are tasked with the development of a hotfix which is ultimately going to change the live data correctly and completely.

Install tSQLt into the sample (dev) database

A safe approach to solve this problem is to use test-driven database hotfix development.

So, please install the tSQLt framework to your development database if you wish to follow the walkthrough. You have to either download tSQLt.class.sql file from tsqlt.org or install it by running the script against SQLDevArticlesTDHD in the file below:

A successful tSQLt installation is shown below:

tSQLt successful installation

Identify potential hotfix object (TitleWithAuthorHotfix)

The next thing right after you have received the hotfix instructions and installed tSQLt is to think of a potential object which serves the purpose which is the ultimate hotfix.

This is the easiest part of test-driven database hotfix development as you are only required to just think of it and name your hotfix object.

Let us call it TitleWithAuthorHotFix.

Create a general hotfix test class

Create a test to check if (hotfix) object exists

As per test-driven database development rules, the first thing is to create a SQL unit test to check if the hotfix object (TitleWithAuthorHotfix) exists.

Please create the SQL unit test as follows:

Run SQL unit test to see if the object exists

Please run the SQL unit test so that we are sure at this point the object does not exist as we expect this test to fail:

The results are shown below:

Run SQL unit test to see if object exists

Now work needs to be done to ensure this test passes in order to move on, so we have to create the hotfix object still at this point we are not concerned about the functioning of the object.

Create hotfix as object (TitleWithAuthorHotfix)

The hotfix as an object can be created as a blank stored procedure as follows:

Rerun SQL unit test to see if the object exists

Let us now rerun the unit tests after we created hotfix object stub (without any code):

The output is as follows:

Test to check if object exists has passed

Create a test to check if hotfix works

Next, we need to create another SQL unit test to check if the hotfix (represented as) object functions properly as now we know the object (TitleWithAuthorHotfix) is there:

Run SQL tests

Now run the tests to see the results:

The results are as follows:

The test to check the hotfix works properly has failed

Refactor hotfix object to pass the test

The hotfix stub object needs to be refactored (modified) to ensure that it serves the purpose and that is the most important part of test-driven database hotfix development where you author (design) the logic of the hotfix and incorporate it into the object keeping in the mind the test which validates this logic:

Let us refactor the hotfix object as follows:

Rerun SQL unit tests after hotfix object is refactored

Please run the tests now:

Both tests have passed

Congratulations! Both SQL unit tests have been passed now and you can now hand it over to your testing team for further tests since it is to be applied to Production database(s).

We have now reached the end of this article but before that let us see the hotfix in action.

Apply the hotfix against the sample database

Let us apply a hotfix to our sample (development) database:

View authors after applying the hotfix

Please check the Article table to see the results after hotfix has been applied:

View articles after the hotfix has been applied

The results clearly show that the hotfix has been applied successfully to the sample database.

However, in professional life scenarios, there may be a number of other steps and teams involved after that which ensure that the hotfix is good enough to serve the purpose but it cannot be easily challenged from the development logic point of view once it is successfully built using test-driven database hotfix development.

Please remember to check SQL database hotfix testing with tSQLt article in which conventional SQL unit testing approach is used to build a hotfix as compared to test-driven database hotfix development.

Once you are familiar with both database hotfix development approaches then making a choice between them is purely based on what is best-suited to your (business) requirements and what you are best at.

Test-driven database hotfix development has an edge over traditional SQL unit testing due to the fact that it is strictly test-driven so your test coverage is going to be just what is required rather than piling up bulk of non-essential SQL unit tests which must be maintained as well.

Therefore, you can save a lot of extra effort using test-driven database hotfix development but then on the other side, you have to go through a tough exercise of refactoring your tests until the condition (business requirement) is met. If you are willing to do that (and it is worth doing it) then test-driven database hotfix development is for you.

Haroon Ashraf
SQL unit 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