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:
- Fundamentals of test-driven database development
- 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 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:
|
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 |
-- 1 Create SQLDevArticlesTDHD database CREATE DATABASE SQLDevArticlesTDHD; GO USE SQLDevArticlesTDHD; GO -- 3 Create article table CREATE TABLE [dbo].[Article] ( [ArticleId] INT IDENTITY (1, 1) NOT NULL, [Title] VARCHAR (300) NOT NULL, [Published_Date] DATETIME2 (7) NOT NULL, [Author] VARCHAR(40) ); -- 5 Populate article table SET IDENTITY_INSERT [dbo].[Article] ON INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date], [Author]) VALUES (1, N'Learn Data Structures with Examples', N'2019-02-02 00:00:00', N'Sarah') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date], [Author]) VALUES (2, N'Advanced Data Analysis and Reporting', N'2019-03-01 00:00:00', N'Asif') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date], [Author]) VALUES (3, N'The Power of Power BI', N'2019-07-03 00:00:00', N'Adil') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date], [Author]) VALUES (4, N'Basics of Data Modeling', N'2019-08-10 00:00:00', N'Peter') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date], [Author]) VALUES (5, N'Database Programming Logic', N'2019-09-20 00:00:00', N'Mike') SET IDENTITY_INSERT [dbo].[Article] OFF |
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:
|
1 2 |
-- View article(s) before the hotfix SELECT a.ArticleId,a.Title,a.Published_Date,a.Author FROM Article a |
The output is as follows:
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:
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
|
1 2 3 4 5 6 7 8 |
Use SQLDevArticlesTDHD; GO -- Creating test class for HotFix CREATE SCHEMA [HotfixTests] Authorization dbo GO EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='HotfixTests' |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE HotFixTests.[test to check TitleWithAuthorHotfix exists] AS BEGIN --Assemble --Act --Assert EXEC tSQLt.AssertObjectExists @ObjectName = N'TitleWithAuthorHotFix' END; GO |
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:
|
1 2 3 4 |
USE SQLDevArticlesTDHD -- Run all database unit tests EXEC tsqlt.RunAll |
The results are shown below:
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:
|
1 2 3 4 5 6 7 8 |
CREATE Procedure [dbo].[TitleWithAuthorHotfix] AS BEGIN -- Hotfix: Add authors' names at the end of the title of their articles SET NOCOUNT ON -- object stub SET NOCOUNT OFF END |
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):
|
1 2 3 4 |
USE SQLDevArticlesTDHD -- Run all database unit tests EXEC tsqlt.RunAll |
The output is as follows:
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:
|
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 |
CREATE PROCEDURE [HotFixTests].[test to check TitleWithAuthorHotfix runs successfully] AS BEGIN -- This is TitleWithAuthorHotfix unit test which checks if each Title of the article has author name at the end (<Title> by <Author>) -- Assemble EXEC tSQLt.FakeTable @TableName='dbo.Article' -- Fake Article table -- Add two articles with authors INSERT INTO dbo.Article (ArticleId,Title,Published_Date,Author) VALUES (1,'Data Structures','01 Jan 2020','Saqib'), (2,'Databases','01 Feb 2020','Peter') Create TABLE [HotFixTests].[Expected] -- Create expected table ( [ArticleId] INT NOT NULL, [Title] VARCHAR (300) NOT NULL, [Published_Date] DATETIME2 (7) NOT NULL, [Author] VARCHAR(40) ) INSERT INTO HotFixTests.Expected -- Insert data into expected table (ArticleId,Title,Published_Date,Author) VALUES (1,'Data Structures by Saqib','01 Jan 2020','Saqib'), (2,'Databases by Peter','01 Feb 2020','Peter') -- Act EXEC dbo.TitleWithAuthorHotfix -- Run HotFix SELECT * INTO HotFixTests.Actual FROM dbo.Article -- Put the records from Article table into Actual table -- Assert (compare expected table with actual table results) EXEC tSQLt.AssertEqualsTable @Expected='HotFixTests.Expected',@Actual='HotFixTests.Actual' END |
Run SQL tests
Now run the tests to see the results:
|
1 2 3 4 |
USE SQLDevArticlesTDHD -- Run all database unit tests EXEC tsqlt.RunAll |
The results are as follows:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
ALTER Procedure [dbo].[TitleWithAuthorHotfix] AS BEGIN -- Hotfix: Add authors' names at the end of the title of their articles SET NOCOUNT ON -- Add author name to the title (column) in Article (table) UPDATE Article SET Title=Title+' by '+Author SET NOCOUNT OFF END |
Rerun SQL unit tests after hotfix object is refactored
Please run the tests now:
|
1 2 3 4 |
USE SQLDevArticlesTDHD -- Run all database unit tests EXEC tsqlt.RunAll |
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:
|
1 2 3 4 |
USE SQLDevArticlesTDHD -- Run the database HotFix EXEC dbo.TitleWithAuthorHotfix |
View authors after applying the hotfix
Please check the Article table to see the results after hotfix has been applied:
|
1 2 |
-- View article(s) after hotfix SELECT a.ArticleId,a.Title,a.Published_Date,a.Author FROM Article a |
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.
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