Putting It All Together: TDD for Dapper Repositories

In the last several posts I've set up the infrastructure for doing Test Driving Development for Dapper with lightweight integration tests. You might want to review these if you haven't already looked at them.

• Lightweight Integration Testing with Dapper
• Solving the Challenges of Dapper Integration Testing: Part 1
• Solving the Challenges of Dapper Integration Testing: Part 2
• Solving the Challenges of Dapper Integration Testing: Part 3

I'm going to keep it relatively simple for this example. Essentially my repositories are just going to implement CRUD operations for each aggregate root. There will be Create, Get and GetAll (Read), Update, and Delete methods. I’m also going to make the choice to have most exceptions bubble up. This might not be the best choice but it will keep the example simple. In fact, you might want to catch most exceptions and transform them into application-level exceptions which you’d handle at higher levels. Testing exceptional behavior is more difficult to set up in integration tests and we can more easily test exceptions with true unit tests in the classes that use our repositories.

Because these are actually integration tests we run into a chicken and egg issue. Unless we write some other (duplicate) code, we can't test Get() without Create() or Create() without Get() in a self-contained test method.
My solution is pragmatic, start with Get(). The logic is relatively simple and easier to convince yourself of it's correctness. You could write some code using a data reader to read and verify that something is created but you'd still have to convince yourself that code is correct. Since we're not so much testing Dapper - it already has tests - as we are our SQL, I prefer to start off with Get() and not write the extra code. As they say, "your mileage may vary," you might want to start with something else.

Note you won't be able to create tests for Get() until you've done Create(), but once you've created your Get() implementation and convinced yourself of its correctness, you can move onto Create() and use TDD to create that code. After you can create and retrieve an object, you're ready for GetAll(), Delete(), Update(), and any specialized methods. Do go back and create tests for Get(). These are after all, integration tests, we can write those after the fact. As we'll see that will come in handy later when we extend our Get() implementation to get some related data from another table.

We’re going to create two separate repositories, one for Users and one for Posts. Let's start with the DapperUserRepository first. All of the code referenced below can be found, though in it's final form, at https://github.com/tvanfosson/dapper-integration-testing

DapperUserRepository

First, we'll solve our "chicken and egg" problem by implementing Get().

public User Get(int id)
{
    const string sql = "SELECT * FROM [Users] WHERE [Id] = @userId";

    var user = Fetch(c => c.Query<User>(sql, new { userId = id }).SingleOrDefault());

    return user;
}

Now, if you’re paying attention, you should be saying “Wait a minute – that’s too much code there. You’re cheating.” Yes, I am.I’m showing you the results after some refactoring – refactoring done in another project.In another project I found it helpful to develop a base repository pattern. This base implementation provides some convenient methods for performing the Dapper Query and Execute operations. Below is the base class implementation for the repositories we'll create. Since I've tested this pattern in other projects, I feel comfortable starting with it as the basis for my new repositories.

public abstract class DapperRepositoryBase
{
    protected readonly IConnectionFactory _connectionFactory;
    protected readonly string _connectionStringName;

    protected DapperRepositoryBase(IConnectionFactory connectionFactory, string connectionStringName)
    {
        _connectionFactory = connectionFactory;
        _connectionStringName = connectionStringName;
    }

    protected DbConnection OpenConnection()
    {
        var connection = _connectionFactory.Create(_connectionStringName);
        connection.Open();
        return connection;
    }

    protected T Fetch<T>(Func<DbConnection, T> func)
    {
        if (func == null)
        {
            throw new ArgumentNullException("func");
        }

        using (var connection = OpenConnection())
        {
            return func(connection);
        }
    }

    protected int Execute(Func<DbConnection, int> func)
    {
        if (func == null)
        {
            throw new ArgumentNullException("func");
        }

        using (var connection = OpenConnection())
        {
            return func(connection);
        }
    }

    // remainder of class omitted
}

If you want to start without the base class implementation, your Get() implementation might look a little bigger, maybe like the following. For the rest of the examples, we'll use my base class and I hope it will be obvious where the refactorings came from.

public User Get2(int id)
{
    const string sql = "SELECT * FROM [Users] WHERE [Id] = @userId";

    using (var connection = _connectionFactory.Create(_connectionStringName))
    {
        connection.Open();

        var user = connection.Query<User>(sql, new { userId = id }).SingleOrDefault();

        return user;
    }
}

Now that we have (as we've convinced ourselves) a working Get() method, we can now turn to developing Create. First, we stub it in and have it throw a NotImplementedException.

public class DapperUserRepository : DapperRepositoryBase, IUserRepository
{
    public DapperUserRepository(IConnectionFactory connectionFactory, string connectionStringName)
        : base(connectionFactory, connectionStringName)
    {
    }

    public void Create(User user)
    {
        throw new NotImplementedException();
    }

    // Get omitted
}

Then, we'll create our first test. Typically I'll start with the success case first. In this case, we'll test that a User can be successfully created. We'll add some helper methods to the test context that allows us to get an instance of the repository and create a user with some pre-filled properties. Note that since we're using autoincrement ids, our create code will need to fill this value in when performing the insert.

public class DapperUserRepositoryTests : TestBase
{
    private DapperUserRepositoryTestContext _c;

    [TestMethod]
    public void When_a_new_user_is_created_the_data_is_inserted()
    {
        var repository = _c.GetRepository();
        var user = _c.CreateStandardUser();

        repository.Create(user);

        var retrievedUser = repository.Get(user.Id);

        Assert.AreEqual(user.DisplayName, retrievedUser.DisplayName);
        Assert.AreEqual(user.Email, retrievedUser.Email);
        Assert.AreEqual(user.Active, retrievedUser.Active);
    }
    
    // test context omitted
}

We run the test and it fails, as expected. Now let's provide the least code necessary, in an integration test context, to satisfy the test, that is, insert the record into the database so that when it's retrieved the stored values are matched by the retrieved values. We need to insert the DisplayName, Email, CreatedDate, and Active state.

public void Create(User user)
{
    const string sql = "INSERT INTO [Users] ([DisplayName], [Email], [CreatedDate], [Active]) OUTPUT inserted.[Id] VALUES (@displayName, @email, @createdDate, @active)";

    var id = Fetch(c => c.Query<int>(sql, new
    {
        displayName = user.DisplayName,
        email = user.Email,
        createdDate = DateTime.Now,
        active = user.Active
    }).Single());

    user.Id = id;
}

We run the test now and it passes. Next we might want to test that the created date is also updated when the user is created. This is a bit tricky because we can't simply compare dates between the CLR and SqlServer; since their resolutions are different we'll get spurious errors. One way around this is to take a snapshot of the time right before and right after the insert and make sure that the inserted date falls between those ranges within an acceptable bound - for me I only care about it being accurate to the nearest second. We'll use another helper method to round our times to the nearest second for comparison. Thus our next test is:

[TestMethod]
public void When_a_new_user_is_created_the_current_time_is_set_as_the_created_date()
{
    var repository = _c.GetRepository();
    var user = _c.CreateStandardUser();

    var before = _c.RoundToSecond(DateTime.Now);
    repository.Create(user);
    var after = _c.RoundToSecond(DateTime.Now);

    var retrievedUser = repository.Get(user.Id);

    var created = _c.RoundToSecond(retrievedUser.CreatedDate);

    // we only care about the nearest second
    Assert.IsTrue(before <= created && created <= after);
}

Running this test fails because we're not retrieving the CreatedDate. In this case I think it's easier to set the value on the object instead of storing it, so we'll implement it as follows.

public void Create(User user)
{
    var date = DateTime.Now;

    const string sql = "INSERT INTO [Users] ([DisplayName], [Email], [CreatedDate], [Active]) OUTPUT inserted.[Id] VALUES (@displayName, @email, @createdDate, @active)";

    var id = Fetch(c => c.Query<int>(sql, new
    {
        displayName = user.DisplayName,
        email = user.Email,
        createdDate = date,
        active = user.Active
    }).Single());

    user.Id = id;
    user.CreatedDate = date;
}

Note that we don't set any values on the object until the Fetch has succeeded - we don't want the object state to be updated if there is an exception. Even though the code (appears) to work that way, we'll create another test to ensure that.

[TestMethod]
public void When_an_exception_is_thrown_the_id_and_date_are_not_updated()
{
    var repository = _c.GetRepository();
    var user = _c.CreateStandardUser();
    var otherUser = _c.CreateStandardUser(1);
    otherUser.DisplayName = user.DisplayName;

    repository.Create(user);

    var expectedId = otherUser.Id = -1;
    var expectedDate = otherUser.CreatedDate = DateTime.MinValue;

    try
    {
        repository.Create(otherUser);
        Assert.Fail("Expected SqlException not thrown.");
    }
    catch (SqlException)
    {
        Assert.AreEqual(expectedId, otherUser.Id);
        Assert.AreEqual(expectedDate, otherUser.CreatedDate);
    }
}

And that test passes, as we hoped. We continue writing tests in this manner, testing small bits of functionality until we believe that our methods perform the way we think they should. While it won't be possible without developing a parallel implementation for our functions to develop the tests without interdependencies, this gives us a reasonably simple way to build up our code in a Test Driven fashion.

Hopefully this small example, with the code included in the GitHub repository, has shown you how to get started doing TDD with Dapper. I've left the code in the repository incomplete so you can experiment with it. In particular, there are some interesting behaviors in the Post repository. You might want to experiment with getting the posts associated with a specific user, which returns a filtered collection, for example.