Write Unit Tests For Database Related Business Code

Background

Unit testing is a very important link in the process of writing code. It can effectively help us find and handle some problems as soon as possible.

When the code needs to read some data from the database or write some data to the database, things will become complicated!

There may be network failures, or the database may be shut down for maintenance. In these cases, we cannot connect to the database, which means that our code cannot run normally.

If we want to continue to write code, we need some unit tests to ensure that we are not affected by external dependencies!

Next, let's see how we can deal with this problem.

Hard To Test

Suppose we need to find the type field of a table in the database according to the ID, and then classify it according to the query results of the database.

Before we implement this method, we may write a empty method as following.

public class SomeAdoDotNet
{
    private readonly string _conn;

    public SomeAdoDotNet(string conn = "")
    {
        this._conn = conn;
    }

    public Task<string> HandleAsync(int id)
    {
        return Task.FromResult(string.Empty);
    }
}

Next, we will start to write unit tests and consider some possible situations.

[Fact]
public async Task HandleAsync_Should_Return_Empty_When_Db_Return_Is_Empty()
{
    var ado = new DbLib.SomeAdoDotNet(" real database connection string ");

    var res = await ado.HandleAsync(1);

    Assert.Equal("empty", res);
}

[Fact]
public async Task HandleAsync_Should_Return_A_When_Db_Return_StartWith_A()
{
    var ado = new DbLib.SomeAdoDotNet(" real database connection string ");

    var res = await ado.HandleAsync(1);

    Assert.Equal("A", res);
}

[Fact]
public async Task HandleAsync_Should_Return_B_When_Db_Return_Not_StartWith_A()
{
    var ado = new DbLib.SomeAdoDotNet(" real database connection string ");

    var res = await ado.HandleAsync(1);

    Assert.Equal("B", res);
}

After finishing the unit tests, we should implement the HandleAsync method according to the flowchart, the following code is a simple implementation.

public async Task<string> HandleAsync(int id)
{
    using var conn = new SqlConnection(_conn);
    var res = await conn.QueryFirstOrDefaultAsync<string>("SELECT type FROM test WHERE Id = @id", new { id });

    if (string.IsNullOrWhiteSpace(res)) return "empty";
    else if (res.StartsWith("A")) return "A";
    else return "B";
}

If we use dotnet test to run the unit tests, there is no doubt that all tests fail.

Because the code depends on a real database, if we input something wrong for it, everything will be down.

How can we modify the code to make it can be tested?

Can Be Tested

we can add a parameter which type is DbConnection, so that we can pass it from the outside to the method.

public async Task<string> Handle2Async(DbConnection conn, int id)
{
    return Task.FromResult(string.Empty);
}

When we using DbConnection, we can mock it without connect to the database.

DbMocker is a great tool that help us to mock a connection to a relational database. The core object is MockDbConnection.

[Fact]
public async void Handle2Async_Should_Return_Empty_When_Db_Return_Is_Empty()
{
    var ado = new DbLib.SomeAdoDotNet("");

    var dbconn = MockDbSelect("");
    var res = await ado.Handle2Async(dbconn, 1);

    Assert.Equal("empty", res);
}

[Theory]
[InlineData("A")]
[InlineData("A1")]
[InlineData("ABC")]
public async void Handle2Async_Should_Return_A_When_Db_Return_StartWith_A(string data)
{
    var ado = new DbLib.SomeAdoDotNet("");

    var dbconn = MockDbSelect(data);
    var res = await ado.Handle2Async(dbconn, 1);

    Assert.Equal("A", res);
}

[Theory]
[InlineData("BA")]
[InlineData("BB")]
[InlineData("C")]
public async void Handle2Async_Should_Return_B_When_Db_Return_Not_StartWith_A(string data)
{
    var ado = new DbLib.SomeAdoDotNet("");

    var dbconn = MockDbSelect(data);
    var res = await ado.Handle2Async(dbconn, 1);

    Assert.Equal("B", res);
}

[Fact]
public async void Handle2Async_Should_Return_Error_When_Db_Throw_Exception()
{
    var ado = new DbLib.SomeAdoDotNet("");

    var dbconn = MockDbException();
    var res = await ado.Handle2Async(dbconn, 1);

    Assert.Equal("error", res);
}

It can mock not only normal SQL execution, but also abnormal conditions.

MockDbSelect is a method for mocking a normal SQL execution, it will return a row when the command text start with select.

private Apps72.Dev.Data.DbMocker.MockDbConnection MockDbSelect(string res)
{
    var dbconn = new Apps72.Dev.Data.DbMocker.MockDbConnection();
    dbconn.Mocks
        .When(c => c.CommandText.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
        .ReturnsRow(res);
    return dbconn;
}

MockDbException is a method for mocking abnormal conditions, it will throw an exception when the command text start with select.

private Apps72.Dev.Data.DbMocker.MockDbConnection MockDbException()
{
    var dbconn = new Apps72.Dev.Data.DbMocker.MockDbConnection();
    dbconn.Mocks
        .When(c => c.CommandText.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
        .ThrowsException(new Exception("DB Error"));
    return dbconn;
}

The implementation of Handle2Async is as following:

public async Task<string> Handle2Async(DbConnection conn, int id)
{
    try
    {
        var res = await conn.QueryFirstOrDefaultAsync<string>("SELECT type FROM test WHERE Id = @id", new { id });

        if (string.IsNullOrWhiteSpace(res)) return "empty";
        else if (res.StartsWith("A")) return "A";
        else return "B";
    }
    catch (Exception)
    {
        return "error";
    }
}

Run unit tests using dotnet test and all tests will succeed.

But in real world, we may not add DbConnection for each method, it just suitable for some extension methods.

Splitting database operation and business operation can make unit tests more easier.

Easy To Test

Define a database operation interface at first.

public interface ISomeRepo
{
    Task<string> QueryAsync(int id);
}

Add a biz class that depends on above interface.

public class SomeBizBasedISomeRepo
{
    private readonly ISomeRepo _repo;

    public SomeBizBasedISomeRepo(ISomeRepo repo)
    {
        this._repo = repo;
    }

    public async Task<string> DoAsync(int id)
    {
        try
        {
            var res = await _repo.QueryAsync(id);

            if (string.IsNullOrWhiteSpace(res)) return "empty";
            else if (res.StartsWith("A")) return "A";
            else return "B";
            
        }
        catch (Exception)
        {
            return "error";
        }
    }
}

At this time, it's very easy to write unit tests.

[Fact]
public async void DoAsync_Should_Return_Empty_When_Db_Return_Is_Empty()
{
    var mock = new Mock<DbLib.ISomeRepo>();
    DoQueryMock(mock, "", false);

    var biz = new DbLib.SomeBizBasedISomeRepo(mock.Object);
    var res = await biz.DoAsync(1);

    Assert.Equal("empty", res);
}

[Theory]
[InlineData("A")]
[InlineData("A1")]
[InlineData("ABC")]
public async void DoAsync_Should_Return_A_When_Db_Return_StartWith_A(string data)
{
    var mock = new Mock<DbLib.ISomeRepo>();
    DoQueryMock(mock, data, false);

    var biz = new DbLib.SomeBizBasedISomeRepo(mock.Object);
    var res = await biz.DoAsync(1);

    Assert.Equal("A", res);
}

[Theory]
[InlineData("BA")]
[InlineData("BB")]
[InlineData("C")]
public async void DoAsync_Should_Return_B_When_Db_Return_Not_StartWith_A(string data)
{
    var mock = new Mock<DbLib.ISomeRepo>();
    DoQueryMock(mock, data, false);

    var biz = new DbLib.SomeBizBasedISomeRepo(mock.Object);
    var res = await biz.DoAsync(1);

    Assert.Equal("B", res);
}

[Fact]
public async void DoAsync_Should_Return_Error_When_Db_Throw_Exception()
{
    var mock = new Mock<DbLib.ISomeRepo>();
    DoQueryMock(mock, "", true);

    var biz = new DbLib.SomeBizBasedISomeRepo(mock.Object);
    var res = await biz.DoAsync(1);

    Assert.Equal("error", res);
}

We only need to use the simple operation of a mock object to complete the database operation.

private Mock<DbLib.ISomeRepo> DoQueryMock(Mock<DbLib.ISomeRepo> mock, string res, bool isEx = false)
{
    var setup = mock.Setup(x => x.QueryAsync(It.IsAny<int>()));

    if (isEx)
    {
        setup.Throws<Exception>(() => new Exception("ex ex ex"));
    }
    else
    {
        setup.Returns(Task.FromResult(res));
    }

    return mock;
}

Run unit tests using dotnet test and all tests will succeed as well.

Summary

This article shows two ways to write unit tests for database related business code. If a method exposes parameters of type DbConnection, we can use DbMocker for simple processing. However, normal business code recommends separating logic and interface oriented programming.

I hope this will help you!