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

The most common approach to developing database solutions is to start creating database objects based on business requirements which is also known as “Conventional Database Development”.

In this article, we are going to explore the implementation of such approaches as conventional database development and test-driven database development on the particular examples.

To begin with, have a closer look at the conventional database development.

Conventional Database Development

The conventional database development starts with the development of database objects such as tables, views, stored procedures, etc.

After the database development team receives database-related business requirements, they start working on the development of database objects to meet these requirements.

The development style consists of the following steps:

  1. Receive the requirements
  2. Create database objects based on requirements
  3. Run unit tests for database objects to see if they meet requirements
  4. Receive new requirements
  5. Modify existing database objects or add new ones to meet the new requirements
  6. Create and run unit tests to check whether new requirements work accordingly and don’t conflict with the previous ones

We are going to consider a particular example to get a quick understanding of a conventional database development style.

SQLDevBlog Sample Database Setup

Create a sample database called SQLDevBlog:

Use the following code to create the tables for the sample database:

Review the database diagram with our newly created tables:

Please note that I am using dbForge Studio for SQL Server so the output look may differ if you run the same code in SSMS (SQL Server Management Studio). However, there is no difference between scripts and their results.

Populating Sample Database (SQLDevBlog)

We have to populate our sample database to create a more realistic scenario as follows:

As a result, we have received the following populated tables:

When we are done with database setup and table population, the next step is to imitate the scenario with a new requirement.

Requirement to Add a New Category

A new requirement states that an administrator should be able to add a new category to the list of available categories.

Creating AddCategory Database Object to Meet Requirement

In order to meet this requirement, your development team has to come up with an idea of developing a stored procedure that can help them to add a new requirement with ease.

To create the required stored procedure, run the following script:

The result is as follows:

Create Database Unit Test to Check whether the Procedure Works Properly

The next step is to create a database unit test to check if the stored procedure meets the specification.

This tip works for dbForge Studio for SQL Server (or only dbForge Unit Test) and SSMS (SQL Server Management Studio). However, please note that when using SSMS (SQL Server Management Studio) I assume you have already installed tSQLt Framework and ready to write the unit tests.

To create the first database unit test, right-click the SQLDevBlog database. On the shortcut menu, click Unit Test and then Add New Test…

The Add New Test window opens. Fill in all the required information as shown below and click Add Test.

Create the unit test as follows and then save it:

Click the Database menu -> Unit Test -> View Test List and run the unit test as shown below:

Congratulations! The unit test to check if AddCategory procedure works or not has been succeeded.

Summary

We can say that if the AddCategory stored procedure unit test runs successfully (pass) then it means a new category can be added to the database (table) which in turn indicated that the requirement to add new category has been satisfied.

Now, we are going to explore the test-driven database development and to describe how the process of writing unit tests can satisfy requirements.

Test Driven Database Development (TDDD)

Test-driven database development begins with writing the unit test that first fails, gets modified to pass, and then further refined, while the conventional database development starts with database objects development to meet the requirements followed by unit tests to see if objects work properly.

In TDDD (test-driven database development), database unit tests are written to meet requirements and unit tests that require database objects to be created and run properly.

SQLDevBlogTDD Sample Database Setup

In order to understand the difference between these two approaches (traditional database development and test-driven database development), let us create the SQLDevBlogTDD database which is the same as SQLDevBlog except that it refers to TDDD to demonstrate how test-driven database development works:

Create the SQLDevBlogTDD sample database:

Then, populate the sample database with the tables:

Populating Sample Database (SQLDevBlogTDD)

We have to populate our sample database to create a more realistic scenario as follows:

Requirement to Add New Category (TDDD)

Now, consider the same requirement that an administrator should be able to add a new category to the list of available categories in the context of the test-driven database development.

Meeting the Requirement (TDDD)

In order to meet the requirement, a database unit test that looks for a potential object should be written first.

That is how TDDD works: the unit test first fails since we assume we are looking for the object which currently does not exist but is going to be there soon.

Create Database Unit Test to Check the Desired Object Exists

The next step is to create a database unit test to check if the desired object exists. Although we know that it does not exist at that point but think of this as a starting point.

In dbForge Studio for SQL Server, the database unit test that supports TDDD by default is created to fail first. Then, we are going to change it a little bit. If you are directly using a tSQLt database unit test framework, then write the following unit test:

Run Database Unit Test to See if it Fails

After you run the database unit test, you can see that the test failed:

Create Database Object and Rerun Unit Test

The next step is to create the required database object which is a stored procedure in our case. Please don’t worry about the functionality at the moment:

Run the unit test again and see that this time it succeeds:

Passing the unit test that checks that the stored procedure exists is not enough. We also need to see whether the stored procedure adds a new category or not.

Create Database Unit Test to Check if Routine Functions Properly

Now, create a database unit test that checks if the routine function adds a new category:

Run Database Unit Test to see it fails

As you can see, the unit test failed for the first time and succeeded for the second time:

Add Functionality to Routine and Rerun Unit Test

Modify stored procedure by adding the required functionality so that the test can succeed as shown below:

Rerun the unit tests to check that they all succeed including the recently modified stored procedure:

Congratulations! You have successfully implemented test-driven database development which helps you to focus on requirements only. Your unit tests encapsulate requirements thereby demanding database objects to be created and run properly to meet the specification.

Please stay in touch! The Part 2 about test-driven database development is coming soon.

Things to Do

That’s it. You are ready to implement test-driven database development right from the beginning of your database development after going through this article.

To improve your skills further, perform the following things:

  1. Please try meeting a new requirement to delete a category using TDDD (creating unit test first followed by database object)
  2. Please try to meet another requirement to search a category using TDDD
  3. Please try working on any other requirement such as logging information about adding a new category which means every time a category is added or deleted a log is maintained
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).
Haroon Ashraf