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:
- Running a T-SQL script against the database
- Using tools such as SSMS (SQL Server Management Studio) to apply changes to the database
- Building and running an SSIS Package to apply changes to the database
- 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:
- A bug is found in the Production database
- New changes destabilized an existing object or objects
- Some new rules have come into place which requires existing data to be modified
- 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:
- A bug is detected in the Production database
- A database hotfix is written to resolve the issue
- The database hotfix is tested to ensure it works well
- The database hotfix is applied to the Production database once the test is passed
- The database hotfix is modified further if the test is failed
- Finally, there is no harm in post-testing the Production database after the hotfix is applied
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:
- A sample database to work with
- 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:
Code:
|
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 SQLDevArticlesFixes database CREATE DATABASE SQLDevArticlesFixes; GO USE SQLDevArticlesFixes; 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 |
View Author table
Let us have a quick look at the Authors table by running the following simple query:
|
1 2 |
-- View authors table SELECT a.AuthorId,a.Name,a.RegistrationDate FROM dbo.Author a |
The output is as follows:
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:
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:
|
1 2 |
-- Run all database unit tests EXEC tsqlt.RunAll |
The results are shown below:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE Procedure [dbo].[Jan01ToJan02HotFix] AS BEGIN -- HotFix: Change all the registration dates from 01 Jan 2019/2020 to 02 Jan 2019/2020 SET NOCOUNT ON UPDATE [dbo].[Author] SET [RegistrationDate] = '02 Jan 2019' WHERE [RegistrationDate] = '01 Jan 2019' UPDATE [dbo].[Author] SET [RegistrationDate] = '02 Jan 2020' WHERE [RegistrationDate] = '01 Jan 2020' SET NOCOUNT OFF END |
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:
|
1 2 3 4 5 |
-- Creating test class for HotFix CREATE SCHEMA [HotFixTests] Authorization dbo GO EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='HotFixTests' |
Hotfix testing logic
The hotfix test should be based on the following things:
- Remove all the data from the Author table by creating a fake table
- Insert a few records where authors were registered on 01 Jan 2019 and 01 Jan 2020
- 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
- Run the stored procedure which applies the fix to the Author table
- Compare Author table after applying the fix with the expected table
- 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:
|
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 40 41 42 43 |
CREATE PROCEDURE [HotFixTests].[test to check Jan01Jan02HotFix runs successfully] AS BEGIN -- This is Jan01Jan02HotFix unit test which checks if registration date 01 Jan 2019 is changed to 02 Jan 2019 and 01 Jan 2020 is changed to 02 Jan 2020 -- Assemble EXEC tSQLt.FakeTable @TableName='dbo.Author' -- Fake Author table INSERT INTO [dbo].[Author] ([AuthorId],[Name] ,[RegistrationDate]) VALUES (1,'Author1','01 Jan 2019'), (2,'Author2','01 Mar 2020'), (3,'Author3','01 Apr 2019'), (4,'Author4','01 Jan 2020') Create TABLE [HotFixTests].[Expected] -- Create expected table ( [AuthorId] INT, [Name] VARCHAR(40) NOT NULL, [RegistrationDate] DATETIME2 NOT NULL ) INSERT INTO HotFixTests.Expected -- Insert data into expected table (AuthorId,Name,RegistrationDate) VALUES (1,'Author1','02 Jan 2019'), (2,'Author2','01 Mar 2020'), (3,'Author3','01 Apr 2019'), (4,'Author4','02 Jan 2020') -- Act EXEC dbo.Jan01ToJan02HotFix -- Run HotFix SELECT * INTO HotFixTests.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='HotFixTests.Expected',@Actual='HotFixTests.Actual' END |
Running hotfix test
We can run all the SQL unit tests now:
|
1 2 |
-- Run all database unit tests EXEC tsqlt.RunAll |
The results can be seen below:
Running hotfix
Please feel free to run this hotfix against the sample database to see it in action:
|
1 2 |
-- Run the database HotFix EXEC dbo.Jan01ToJan02HotFix |
View Author table after applying the hotfix
It is time to view the Author table after we have applied the hotfix:
|
1 2 |
-- View authors table after the hotfix has been applied SELECT a.AuthorId,a.Name,a.RegistrationDate FROM dbo.Author a |
The results are shown below:
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.
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
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023
- Common use cases of SQL SELECT Distinct - February 2, 2023