Test Driven Development with Modern Database Tools using tSQLt

By: Haroon Ashraf   |   Updated: 2018-01-12   |   Comments (1)   |   Related: > Testing

Problem

As a database developer I would like my development team to overcome the shortcomings of conventional database development with a better approach that not only helps us with early bug detection and designing requirements focused on the solution, but also speeds up database delivery cycles.

Solution

The solution is to implement test-driven database development (TDD) using advanced database unit-testing framework (tSQLt) with modern database tools (SSDT) which can easily replace the conventional database development and testing cycle with a more robust and reliable approach.

Before moving on to the implementation of TDD let's understand these approaches.

About Conventional Database Development

Let's have a quick look at the traditional/conventional database development methodology which looks good, but has a lot of shortcomings when it comes to real time database development scenarios.

What is Conventional Database Development

Conceptually speaking conventional database development in the context of unit-testing consists of the following steps:

1. Wait for the requirements to come
2. Design the whole system to meet the requirements
3. Define set of database objects to map the requirements
4. Build database objects (such as views or stored procedures)
5. Create unit-test for each database object
6. Run the unit-tests

This can be illustrated by the following diagram:

The best implementation of traditional database development methodology is if unit-level requirements can be defined inside the database objects themselves, according to a SQL expert.

Shortcomings of Conventional Database Development

1. Database objects end up with extra code/functionality which could have been consumed in meeting the requirements
2. Requirements scatter across a number of database objects which become difficult to maintain as the size of the database objects grow
3. Passed Unit-Tests do not guarantee requirements have been met
4. Unnoticed issues in objects under test become Production issues
5. A complete Test coverage is not possible
6. Objects and their unit-tests suffer maintainability issues when modifying their functionality
7. The code for the database object followed by its test often ends up with extra and un-desired unit-tests

About Test-Driven Development (TDD)

Test-Driven Development overcomes the shortcomings of conventional database development and testing. Let's develop a quick understanding of TDD.

Test-Driven Development (TDD)

Test-Driven Development methodology uses tests rather than objects to meet the requirements which means we write tests, in our case, database unit-tests to check if a particular user requirement at the unit-level is met by the object under test.

TDD consists of the following steps:

1. A unit-test is written to fail at first
2. The unit-test is then modified to meet certain requirements (by an object)
3. The unit-test is optimized and cleaned further without the need of any extra functionality

In other words, in Test-Driven Development (TDD) the database development begins with writing failed database unit-tests followed by modifying unit-tests to meet requirements followed by cleaning and optimizing the unit-tests.

So, each unit-test initiates the development which is purely focused on meeting the requirements without the need to worry about adding any extra functionality.

Benefits of Test-Driven Development (TDD)

1. The requirements are defined as test case(s)
2. The tests (or test cases) are primarily focused on meeting the requirements only
3. The development begins with testing rather than development which makes the overall solution easier to develop, test and maintain
4. TDD leaves no room for the writing extra functionality which is also not business priority
5. TDD helps in early bug detection rather than waiting for issues to be raised in a Production environment after deployment
6. It is easy to add new requirements ensuring they don’t conflict with existing ones
7. The strength of unit-tests defines the strength of your solution and development teams easily transform into test teams as far as unit-tests are concerned

TDD Implementation Scenario

Consider your development team already has a database which contains objects as a result of previously received requirements, but you have observed that the current approach is not helping your team in early bug detection and customer focused development.  Rather, more time is spent on adding unnecessary extra functionality and resolving issues becoming Production issues.

Now, you have received new business requirements and this time you have decided to implement test-driven development to get your team focused on meeting requirements only and see if early bugs can be detected rather than leaving them to become Production issues.

Database Setup (OfficeSuppliesSampleV2)

We will use OfficeSuppliesSampleV2 database (for demonstration purposes) which contains the following database objects:

1. Orders Table
2. Product Table
3. Customer Table
4. OrderType Table

Apart from the above four objects (tables) there is a procedure to add a customer and view to see the orders.

Please download and run the scripts in the link below to create and populate OfficeSuppliesSampleV2 database:

Download the Database Setup Files

Using Modern Database Tools (SSDT – SQL Server Data Tools)

Since modern database tools (SSDT - SQL Server Data Tools) are in use by development teams, the OfficeSuppliesSampleV2 database mentioned above is implemented in the form OfficeSuppliesSampleV2 Database Project to mimic a main transactional database which contains a simple version of customer-order-product scenario with the type of orders for office supplies.

In order to implement Test-Driven Development with SSDT we have to create a database project (mimicking a transactional database) and test project (to test database objects).

So we are looking to have the following database projects up and running:

1. OfficeSuppliesSampleV2_Data
2. OfficeSuppliesSampleV2_Tests

Please refer to my previous tip about implementing tSQLt using SSDT to run through all the steps required to create a database project and a test project.

Creating Database Project (OfficeSuppliesSampleV2_Data)

Create a new solution and add a database project called “OfficeSuppliesSampleV2_Data”.

Target Platform:

• Set the Target Platform as desired in our case we have set it to SQL Server 2012.

Debug Database:

• Also, set the debug database to point to your local SQL instance on dev machine (if you prefer to do so).

Always Recreate Database Deployment Option:

• Please set “Always recreate database” deployment option to true under Project Settings > Debug

Importing OfficeSuppliesSampleV2:

• Import the previously created OfficeSuppliesSampleV2 database and add the Reference Data scripts to be executed by the Post-Deployment Script to populate the debug database.
• Please download and add reference data scripts to the project under “Reference Data” folder
• Download Reference Data Files

SQLCMD Mode Enabled:

• Please make sure that SQLCMD mode is enabled which can be done by clicking on SQL (from main menu) > Execution Settings > SQLCMD Mode menu item as follows:

• The project is ready to be debugged after running post deployment scripts to populate the database objects:

Data Check

Next create debug database “OfficeSuppliesSample_Data” by debugging (pressing F5) the project.

Now check the debug database by refreshing SQL Server Object Explorer and clicking on view data for OrdersView:

Creating Test Project (OfficeSuppliesSampleV2_Tests)

Now create the OfficeSuppliesV2_Tests database project and add the tSQLt Database Unit-Testing Framework to it by referencing my previous tip.

The summary of the steps is as follows:

1. Download tSQLt from the website tSQLt.ORG or from this zip file
2. Create a temporary database “TSQLT_Temp” to hold tSQLtClass.sql script output
3. Run tSQLtClass.SQL script in TSQLT_Temp database to create tSQLt objects
4. Import TSQLT_Temp into freshly created test project (OfficeSuppliesSampleV2_Tests)
5. Set Target Platform of Test Project (OfficeSuppliesSample_Tests) is same as Database Project (OfficeSuppliesSampleV2_Data)
6. Set Target Connection settings (debug database) to point to the same SQL instance with a different database name (OfficeSuppliesSampleV2_Tests) used by database project
7. Pre-Deployment script (as shown below) which enables CLR must be there

/* 
Pre-Deployment Script Template 
-------------------------------------------------------------------------------------- 
This file contains SQL statements that will be executed before the build script. 
Use SQLCMD syntax to include a file in the pre-deployment script. 
Example:      :r .\myfile.sql 
Use SQLCMD syntax to reference a variable in the pre-deployment script. 
Example:      :setvar TableName MyTable 
               SELECT * FROM [$(TableName)] 
-------------------------------------------------------------------------------------- 
*/ 
  
EXEC sp_configure 'clr enabled', 1; 
RECONFIGURE; 
GO 

8. Post-Deployment script (as shown below) which runs all the unit-tests must also be there

EXEC tSQLt RunAll 

These steps are mentioned in detail in my previous tip.

As a result of following above steps, our test project is now ready as shown below:

Test Run

Before we do a test run we have to ensure (as already mentioned) that the Target Platform for the test project is the same as the main project and the debug database has been configured successfully to point to the desired SQL instance and the following options are selected:

Now debugging of the test project (OfficeSuppliesSampleV2_Tests) should run the tSQLt tests:

Satisfying Business Requirements with TDD

Now the database project and test project have been setup successfully and we have replicated the scenario where the database already contains objects, so, it is time to begin the test-driven development to satisfy the requirements mentioned before.

Business Requirements

You have received the following business requirements:

1. How many Orders have been placed for each Order Type?
2. How many Customers placed Orders for each Order Type?

Pick an object such as OrderTypesReport

After receiving the business requirements, the first thing that comes to mind is to map these requirements to a single object (if possible) which we think should be able to meet the new requirements.

We have picked OrderTypesReport database object which is going to meet the new requirements.

At this point it does not matter whether the desired object (OrderTypesReport) is going to be a View or Stored Procedure and also it is not necessarily a report (object), but has the capability to become a report (object).

Create OrderTypesReportTests Schema (Test Class)

The first step in TDD using tSQLt is to create a test class where we can group all our related unit-tests.

According to Dave Green if you name your test class (schema) same as the name of the object under test, this makes unit-tests easier to manage. We are following the same rule as suggested by Dave Green who is an expert in this field.

So, we create a new folder OrderTypesReportTests and then create schema OrderTypesTests as follows:

Then writing the following code:

CREATE SCHEMA [OrderTypesReportTests]
Authorization dbo
GO
EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='OrderTypesReportTests'
   

TDD Begins by Creating a Test rather than Object

The test-driven development (TDD) begins by creating a unit-test to check if the object which is responsible to meet the requirements exists or not and we know at this point it does not exist, so we expect the test to fail first.

Therefore, TDD begins by creating a unit-test to check if the object exists rather than first creating the object and then creating a test.

The benefit of this approach is that it is a purely test-driven approach, so it is initially the test rather than the object that ensures the business requirements.

Create a Unit-Test to check OrderTypesReport object exists

Create a unit-test by creating a new stored procedure under OrderTypesReportTests schema by adding a Stored Procedure as follows:

CREATE PROCEDURE [OrderTypesReportTests].[test to check OrderTypesReport exists]
AS
BEGIN
 EXEC tSQLt.AssertObjectExists 'dbo.OrderTypesReport'
END 

Run Unit-Test that checks OrderTypesReport object exists

Press F5 to start debugging and the test results should fail as shown below:

Create OrderTypesReport object

Now create OrderTypesReport object as a view in the database project, but as a stub (placeholder) without the desired output since the purpose here is to create objects first (keeping the output in mind) as follows:

CREATE VIEW [dbo].[OrderTypesReport]
AS
SELECT 'X' as OrderType,0 as OrderCount,0 CustomersCount

Now keep the project (OfficeSuppliesSampleV2_Data) selected and press F5 to deploy the changes and then using SQL Server Object Explorer (SSOX) run the newly created view (stub) after refreshing the database (OfficeSuppliesSampleV2_Data) to see the expected result pattern:

Rerun Unit-Test to check object exists

Now re-run the unit-test (by pressing F5) and it should be successful:

Steps to Create a Unit-Test to check OrderTypesReport outputs desired data

Next we are going to create a unit-test to check OrderTypesReport outputs correct data which we are calling desired data. This can be achieved by first focusing on the requirements below:

1. How many Orders have been placed for each Order Type?
2. How many Customers placed Orders for each Order Type?

Isolating Unit-Test from Data and Actual Tables

According to Dave Green as a standard practice the unit-test should not rely on the following:

1. Data of the database whose objects are under test
2. Physical Tables of the database under test

We have to isolate the unit-test from both data and the physical tables of the database under test so that unit-test should not depend on the data present in the database and does not need to be concerned about constraints and dependencies of the table(s) under test.

The solution is to use tSQLt Fake Tables (which are true copies of original tables with no constraints) and embed realistic data inside the unit-test.

Create a Unit-Test to check OrderTypesReport outputs desired data

Let's begin by creating a unit-test (stored procedure) as follows:

CREATE PROCEDURE [OrderTypesReportTests].[test to check OrderTypesReport outputs desired data]
AS
-- Assemble
EXEC tSQLt.FakeTable @TableName='dbo.Customer' -- Fake Customer table
EXEC tSQLt.FakeTable @TableName='dbo.OrderType'-- Fake OrderType table
EXEC tSQLt.FakeTable @TableName='dbo.Orders' -- Fake Orders table
 
INSERT INTO dbo.Customer -- Insert realistic data into Customer fake table
(CustomerID,Name)
VALUES
(1,'Adil'),
(2,'Sadaf'),
(3,'Sam')
 
INSERT INTO dbo.OrderType -- Insert realistic data into OrderType fake table
(OrderTypeID,Name)
VALUES
(1,'Online'),
(2,'In-store'),
(3,'Email')
 
INSERT INTO dbo.Orders -- Insert realistic data into Orders fake table
(OrderID,OrderTypeID,CustomerId)
VALUES
(1,1,1), -- Online (1)
(2,1,1), -- Online (2) 
(3,1,2), -- Online (3) 
(4,2,3), -- In-store (1)
(5,3,3)  -- Email (1)
 
Create TABLE [OrderTypesReportTests].[Expected] 
(
[OrderType] VARCHAR (40)  NOT NULL,
[OrdersCount] INT NOT NULL,
[CustomersCount] INT NOT NULL
 
)
 
INSERT INTO OrderTypesReportTests.Expected -- Insert results based on above tables into expected table
(OrderType,OrdersCount,CustomersCount)
VALUES
('Online',3,3), -- Online: 3 Orders
('In-store',1,1), -- In-stored: 1 Order
('Email',1,1) -- Email: 1 Order
 
-- Act
SELECT * INTO OrderTypesReportTests.Actual FROM OrderTypesReport -- Put the results of the OrderTypesReport view into Actual table
 
 -- Assert (compare expected table with actual table results)
EXEC tSQLt.AssertEqualsTable @Expected='OrderTypesReportTests.Expected',@Actual='OrderTypesReportTests.Actual'
  

Run Unit-Test to check OrderTypesReport outputs desired data

Now run the unit-test by debugging the test project and expect the results to fail first:

Update OrderTypesReport view for the Unit-Test to Pass

Next modify OrderTypesReport view’s script to get the desired output, so the unit-test meets the desired requirements and can pass.

The modified script output below looks promising:

USE OfficeSuppliesSampleV2_Data
 
-- Total orders and customers per OrderType
SELECT TYP.Name as OrderType,COUNT(ORD.OrderID) as OrdersCount,COUNT(CST.CustomerID) as CustomersCount FROM OrderType TYP
INNER JOIN Orders ORD ON TYP.OrderTypeID=ORD.OrderTypeID
INNER JOIN Customer CST ON CST.CustomerID=ORD.CustomerId
GROUP BY TYP.Name
ORDER BY TYP.Name

Now update the view based on above script excluding the Order By clause which is not allowed in view(s):

CREATE VIEW [dbo].[OrderTypesReport]
AS
-- Total orders and customers per OrderType
SELECT TYP.Name as OrderType,COUNT(ORD.OrderID) as OrdersCount,COUNT(CST.CustomerID) as CustomersCount FROM OrderType TYP
INNER JOIN Orders ORD ON TYP.OrderTypeID=ORD.OrderTypeID
INNER JOIN Customer CST ON CST.CustomerID=ORD.CustomerId
GROUP BY TYP.Name 

Please note that SSDT supports declarative database development, we simply declare what we need rather than how we need, so updating the view is not required since replacing the existing view would suffice here.

While OfficeSuppliesSampleV2_Data project is selected press F5 to deploy the new changes successfully to the debug database.

Re-run Unit-Test to check OrderTypesReport outputs desired data

Now re-run the unit-test to see it pass this time:

Your Test-Driven Development has been implemented successfully and your development team is only concerned with writing unit-test(s) which meet requirements, but obviously a unit-test which checks if a requirement is met must first check (in another unit-test) whether the object which meets the requirements exists or not.

So, it is very unlikely that a unit-test to validate requirements is skipped in a fully functional test-driven development and also adding new requirements should not conflict with existing requirements which can also be easily tested by running all the unit-tests. It is also easy to detect bugs earlier by simply running the unit-tests. There are other benefits of this approach such as not having to write extra code and teams are focused on meeting business requirements.

Next Steps

• Try meeting another requirement “which customer has placed the most orders?” using test-driven development (TDD)
• Try meeting another requirement “which order type is mostly used by the customers to place orders on weekends (Saturday and Sunday)?” using test-driven development (TDD)
• Try converting Tip # 2 unit-tests created using SSDT (SQL Server Data Tools) into test-driven development (TDD)
• Try converting Tip # 1 unit-tests created using SSMS (SQL Server Management Studio) into test-driven development (TDD)
• Check out tSQLt Tutorials to gain in-depth understanding of how it works

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips