Testing and databases

I prefer unit tests over integration tests any time. But at some point you just can’t avoid the latter. You need your components to hit a database to verify that your queries are correct and that you update the right records.

Some databases (like RavenDB for example, see Ayende’s answer) support running completely in-memory specifically for testing scenarios. If you are in the convenient situation to use one of them you can spin up a db instance, fill it with data, run your tests and throw the instance away. Otherwise you have to find another way to prepare your materialized database to offer a well defined set of records.

There are at least two ways to do that: Have a designated test database. Run each test inside a transaction and rollback after the test. Your database should be returned to the original state it was in before the test. Or you can drop the database after each test and recreate it from scratch with the data needed for each test.

I want to talk about the second approach. I wrote a small helper class for a task I’m currently working on. It allows you to define some setup steps (like picking a connection string or selecting the name of the database to create for the test) and a build-up sequence (drop existing db, create empty db, create empty tables etc.). And to make using it nice and easy it automatically drops the created database at the end of the test run.

public void Should_CreateAndDropDatabase()
  using (var result = Database.Build(
    x =>
          db =>

    // ... run your actual test code

Running this test writes some basic information about what’s going on to the console. But you can easily use some other means for tracing.

Retrieving ConnectionString named ‘MyConnectionString’.
Using database name ‘FooDatabase’.
Droping database ‘FooDatabase’.
Creating empty database ‘FooDatabase’.
Creating tables.
Creating test data.
Droping database ‘FooDatabase’.

How is it working? We start from a static entry point (similar to TopShelf’s HostFactory):

public static class Database
  public static DatabaseBuildResult Build(Action<DatabaseBuildConfiguration> action)
    Guard.AssertNotNull(action, "action");
    var config = new DatabaseBuildConfiguration();
    return config.Execute();

From there we make calls to DatabaseBuildConfiguration

public class DatabaseBuildConfiguration
  private readonly AppendOnlyCollection<Action> actions;
  private bool dontDropDatabaseOnDispose;
  public DatabaseBuildConfiguration()
    this.actions = new AppendOnlyCollection<Action>();
  public IAppendOnlyCollection<Action> Actions { get { return this.actions; } }
  public string ConnectionString { get; private set; }
  public string Database { get; private set; }
  public DatabaseBuildResult Execute()
    Action onDispose = this.dontDropDatabaseOnDispose ? () => { } : this.GetDropDatabaseAction();
    var result = new DatabaseBuildResult(onDispose);
      foreach (Action action in this.Actions)
    catch (Exception ex)
      result.Error = ex;
    return result;
  // ... more methods
  public void WithConnectionStringNamed(string connectionStringName)
    Guard.AssertNotEmpty(connectionStringName, "connectionStringName");
    Action action = () =>
        Console.WriteLine("Retrieving ConnectionString named '{0}'.", connectionStringName);
        this.ConnectionString =
  public void BuildSequence(Action<DatabaseBuildSequenceConfiguration> action)
    var config = new DatabaseBuildSequenceConfiguration(this);

This class is basically a container for a list of actions. The methods on the class place actions in the list. Execute() adds some error handling and runs these actions.

DatabaseBuildSequenceConfiguration defines the actions used to manipulate the database. I won’t show it’s code here as it works in the same way as DatabaseBuildConfiguration.

In the sample at the beginning of this post you can see a using-block surrounding the actual test code. The DatabaseBuildResult implements IDisposable. By default disposing the result will also drop the entire database. But you can turn off that behavior by calling DontDropDatabaseOnDispose().

You can add more methods for e.g. running sql scripts to generate or manipulate data or hookup ready to load database files. The model is quite easy to extend.

It’s surely not as good as having a fully functional (and functionally equivalent!) in-memory database but for a reasonably complex database it makes my live a lot easier!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: