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.

  • 20196 views

  • Rate
    [Total: 18    Average: 4.3/5]
  • AlexK

    maybe too much work?
    Hi Greg,

    In my environment I would not ever want to unit test a trivial procedure such as OrderItemInsert at all. The reason is this: we do not really struggle when we develop such simple modules. With or without unit tests they work anyway. In fact, we do not write them manually: they are mostly generated.

    In my experience we can just invoke OrderItemInsert as well as other CRUD modules to set up test data, and verify that selecting stored procedures return correct data that was set up by OrderItemInsert.

    If there are any defects, this simple approach will surely expose them (it has never failed for us in five years), and at a fraction of the cost. We don’t have to spend precious time on trivial unit tests, when a much cheaper approach is just as useful.

    This is why we have been unit testing stored procedure for five years, and our testing framework still does not have the ability to mock tables. In fact, we have never needed the ability to explicitly test trivial modules to begin with. Higher level BDD-style integration tests have never failed to ensure very high quality, and they are much easier to set up and maintain, so there is no practical need to go into more detail and spend more time.

    Also I have problems understanding the following: "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"

    If we have added not-null columns to the OrderItem table, then OrderItemInsert procedure is broken until we change it accordingly. If a unit test fails to detect it, then what is the point of having this test in the system at all? This unit test seems useless to me. Probably I am missing something.

    Besides, since we have added new columns, surely we want to test how they are populated. Should we modify existing tests or create new ones? If we keep creating new ones, our test harness will soon bloat and become less useful. Long term it is cheaper to modify existing tests and keep the test harness lean. Does it make sense?

    Of course, reducing maintenance costs of unit tests is a very valid concern, but we should overreact and render our test harness useless.

    What do you think?

  • gregmlucas

    No, not too much work if you do it right
    Hi Alex,

    Thanks for taking the time to respond at such length.

    I think that the first thing to say in my defence is that this article was not about how to unit test or even what to test as much as why I would choose this particular framework. That’s why there are no code samples. I am not telling readers what they should do but about the features that I think make tSQLt so useful and how they relate to established patterns and practices

    I am familiar with your work, both here on Simple-Talk and your blog and I recognise the value in your approach. However, we are coming at this from different angles. For example, with your approach test data is driven by external files and I think added just the once before all test are run (correct me if I am wrong). I prefer the tSQLt approach where test data is managed within each test or test class (using the SetUp procedure). This means that data relating to each test is visible to be as I write the test. We are each neither right nor wrong – just different.

    I do not agree that OrderItemInsert does not require unit tests. Such a procedure will have at least four parameters, possibly five if the resulting IDENTITY value is to be provided as an output. The purpose of testing such a trivial procedure, as a minimum, would be to ensure that the right parameter values get inserted into the correct columns.

    Of course, if you are using an in-house tool to auto-generate your CRUD sprocs, I assume that the tool itself is sufficiently covered by tests to give you confidence that the procedures it generates work correctly. That would be a valid case for not needing to test such simple procedures.

    On reflection, an INSERT procedure might not have been the best example for having a mock table with all nullable columns for precisely the reasons you describe. If other NOT NULL columns are added to that table, we do need the OrderItemInsert test to fail. I was trying to use a simple example that readers could visualise easily, perhaps I should have used [OrderItemUpdate]. For the record, this is one way I could test [OrderItemInsert] – leveraging the fact that tSQLt tests run within a transaction which is rolled back on completion (so leaving the foreign keys in place).

    CREATE PROCEDURE [TestOrderItemInsert].[test_correct_values_in_columns]
    AS
    BEGIN
    –! Assemble
    ALTER TABLE dbo.OrderItem DROP CONSTRAINT FK_OrderHeader_Product;
    ALTER TABLE dbo.OrderItem DROP CONSTRAINT FK_OrderItem_OrderHeader;

    CREATE TABLE #expected (OrderId int, ProductId int, Quantity int, Price decimal(18,2));

    INSERT #expected VALUES (1, 2, 3, 4.5);

    –! Act
    EXEC dbo.OrderItemInsert @OrderId = 1, @ProductId = 2, @Quantity = 3, @Price = 4.5

    SELECT OrderId, ProductId, Quantity, Price INTO #actual FROM dbo.OrderItem

    –! Assert
    EXEC tSQLt.AssertEqualsTable ‘#expected’, ‘#actual’
    END

    I am not suggesting that more OrderItemInsert tests should be added every time new columns are added. I agree that such an approach would add unnecessary bloat. If those new columns were NOT NULL, the above test would fail and would need to be altered in line with changes to OrderItemInsert.

    You take issue with my assertion that FakeTable helps reduce test case maintenance in future when additional NOT NULL columns might be added to the OrderItem table. You have taken that slightly out of context, as the section in question actually follows a discussion around tests for an OrderItemView. I would absolutely want to mock the OrderItem table for these tests as any new NOT NULL columns would otherwise force me to change all the setup for these tests – even though the view might have nothing to do with those new NOT NULL columns.

    If one is creating a green field project it may be possible to build up a set of test data which is added to the database once before all tests are run. If the database schema is developed over time that approach could work. However, most of the projects I have used database TDD for have been established, complex databases with hundreds of tables and foreign keys. Often I will only be interested in refactoring a small part of that and, without the ability to mock tables, pre-populating 20 or more tables does become too much work. If my area of interest is restricted to three or four tables, then FakeTable means I can choose to safely ignore the rest of the database which I might be unfamiliar with and do not at that point in time, even need to understand.

    You have obviously been developing your test framework together with your unit and integration tests over a long period. Your approach works for you and you have a lot invested in that way of doing things.

    Although, like you, I can also code in C#, and am familiar with tools such as NUnit and NMock et al, this is not true of all SQL developers. One of the reasons I like tSQLt so much is that it is inclusive. To start doing effective TDD, the only thing that a database developer has to get their head around is the TDD paradigm – not a whole new language and IDE.

    If unit tests are written in C#, the only SQL developers who will write and maintain tests will be those who are also confident in .Net. The others will never even run the tests (themselves) let alone create or modify unit tests. And in that scenario, your unit tests quickly get out of date and become useless – and that would be a waste of time.

    I like tSQLt because the tests are written in T-SQL, in the same IDE that the rest of my team uses – which means that everyone can contribute to the unit testing effort. Just because a framework has such powerful features as FakeTable doesn’t mean they should always be used – as demonstrated in my code sample above. Features such as the ability to mock database objects and the database-specific assertions make it very easy to adopt TDD without spending more time than necessary on setup – which I’m sure we will both agree is the one of the barriers to effective test-driven database development.

  • AlexK

    Now I understand the context
    Hi Greg,

    Thank you for such a detailed response. Now I understand the context in which we are recommended to use your approach.

    Of course if we need to work on a system without any automated tests running a test in complete isolation makes sense, no argument here.

    However, if we keep using this approach for the long term and accumulate lots of tests, then we are likely to feel growing pains: more time to run the test harness, and more time to maintain them. This is when the approach I am using could eventually become cheaper in the long run.

    Thank you again for the detailed explanation!

  • steveculshaw

    tSQLt with NUnit
    Have you any thoughts/suggestions on options for hooking up tSQLt with NUnit tests?

    … I’d like to stay in Visual Studio and just run all my unit tests from Resharper, including the tSQLt ones