There is a follow-up article about integration tests in 2016. Recommended for reading if you are using VSTS and looking at Cake build system

Unit tests are fine and dandy, but if you persist your data in database, persistence code must be tested as well. Some possible bugs will never be picked up only by unit tests. One of the issues I’ve been burned by is entity validation, another is a missing foreign key relationship.

Validation issues can burn you very easy. Look at this bit of code:

public class Person 
{
    [Key]
    public Guid PersonId { get; set; }

    [Required]
    public String FirstName { get; set; }

    [Required]
    public String LastName { get; set; }
}

public void CreatePerson(string firstName, string lastName)
{
    var newPerson = new Person();
    newPerson.FirstName = firstName;

    // dbContext was injected via constructor
    dbContext.Persons.Add(newPerson); 
    dbContext.SaveChanges();
}

NB: This is just a sample code. Nowhere in my projects we do such things.

Do you see the problem with this piece of code? Last Name on Person was not populated. This issue should be picked up by unit tests, but let’s suppose this is not picked up. What would happen when you try to save this object into database? You’d get DbEntityValidationException thrown with one of the validation errors saying LastName field is required because there is [Required] attribute is decorating this property. You will not see this issue until you hit the database. If missing LastName is not picked up by unit tests (which in this case should happen), you will only see the exception only during run-time.

Also there is another bunch of good reasons to do database tests. But most of my db-tests check if query is valid for the query and if I get any exceptions with random combination of query parameters as null.

There have been a few write-ups about how to do testing in DB. Most notable are from Jimmy Bogard: Strategies for isolating the database in tests and Isolating database data in integration tests

We have tried most of the recommended approaches, and now (I think) we have settled on something that is a combination of approaches, so worth writing about.

Repeatability and Independence

One of the conditions for unit-tests is repeatability. You should be able to run your tests many times over without implications. Database tests are special in their own way. Usually for DB-tests to work you need to have some data pre-seeded in your test-database. And then you do some manipulations with data. So for the next test to succeed, you need to have the same starting data. One of the ways to do that is to delete all the data from DB and re-seed with known state. Deleting ALL data from your database can be tricky – you need to know graph of table dependencies. You can have a manual script that deletes data in the right order. But with any kind of scale in your database and this becomes a chore. Imaging a hundred-odd tables and organising a delete script for that? I have tried – not my idea of fun.

You can find a script that deletes data for you in an orderly fashion, but that also did not work for me.

Better way is not to write any data to the database, so you won’t have to delete it later! Just start a transaction before every DB test, do you writes and then roll-back after test is finished.

NUnit does not have in-built way of doing transactions and rollbacking. So I rolled my own attribute:

using System;
using System.Transactions;
using NUnit.Framework;


/// <summary>
/// Rollback Attribute wraps test execution into a transaction and cancels the transaction once the test is finished.
/// You can use this attribute on single test methods or test classes/suites
/// </summary>
public class RollbackAttribute : Attribute, ITestAction
{
    private TransactionScope transaction;

    public void BeforeTest(TestDetails testDetails)
    {
        transaction = new TransactionScope();
    }

    public void AfterTest(TestDetails testDetails)
    {
        transaction.Dispose();
    }

    public ActionTargets Targets
    {
        get { return ActionTargets.Test; }
    }
}

Simple. Before every test SQL transaction is started. After every test the transaction is disposed, not committed. And actual test would look like this:

[Test, Rollback]
public void Do_Your_Writes()
{
    dbContext.Persons.Add(new Person());
    dbContext.SaveChanges();
} 

And after the test execution, nothing is saved to the database. So if all your tests are wrapped into a transaction, you know your data in the database is always at the same state.

XUnit does have it’s own [AutoRollback] attribute that does the same work.

Changing Database Structure

Database structure is yet another issue with the db-tests. You must make sure that test-database is always at the same state as your production database. I was ignoring this for a long time, until recently, when I had to come up with a better way of dealing with change.

If you run Entity Framework, it is silly not to use Database Migrations – very useful tool. EF can build a database from scratch given the schema. Or you can run all your migration scripts to get to the latest state of the database exactly as in production. In the past I have been rebuilding the test-database from the context, ignoring the migrations. And I have been doing that for every test. Reason for that is to guarantee that tests would not fail if executed in random order: each test took responsibility of rebuilding db if required. The process for every test looked like this:

  • Check if DB is in a good shape
  • If schema does not match domain models, drop and re-create the DB
  • Run the test.

Checking the state of database for every test was very slow. Even though only the first executed test dropped-created database, every other test was checking the schema. Schema checking was excessive and expensive – schema stayed untouched during the tests run.

A better approach is to always have your test database in a good shape and let test just run, presuming the schema is good.

For that just re-build your database only once after every change (honestly, how often do you change your schema?) And to re-build your test-database, use your migrations.

Here is what I’ve started to use recently to re-buld the test-database:

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Migrations;
using MyApp.Tests.Stubs;
using MyApp.Services.Configuration;
using MyApp.Data;
using NUnit.Framework;


public class DatabaseSetup
{
    // you don't want any of these executed automatically
    [Test, Ignore("Only for manual execution")]
    public void Wipe_And_Create_Database()
    {
        var connectionString = new StubConfiguration().GetDatabaseConnectionString();

        // drop database first
        ReallyDropDatabase(connectionString);

        // Now time to create the database from migrations
        // MyApp.Data.Migrations.Configuration is migration configuration class 
        // this class is crated for you automatically when you enable migrations
        var initializer = new MigrateDatabaseToLatestVersion<DomainContext, MyApp.Data.Migrations.Configuration>();

        // set DB initialiser to execute migrations
        Database.SetInitializer(initializer);

        // now actually force the initialisation to happen
        using (var domainContext = new DomainContext(connectionString))
        {
            Console.WriteLine("Starting creating database");
            domainContext.Database.Initialize(true);
            Console.WriteLine("Database is created");
        }

        // And after the DB is created, you can put some initial base data 
        // for your tests to use
        // usually this data represents lookup tables, like Currencies, Countries, Units of Measure, etc
        using (var domainContext = new DomainContext(connectionString))
        {
            Console.WriteLine("Seeding test data into database");
            // discussion for that to follow
            SeedContextForTests.Seed(domainContext);
            Console.WriteLine("Seeding test data is complete");
        }
    }

    // this method is only updates your DB to latest migration.
    // does the same as if you run "Update-Database" in nuget console in Visual Studio
    [Test, Ignore("Only for manual execution")]
    public void Update_Database()
    {
        var connectionString = new StubConfiguration().GetDatabaseConnectionString();

        var migrationConfiguration = new MyApp.Migrations.Configuration();

        migrationConfiguration.TargetDatabase = new DbConnectionInfo(connectionString, "System.Data.SqlClient");

        var migrator = new DbMigrator(migrationConfiguration);

        migrator.Update();
    }


    /// <summary>
    /// Drops the database that is specified in the connection string.
    /// 
    /// Drops the database even if the connection is open. Sql is stolen from here:
    /// http://daniel.wertheim.se/2012/12/02/entity-framework-really-do-drop-create-database-if-model-changes-and-db-is-in-use/
    /// </summary>
    /// <param name="connectionString"></param>
    private static void ReallyDropDatabase(String connectionString)
    {
        const string DropDatabaseSql =
        "if (select DB_ID('{0}')) is not null\r\n"
        + "begin\r\n"
        + "alter database [{0}] set offline with rollback immediate;\r\n"
        + "alter database [{0}] set online;\r\n"
        + "drop database [{0}];\r\n"
        + "end";

        try
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var sqlToExecute = String.Format(DropDatabaseSql, connection.Database);

                var command = new SqlCommand(sqlToExecute, connection);

                Console.WriteLine("Dropping database");
                command.ExecuteNonQuery();
                Console.WriteLine("Database is dropped");
            }
        }
        catch (SqlException sqlException)
        {
            if (sqlException.Message.StartsWith("Cannot open database"))
            {
                Console.WriteLine("Database does not exist.");
                return;
            }
            throw;
        }
    }
}

You will say, these tests do not assert anything. That’s true! These are not really tests, only marked to be tests. I use test framework to create a small snippets of code to be executed in separation from the rest of the application. And that is why these methods are marked as ignored.

So now after every new db-migration added you manually execute Update_Database() method. That would update local test-database. And this should work for all developers.

Granted, this approach involves one extra step for developers – they will have to manually execute one of the methods to be able to run db-tests locally. Yes, but this is a small price to pay for such an optimisation. With the old approach, when every test checked the database schema, 80ish database tests took 6 minutes to execute. Now our whole suite of 1300 tests run in 40 seconds on dev-machines. That is a huge improvement!

Execute Db-Tests on Build Server vs local execution

Every developer has his own instance of SQL Server Express running on their own rig. But we keep connection strings identical for everyone (using Windows Authentication). For db-test we have a separate database for every developer and one extra on a build server. Connection string for test database is taken from app.config file.

Connection string on build-server is different from developers connection strings, so we transform app.config in the same way you can do web.config transformation.

To do app.config transformation you’ll need to have SlowCheetah nuget package. Latest version of this nuget is taking care of many things for you, so transformation is mostly trouble-free experience.

Anyway, I digress. On the build server you need something or somebody to run the the Wipe_And_Create_Database() method for you. And now the black magic comes into play!

Make your test assembly to be a command line executable, and then you can execute whatever you like from command line.

public static class Program
{
    public static int Main(string[] args)
    {
        if (args[0] == "UpdateDatabase")
        {
            Console.WriteLine("Wiping and restoring database");

            // this is the class with tests listed above
            var databaseRestorer = new DatabaseSetup();

            try
            {
                databaseRestorer.Wipe_And_Create_Database();
            }
            catch (Exception exception)
            {
                Console.WriteLine("Failed to wipe and restore database");
                Console.WriteLine(exception.ToString());
                return 1;
            }

            Console.WriteLine("Restoring database complete");
        }
        else
        {
            Console.WriteLine(@"Nothing is happening. The only available command is UpdateDatabase. Use this program like this: ""C:/>MyApp.Tests.exe UpdateDatabse""");
        }

        return 0;
    }
} 

And on your build server you’ll have to execute command similar to this:

MyApp.Tests.exe UpdateDatabase

This will rebuild your test database. After that you can run your database tests and be sure that schema is created by migrations.

One little catch for TeamCity users. As far as I know, TeamCity does not make available compiled assemblies until end of the build process. So you’ll have to have 2 build processes: one compiles your project, including MyApp.Tests.dll, possibly run your fast unit tests. And if everything is fine in this step, publish artefacts. Then have next build stage that depends on the first stage. And this stage will have access to your compiled .exe file. After that you’ll be able to re-create your database for testing, and run database tests.

Setting up TeamCity for this process was trivial, so I’ll omit the description of that.

And this is how you should organise your database-dependent tests, kids!

  • Tawani

    Excellent and simple approach.

  • the_fish12345

    Do you by chance have a URL with all the code from this example? I’m trying to make this run, and it would be simpler if everything was provided, like StubConfiguration.

  • TryAgain

    As i know, sql server doesn’t suppot nested transactions, so calling rollback in inner transaction resets all outer transactions as well. Can’t it be a problem if i use transactions in code under test?

    • Your information about nesting of transactions is not correct. See this documentation: http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx

      Also EF DbContext.SaveChanges() saves everything in a transaction. And recently I’ve written a load of tests working with hand-rolled transaction across multiple EF-transactions. And Rollback worked as expected. So there is no problem with transactions.

  • Tim Johnson

    Glad to see someone else is doing this too. i have found cases where it does not necessarily work in all cases, but mostly those have actually indicated bugs in our code where we were violating consistency rules for the persistence.

    • Over time I grew fond of integration tests and stopped using mocks for any tests that touch DB.

  • Patrick Michalina

    I really like this post. Thanks.

  • Kennedy Sigauke

    Where is this object defined.?

    SeedContextForTests.Seed(domainContext);