Photo by bucklava

Automated testing is a good software engineering principle regardless of which development management process being used, Scrum, TDD, XP, etc. You know all about the benefits of unit-testing. It will reduce the number of defects, catch bugs earlier, unicorns will manifest themselves and bring you a beer. Great! But how do you start?

There are various opinions on how to test your Data Access Layer (DAL). Some would argue that it makes sense to write tests that run against an actual database. Others will argue that DAL tests should be mocked. While, I’ve talked previously about techniques for unit-testing, advocating, pleading, begging for the usage of mocked dependencies (using a mocking framework). When it comes to testing the DAL, this is where I make an exception to that guidance.

Don’t get me wrong, the database is a dependency on the DAL, but the trade offs between mocking the database versus having a separate test database is a more efficient use of development effort. Grated, using a mock framework in place of a database dependency will allow you to run your tests quickly, and all those great things that come with pure unit-tests. But the DAL is special, it is like an ice berg, its runtime logic is only partially contained within its code. (Hint: The other portion is contained in the database)

It does not matter if you are using a the latest-and-greatest wiz-bang ORM, stored procedures, or dynamic SQL, your database will always contain some logic that needs to be tested. Which is why am a supporter of integration testing your Data Access Layer, and not unit-testing. There are many issues that can be uncovered by integration testing your DAL. Including, but not limited to, typographical errors, foreign-key violations, and plain old incorrect SQL.

As an aside, having database integration tests are a great way to performance tune your data schema and SQL.

Builder Pattern

So how exactly do we go about creating integration tests for our Data Access Layer. Well, this is a little tricky, since rarely will you have one domain object that does not have a dependency on another. So creating Create, Read, Update, Delete (CRUD) integration tests can quickly get out of hand and result in lots of duplicated code across tests.

This is where the Builder pattern will help. The Builder design pattern is similar to the Factory pattern, in that  both patterns abstract away the instantiation of an object, however the builder pattern has a few more features that suite it well for creating integration tests.

The purpose of the Builder pattern create a flexible interface by which a developer can create a dataset that can be used to test the CRUD operations of a Data Access component. Lets take a look at an example:

        public override Person Build()
        {
            return Builder.CreateNew()
                .And(x => x.OrganizationId = organization.OrganizationId)
                .And(x => x.Name = GetRandom.Phrase(10))
                .And(x => x.StartDT = startDate)
                .Build();
        }

The example PersonBuilder class has two main functions, Build and Save. The build function simply creates an instance of a person with all reasonable defaults initialized. In this example, the Name, StartDT and OrganizationId. I am also using a framework called NBuilder to do the actual instantiation of the Person object. NBuilder provides some very handy features for rapid object generation.

        public override Person Save()
        {
            Person person = Build();
 
            new PersonDA().Insert(person);
 
            return person;
        }

The Save function is a little trickier, since this is where the instance of a person is actually persisted to the database. This is where you must be aware of your particular database constraints. In our example, the Person Table has a relationship with the Organization Table, so we must save the Organization to the database before we can save the Person. This is the responsibility of the caller, the integration test.

        public PersonBuilder WithStartDate(DateTime startDate)
        {
            this.startDate = startDate;
            return this;
        }
 
        public PersonBuilder WithOrganization(Organization organization)
        {
            this.organization = organization;
            return this;
        }

There are two other methods that should be highlighted, WithStartDate, and WithOrganization. These methods allow a developer to override the default values of the OrganizationId and StartDT property on the Person instance. Providing these methods allows a developer to craft a dataset to fits the need of a particular test. For example, asserting the cardinality between an Organization and Person, or testing specific query logic that may filter records.

In practice, these extra methods are not needed for every single property of a class, you will have to use your best judgement to determine when they are needed under a particular unit testing scenario. When going through this process, a Domain Specific Language (DSL) is being created, that will allow other developers to quickly ramp up to using a set of builder classes to create complex integration tests.

One last thing about this examples DSL methods you may have noticed, the return type is this. Returning the current builder instance creates a fluent interface for the DSL. Making possible method calls like this:

var personBuilder = new PersonBuilder();
personBuilder
    .WithStartDate(DateTime.Now.AddDays(3))
    .WithOrganization(organization)
    .Save()

The full source for the PersonBuilder class is available here.

Conclusion

Using the Builder Design Pattern has numerous advantages, and its my perfered method of creating an easy to use framework when testings Data Access Components. How do you test your Data Access Layer?