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 |
|
|
A more comprehensive comparison between both methodologies is as follows:
Conventional SQL unit testing | Test-driven database development |
|
|
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.
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.
- Note: To get better understand of tSQLt please read the following article tSQLt – A Forgotten Treasure in Database Unit Testing
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:
- Author
- Article
Let us create a sample database SQLDevArticlesV3 by running the following 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 |
-- 1 Create SQLDevArticlesV3 database CREATE DATABASE SQLDevArticlesV3; GO USE SQLDevArticlesV3; 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 |
As a result of running the above script you see the sample database getting created.
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.
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:
|
1 2 3 4 5 6 7 8 |
USE SQLDevArticlesV3; GO -- Creating unit test calss ArticleTests CREATE SCHEMA [ArticleTests] Authorization dbo GO EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='ArticleTests' |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE ArticleTests.[test to check AddArticle exists] AS BEGIN --Assemble --Act --Assert EXEC tSQLt.AssertObjectExists @ObjectName = N'AddArticle' END; GO |
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:
|
1 2 |
-- Run all unit tests related to ArticleTest test class EXEC tsqlt.RunTestClass "ArticleTests" |
It is obvious from the test class output that the unit test to check 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:
|
1 2 3 4 5 6 7 8 9 10 |
-- Creating database object (stored procedure) AddArticle stub (placeholder) CREATE PROCEDURE AddArticle (@Name VARCHAR(40),@Published_Date DATETIME2) AS BEGIN SET NOCOUNT ON; END GO |
Rerun the unit test to check object exists
After creating object stub please rerun the unit test class:
|
1 2 |
-- Rerun ArticleTest test class EXEC tsqlt.RunTestClass "ArticleTests" |
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:
|
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 |
--Create unit test to check AddArticle works CREATE PROCEDURE [ArticleTests].[test to check AddArticle adds article to the table] AS -- Assemble EXEC tSQLt.FakeTable @TableName='dbo.Article',@Identity=1 -- Fake Article table Create TABLE [ArticleTests].[Expected] -- Create expected table ( [ArticleId] INT NOT NULL, [Title] VARCHAR(40) NOT NULL, [Published_Date] DATETIME2 NOT NULL ) INSERT INTO ArticleTests.Expected -- Insert data into exepcted table (ArticleId,Title,Published_Date) VALUES (1,'Reporting Fundamentals','10 Nov 2018') -- Act EXEC dbo.AddArticle 'Reporting Fundamentals','10 Nov 2018' -- Run AddArticle procedure which adds new article to the Article table SELECT * INTO ArticleTests.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='ArticleTests.Expected',@Actual='ArticleTests.Actual' |
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.
|
1 2 |
-- Rerun ArticleTest test class EXEC tsqlt.RunTestClass "ArticleTests" |
Refactor object and rerun unit tests
Modify AddArticle stored procedure with correct insert statement such that all the unit tests pass now.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Alter database object (stored procedure) AddArticle to properly add new article ALTER PROCEDURE AddArticle(@Title VARCHAR(40),@Published_Date DATETIME2) AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.Article (Title, Published_Date) VALUES (@Title,@Published_Date) END GO |
Run the unit tests:
|
1 2 |
-- Run unit tests related to ArticleTests EXEC tsqlt.RunTestClass "ArticleTests" |
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
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