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

This article is a walk-through of creating a report base on a database object developed and tested by using test-driven development (TDDD). Furthermore, some tips for improving database unit testing via TDDD will be discussed in this article too.

Test-driven database development (TDDD) Recap

In simple words, TDDD is all about writing unit-test before the database object is even created. So, if we want to create a database object to satisfy some business requirement, it should be processed by creating a unit-test that ensures the object exists. Moreover, it has to be followed by another unit-test that will ensure the proper functioning of the database object (at the minimum) though that functioning check should only be limited to meet only the desired requirement.

Jump to Start TDDD Part-1 Recap

In the first part of the article, traditional database development was compared with test-driven database development (TDDD) and provided with coding examples. TDDD began with the unit-test aimed at checking of the object exists and followed by the unit test that checked if the object meets the requirement.

For a detailed walk-through, please, refer to the first part of this article.

Jump to Start TDDD Part-2 Recap

The second part of the article was focused on meeting a reporting requirement to view articles per author. This requirement was finally met by the unit test aimed at checking the object creation and followed by the unit test aimed at checking if the object sends the correct number of articles per author or not.

We ran the database unit tests that passed in order to satisfy the reporting requirement.

Please, refer to the second part of this article to the see details of all the steps for meeting the report requirements.

Anatomy of Unit Test to Check Object Outputs Correct Data

One of the main unit tests in TDDD is aimed at checking whether database object outputs correct data or not. It is achieved as follows:

  1. Creating of mocked up tables (blank copies of original tables without constraints and data).
  2. Adding rows to the mocked-up tables.
  3. Creating the expected table.
  4. Adding expected results to the expected table.
  5. Running database object (such as view) and putting results into an actual table.

Comparing the expected table with the actual table.TDDD part3 pic1

Implementing Reporting Requirement (ArticlesPerAuthorReport)

In this article, we are going to implement the reporting requirement that was deeply discussed in the previous part of the article.

Reset SQLDevBlogReportTDD Sample Database

We are going to reset SQLDevBlogReportTDD database which was created by us in the previous part of this article and add more data to it.

Sample Database Already Present

If you have already created SQLDevBlogReportTDD database by following walk-through in the second part, then, please, don’t run the scripts in steps 9, 10 and 11.

Creating Sample Database for the first time

If you are creating the database for the first time, then, please, create a blank database SQLDevBlogReportTDD first and then follow all the steps from 1 through 11.


Data Check by Running View vwAuthors

In order to make sure of adding enough data to our sample database, retrieve data from the view vwAuthors to see all the records:

TDDD part3 pic2

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.

Running the unit tests

Run the database unit tests that we created originally (in previous part or just now) through test-driven database development:

TDDD part3 pic3

Congratulations, both tests have passed that means the desired database object is capable of meeting the reporting requirement to view articles per author.

Create Database Report Based on ArticlesPerAuthorsReport Object

You can create a database report in a number of ways (such as using Report Builder, creating Report Server Project in Visual Studio Data Tools or using dbForge Studio for SQL Server).

In this article, we are using dbForge Studio for SQL Server for report creating.

To proceed further, please, click on “New” from File Menu and then select “Data Report”:

Click “Standard Report” as shown below:

TDDD part3 pic5

Select “Simple Table\View” as “Data Type”:

TDDD part3 pic6

Add the base object (ArticlesPerAuthorReport) which is view in our case:

TDDD part3 pic7

Add the required fields:TDDD part3 pic8

No grouping is required at this point so proceed further by clicking “Next”:

TDDD part3 pic9

Select “Layout” and “Orientation” of the data report:

TDDD part3 pic10

Finally, add title “Articles per Author Report” to the report and click “Finish”:

TDDD part3 pic11

Next, adjust the formatting of the report as per requirement:

TDDD part3 pic12

Click “Preview” to see the database report:

TDDD part3 pic13

Save the report as “ArticlesPerAuthorReport”. Finally, database report has been created due to the business requirements.

Use of Setup Procedure

While writing database unit tests via using tSQLt, some test code is repeated quite often. It can be defined in a setup procedure and reused afterward in all the unit tests of that particular test class.

According to Dave Green, each test class can have only one setup procedure that runs automatically before the unit tests of that class processes.

Almost all the test code, which is written under Assemble (section), can be put under setup procedure to avoid code duplication.

For instance, creating mocked tables along with expected table and adding data to mocked tables and then to the expected table can be easily defined under the setup procedure and that it can be reused afterward.

Creating Setup Procedure to avoid test code duplication

Create a stored procedure in SQLDevBlogTDD as follows:

TDDD part3 pic14

Now remove the test code, which we have already written in setup procedure, from the unit test to check ArticlesPerAuthorReport outputs correct as follows:

Running All Unit Tests to Check Setup Procedure Working

Run the unit tests and see the results:

TDDD part3 pic15

Congratulations, the unit tests have run successfully despite the fact we are using setup procedure to run some part of test code before these unit tests are running.

Please, stay in touch because more about test-driven database development will come in Part-4 (soon).

Things to Do

You can now easily create a database report based on the object which was created through test-driven development in order to meet the business requirement.

  1. Please, try to meet another reporting requirement to show articles per year while using TDDD.
  2. Please, try to do the same to show articles by category while following test-driven database development.
  3. Please, while using TDDD, try to meet a reporting requirement to view, which authors have not written any articles.
  4. Please, try to meet a business requirement to search article by title via using TDDD.

Useful tool:

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

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).
Haroon Ashraf