Test-Driven Development in SQL Server

This blog post was originally published on the ThoroughTest website, back when that was a thing. As a co-founder and primary content contributor for ThoroughTest, I absolutely own the rights to this post and the source code to which it refers. I intend to reproduce each blog post here on my personal blog since the company is no longer in business.

We've heard from a few developers working with SQL Server that they're having a hard time justifying test-driven development of their "code" when a lot of their logic resides in stored procedures. That's a valid point: why use test-driven development (or write unit tests at all) if you can't cover the entire codebase? Well, that's a larger topic that we may discuss in a future post, but for now we can resolve the issue by introducing a method for unit testing SQL Server objects with the tSQLt testing framework.

For starters, you can check out the official documentation of tSQLt by visiting their website. We'll use the rest of this post to show a few simple ways we can do test-driven development using the framework. Keep in mind that this is a limited overview of what tSQLt provides and can do so just because you don't see a solution for your scenario here doesn't mean they don't have one. Review their official docs to get a more thorough understanding of what's possible.

For these examples we'll create a database called CookBook. You can create it on any instance of SQL Server (including Express) and any version since 2005. This database will be a repository for our recipes so we'll create two tables and one join table: Recipe, Ingredient, and RecipeIngredient. You can use the scripts below to follow along.

CREATE TABLE Recipe
  (
     Id          INT IDENTITY(1, 1),
     Name        VARCHAR(100) NOT NULL,
     DateCreated DATETIME2 DEFAULT GETDATE()
  )

ALTER TABLE Recipe
  ADD CONSTRAINT PK_Recipe_Id PRIMARY KEY CLUSTERED (Id)

CREATE TABLE Ingredient
  (
     Id          INT IDENTITY(1, 1),
     Name        VARCHAR(100) NOT NULL,
     DateCreated DATETIME2 DEFAULT GETDATE()
  )

ALTER TABLE Ingredient
  ADD CONSTRAINT PK_Ingredient_Id PRIMARY KEY CLUSTERED (Id)

CREATE TABLE RecipeIngredient
  (
     Id           INT IDENTITY(1, 1),
     RecipeId     INT NOT NULL,
     IngredientId INT NOT NULL,
     Amount       DECIMAL(4, 2) NOT NULL,
     Measurement  VARCHAR(100) NOT NULL
  )

ALTER TABLE RecipeIngredient
  ADD CONSTRAINT PK_RecipeIngredient_Id PRIMARY KEY CLUSTERED (Id)

ALTER TABLE RecipeIngredient
  ADD CONSTRAINT FK_RecipeIngredient_Ingredient FOREIGN KEY (IngredientId)
  REFERENCES Ingredient(Id)

ALTER TABLE RecipeIngredient
  ADD CONSTRAINT FK_RecipeIngredient_Recipe FOREIGN KEY (RecipeId)
  REFERENCESRecipe(Id) 

Now that we have our table structure we want to create a stored procedure that gets all the ingredients for a specific recipe in our cook book. We want to see the Ingredient Name, Amount, and Measurement for each item in the recipe and we want to specify the recipe by name. We know our requirements so now we can write our tests. Before we can start writing tests that will actually work we need to install the tSQLt framework, which is pretty simple to do. We just download the zip file, unzip it, and run SetClrEnabled.sql followed by tSQLt.class.sql on your database. Now that the framework is installed it's time to write a unit test.

The first thing we want to do is create a test class, which will be used to group our tests together. We prefer to create test classes with the name of the stored procedure followed by ".Tests" to be clear what's being tested and that it is a test class. Since a test class is just a schema we should see a new schema created after we take this action.

EXEC tsqlt.Newtestclass 'GetIngredientsByRecipeName.Tests' 

Now we have a test class for our stored procedure (which you'll notice we plan to name GetIngredientsByRecipeName) and we can create our first actual test. Tests in tSQLt are just stored procedures named a specific way so we can use our existing TSQL skills to create our tests. The first thing we need to do in our test stored procedure is setup our data, which we can do by having tSQLt create fake tables for the tables we'll need. Once we have our fake tables we can populate them with fake data. By using fake data we'll be sure that our tests will always pass without having to worry about what recipes are actually in the database. Here's what we have so far:

CREATE PROCEDURE
[GetIngredientsByRecipeName.Tests].[Test that all ingredients are returned]
AS
  BEGIN
      -- Arrange
      EXEC tsqlt.FakeTable
        'Recipe'

      EXEC tsqlt.FakeTable
        'Ingredient'

      EXEC tsqlt.FakeTable
        'RecipeIngredient'
  END 

The FakeTable procedure opens a transaction, renames the table being passed, then recreates a table with the same name and structure, but without any constraints, defaults, or triggers. After the code above runs, all three tables will be empty shells of their normal selves, allowing us to populate whatever data we want into them. We'll populate our fake tables with some fake data so we can anticipate the results of our stored procedure.

INSERT INTO Recipe(Id,Name) VALUES(1,'Grilled Cheese')

INSERT INTO Ingredient (Id, Name)
VALUES(1, 'Butter'), (2, 'Cheddar Cheese'), (3, 'White Bread')

INSERT INTO RecipeIngredient (RecipeId, IngredientId, Amount, Measurement)
VALUES(1, 1, 1, 'Tbsp'), (1, 2, 1, 'Slice'), (1, 3, 2, 'Slices')

INSERT INTO Recipe (Id, Name) VALUES(2, 'Quesadilla')

INSERT INTO Ingredient (Id, Name) VALUES(4, 'Tortilla')

INSERT INTO RecipeIngredient (RecipeId, IngredientId, Amount, Measurement)
VALUES(2, 4, 1, 'Tortilla'), (2, 2, .5, 'Cups')

Note: Even though we could normally exclude inserting values into the Id fields of Recipe and Ingredient (because they are identity fields and should automatically get the next number) we have to explicitly include them in our test because the fake tables are created without identity fields.

Now we have two recipes' worth of fake data and we know what we expect our stored procedure to do. We're going to want to compare the results of our stored procedure to what we expect so we'll create a temp table to store the results and a temp table containing our expected results.

CREATE TABLE #temp
  (
     IngredientName VARCHAR(100),
     Amount         DECIMAL (4, 2),
     Measurement    VARCHAR(100)
  )

CREATE TABLE #expected
  (
     IngredientName VARCHAR(100),
     Amount         DECIMAL (4, 2),
     Measurement    VARCHAR(100)
  )

INSERT INTO #expected
VALUES('Butter', 1, 'Tbsp'), ('Cheddar Cheese', 1, 'Slice'), ('White Bread', 2, 'Slices')

-- Act
INSERT INTO #temp
EXEC GetIngredientsByRecipeName 'Grilled Cheese' 

Finally, we'll actually compare the results of the two tables by executing the AssertEqualsTable procedure from the tSQLt framework. This procedure compares the contents of two tables for equality. Since we want to confirm multiple values across multiple rows, this option makes the most sense for us.

-- Assert
EXEC tsqlt.AssertEqualsTable '#temp', '#expected' 

Now we can create the stored procedure and run it using the Run procedure from tSQLt and passing either the test class or the test name to the procedure as a parameter. We'll use the test class name because going forward we'll want all of our tests to run whenever we make a change to our stored procedure. This is a good habit to get into now.

EXEC tsqlt.Run 'GetIngredientsByRecipeName.Tests' 

Good news; the test failed! There is no stored procedure named GetIngredientsByRecipeName yet so the test failed. We've established our first Red step in test-driven development! Create the procedure, but don't put anything in it yet.

CREATE PROCEDURE GetIngredientsByRecipeName
(
    @RecipeName VARCHAR(100)
)
AS
BEGIN
    PRINT 'Called'
END 

Run the test again and look at the output. This time, instead of getting an error message that it "could not find stored procedure 'GetIngredientsByRecipeName'" we see "(Failure) Unexpected/missing resultset rows!" and then a description of how the two tables failed to match. For more details on how to read this output, check out the tSQLt docs for AssertEqualsTable.

Let's finally modify our stored procedure to do what we want it to do: get the ingredients for the specified recipe.

CREATE PROCEDURE GetIngredientsByRecipeName
(
    @RecipeName VARCHAR(100)
)
AS
BEGIN
    SELECT
         Ingredient.Name
        ,RecipeIngredient.Amount
        ,RecipeIngredient.Measurement
    FROM Ingredient
    INNER JOIN RecipeIngredient
        ON Ingredient.Id = RecipeIngredient.IngredientId
    INNER JOIN Recipe
        ON RecipeIngredient.RecipeId = Recipe.Id
    WHERE  Recipe.Name = @RecipeName
END 

When we run our test one more time we see that it passed. Now we have our Green step so we'll review our stored procedure for any opportunities to improve. We don't see any so our Refactor step is complete without any changes.

We've got a new requirement that ingredient amounts should be summed up when the same ingredient is in the same recipe with the same measurement more than once. First we'll write the test:

CREATE PROCEDURE
  [GetIngredientsByRecipeName.Tests].
   [Test that ingredient amounts are summed]
AS
BEGIN
    -- Arrange
    EXEC tsqlt.FakeTable 'Recipe'

    EXEC tsqlt.FakeTable 'Ingredient'

    EXEC tsqlt.FakeTable'RecipeIngredient'

    INSERT INTO Recipe (Id, Name) VALUES(1, 'Salt Soup')

    INSERT INTO Ingredient (id, Name)
      VALUES (1, 'Salt'),
        (2, 'Chicken Broth'), (3, 'Carrots'), (4, 'Leather Boot')

    INSERT INTO RecipeIngredient(
      RecipeId,
      IngredientId,
      Amount,
      Measurement
    )
      VALUES (1, 1, 1, 'Tbsp'), (1, 2, 10, 'Cups'),
        (1, 3, 10, 'Carrots'), (1, 4, 1, 'Boot'), (1, 1, 16, 'Tbsp')

    CREATE TABLE #temp
      (
         IngredientName VARCHAR(100),
         Amount         DECIMAL (4, 2),
         Measurement    VARCHAR(100)
      )

    CREATE TABLE #expected
      (
         IngredientName VARCHAR(100),
         Amount         DECIMAL (4, 2),
         Measurement    VARCHAR(100)
      )

    INSERT INTO #expected
      VALUES ('Salt', 17, 'Tbsp'), ('Chicken Broth', 10, 'Cups'),
        ('Carrots', 10, 'Carrots'), ('Leather Boot', 1, 'Boot')

    -- Act
    INSERT INTO #temp
    EXEC GetIngredientsByRecipeName 'Salt Soup'

    -- Assert
    EXEC tsqlt.AssertEqualsTable '#temp', '#expected'
END 

Then we'll run all of the tests in the test class:

EXEC tsqlt.Run 'GetIngredientsByRecipeName.Tests' 

We get an exception: "(Failure) Unexpected/missing resultset rows!". We update the stored procedure:

ALTER PROCEDURE GetIngredientsByRecipeName
(
    @RecipeName VARCHAR(100)
)
AS
BEGIN
    SELECT
         Ingredient.Name
        ,SUM(RecipeIngredient.Amount)
        ,RecipeIngredient.Measurement
    FROM Ingredient
    INNER JOIN RecipeIngredient
        ON Ingredient.Id = RecipeIngredient.IngredientId
    INNER JOIN Recipe
        ON RecipeIngredient.RecipeId = Recipe.Id
    WHERE  Recipe.Name = @RecipeName
    GROUP BY
       Ingredient.Name
      ,RecipeIngredient.Measurement
END 

And finally we run all of our tests again:

EXEC tsqlt.Run 'GetIngredientsByRecipeName.Tests' 

This time our test summary shows that we have two tests that ran and both of them passed.

Stored procedures are an important part of database programming and sometimes play a large role in applications and architecture. Using the tSQLt framework we can realize the advantages of test-driven development even when we're working with SQL Server.