Introduction

The structure of tables that exist in your database (or views) are essentially the API that you, as a database developer or designer, are presenting to client applications. While you may want to only allow access through stored procedures, that hasn't provide to be a practical practice in the real world, and often at least some part of your application has access directly to tables or views.

This article looks at writing tests in the tSQLt framework that directly check the metadata, or API, of your tables and allow you to catch breaking changes. The value of using these tests in your development process is a discussion for another piece, but for the remainder of this discussion, we will assume you want to check the structure of your API against some known value.

We will look at an existing table and writing tests against it. However the process would be the same if you were developing a new table from scratch, with the exception that you might choose to write the test first.

Setup

For this test, we will be using the SQLServerCentral database objects.  In this example, here is the setup for one of our tables:

CREATE TABLE [dbo].[ContentItems](
[ContentItemID] [INT] IDENTITY(1,1) NOT NULL,
[PrimaryTagID] [INT] NULL,
[Title] [VARCHAR](200) NOT NULL,
[ShortTitle] [VARCHAR](200) NULL,
[Description] [VARCHAR](3500) NULL,
[Text] [TEXT] NULL,
[ExternalURL] [VARCHAR](250) NULL,
[PublishingStatus] [INT] NOT NULL,
[SourceID] [INT] NULL,
[ForumThreadID] [INT] NULL,
[UpdatesContentItemID] [INT] NULL,
[CreatedDate] [DATETIME] NOT NULL,
[LastModifiedDate] [DATETIME] NULL,
[DollarValue] [FLOAT] NULL,
[IconFileID] [INT] NULL,
[DisplayStyle] [INT] NOT NULL CONSTRAINT [DF_ContentItems_DisplayStyle]  DEFAULT ((0)),
[PopularityRank] [FLOAT] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_ContentItems] PRIMARY KEY CLUSTERED 
(
[ContentItemID] ASC
)
)

This is the table that holds the details of content on the site. We populate this with everything we publish: articles, scripts, questions, etc. The actual data isn't important for the sake of testing.

Writing the Test

We will use the AssertResultSetsHaveSameMetaData method for this test. This method takes two commands, which can be any valid SQL statements and executes them both. The result set meta data is then compared. This includes the columns and their properties, but not the data in the result set. If the two result sets match in terms of their metadata, the assertion (and thus the test) passes. If not, it fails.

Let's look at this test in terms of the AAA format and examine each section.

Assemble

For our assemble section, we want to build a table that looks like the table we expect to get back. In this case, I can essentially use the CREATE TABLE statement above from our Setup. However I need a different name for the table as I can't have two objects with the same name. I created a Test Class called tEmailer for the tests in this subsystem, and I'll use that schema to build my table. Remember, this table only exists for the lifetime of this test, so I don't need to worry if this code is repeated in other tests.

CREATE TABLE [tContent].[Expected](
[ContentItemID] [INT] IDENTITY(1,1) NOT NULL,
[PrimaryTagID] [INT] NULL,
[Title] [VARCHAR](200) NOT NULL,
[ShortTitle] [VARCHAR](200) NULL,
[Description] [VARCHAR](3500) NULL,
[Text] [TEXT] NULL,
[ExternalURL] [VARCHAR](250) NULL,
[PublishingStatus] [INT] NOT NULL,
[SourceID] [INT] NULL,
[ForumThreadID] [INT] NULL,
[UpdatesContentItemID] [INT] NULL,
[CreatedDate] [DATETIME] NOT NULL,
[LastModifiedDate] [DATETIME] NULL,
[DollarValue] [FLOAT] NULL,
[IconFileID] [INT] NULL,
[DisplayStyle] [INT] NOT NULL CONSTRAINT [DF_ContentItems_DisplayStyle]  DEFAULT ((0)),
[PopularityRank] [FLOAT] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_tContent_ContentItems] PRIMARY KEY CLUSTERED 
(
[ContentItemID] ASC
)
);

I like using the .Expected name as it makes it easy for me to look at this test and realize this is something I expect. This structure is the API I am presenting to a few applications that access this table.

Note that I also needed to change the name of the constraint for the PK here. Those are objects in SQL Server and must be unique as well. 

Act

There is no act for this test as the execution of the commands takes place in the assert statement.

Assert

This is the section where I assert things are true. In this case, I am going to assume that a query of my tContent.Expected table will return a result set that matches the same query against the dbo.ContentItems table. Here's how that statement looks

--Assert
EXEC tSQLt.AssertResultSetsHaveSameMetaData
    @expectedCommand = N'select * from tContent.Expected',
    @actualCommand = N'select * from ContentItems';   

Note that I'm put the expected and actual parameters on separate lines. Formatting is personal, but in this case I think it really helps to see these two commands near each other at a glance.

If I run this test (in SQL Test) I can see it passes. I could also run this with tSQLt.Run, but as a developer, I like to be able to run a bunch of tests at once and the SQL Test add-in makes it simple.

At this point, I would normally commit this test to source control and have it run automatically in whatever CI process I have set up. 

Failures When Things Change

One of the things we often do in database development is refactor our tables. We find the need to add columns or change values. Let's show how that works with this test. 

Assume that I have a new requirement to add a column to this table that gets the estimated reading time. Now I could run this code:

  ALTER TABLE dbo.ContentItems
  ADD EstimateofReadingTime TIME;

However, what I really should do to ensure my test works is to actually change the test first. That's a TDD principle, so let's change the test. Here's the entire test, but I've just just added a column to my Assemble section in the CREATE TABLE statement.

ALTER PROCEDURE [tContent].[test ContentItems Metadata]
AS
BEGIN
  --Assemble
CREATE TABLE [tContent].[Expected](
[ContentItemID] [INT] IDENTITY(1,1) NOT NULL,
[PrimaryTagID] [INT] NULL,
[Title] [VARCHAR](200) NOT NULL,
[ShortTitle] [VARCHAR](200) NULL,
[Description] [VARCHAR](3500) NULL,
[Text] [TEXT] NULL,
[ExternalURL] [VARCHAR](250) NULL,
[PublishingStatus] [INT] NOT NULL,
[SourceID] [INT] NULL,
[ForumThreadID] [INT] NULL,
[UpdatesContentItemID] [INT] NULL,
[CreatedDate] [DATETIME] NOT NULL,
[LastModifiedDate] [DATETIME] NULL,
[DollarValue] [FLOAT] NULL,
[IconFileID] [INT] NULL,
[DisplayStyle] [INT] NOT NULL CONSTRAINT [DF_ContentItems_DisplayStyle]  DEFAULT ((0)),
[PopularityRank] [FLOAT] NOT NULL DEFAULT ((0)),
EstimateofReadingTime TIME,
 CONSTRAINT [PK_tContent_ContentItems] PRIMARY KEY CLUSTERED 
(
[ContentItemID] ASC
)
);  
  --Act
  
--Assert
EXEC tSQLt.AssertResultSetsHaveSameMetaData
    @expectedCommand = N'select * from tContent.Expected',
    @actualCommand = N'select * from ContentItems';   
END;

Now I can compile this and run the test. This time I'll use tSQLt.Run.

tsqlt.run '[tContent].[test ContentItems Metadata]';

The results are:

The test failed, as expected. Our test requires the metadata to be the same, and in this case, our test has an extra column. If I now run the code above to alter the table, the test will pass.

That's the TDD way to run a test, and I think it works fine here. If I did this in the reverse order, changing the table first, it also works.

Let's also test  a couple more changes. Assume I run this code:

ALTER TABLE dbo.ContentItems
 ALTER COLUMN EstimateofReadingTime TIME NOT NULL;

Now if I run my test, things fail.

If I change the ordering of my columns in the test, or the names, then the tests also fail. I'd encourage you to experiment a bit.

Summary

This is a short example of how to write a test to enforce a particular schema, or API, for a table with a test. You've seen how the test is constructed and how it can fail as a table is altered. I've also shown how to bring the test back into a passing state.

The value of doing this will depend on your application and the affects of change. While I might not do this for every table or view, I would consider this as a way to ensure table changes can be used to raise a flag to developers for particularly sensitive objects.