Jump to Start Test-Driven Database Development (TDDD) – Part 2

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:

  1. Creating of database unit test to check the existence of AddNewCategory database object.
  2. Failing of the unit test because of the database object absence.
  3. Creating the AddNewCategory object in order for the unit test to pass.
  4. The unit test determines whether AddNewCategory stored procedure is actually adding a new category or not.
  5. That unit test also fails.
  6. 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:

TDDD example1

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)

Then create and populate the tables for the sample database using the following code:

USE SQLDevBlogReportTDD;
-- (1) Create Author table in the sample database
 ,Name VARCHAR(40)
 ,RegistrationDate DATETIME2
 ,Notes VARCHAR(400)

-- (2) Create Article Category table in the sample database
 ,Name VARCHAR(50)
 ,Notes VARCHAR(400)

-- (3) Create Article table in the sample database
 ,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)


-- (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)
  ('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', '')

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

Run the view of the created table to see the results:

TDDD Example2

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:

  1. Developing a single database object that will meet the reporting requirement.
  2. Creating a unit test to check the object existence (in order to fail the test as at that point the object does not exist).
  3. Creating of an object stub (placeholder) to pass the first test.
  4. Creating a second unit test to check if object outputs correct data in the table with correct input.
  5. Modifying the object definition to let the second test pass.

This can be illustrated as follows:

TDDD Example3

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…”

TDDD Example4

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]
   EXEC tSQLt.AssertObjectExists @ObjectName = N'ArticlesPerAuthorReport'

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
    SELECT 'Adil' AS Author, 10 AS [Total Articles]
    SELECT 'Sam' AS Author, 5 AS [Total Articles]

TDDD Example6

Run the Unit test that should pass now:

TDDD Example7

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:

TDDD Example8

Add the following unit test code:

CREATE PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport outputs correct]
  --  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);

  --  Run ArticlesPerAuthorReport object (view) and put results into an actual table
  SELECT * INTO ArticlesPerAuthorReport.Actual FROM ArticlesPerAuthorReport apar

  --  Compare the expected and actual tables
  EXEC TSQLT.AssertEqualsTable @Expected = N'ArticlesPerAuthorReport.Expected'
                              ,@Actual = N'ArticlesPerAuthorReport.Actual'


Run Unit Test to check Object Outputs Correct Data

Run the unit test that has to fail as well to comply with TDDD:

TDDD Example9

TDDD Example10

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
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:

TDDD Example11

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.

Please stay in touch to learn more about test-driven database development that will come in Part-3 (soon).

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:

  1. 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.
  2. Please try to meet another reporting requirement to see total articles per year.
  3. Do the same to see average articles per year.

Useful tool:

dbForge Unit Test – an intuitive and convenient GUI for implementing automated unit testing in SQL Server Management Studio.

Haroon Ashraf

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional. He began his professional life as a computer programmer more than 10 years 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).

Leave a Reply

Your email address will not be published. Required fields are marked *