We discussed the basics of test-driven database development (TDDD) with examples and compared it with traditional database development in the first part of this article.
In the second part, we are going to move beyond basics to focus on a more realistic scenario of meeting report requirements by using TDDD.
A quick recap of TDDD at this point is handy in understanding how it can help us to achieve such goals.
Test-driven database development (TDDD) Recap
As the name indicates, test-driven database development (TDDD) is a database development method of meeting requirements through unit tests.
It is always necessary to implement unit tests before the main database code since they drive the database development as a whole.
In other words, database objects (such as “view”, “stored procedure”, etc.) are necessary to meet the database-related business requirements but they always have to pass the unit test(s) before they are enough appropriate for such purpose.
In TDDD, business requirements are encapsulated in database unit tests.
In case the requirement is adding a new category to the Category table, it is necessary to implement TDDD according to the following steps:
- Creating of database unit test to check the existence of AddNewCategory database object.
- Failing of the unit test because of the database object absence.
- Creating the AddNewCategory object in order for the unit test to pass.
- The unit test determines whether AddNewCategory stored procedure is actually adding a new category or not.
- That unit test also fails.
- AddNewCategory procedure code changes to add a new category that verifies after rerunning the unit test, which is able to pass now.
This is shown below:
Please, refer to the first part of this article to see the detailed walk-through of the previous steps.
Let’s see how effective is TDDD when it comes to satisfying a business reporting requirement.
Satisfying Business Reporting Requirement through TDDD
Let’s go through the steps of meeting a business reporting requirement using TDDD.
We assume the database already got database objects (such as tables) before receiving the new business reporting requirement.
SQLDevBlogReportTDD Sample Database Setup
Create a sample database called “SQLDevBlogReportTDD” as follows:
-- Create sample database (SQLDevBlogReportTDD) CREATE DATABASE SQLDevBlogReportTDD; GO
Then create and populate the tables for the sample database using the following code:
USE SQLDevBlogReportTDD; -- (1) Create Author table in the sample database CREATE TABLE Author ( AuthorId INT PRIMARY KEY IDENTITY (1, 1) ,Name VARCHAR(40) ,RegistrationDate DATETIME2 ,Notes VARCHAR(400) ) -- (2) Create Article Category table in the sample database CREATE TABLE Category ( CategoryId INT PRIMARY KEY IDENTITY (1, 1) ,Name VARCHAR(50) ,Notes VARCHAR(400) ) -- (3) Create Article table in the sample database CREATE TABLE Article ( ArticleId INT PRIMARY KEY IDENTITY (1, 1) ,CategoryId INT ,AuthorId INT ,Title VARCHAR(150) ,Published DATETIME2 ,Notes VARCHAR(400) ) -- Adding foreign keys for author and article category ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId) ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId) GO -- (4) Populating Author table INSERT INTO Author (Name, RegistrationDate, Notes) VALUES ('Peter', '2017-01-01', 'Database Analyst'), ('Adil', '2017-01-02', 'Database and Business Intelligence Developer'), ('Sarah', '2018-01-01', 'Database Analyst Programmer'), ('Asim', '2018-01-01', 'Database Analyst') -- (5) Populating Category table INSERT INTO Category (Name, Notes) VALUES ('Analysis', 'Database Analysis'), ('Development', 'Articles about database development'), ('Testing', 'Database testing related articles'), ('DLM', 'Database lifecycle management') -- (6) Populating Article INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes) VALUES (1, 1, 'Replicating a problem in SQL', '02-01-2018', ''), (1, 2, 'Modern Database Development Tools', '02-01-2018', ''), (3, 3, 'Test Driven Database Development (TDDD)', '03-01-2018', ''), (3, 1, 'Database Unit Testing Fundamentals', '10-01-2018', ''), (3, 3, 'Unit Testing with tSQLt', '10-01-2018', '') GO
Then create a view to see the list of all authors, articles and articles’ categories:
-- (7) Create view to see list of authors, articles and categories CREATE VIEW dbo.vwAuthors AS SELECT a.Name AS AuthorName,a1.Title AS ArticleTitle,c.Name AS CategoryName FROM Author a INNER JOIN Article a1 ON a.AuthorId = a1.AuthorId INNER JOIN Category c ON a1.CategoryId = c.CategoryId GO
Run the view of the created table to see the results:
Please note that I am using dbForge Studio for SQL Server in this article so only output look may differ in case you run the same code in SSMS (SQL Server Management Studio). Nevertheless, there is no difference because scripts and their results are similar.
After processing the database setup and populating the tables, the next step is to imitate the scenario where we received a new requirement.
The TDDD implementation in this article is based on unit testing standard practices by Dave Green (an expert in this area).
Business Requirement: Total Number of Articles per Author Report
Consider a new business reporting requirement. It has to state a database report to view the total number of articles per author and has to be desired by the internal staff.
Assigning ArticlesPerAuthorReport Database Object
The first thing is to assign a database object which is capable of meeting the business requirement. In our case, we can think about “ArticlesPerAuthorReport” database object.
Meeting the Requirement (TDDD)
In order to meet the requirement, firstly it is necessary to create a database unit test that looks for a potential appropriate object.
This is how TDDD works. On the top of it, there is the unit test that firstly fails because of looking for the currently non-existing object that is going to be there soon.
TDDD Implementation Plan
It is crucial to understand what steps it is necessary to follow to implement test-driven database development properly.
According to Dave Green’s standards of test-driven database unit testing, the following things have to exist to meet the reporting requirement:
- Developing a single database object that will meet the reporting requirement.
- Creating a unit test to check the object existence (in order to fail the test as at that point the object does not exist).
- Creating of an object stub (placeholder) to pass the first test.
- Creating a second unit test to check if object outputs correct data in the table with correct input.
- Modifying the object definition to let the second test pass.
This can be illustrated as follows:
Think of a single database object
The first thing is to assign a database object which is capable of meeting the business requirement. In our case, we can think of “ArticlesPerAuthorReport” database object.
Create Database Unit Test to Check Desired Object Exists
Next step is to create a database unit test to check desired object existence. Although we know it does not exist at that point, it is necessary to take it as a starting point.
This tip works for dbForge Studio for SQL Server (or only dbForge Unit Test) and SSMS (SQL Server Management Studio). However, please note that I assume you already installed tSQLt Framework and ready to write the unit tests when using SSMS (SQL Server Management Studio).
To create the first database unit test, right-click the “SQLDevBlogReport database”. On the shortcut menu, click “Unit Test” and then “Add New Test…”
Write the following code to create the unit test that checks the existence or absence of the desired object:
CREATE PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport exists] AS BEGIN --Assemble --Act --Assert EXEC tSQLt.AssertObjectExists @ObjectName = N'ArticlesPerAuthorReport' END; GO
Run the Unit Test before Object is Created
Run the unit test that must fail since it checks the object created before. The object itself is created to comply with TDDD:
Create Object Stub and Run the Unit
Create an object stub with some hard-coded expected output, since at this point we just want to create a database object with the expected result.
Create ArticlesPerAuthorReport object as view stub (placeholder) at first:
-- (8) Create ArticlesPerAuthorReport view stub CREATE VIEW ArticlesPerAuthorReport AS SELECT 'Adil' AS Author, 10 AS [Total Articles] UNION ALL SELECT 'Sam' AS Author, 5 AS [Total Articles]
Run the Unit test that should pass now:
Creating a stub serves as a kick starter for TDDD that separates the concerns in the following way: firstly we need to create the object to pass the test and do not bother about the actual functioning of the object.
Create Unit Test to check Object Outputs Correct Data
The basic unit test passed so now it is time to check the desired object functioning properly or not.
Create another unit test to check desired object (ArticlesPerAuthorReport) output correct data by adding a new unit test to the database as follows:
Add the following unit test code:
CREATE PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport outputs correct] AS BEGIN --Assemble -- Create mocked up tables (blank copies of original tables without constraints and data) EXEC tSQLt.FakeTable @TableName = N'Author' ,@SchemaName = N'dbo' EXEC tSQLt.FakeTable @TableName = N'Article' ,@SchemaName = N'dbo' EXEC tSQLt.FakeTable @TableName = N'Category' ,@SchemaName = N'dbo' -- Add rows to the mocked up tables INSERT INTO Author (AuthorId,Name, RegistrationDate, Notes) VALUES (1,'Zak', DATEFROMPARTS(2017,01,01), 'Database Author'), (2,'Akeel',DATEFROMPARTS(2018,01,01),'Business Intelligence Author') INSERT INTO Category (CategoryID,Name, Notes) VALUES (1,'Database Development', '-'), (2,'Business Intelligene','-'); INSERT INTO Article (ArticleId,CategoryId, AuthorId, Title, Published, Notes) VALUES (1,1, 1, 'Advanced Database Development', DATEFROMPARTS(2017,02,01),'10K Views'), (1,1, 1, 'Database Development with Cloud Technologies', DATEFROMPARTS(2017,02,01),'5K Views'), (1,1, 1, 'Developing Databases with Modern Tools', DATEFROMPARTS(2017,03,01),'20K Views'), (1,2, 2, 'Business Intelligence Fundamentals', DATEFROMPARTS(2017,02,01),'10K Views'), (1,2, 2, 'Tabular Models', DATEFROMPARTS(2017,02,01),'50K Views') -- Create expected table CREATE TABLE ArticlesPerAuthorReport.Expected (Author VARCHAR(40),[Total Articles] int) -- Add expected results into expected table INSERT INTO ArticlesPerAuthorReport.Expected (Author, [Total Articles]) VALUES ('Zak', 3), ('Akeel',2); --Act -- Run ArticlesPerAuthorReport object (view) and put results into an actual table SELECT * INTO ArticlesPerAuthorReport.Actual FROM ArticlesPerAuthorReport apar --Assert -- Compare the expected and actual tables EXEC TSQLT.AssertEqualsTable @Expected = N'ArticlesPerAuthorReport.Expected' ,@Actual = N'ArticlesPerAuthorReport.Actual' END; GO
Run Unit Test to check Object Outputs Correct Data
Run the unit test that has to fail as well to comply with TDDD:
Add Required Functionality to ArticlesPerAuthorReport object
The unit test that checks the functionality of the object requires modified structure so that test can pass.
Modify ArticlesPerAuthorReport view to let the second unit test pass just like the first one:
ALTER VIEW ArticlesPerAuthorReport AS SELECT a.Name AS [Author],COUNT(a1.ArticleId) AS [Total Articles] FROM Author a INNER JOIN Article a1 ON a.AuthorId = a1.AuthorId GROUP BY a.Name
Run unit test to check if object outputs correct data
The database object has been successfully modified to output desired data.
Run all the unit tests:
Congratulations, both unit tests passed and the reporting requirements have been successfully met.
ArticlesPerAuthorReport object is ready to run behind the report that shows the total number of articles per author.
Unit Test to show a friendly message in case of no data
Don’t we have to create a handy database unit test to check a friendly message in case there is no data there?
The answer is “No” because nobody asked us to do that. These are the benefits of TDDD – you are only concerned with writing the unit tests that meet business requirements and do not provide extra coding.
Things to Do
Now you are ready to implement test-driven database development right from the beginning. After going through this article you can improve your skills further by the following things:
- Please try meeting the requirement to check whether object shows a friendly message in case of data absence, the requirement we deliberately left out in this article considering. It is also the part of the business requirement.
- Please try to meet another reporting requirement to see total articles per year.
- Do the same to see average articles per year.
dbForge Unit Test – an intuitive and convenient GUI for implementing automated unit testing in SQL Server Management Studio.
- Basics of SQL Server Management Studio (SSMS) – Part 1 - December 24, 2020
- Learn Database Design with SQL Server Management Studio (SSMS) – Part 2 - December 16, 2020
- A Practical Use of the SQL COALESCE Function - December 9, 2020