SQL database hotfix testing with tSQLt

SQL database hotfix testing with tSQLt

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

This article talks about getting your database hotfixes tested with tSQLt provided they do not have any inherited complexities or dependencies on things other than SQL database.

In this article, I am going to walk you through the steps of creating and running tests to check a hotfix before it is actually run against the database.

Additionally, the readers are going to get a conceptual understanding of database hotfixes alongside knowing the flexibility of our chosen SQL unit testing framework for simple hotfix testing.

About database hotfixes

The understanding of database hotfixes may vary from scenario to scenario, but that does not stop us from defining a database hotfix, which (definition) can be generally accepted.

What is a hotfix

A hotfix is generally a change to be applied to the Production system often without bringing it offline and with very little disruption to the existing system.

What is a database hotfix

A database hotfix is generally applied to Production database(s) in order to fix an existing or potential issue related to consistency, integrity, or performance of data.

However, under exceptional circumstances, a database hotfix can be applied to the databases other than Production, such as QA or even HOTFIX (database).

How is a database hotfix applied

A database hotfix can be applied in many ways, including the following:

  1. Running a T-SQL script against the database

  2. Using tools such as SSMS (SQL Server Management Studio) to apply changes to the database

  3. Building and running an SSIS Package to apply changes to the database

  4. Using an automatic recommendation along with an auto-generated script (as in case of Azure SQL database)

However, before we apply the hotfix, it must be tested, and if it is a simple SQL database hotfix, then tSQLt is the best candidate for testing it.

When is a database hotfix desired

We may need a hotfix for a database in a number of scenarios, including the following:

  1. A bug is found in the Production database

  2. New changes destabilized an existing object or objects

  3. Some new rules have come into place which requires existing data to be modified

  4. Your test team finds an issue with the data when testing something else

SQL database hotfix testing Lifecycle

A database hotfix testing lifecycle can be as simple as follows:

  1. A bug is detected in the Production database

  2. A database hotfix is written to resolve the issue

  3. The database hotfix is tested to ensure it works well

  4. The database hotfix is applied to the Production database once the test is passed

  5. The database hotfix is modified further if the test is failed

  6. Finally, there is no harm in post-testing the Production database after the hotfix is applied
Leia Também  Faça backup do SQL Server 43% -67% mais rápido gravando em vários arquivos.

However, this may get pretty complicated when you have multiple versions and multiple environments of your database, but let us keep our focus on getting hands-on testing hotfix with before it gets deployed to Production or any other desired environment.

SQL database hotfix testing set up

To begin SQL database hotfix testing, we need the following things:

  1. A sample database to work with

  2. A SQL unit testing framework installed on the sample database

Please remember the sample database in this article represents a development database, and it is not at all recommended to install tSQLt into your Production or QA database(s).

Set up a sample database

We can create a sample database named SQLDevArticlesFixes by running the following T-SQL code against the master database:


View Author table

Let us have a quick look at the Authors table by running the following simple query:

The output is as follows:

Author table data before hotfix

Please keep in mind (or you may refer to it later on) the last record where the registration date is 01 Jan 2019.

Install tSQLt

In order to create and run SQL unit tests against your development database, please download the tSQLt.class.sql file from tsqlt.org and run it against SQLDevArticlesFixes.

Alternatively, you can install it by clicking this link: tSQLt_V1.0.5873.27393

A successful installation must be showing the output similar to the one below:

Successful installation of SQL unit testing framework

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

Please refer to the article, Conventional SQL Unit Testing with tSQLt in Simple Words to get more information about tSQLt installation if you are still having any issue.

Quick Check

Please test run all the SQL unit tests:

The results are shown below:

Leia Também  Tabelas com otimização de memória no SQL Server - Um exemplo do SQLEspresso

Dry test run of SQL unit tests

If your output is the same as above (with the exception of date and time), then you are good to go as we have not yet written any SQL unit test, but we can see tSQLt has been successfully setup.

SQL database hotfix testing

We can now create and run SQL unit tests against our sample database; however, we need some solid requirements before we start our work.

Hotfix testing scenario

Let us suppose we have just been informed about the following business requirements:

“All the registration dates of the authors must be changed to the next day (02 Jan) if they fall on 01 Jan 2019 or 01 Jan 2020.”

The infrastructure team decides to apply this change as a hotfix, but they have asked the development team to create this hotfix so that they can apply it in the Production environment.

Creating an object for the hotfix

The development team decides to create a stored procedure to apply this hotfix, which is going to change all the registration dates from 01 January to 02 January, where the registration year is either 2019 or 2020.

We can call this Jan01ToJan02HotFix so let us create its stored procedure as follows:

Creating a test class for the hotfix

Now that we have created an object which applies the hotfix, we must not let it go without testing it, and that is the reason we set up tSQLt. Let us create a test class for hotfixes as follows:

Hotfix testing logic

The hotfix test should be based on the following things:

  1. Remove all the data from the Author table by creating a fake table

  2. Insert a few records where authors were registered on 01 Jan 2019 and 01 Jan 2020

  3. Create an expected table in which authors registered on 01 Jan 2019 have had their registration date changed to 02 Jan 2019 and do the same (date change) for the year 2020

  4. Run the stored procedure which applies the fix to the Author table

  5. Compare Author table after applying the fix with the expected table

  6. If the results match then the test has passed else check your code

Creating SQL unit test for the hotfix

Create SQL unit test for the hotfix by running the following T-SQL script:

Running hotfix test

We can run all the SQL unit tests now:

Leia Também  E se você realmente precisar reduzir um banco de dados?

The results can be seen below:

hotfix test has passed

Running hotfix

Please feel free to run this hotfix against the sample database to see it in action:

View Author table after applying the hotfix

It is time to view the Author table after we have applied the hotfix:

The results are shown below:

Author table after hotfix has been applied successfully

Congratulations, you have successfully tested a hotfix with one of the most advanced SQL unit testing frameworks, followed by applying it to the sample database.

It is easy to say that tSQLt cannot only help in database object unit testing, but it can also play a vital role in SQL based simple hotfix testing, plus all of these hotfixes can be grouped into a separate test class for future reference and (re)use.

However, this approach is suitable only for simple SQL based hotfixes, as discussed in this article. As for more complicated ones, you have to work with other tools, technologies, and team(s) to test them properly.

Haroon Ashraf
Latest posts by Haroon Ashraf (see all)