Unit Testing Databases – Adapting the Test Data Builder Pattern for T-SQL

by Greg M Lucas on 30 November 2011

One of the bigger obstacles to practising test-driven development, can be the perceived pain of building complex objects over and over again. This can be particulalrly frustrating when you have to construct an object with lots of attributes, most of which are irrelevant to any single test. For this reason, I am a fan of the Test Data Builder (TDB) pattern which allows me to instantiate objects for specific tests, writing the minimum code to only define the properties that I need for a particular test. The <Object>Builder class takes care of the rest and ensures that I have an otherwise valid object. The difficulties in providing suitable test data are not restricted to the object oriented world, in fact generating good test data in the database arena can be even more problematic. In this post, I demonstrate how the Test Data Builder pattern can be put to good use when unit testing databases.

I first have to thank an ex-colleague, David Laing (blog | twitter) for introducing me to the TDB pattern when I was working on a heavily data dependant Windows application. The principle behind TDB is that it is the Builder class that is responsible for creating a valid object, with all minimum or required values defined. Then you can user the Builder to create objects specifying values only for the properties you are interested in for a particular test. I want to avoid writing too much C# as this article is about adapting TDB for use in database tests. So let us assume that we have an object that reflects the following table structure:

CREATE TABLE [tdbExample].[Person]
(
  [PersonId]          int                NOT NULL IDENTITY( 1,1 )
, [DateActivated]     datetime           NOT NULL
, [LastActivityTime]  datetime           NOT NULL
, [LastName]          varchar  (  64 )   NOT NULL
, [FirstName]         varchar  (  64 )   NOT NULL
, [MiddleName]        varchar  (  64 )   NULL
, [Title]             varchar  (   8 )   NOT NULL
, [Gender]            char     (   1 )   NOT NULL
, [Nationality]       varchar  (  64 )   NOT NULL
, [DateOfBirth]       date               NULL
, [DeactivateTime]    datetime           NULL
)
GO

ALTER TABLE [tdbExample].[Person] ADD CONSTRAINT [PK_Person] PRIMARY KEY ([PersonId]);
ALTER TABLE [tdbExample].[Person] ADD CONSTRAINT [DF_Person_DateActivated] DEFAULT GETDATE() FOR [DateActivated];
ALTER TABLE [tdbExample].[Person] ADD CONSTRAINT [DF_Person_LastActivityTime] DEFAULT GETDATE() FOR [LastActivityTime];
ALTER TABLE [tdbExample].[Person] ADD CONSTRAINT [CK_Person_Gender] CHECK ([Gender] IN ('M', 'F', 'U', 'X'));
ALTER TABLE [tdbExample].[Person] ADD CONSTRAINT [DF_Person_Gender] DEFAULT 'U' FOR [Gender];

So, switching into C# mode and assuming that we have already written a PersonBuilder class, to create a new object with valid state, the minimum code we would need to write would be this:

Person myPerson = new PersonBuilder()
    .Build();

Now let’s say we need to write a test that checks a person’s nationality, we would create this object

Person myPerson = new PersonBuilder()
    .WithNationality("British")
    .Build();

If the only property we’re interested in is Nationality, we let the object Builder do the rest and guarantee we have a valid object. We don’t care about the other attributes such as first name, last name or date of birth etc – they are not relevant to the test in hand.   And as a final example, if we wanted to test a collection that ordered Person objects by last name, then first name we might start by creating these objects:

Person personA = new PersonBuilder()
    .WithLastName("Smith")
    .WithFirstName("John")
    .Build();

Person personB = new PersonBuilder()
    .WithLastName("Smith")
    .WithFirstName("Jeff")
    .Build();

Person personC = new PersonBuilder()
    .WithLastName("Smythe")
    .WithFirstName("John")
    .Build();

I really like this “fluent” style of instantiating objects and below I show how you can use a similar approach to generate small amounts of test data to help write quick, effective unit tests for your database.

Create an Object (read: Row) Builder in T-SQL

In fact, implementing a Test Data Builder method in T-SQL is probably easier than in C#. My unit-testing framework of choice right now is tSQLt, and the way I’ve chosen to implement TDB here reflects that choice but this approach could easily be adapted to other testing frameworks. The T-SQL source code for this post can be downloaded here.

tSQLt uses schemas to group tests into classes so in any test project I tend to define a schema specifically to hold my Test Data Builders – provided the procedure names don’t start with “test”, they will just be ignored by tSQLt. Using the table definition above, below is what a PersonBuilder might look like.

Notice that all parameters have a value, remember, we want to be able to add a Person row with as little code as possible, i.e. EXEC [TestDataBuilder].[PersonBuilder]. You will have noted from the table definition above that PersonId auto-increments so we need to be able to return that, along with the date activated and last activity time. The values for these fields are less predictable and so are populated by PersonBuilder then provided to the caller as output parameters.  Notice that the default values we supply for these parameters are the absolute minimum that will allow us to insert a row in the Person table. Choosing default values such as “John” and “Smith” etc. might be fine now but in a few months when you’ve forgotten what the defaults were, or when someone else starts to use your Builder module, using specific values will just add pain.

The other thing to note is the check for whether the Person table does actually have an IDENTITY column. We do this because tSQLt has this really great feature that allows us to mock the bare bones of a table so that we can reference it in a test without worrying about any constraints or needing to populate other tables due to the presence of foreign keys. Using tSQL.FakeTable to mock a table in this way does not carry across the IDENTITY property to the new table and so we have to code defensively for this in PersonBuilder.

CREATE PROCEDURE [TestDataBuilder].[PersonBuilder]
(
  @LastName          varchar   (  64 )   = ''
, @FirstName         varchar   (  64 )   = ''
, @MiddleName        varchar   (  64 )   = ''
, @Title             varchar   (   8 )   = ''
, @Gender            char      (   1 )   = 'U'
, @Nationality       varchar   (  64 )   = ''
, @DateOfBirth       date                = NULL
, @DeactivateTime    datetime            = NULL
, @PersonId          int                 = NULL  OUT
, @DateActivated     datetime            = NULL  OUT
, @LastActivityTime  datetime            = NULL  OUT
)

AS
BEGIN
    DECLARE @ReturnValue int = 0;
    --!
    --! DateActivated and LastActivityTime have DEFAULT constraints,
    --! so if they haven't been specified on input, fill them now
    --! so they can be output
    --!
    SET @DateActivated     = COALESCE(@DateActivated, GETDATE());
    SET @LastActivityTime  = COALESCE(@LastActivityTime, GETDATE());

    BEGIN TRY
        --!
        --! If we're inserting the real Person table, we need to
        --! collect the IDENTITY value to populate the output parameter.
        --! On the other hand, if this table has been mocked, it may not
        --! have IDENTITY set on PersonId so we need to generate a new
        --! ID (to insert and output)
        --!
        IF OBJECTPROPERTY(OBJECT_ID(N'[tdbExample].[Person]'), N'TableHasIdentity') = 1
            BEGIN
                INSERT [tdbExample].[Person]
                (
                  [DateActivated]
                , [LastActivityTime]
                , [LastName]
                , [FirstName]
                , [MiddleName]
                , [Title]
                , [Gender]
                , [Nationality]
                , [DateOfBirth]
                , [DeactivateTime]
                )
                VALUES
                (
                  @DateActivated
                , @LastActivityTime
                , @LastName
                , @FirstName
                , @MiddleName
                , @Title
                , @Gender
                , @Nationality
                , @DateOfBirth
                , @DeactivateTime
                )

                SET @PersonId = SCOPE_IDENTITY();
            END
        ELSE
            BEGIN
                SET @PersonId = COALESCE(@PersonId, (SELECT MAX(PersonId) FROM [tdbExample].[Person]) + 1, 1);

                INSERT [tdbExample].[Person]
                (
                  [PersonId]
                , [DateActivated]
                , [LastActivityTime]
                , [LastName]
                , [FirstName]
                , [MiddleName]
                , [Title]
                , [Gender]
                , [Nationality]
                , [DateOfBirth]
                , [DeactivateTime]
                )
                VALUES
                (
                  @PersonId
                , @DateActivated
                , @LastActivityTime
                , @LastName
                , @FirstName
                , @MiddleName
                , @Title
                , @Gender
                , @Nationality
                , @DateOfBirth
                , @DeactivateTime
                )
            END
    END TRY
    BEGIN CATCH
        --!
        --! Use TRY... CATCH... to keep things clean and supply useful failure info.
        --! The simplest way to report this failure is via tSQLt.Fail which will
        --! cause the test that calls PersonBuilder to fail with the failure reason
        --! being the error we collect here.
        --!
        DECLARE @ErrorMessage nvarchar(2000) = '[TestDataBuilder].[PersonBuilder] - ERROR: ' + ERROR_MESSAGE();
        SET @ReturnValue = ERROR_NUMBER()

        EXEC tSQLt.Fail @ErrorMessage;
    END CATCH

    RETURN (@ReturnValue);
END;

The final point to note is how we use TRY… CATCH… to pick up any error information. As I have based this on tSQLt, we can then call tSQLt.Fail passing the error information to record a test failure. In other testing frameworks, you might need to call RAISERROR or have the caller evaluate PersonBuilder’s RETURN_VALUE which in this case will be non-zero if there is any kind of error.

So to compare how we would use the T-SQL version of PersonBuulder with the C# examples earlier, this first call will add a single, valid row to the Person table:

EXEC [TestDataBuilder].[PersonBuilder];

Now let’s say we need to write a test that check’s a person’s nationality, we would create this row:

EXEC [TestDataBuilder].[PersonBuilder] @Nationality = 'British';

If we wanted to test that some stored procedure returned results ordered by last name, then first name we could start by generating these three rows. We might also collect the IDENTITY value of each row added to help with some later validation.

DECLARE @personA int, @personB int, @personC int;

EXEC [TestDataBuilder].[PersonBuilder]
      @LastName  = 'Smith'
    , @FirstName = 'John'
    , @PersonId  = @personA  OUT;

EXEC [TestDataBuilder].[PersonBuilder]
      @LastName  = 'Smith'
    , @FirstName = 'Jeff'
    , @PersonId  = @personB  OUT;

EXEC [TestDataBuilder].[PersonBuilder]
      @LastName  = 'Smythe'
    , @FirstName = 'John'
    , @PersonId  = @personC  OUT;

Conclusion

If you consider how much code you would need to write just to insert three rows where most columns do not allow NULL, using the Test Data Build pattern is, I think, a much more elegent solution and certainly requires less key strokes.  Just to re-cap on my tips for using the TDB pattern in T-SQL:

  • All parameters must have a default value – even if that is NULL
  • Handle any unpredictable values such as those deriving from auto-increment columns or default constraints within the Builder and provide them as output parameters
  • Be aware of whether the destination table has an auto-increment column and code accordingly
  • Finally, less is more.  Unless a constraint forces you to specify more, stick with empty strings, NULLs and zeros for the default values.

If you are interested in learning more about using the Test Data Builder pattern in C# (et al), Cunningham & Cunningham is a good place to start. There is also a comparison between the (similar) Object Mother & Test Data Builder patterns here.

Comments on this entry are closed.

Previous post:

Next post: