Test-driven Database Development – Why tSQLt?

Test-Driven Development (TDD) has a good track record in application development, but is less well-established in database development work. This is set to change with the arrival of test frameworks that use SQL, and a plug-in for SQL Server Management Studio. Greg Lucas explains why.

DevOps, Continuous Delivery & Database Lifecycle Management
Continuous Integration

There are a number of unit testing frameworks available that facilitate effective test-driven development (TDD) for compiled application code. This has established some patterns and practices that, with a suitable framework, also make TDD an effective and appropriate practice with database code.

After experimenting with a number of database unit testing tools over the years and having actually used several on production projects, I have grown to prefer the tSQLt framework. This free open-source library has been creating a lot of interest lately; even more so since Red Gate put their weight behind it by making tSQLt the backbone of SQL Test. This plugin for SQL Server Management Studio (SSMS) is a graphical test manager, built on tSQLt, originally created during one of Red Gate’s down-tools weeks. Now it has grown to become an established part of their SQL Developer product suite.

We will start by reviewing the way that unit-testing frameworks support some of the TDD practices commonly used by application coders. I will then explain how tSQLt and SQL Test provide those same features for database developers and why they make it so much easier to do effective test-driven development in T-SQL.

Test-driven Development Practices

Test-Driven Development is comprised of a set of patterns and practices, honed by application developers over many years. TDD is generally acknowledged to aid the delivery of high-quality software. Because of this, several mature and feature-rich unit testing frameworks have emerged. There are some excellent, open source solutions that make it easy to adopt TDD in compiled code development and there is also a wealth of knowledge on forums, blogs and in books. However, this isn’t the case within database development circles because it is more of a challenge to write true unit tests for databases, it requires more effort to learn, and database testing frameworks have taken longer to appear. Many teams either depend on tests in their application code to exercise the database or just put database unit testing to one side, hoping to return to it later.

Databases and SQL are designed to solve a different set of problems to application code, so the solutions look and behave differently. This requires a different approach to test-first development, a different way of thinking.

So, after being left out in the cold for so long, things are finally beginning to improve for us SQL developers. Let’s start by looking at the arsenal of tools and methods that most application developers take for granted.

About Frameworks

Unit tests are usually written within the same Integrated Development Environment (IDE) and in the same language as the production code with the aid of some kind of unit testing framework such as NUnit or MBUnit. Alternatively the IDE may have built-in support for unit testing (e.g. MSTest in Visual Studio).

These frameworks typically support a number of common features designed to facilitate writing tests quickly and easily. For example, built-in set-up and tear-down methods run automatically before and after each individual test or suite of tests reducing the amount repetitive code that needs to be written. Built-in assertions save time by encapsulating common logic used in tests. E.g. “Are these two objects the same?” or “Is value x the same as value y?”

Most testing frameworks, whether open source or built-in to an IDE, will have some kind of graphical test runner that will allow tests to be run singly, by test class or for the entire application at the click of a button. In this NUnit screenshot any failing tests would appear with red crosses.

1542-1-03c2d3d8-b710-48dd-a6fd-e33464ba5

Some tests may also utilise a mocking framework such as Rhino Mocks or NMock to create mock objects which help to keep tests properly isolated – of which more later.

About Mocking

A typical N-tier application might consist of a database, data access and business logic layers plus some sort of user interface. Such applications may be very tightly coupled – that is there are rigid dependencies between each layer – and this can make it harder to write proper unit tests.

One solution is to mock the object(s) on which the unit under test depends. A mock object is like a shadow of the real object; it has the same shape (inputs and outputs) but none of the substance (implementation).

A Mocking Framework helps developers create mock objects. This allows calls made to the mock object by the unit under test to be recorded and/or played back. It also allows the developer to validate the parameters passed on each call and optionally define what the outputs of each call will look like.

Mock objects allow dependencies between different layers or objects within a complex application to be isolated for unit testing purposes. This is a huge time saver when developing application code and, without the database calls or network I/O, the tests run much faster too.

What Makes a Good Unit Testing Framework?

There are some essential features for unit-testing frameworks. These may be provided by several libraries working in concert (e.g. NUnit and NMock) or by a single framework but they are all important, if not critical, to successful unit testing.

I’ve listed the most important features in the table below and outlined how I believe that tSQLt measures up – even more so when combined with SQL Test. I’ve been using tSQLt for a couple of years now, certainly before Red Gate brought out SQL Test so I’ve got used to working without the UI. However, having the user interface now makes it even easier to create and run tests.

Run tests singly, in groups or en masse and in any order 1542-greentick.png In tSQLt, a test is a stored procedure that is placed into a schema (a.k.a. test class). Tests can be run singly, by schema or for all schemas in a database. If you’re executing tests using the T-SQL command line (via SSMS or sqlcmd.exe), you can also run all tests in multiple databases although this isn’t yet supported in SQL Test.
Self-contained tests, no dependence between tests 1542-greentick.png Each test is run in its own dedicated transaction, guaranteeing that it can be run in total isolation. However, you should also write your tests in such a way that ensures that no test depends on another.
Built-in assertions to reduce repetitive coding within tests 1542-greentick.png tSQLt offers a number of common assertions of which the most important are the means of comparing the contents of two tables or the metadata of two result sets. More on these features later.
Supports refactoring, automatic set-up and tear-down 1542-greentick.png tSQLt has support for a single “SetUp” procedure in each test class (schema) that is run automatically before each test in that class. Because each test is run in its own dedicated transaction which is rolled back on completion there is no need for a Tear-Down equivalent. It is also possible to add other helper procedures that can be called from within your tests e.g. Test Data Builders.
Tests can be written in the same language and IDE as production code 1542-greentick.png Although tSQLt makes use of CLR under the hood, all tests are written and executed using T-SQL in whatever application you use to write your production code – be that SSMS or some text editor. If using SSMS, the SQL Test plugin makes it even easier to write and execute tests using the built-in test template.
Unit tests can be separated from production code for release 1542-bluetick.png This only gets a blue tick because the test schemas and procedures along with the tSQLt framework itself all reside in the same database as the production code. However, whether you use an established toolset or your own custom process to build database deployments it is quite easy to exclude these. In fact, if you’re already using the current version of SQL Source Control and SQL Compare, these tools have built in support for excluding test code from any build.
Can run tests interactively or automatically 1542-greentick.png Tests can be executed a number of ways: interactively using SSMS via T-SQL or SQL Test (during development), or automatically via PowerShell, or from the command shell using sqlcmd.exe as part of a continuous build.
Support for Continuous Integration standard(s) 1542-greentick.png tSQLt tests can be run from the command line and test results can be configured to conform to JUnit standards so can be used as part of a CI process using TeamCity or Bamboo for example.
Supports the mocking of objects 1542-bluetick.png The ability to mock selected database objects is tSQLt’s most significant benefit. This is such an important feature that I cover this in more detail below. The only reason it gets a blue tick instead of a green one is that you can’t yet mock all types of database objects but you can mock the most important ones.

Some tSQLt Features in Detail

Assertions

In addition to some standard assertions that allow you to compare strings or numbers, here are two that make this framework really stand out for database development.

AssertResultSetsHaveSameMetaData allows you to compare the metadata of two queries. This will identify differences in column name, column order, data type and even nullability. I find this particularly useful when validating table structure and give an example of how to do this here. This allows you to validate the basic structure of a table in a single test.

AssertEqualsTable allows you to compare the contents of two tables. If this test fails, it displays the differences, highlighting the rows that are the same and those that are different. For example, in the test failure below the rows marked “<” are what was expected and those marked “>” are what was actually returned (those marked “=” are matches and can be ignored).

1542-1-c75fb6bf-00b6-427f-adda-90941bd8c

Mocking

One of the most powerful features in tSQLt is the ability to mock tables, views and stored procedures.

FakeTable

This allows you to create an empty copy of a real table or view for test purposes. By default, the mock table has none of the constraints associated with the real table and all the columns allow null although you can choose to add selected constraints if required.

For example, look at the schema below and think about how you would write tests against a stored procedure called OrderItemInsert which adds a row to the OrderItem table.

1542-Picture%205-10db344c-0a6a-49cd-a6f2

For every single OrderItemInsert test, you would have to populate ten other tables to satisfy the foreign key constraints. By the time I had written all that set-up, I would probably have forgotten what I was supposed to be testing in the first place. None of those ten tables have anything to do with testing the OrderItemInsert procedure. I really want to concentrate on the OrderItem table as that is directly related to the unit under test and completely ignore all the foreign keys.

If you are a SQL developer with little or no exposure to test-driven development, this might, at first glance, seem to be sacrilege. One of the hurdles I had to overcome when I started applying TDD to my database development was grasping the concept of the Single Responsibility Principle (SRP).

SRP teaches us that the job of OrderItemInsert is just to add a row to the OrderItem table – putting the right values into the correct columns and possibly returning the IDENTITY value. OrderItemInsert does not and should not care about referential integrity – that is the job of the foreign keys which should be subject to their own separate tests.

By being able to mock the OrderItem table you no longer need to manage all that setup – making it very easy and fast to write discrete tests for just the unit being tested.

As another example, imagine an OrderItemView that calculates the order value for each line item (i.e. Price x Quantity). To test the view you would only need, at the simplest level, one row in the OrderItem table and only the Price and Quantity columns would need to have values. Using FakeTable means that those are the only columns that need to be populated for this test – all the other columns allow null and so can be ignored.

This becomes even more important when, in the future, other not-null columns may be added to the OrderItem table. Without the ability to mock tables, those new NOT NULL columns could break completely unrelated tests, forcing a time consuming and messy refactoring exercise.

FakeTable dramatically reduces the time it takes to write tests in complex databases, maximises test case independence and minimises the burden of future test case maintenance.

SpyProcedure

This renames the real stored procedure and replaces it with a facsimile of the original. The copy has identical parameters but none of the real implementation. Using SpyProcedure you can record any calls to the procedure being mocked and what parameters were passed on each call. Furthermore, this feature allows you to specify any additional actions to be carried out when the mocked procedure is executed (instead of whatever the real procedure does), such as populating an output parameter value or adding some data to a table.

For example, imagine a stored procedure called myComplexWorker that has some input parameters and an output parameter. Depending on the values of the inputs, this procedure gathers data from a number of tables using a complicated set of joins and predicates. Based on those results, it adds one row to another table and populates the output parameter with something meaningful. Let us assume that this procedure is already sufficiently covered by unit tests.

Now imagine a new procedure called mySimpleManager which also has some input parameters. This procedure does something clever with those inputs to generate some values which are then passed to myComplexWorker. Then, depending on the output parameter value returned, mySimpleManager inserts one or another table utilising a join to the table populated by myComplexWorker.

When it comes to writing unit tests for the new procedure, we are actually only interested in testing three things:

  1. How it processes the inputs to produce the values passed to myComplexWorker
  2. Assert that it does actually call myComplexWorker and with the correctly ordered values
  3. Confirm that, based on the output parameter, it inserts the correct table with the right data

When testing mySimpleManager, we’re not remotely interested in the implementation of the called procedure. That has already been tested and we don’t want or need to repeat all that setup. We are concerned with only the inputs and outputs – the output parameter and the table that gets populated by myComplexWorker.

So in our example above, using SpyProcedure to mock myComplexWorker allows us complete tests 1 and 2 because we can record each call to a mocked procedure and what parameters were supplied. SpyProcedure also allows us supply a string containing dynamic SQL that is run each time the mock procedure is called. This means we can populate the output parameter and add a row to the table that would ordinarily be populated by myComplexWorker and so complete test 3.

SpyProcedure saves us time by removing the need for additional setup that is actually nothing to do with the test in hand and makes it very easy to test the behaviours of one procedure that depends on another whilst isolating the test dependencies.

Executing Tests

You can choose to manage and run all your tests using Red Gate’s SQL Test as shown in the earlier screenshot (under Assertions). You can also execute tests using T-SQL commands, either using SQLCMD as part of a continuous build or automated testing process, or from SSMS which gives a nice user-friendly result as shown below:

1542-1-36537d30-44f4-4f55-9052-3d40c38ef

Opportunities for Enhancement

Having used tSQLt on a number of production projects, there are a couple of minor improvements I would like to see, although none of these are blockers and all have some workaround.

  • It is possible to mock selected foreign keys but currently only single-column keys are supported and there is no support for cascading deletes. However, that doesn’t stop me writing tests against the real keys in those cases.
  • tSQLt runs each test in its own transaction but because SQL Server does not (and should not) support nested transactions, you can run into problems when the unit under test includes commit or rollback logic. There are patterns to use in these circumstances, one of which is here, written by Sebastian Miene – one of the authors of tSQLt.
  • There is currently no support for mocking functions or triggers. Once again this doesn’t prevent writing tests against the real objects.

The Benefits of using tSQLt for Test-driven Database Development

I don’t think that there is any question about the benefits of implementing test-driven database development. Doing so will result in a better designed, less fragile code base that is more open to change and with lower on-going maintenance costs. Developers will spend their time concentrating on producing great code rather than hunting bugs.

However, one of the things that will make a big difference to the success or otherwise of that implementation is the choice of unit testing library.

Features such as FakeTable and SpyProcedure mean that test dependencies can be completely isolated. This saves so much time, both when writing tests in the first place and later on as the system evolves and future test maintenance is minimised. In fact, right now I believe that tSQLt is the only unit testing library for SQL Server that provides the capacity to mock database objects.

The database-specific assertions such as AssertResultSetsHaveSameMetaData and AssertEqualsTable also make it very easy to write the kind of tests that only database developers would need to. This is like having declarative tests for declarative code.

tSQLt takes the established, time-proven patterns and practices of TDD, refined over many years by application developers, and integrates them with the database development paradigm to produce a unit testing framework that really delivers.

If you are serious about adopting TDD for databases, you need the support of a great framework with features that make your job easier. There are a number of unit-testing frameworks available for databases and whilst they all have some great attributes, none that I’ve used come so close to fulfilling all the requirements of a good unit testing framework as tSQLt.

Further Reading

If you are interested in the business case for adopting TDD, my previous article “Test-driven Database Development – Why Bother?” discusses the pros and cons, highlighting some of the many benefits of this practice. I have also written a series of tSQLt tutorials and other articles covering more advanced topics here.

You could also start at the tSQLt home page where you can download the latest version of this free open source framework and will also find quick start and user guides. If you prefer a UI-based test approach, why not download a 28-day free trial of SQL Test from Red Gate.

DevOps, Continuous Delivery & Database Lifecycle Management
Go to the Simple Talk library to find more articles, or visit www.red-gate.com/solutions for more information on the benefits of extending DevOps practices to SQL Server databases.