09 Apr 2012 10 min read

Unit Testing Database Access

The goal of unit testing is to create a small, self-contained tests for a piece of code that tests the functionality of that piece of code in isolation from the rest of the system. Put more simply, it should test the code you want to test and nothing more.

This can be a little tricky when dealing with writing tests for database or file interaction. Should you create a database or text file just for testing and then work with that? What happens if the database is currently unavailable or someone else happens to be running tests against the same tables at the same time?

For a unit test to be useful, neither of these should be an issue.

So, here we’re going to work through writing a unit test for some data access code.

The Code

Right, let’s start off with the code we want to test:

public class LogServices
{
    /// <summary>
    /// Saves an API Access log.
    /// </summary>
    /// <param name="apiAccessLogEntry">The API developer key</param>
    public void Save(ApiAccessLogEntry apiAccessLogEntry)
    {
        var result = 0;
        using (var command = new OracleCommand(
            InsertCommandText, _context.OracleConnection, _context.OracleTransaction))
        {
            command.Parameters.Add(
                new OracleParameter("devkey", apiAccessLogEntry.Key));
 
            command.Parameters.Add(
                new OracleParameter("methodName", apiAccessLogEntry.MethodName));
 
            command.Parameters.Add(
                new OracleParameter("logdate", apiAccessLogEntry.Date));
 
            result = command.ExecuteNonQuery();
        }
    }
 
    const string InsertCommandText = "insert... { some command }";
}

First of all we need to decide what we want to test here. Do we need to test that the Oracle provider can write to the database? We’re not testing how the OracleCommand object works, so the answer is no. We want to test the code that we’ve written.

All the Save function needs to do is use the Oracle connection to persist the object to the database. Whether the Oracle provider successfully does this or not in production isn’t something we need to test. We can, however, test for how our code handles these failures. To do that, we need to be able to explicitly control how our database connection behaves in various circumstances.

For that, we’ll use a mock object.

Splitting up the code

Have a look at this line:

using (var command = new OracleCommand(
    InsertCommandText, _context.OracleConnection, _context.OracleTransaction))

The code is creating a new instance of an Oracle command directly in the method so it’s going to be hard to get our test code to control that. What if we wrote a factory to provide the database connection rather than instantiate it directly in the method? Let’s split things up a little and try that.

To begin with, we need some sort of way to get a new database command. The current code is already aware of the Oracle context and the command, so we’ll reuse that and build contracts for the other elements we need.

public class DataAccess.Oracle
{
    /// <summary>
    /// Contract for a repository using Oracle for persistence.
    /// </summary>
    public interface IOracleRepositoryContext
    {
        /// <summary>
        /// The Oracle connection for this context.
        /// </summary>
        OracleConnection OracleConnection { get; }
 
        /// <summary>
        /// The Oracle transaction for this context.
        /// </summary>
        OracleTransaction OracleTransaction { get; }
    }
 
    /// <summary>
    /// Contract of a factory for creating Oracle database commands.
    /// </summary>
    public interface IOracleDbCommandFactory
    {
        /// <summary>
        /// Gets the Oracle DB command using the specified values.
        /// </summary>
        /// <param name="commandText">The command text.</param>
        /// <param name="oracleRepositoryContext">The oracle repository context.</param>
        /// <returns>
        /// An <see cref="IDbCommand"/> for an Oracle connection.
        /// </returns>
        /// <exception cref="ArgumentNullException">Throw if
        /// <paramref name="commandText"/> or
        /// <paramref name="oracleRepositoryContext"/> are null.</exception>
        IDbCommand GetDbCommand(
            string commandText, IOracleRepositoryContext oracleRepositoryContext);
    }
}

Now that we have our factory set up, we can refactor the Save command to this:

public class LogServices
{
    /// <summary>
    /// Local instance of an implementation of
    /// <see cref="IOracleRepositoryContext"/>.
    /// </summary>
    private readonly IOracleRepositoryContext _context;
 
    /// <summary>
    /// Local instance of an implementation of
    /// <see cref="IOracleDbCommandFactory"/>.
    /// </summary>
    private readonly IOracleDbCommandFactory _commandFactory;
 
    // New constructor to pass in the context.
    public LogServices(
        IOracleRepositoryContext oracleRepositoryContext,
        IOracleDbCommandFactory databaseCommandFactory)
    {
        this._context = oracleRepositoryContext;
        this._commandFactory = databaseCommandFactory;
    }
 
    /// <summary>
    /// Saves an API Access log.
    /// </summary>
    /// <param name="apiAccessLogEntry">The API developer key</param>
    public void Save(ApiAccessLogEntry apiAccessLogEntry)
    {
        var result = 0;
        using (var command = this._commandFactory.GetDbCommand(
            InsertCommandText, this._context))
        {
            command.Parameters.Add(
                new OracleParameter("devkey", apiAccessLogEntry.Key));
 
            command.Parameters.Add(
                new OracleParameter("methodName", apiAccessLogEntry.MethodName));
 
            command.Parameters.Add(
                new OracleParameter("logdate", apiAccessLogEntry.Date));
 
            result = command.ExecuteNonQuery();
        }
    }
 
    const string InsertCommandText = "insert... { some command }";
}

Despite the added classes, we’ve now reduced the complexity of the code we want to test. The Save method is no longer instantiating a new command, it’s just using the factory and the context that we have provided it with as part of the containing service’s constructor.

Mocking

A mock object in unit testing is:

simulated object that mimics the behavior of a real object in controlled ways

We can use a mocking framework such as Moq to create objects from interfaces that will respond in any way we want.

A Mocking Example

Take this interface:

public interface IFileChecker
{
    bool FileExists(string fileName);
}

Without using mocking, testing anything that calls an implementation of this method would require creating a text file for the test, putting it in a specified place and then running the code against a concrete instance. All just to get this method to return true. So we’d have something like this:

public interface IFileIoServices
{
    string GetFileContent(string fileName);
}
 
// implementation not important here
public class FileChecker : IFileChecker { ... }
 
// implementation not important here either
public class FileIoServices : IFileIoServices { ... }
 
public class FileReader
{
    private IFileChecker _fileChecker;
    private IFileIoServices _fileIoServices;
 
    public FileReader(IFileChecker fileChecker, IFileIoServices fileIoServices) {
        _fileChecker = fileChecker;
        _fileIoServices = fileIoServices;
    }
 
    public string ReadFile(string fileName) {
        if (_fileChecker.FileExists(filename)) {
            return _fileIoServices.GetFileContent(fileName);
        }
    }
}
 
[Test]
public void FileCanBeRead()
{
    // Arrange
    const string FileName = "testfile.txt";
    const string TextFileContent = "This is the text that’s in the file.";
 
    var fileChecker = new FileChecker();
    var fileIo = new FileIoServices();
    var fileReader = new FileReader(fileChecker, fileIoServices);
 
    // Act
    var output = fileChecker.ReadFile(FileName);
 
    // Assert
    Assert.AreEqual(TextFileContent, output);
}

To test the method FileCanBeRead, we’re having to create:

A test file on disk, somewhere accessible to the test class
A constant with text that we know exactly matches the content of the created file (without having any issues with line endings)
An instance of FileChecker
An instance of FileIoServices

What happends if the test file isn’t available or the text inside changes or one of the dependent classes has an error? The code you’re trying to test may be fine but you’re still going to get a fail.

Let’s rewrite that test using mocks.

[Test]
public void FileCanBeRead()
{
    // Arrange
    const string FileName = "testfile.txt";
    const string TextFileContent = "This is the text that’s in the file.";
 
    var fileChecker = new Mock<IFileChecker>();
    var fileIo = new Mock<IFileIoServices>();
 
    fileChecker.SetUp(x => x.FileExists(FileName)).Returns(true);
    fileIo.SetUp(x => x.GetFileContent(FileName)).Returns(TextFileContent);
 
    var fileReader = new FileReader(fileChecker.Object, fileIoServices.Object);
 
    // Act
    var output = fileChecker.ReadFile(FileName);
 
    // Assert
    Assert.AreEqual(TextFileContent, output);
}

In lines 11 and 12, we’re setting up how we want out objects to responsd to certain calls. In this test, we’re not interested in how the FileExists or GetFileContent methods functions work (these will have their own tests); all we care about is how our function handles the reponses from them.

The only other difference is in line 14. The mock instances have an Object property that is an instance of the type being mocked.

Using mocking, it’s easy to test for all sorts of things that can happen when using the class.

What if we want to test for a non-existent file?

fileChecker.SetUp(x => x.FileExists(FileName)).Returns(false);

Or what if we want to test how we handle what to do when the file we’re trying to read is locked?

fileIo.SetUp(x => x.GetFileContent(FileName))
    .Throws<FileLockedException>();

What if we want it to fail for every file?

fileIo.SetUp(x => x.GetFileContent(It.IsAny<string>))
    .Throws<FileLockedException>();

This last code will match and throw an exception for any string passed to the method.

Mocking the Database

So, back to our database code:

/// <summary>
/// Saves an API Access log.
/// </summary>
/// <param name="apiAccessLogEntry">The API developer key</param>
public void Save(ApiAccessLogEntry apiAccessLogEntry)
{
    var result = 0;
    using (var command = this._commandFactory.GetDbCommand(
                             InsertCommandText, this._context))
    {
        command.Parameters.Add(
            new OracleParameter("devkey", apiAccessLogEntry.Key));
 
        command.Parameters.Add(
            new OracleParameter("methodName", apiAccessLogEntry.MethodName));
 
        command.Parameters.Add(
            new OracleParameter("logdate", apiAccessLogEntry.Date));
 
        result = command.ExecuteNonQuery();
    }
}

This is what we want to test:
– Does the method request a new command from the factory?
– Does the method execute a non query on that command?

What we don’t care about testing is:
– How does the factory get the command? (irrelevant; this is part of the contract’s implementation)
– Does data get written to the database? (that would be an integration test)
– What is the text of the InsertCommandText constant? (irrelevant; we’re not writing to an actual database)

Let’s write the tests.

Verifiable

Another useful feature of mocks is that we can verify that code has been called as expected. So, if we want to check that something has been run, we can simply, add .Verifiable() to the end of its setup chain and then call Verify() from the mock object while making assertions.

You can see that in use as part of the test.

[Test]
public void SaveExecutesCommandWithCorrectParameters()
{
    // Arrange
    const string ExpectedDevKey = "a dev key";
    const string ExpectedMethodName = "method name";
    var expectedLogDate = DateTime.Now;
 
    var logToSave = new ApiAccessLogEntry
    {
        DevKey = ExpectedDevKey,
        MethodName = ExpectedMethodName,
        LogDate = expectedLogDate
    };
 
    // Create the mock objects
    var oracleRepositoryContext = new Mock<IOracleRepositoryContext>();
    var databaseCommandFactory = new Mock<IOracleDbCommandFactory>();
    var dbCommand = new Mock<IDbCommand>();
    var commandParameters = new Mock<IDataParameterCollection>();
 
    // Set up the command and parameters
    dbCommand.SetupGet(x => x.Parameters)
        .Returns(commandParameters.Object);
 
    dbCommand.SetUp(x => x.ExecuteNonQuery()).Verifiable();
 
    // Set up the command factory
    databaseCommandFactory.SetUp(x => GetDbCommand(
            It.IsAny<string>(),
            oracleRepositoryContext.Object))
        .Returns(dbCommand.Object)
        .Verifiable();
 
    var logServices = new LogServices(
        oracleRepositoryContext.Object,
        databaseCommandFactory.Object);
 
    // Act
    logServices.Save(logToSave);
 
    // Assert: make sure GetDbCommand has been called
    databaseCommandFactory.Verify();
 
    // Assert: make sure ExecuteNonQuery has been called
    dbCommand.Verify();
 
    // Assert: Check the parameters match
    Assert.AreEqual(ExpectedDevKey, commandParameters.Object["devkey"]);
    Assert.AreEqual(ExpectedMethodName, commandParameters.Object["methodName"]);
    Assert.AreEqual(expectedLogDate, commandParameters.Object["logdate"]);
}

And that’s the test complete and runable with no external dependencies.

If we want to write additional tests to check how we handle database exceptions and connectivity issues, we can rewrite the setup to whatever we need.

Command factory exception:

[Test]
public void SaveThrowsAnExceptionIfCommandFactoryFails() {
 
    // Arrange
    var databaseCommandFactory = new Mock<IOracleDbCommandFactory>();
 
    // Set up the command factory
    databaseCommandFactory.SetUp(x => GetDbCommand(
            It.IsAny<string>,
            It.IsAny<IOracleRepositoryContext>))
        .Throws<CommandFactoryException>()
        .Verifiable();
 
    // Act and Assert
    Assert.Throws<CommandFactoryException>(
        () => new LogServices(
            oracleRepositoryContext.Object,
            databaseCommandFactory.Object));
 
    databaseCommandFactory.Verify();
}

Easy.

Summary

Unit tests are very useful in software development but only if they can be reliably run and tests functions in isolation of other code.

Mocking is a very powerful tool for handling external dependencies in a controlled way and forces us to write code that is very loosely coupled and written to contracts.