Art of Isolating Dependencies and Data in Database Unit Testing

All the database developers more or less write database unit tests that not only help in detecting bugs early but also save a lot of time and efforts when the unexpected behavior of database objects becomes a production issue.

Nowadays, there are a number of database unit testing frameworks such as tSQLt along with third-party unit testing tools including dbForge Unit Test.

On the one hand, the benefit of using third-party testing tools is that the development team can instantly create and run unit tests with added features. Also, using a testing framework directly gives you more control over the unit tests. Therefore, you can add more functionality to the unit testing framework itself. However, in this case, your team must have time and a certain level of expertise to do this.

This article explores some standard practices that can help us to improve the way we write database unit tests.

First, let us go through some key concepts of database unit testing.

What is Database Unit Testing

According to Dave Green, database unit tests ensure that small units of the database, such as tables, views, stored procedures, etc., are working as expected.

Database unit tests are written to verify whether the code meets business requirements.

For example, if you receive a requirement such as “A librarian (end-user) should be able to add new books to the library (Management Information System)”, you need to think of applying unit tests for the stored procedure to check if it can add a new book to the Book table.

Sometimes, a series of unit tests ensure that the code meets the requirements. Therefore, most of the unit testing frameworks including tSQLt allow grouping related unit tests into a single test class rather than running individual tests.

AAA Principle

It is worth mentioning about the 3-step principle of unit testing which is a standard practice to write unit tests. The AAA principle is the basis for unit testing and consists of the following steps:

1. Arrange/Assemble
2. Act
3. Assert

The Arrange section is the first step in writing database unit tests. It guides through configuring a database object for testing and setting up the expected results.

The Act section is when a database object (under test) is called to produce the actual output.

The Assert step deals with matching the actual output to the expected one and verifies whether the test either passes or fails.

Let’s explore these methods on particular examples.

If we create a unit test to verify that the AddProduct stored procedure can add a new product, we set up the Product and ExpectedProduct tables after the product is added. In this case, the method comes under the Arrange/Assemble section.

Calling the AddProduct procedure and putting the result into the Product table is covered by the Act section.

The Assert part simply matches the Product table with the ExpectedProduct table to see whether the stored procedure has been executed successfully or failed.

Understanding Dependencies in Unit Testing

So far, we have discussed basics of database unit testing and importance of the AAA (Assemble, Act, and Assert) principle when creating a standard unit test.

Now, let us focus on another important piece of the puzzle – dependencies in unit testing.

Apart from following the AAA principle and focusing only on a particular database object (under test), we also need to know the dependencies that may affect unit tests.

The best way to understand dependencies is to look at an example of a unit test.

EmployeesSample Database Setup

To move on, create a sample database and call it EmployeesSample:

-- Create the Employees sample database to demonstrate unit testing

CREATE DATABASE EmployeesSample;
GO

Now, create the Employee table in the sample database:

-- Create the Employee table in the sample database

USE EmployeesSample

CREATE TABLE Employee
  (EmployeeId INT PRIMARY KEY IDENTITY(1,1),
  NAME VARCHAR(40),
  StartDate DATETIME2,
  Title VARCHAR(50)
  );
GO

Populating Sample Data

Populate the table by adding few records:

-- Adding data to the Employee table
INSERT INTO Employee (NAME, StartDate, Title)
  VALUES 
  ('Sam','2018-01-01', 'Developer'),
  ('Asif','2017-12-12','Tester'),
  ('Andy','2016-10-01','Senior Developer'),
  ('Peter','2017-11-01','Infrastructure Engineer'),
  ('Sadaf','2015-01-01','Business Analyst');
GO

The table looks like this:

-- View the Employee table

  SELECT e.EmployeeId
        ,e.NAME
        ,e.StartDate
        ,e.Title FROM  Employee e;
GO

Please note that I am using dbForge Studio for SQL Server in this article. Thus, the output look may differ if you run the same code in SSMS (SQL Server Management Studio). There is no difference when it comes to scripts and their results.

Requirement to Add New Employee

Now, if a requirement to add a new employee has been received, the best way to meet the requirement is to create a stored procedure which can successfully add a new employee to the table.

To do this, create the AddEmployee stored procedure as follows:

-- Stored procedure to add a new employee 

CREATE PROCEDURE AddEmployee @Name VARCHAR(40),
@StartDate DATETIME2,
@Title VARCHAR(50)
AS
BEGIN
  SET NOCOUNT ON
    INSERT INTO Employee (NAME, StartDate, Title)
  VALUES (@Name, @StartDate, @Title);
END

Unit Test to Verify whether the Requirement is Met

We are going to write a database unit test to verify if the AddEmployee stored procedure meets the requirement to add a new record to the Employee table.

Let us focus on understanding the unit test philosophy by simulating a unit test code rather than writing a unit test with a testing framework or third-party unit testing tool.

Simulating Unit Test and Applying AAA Principle in SQL

The first thing we need to do is to imitate the AAA principle in SQL since we are not going to use any unit testing framework.

The Assemble section is applied when the actual and expected tables are normally set up along with the expected table getting populated. We can make use of SQL variables to initialize the expected table in this step.

The Act section is used when the actual stored procedure is called to insert data into the actual table.

The Assert section is when the expected table matches the actual table. Simulating the Assert part is a little bit tricky and can be achieved by the following steps:

• Counting the common (matching) rows between two tables which should be 1 (since the expected table has just one record that should match the actual table)
• Excluding the actual table records from the expected table records should equal 0 (if the record in the expected table exists in the actual table as well, then excluding all the actual table records from the expected table should return 0)

The SQL script is as follows:

[expand title=”Code”]

-- Simulating unit test to test the AddEmployee stored procedure

CREATE PROCEDURE TestAddEmployee
AS
BEGIN
  -- (1) Assemble

  -- Set up new employee data
  DECLARE @EmployeeId INT = 6
         ,@NAME VARCHAR(40) = 'Adil'
         ,@StartDate DATETIME2 = '2018-03-01'
         ,@Title VARCHAR(50) = 'Development Manager'


  -- Set up the expected table
  CREATE TABLE #EmployeeExpected (
    EmployeeId INT PRIMARY KEY IDENTITY (6, 1) 
    -- the expected table EmployeeId should begin with 6 
    -- since the actual table has already got 5 records and 
    -- the next EmployeeId in the actual table is 6
   ,NAME VARCHAR(40)
   ,StartDate DATETIME2
   ,Title VARCHAR(50)
  );

  -- Add the expected table data
  INSERT INTO #EmployeeExpected (NAME, StartDate, Title)
    VALUES (@NAME, @StartDate, @Title);

  -- (2) Act

  -- Call AddEmployee to add new employee data to the Employee table
  INSERT INTO Employee
  EXEC AddEmployee @NAME
                  ,@StartDate
                  ,@Title



  -- (3) Assert

  -- Match the actual table with the expected table
  DECLARE @ActualAndExpectedTableCommonRecords INT = 0 -- we assume that expected and actual table records have nothing in common

  SET @ActualAndExpectedTableCommonRecords = (SELECT
      COUNT(*)
    FROM (SELECT
        e.EmployeeId
       ,e.NAME
       ,e.StartDate
       ,e.Title
      FROM Employee e
      INTERSECT
      SELECT
        ee.EmployeeId
       ,ee.NAME
       ,ee.StartDate
       ,ee.Title
      FROM #EmployeeExpected ee) AS A)


  DECLARE @ExpectedTableExcluldingActualTable INT = 1 -- we assume that expected table has records which do not exist in the actual table

  SET @ExpectedTableExcluldingActualTable = (SELECT
      COUNT(*)
    FROM (SELECT
        ee.EmployeeId
       ,ee.NAME
       ,ee.StartDate
       ,ee.Title
      FROM #EmployeeExpected ee
      EXCEPT
      SELECT
        e.EmployeeId
       ,e.NAME
       ,e.StartDate
       ,e.Title
      FROM Employee e) AS A)


  IF @ActualAndExpectedTableCommonRecords = 1
    AND @ExpectedTableExcluldingActualTable = 0
    PRINT '*** Test Passed! ***'
  ELSE
    PRINT '*** Test Failed! ***'

END

[/expand]

Running Simulated Unit Test

After the stored procedure is created, execute it with the simulated unit test:

-- Running simulated unit test to check the AddEmployee stored procedure
EXEC TestAddEmployee

The output is as follows:

Congratulations! The database unit test passed.

Identifying Issues in the form of Dependencies in Unit Test

Can we detect anything wrong in the unit test we created despite the fact that it has been written and run successfully?

If we closely look at the unit test setup (the Assemble part), the expected table has an unnecessary binding with the identity column:

Before writing a unit test we have already added 5 records to the actual (Employee) table. Thus, at the test setup, the identity column for the expected table begins with 6. However, this means that we always expect 5 records to be in the actual (Employee) table to match it with the expected table (#EmployeeExpected).

In order to understand how this may affect the unit test, let us have a look at the actual (Employee) table now:

Add another record to the Employee table:

-- Adding a new record to the Employee table

INSERT INTO Employee (NAME, StartDate, Title)
  VALUES ('Mark', '2018-02-01', 'Developer');

Have a look at the Employee table now:

Delete EmpoyeeId 6 (Adil) so that the unit test can run against its own version of EmployeeId 6 (Adil) rather than the previously stored record.

-- Deleting the previously created EmployeeId: 6 (Adil) record from the Employee table

DELETE FROM Employee
  WHERE EmployeeId=6

Run the simulated unit test and see the results:

-- Running the simulated unit test to check the AddEmployee stored procedure

EXEC TestAddEmployee

The test has failed this time. The answer lies in the Employee table result set as shown below:

The Employee Id binding in the unit test as mentioned above does not work when we re-run the unit test after adding a new record and deleting the previously added employee record.

There are three types of dependencies in the test:

1. Data Dependency
2. Key Constraint Dependency
3. Identity Column Dependency

Data Dependency

First of all, this unit test depends on data in the database. According to Dave Green, when it comes to the unit testing database, the data itself is a dependency.

This means that your database unit test should not rely on the data in the database. For example, your unit test should contain the actual data to be inserted into the database object (table) rather than rely on the data already existing in the database which can be deleted or modified.

In our case, the fact that five records have already been inserted into the actual Employee table is a data dependency which must be prevented because we should not violate the philosophy of unit test that says only the unit of the code is tested.

In other words, test data should not rely on the actual data in the database.

Key Constraint Dependency

Another dependency is a key constraint dependency which means that the primary key column EmployeeId is also a dependency. It must be prevented in order to write a good unit test. However, a separate unit test is required to test a primary key constraint.

For example, in order to test the AddEmployee stored procedure, the Employee table primary key should be removed so that an object can be tested without the worry of violating a primary key.

Identity Column Dependency

Just like a primary key constraint, the identity column is also a dependency. Thus, there is no need in testing the identity column auto-increment logic for the AddEmployee procedure; it must be avoided at any cost.

Isolating Dependencies in Unit Testing

We can prevent all three dependencies by removing the constraints from the table temporarily and then do not depend on the data in the database for the unit test. This is how the standard database unit tests are written.

In this case, one might ask where the data for the Employee table came from. The answer is that the table gets populated with test data defined in the unit test.

Altering Unit Test Stored Procedure

Let us now remove the dependencies in our unit test:

[expand title=”Code”]

-- Simulating dependency free unit test to test the AddEmployee stored procedure
ALTER PROCEDURE TestAddEmployee
AS
BEGIN
  -- (1) Assemble

  -- Set up new employee data
  DECLARE @NAME VARCHAR(40) = 'Adil'
         ,@StartDate DATETIME2 = '2018-03-01'
         ,@Title VARCHAR(50) = 'Development Manager'

  -- Set actual table
  DROP TABLE Employee -- drop table to remove dependencies

  CREATE TABLE Employee -- create a table without dependencies (PRIMARY KEY and IDENTITY(1,1))
  (
    EmployeeId INT DEFAULT(0)
   ,NAME VARCHAR(40)
   ,StartDate DATETIME2
   ,Title VARCHAR(50)
  )

  -- Set up the expected table without dependencies (PRIMARY KEY and IDENTITY(1,1)
  CREATE TABLE #EmployeeExpected (
    EmployeeId INT DEFAULT(0)
   ,NAME VARCHAR(40)
   ,StartDate DATETIME2
   ,Title VARCHAR(50)
  )

  -- Add the expected table data
  INSERT INTO #EmployeeExpected (NAME, StartDate, Title)
    VALUES (@NAME, @StartDate, @Title)

  -- (2) Act

  -- Call AddEmployee to add new employee data to the Employee table
  EXEC AddEmployee @NAME
                  ,@StartDate
                  ,@Title
 
  -- (3) Assert

  -- Match the actual table with the expected table
  DECLARE @ActualAndExpectedTableCommonRecords INT = 0 -- we assume that the expected and actual table records have nothing in common

  SET @ActualAndExpectedTableCommonRecords = (SELECT
      COUNT(*)
    FROM (SELECT
        e.EmployeeId
       ,e.NAME
       ,e.StartDate
       ,e.Title
      FROM Employee e
      INTERSECT
      SELECT
        ee.EmployeeId
       ,ee.NAME
       ,ee.StartDate
       ,ee.Title
      FROM #EmployeeExpected ee) AS A)


  DECLARE @ExpectedTableExcluldingActualTable INT = 1 -- we assume that the expected table has records which donot exist in actual table

  SET @ExpectedTableExcluldingActualTable = (SELECT
      COUNT(*)
    FROM (SELECT
        ee.EmployeeId
       ,ee.NAME
       ,ee.StartDate
       ,ee.Title
      FROM #EmployeeExpected ee
      EXCEPT
      SELECT
        e.EmployeeId
       ,e.NAME
       ,e.StartDate
       ,e.Title
      FROM Employee e) AS A)


  IF @ActualAndExpectedTableCommonRecords = 1
    AND @ExpectedTableExcluldingActualTable = 0
    PRINT '*** Test Passed! ***'
  ELSE
    PRINT '*** Test Failed! ***'

  -- View the actual and expected tables before comparison
    SELECT e.EmployeeId
          ,e.NAME
          ,e.StartDate
          ,e.Title FROM Employee e

      SELECT    ee.EmployeeId
               ,ee.NAME
               ,ee.StartDate
               ,ee.Title FROM #EmployeeExpected ee
  
  -- Reset the table (Put back constraints after the unit test)
  DROP TABLE Employee
  DROP TABLE #EmployeeExpected

  CREATE TABLE Employee (
    EmployeeId INT PRIMARY KEY IDENTITY (1, 1)
   ,NAME VARCHAR(40)
   ,StartDate DATETIME2
   ,Title VARCHAR(50)
  );

END

[/expand]

Running Dependency-Free Simulated Unit Test

Run the simulated unit test to see the results:

-- Running the dependency-free simulated unit test to check the AddEmployee stored procedure

EXEC TestAddEmployee

Rerun the unit test to check the AddEmployee stored procedure:

-- Running the dependency-free simulated unit test to check the AddEmployee stored procedure

EXEC TestAddEmployee

Congratulations! Dependencies from the unit test have been removed successfully.

Now, even if we add a new record or set of new records to the Employee table, it is not going to affect our unit test since we have removed the data and constraint dependencies from the test successfully.

Creating Database Unit Test Using tSQLt

The next step is to create a real database unit test based on the simulated unit test.

If you are using SSMS (SQL Server Management Studio) you will have to install the tSQLt framework, create a test class and enable CLR before writing and running the unit test.

If you are using dbForge Studio for SQL Server you can create the unit test by right-clicking the AddEmployee stored procedure and then clicking “Unit Test” => “Add New Test…” as shown below:

To add a new test, fill in the required unit test information:

To write the unit test, use the following script:

--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
CREATE PROCEDURE [BasicTests].[test if new employee can be added]
AS
BEGIN
  --Assemble
  DECLARE @NAME VARCHAR(40) = 'Adil'
         ,@StartDate DATETIME2 = '2018-03-01'
         ,@Title VARCHAR(50) = 'Development Manager'


  EXEC tSQLt.FakeTable "dbo.Employee" -- This will create a dependency-free copy of the Employee table
  
  CREATE TABLE BasicTests.Expected -- Create the expected table
  (
    EmployeeId INT 
    ,NAME VARCHAR(40)
   ,StartDate DATETIME2
   ,Title VARCHAR(50)
  )


  -- Add the expected table data
  INSERT INTO BasicTests.Expected (NAME, StartDate, Title)
    VALUES (@NAME, @StartDate, @Title)

  --Act
  EXEC AddEmployee @Name -- Insert data into the Employee table
                  ,@StartDate 
                  ,@Title 
  

  --Assert 
  EXEC tSQLt.AssertEqualsTable @Expected = N'BasicTests.Expected'
                              ,@Actual = N'dbo.Employee'
                              ,@Message = N'Actual table matched with expected table'
                              ,@FailMsg = N'Actual table does not match with expected table'

END;
GO

Then, run the database unit test:

Congratulations! We have successfully created and run database unit test which is free from dependencies.

Things to Do

That’s it. You are ready to isolate dependencies from database unit tests and create database unit test free from data and constraint dependencies after going through this article. As a result, you can improve your skills performing the following things:

1. Please try adding the Delete Employee stored procedure and create a simulated database unit test for Delete Employee with dependencies to see if it fails under certain conditions
2. Please try adding the Delete Employee stored procedure and create a database unit test free from dependencies to see if an employee can be deleted
3. Please try adding the Search Employee stored procedure and create a simulated database unit test with dependencies to see if an employee can be searched for
4. Please try adding the Search Employee stored procedure and create a database unit test free from dependencies to see if an employee can be searched for
5. Please try more complex requirements by creating stored procedures to meet the requirements and then writing database unit tests free from dependencies to see if they pass the test or fail. However, please make sure that the test is repeatable and focused on testing the unit of the code

Useful tool:

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