Faster Database Tests
Tags: testing, database, csharp
Published: 2020-06-15 18:28:31

I have long had a theory, one that wasn’t well received by others, that fast tests that are hard to write are worse than slow tests that are easier to write.

On previous projects I have championed writing more integration tests (usually easier to write, but slower to run) than unit tests. Obviously unit tests have their place, but due to the pains of mocking data, mocking the database, and mocking everything else, they can be a pain to write.

This past week I spent almost 100% of my time focused on writing tests, which also meant I felt a lot of pain.

*Note: We are using .net core, .net core EF, Sql Server, and XUnit.*

I started writing database unit tests. These tests mocked everything but the db context. I would pass in an actual DB context for the queries to execute against.
To make this a little easier, I created a database fixture (fixtures are scoped for the test run, not test).

```c#

public class DatabaseFixture : IDisposable
{
    public MyDbContext Ctx { get; internal set; }
    private bool _isInitialized;
    private readonly string _connectionString;

    public DatabaseFixture()
    {
        _connectionString =
            "Server=(localdb)\\mssqllocaldb;Database=PulseTest;Trusted_Connection=True;MultipleActiveResultSets=true";
    }

   }

[CollectionDefinition("Database collection")]
public class DatabaseCollection : ICollectionFixture<DatabaseFixture>
{
    // This class has no code, and is never created. Its purpose is simply
    // to be the place to apply [CollectionDefinition] and all the
    // ICollectionFixture<> interfaces.
}
```

At first I tried using an in memory database, but it wasn’t quite what I wanted. I lacked faith that my results would be valid and some things didn’t work with an in memory database, e.g. stored procedures, etc. Here is a good primer on in memory databases https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/in-memory

Lets just use the database, but that can be slow. To help with the slowness I did a few things:

* Use transaction to avoid recreating the database on each test.
* Track the state of the database to avoid recreating if its clean.

#Transactions#
For the most part I want all of my tests to run in transactions, this makes them faster and avoids recreating the database. I created a base class, called TransactionSoped, to take care of all of the transaction stuff.

```c#
using System;
using System.Data;
using System.Diagnostics;
using System.Linq;
using CommunityPulse.Data;
using CommunityPulse.Data.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using Xunit;

namespace CommunityPulse.Test.DatabaseTests
{
    [Collection("Database collection")]
    public class TransactionScoped : IDisposable
    {
        internal readonly DatabaseFixture DatabaseFixture;
        internal readonly IDbContextTransaction Transaction;
        internal readonly MyDbContext Ctx;
        internal readonly DataGenerator DataGenerator;
        public TransactionScoped(DatabaseFixture databaseFixture)
        {
            DatabaseFixture = databaseFixture;
            databaseFixture.Init();
            
            Ctx = databaseFixture.Ctx;
            DatabaseFixture.IncrementDecrementDirtyTests(1);

            if (!Debugger.IsAttached)
            {
                Transaction = Ctx.Database.BeginTransaction();
            }
            DataGenerator = new DataGenerator(Ctx);
        }

        public void Dispose()
        {
            Transaction?.Rollback();
            if (Transaction == null) return;
            DatabaseFixture.IncrementDecrementDirtyTests(-1);
        }
    }
```

The TransactionScoped class increments a “DirtyTests” flag on start, decrements on end. **This is important.** In order to avoid recreating the database on each test run, we need to know if the database is in a clean state. I am using a counter to support parallel test runs. If “DirtyTests” is greater than 0 on our next run the database is dirty and needs to be recreated.

The class also creates a transaction :), which is rolled back upon completion.

Debugging a test with a transaction can be difficult, to overcome this if the debugger is attached, we don’t create a transaction. We also don’t decrement the dirty tests flag, ensuring the database gets recreated next time. Note: If you try and debug tests in parallel (not sure you even can) it will fail.

*Credit to Alex Wells who gave me the idea to use*

#Tracking the state of the database#
TransactionScoped increments and decrements a counter of tests running. When we initialize the database, we check that counter. If its greater than 0 we destroy the database, if not, we proceed.
DatabaseFixture handles this in the Init Function

```c#
using System;
using System.Data;
using System.Linq;
using System.Runtime.InteropServices.ComTypes;
using CommunityPulse.Data;
using CommunityPulse.Data.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using Xunit;

namespace CommunityPulse.Test
{
    public class DatabaseFixture : IDisposable
    {
        public MyDbContext Ctx { get; internal set; }
        private bool _isInitialized;
        private readonly string _connectionString;

        public DatabaseFixture()
        {
            _connectionString =
                "Server=(localdb)\\mssqllocaldb;Database=PulseTest;Trusted_Connection=True;MultipleActiveResultSets=true";
        }

        public void Dispose()
        {
            Ctx?.Dispose();
        }

        public void Init(bool force = false)
        {
            if (_isInitialized && !force) return;

            var builder = new DbContextOptionsBuilder<MyDbContext>();
            builder.UseSqlServer(_connectionString);
            var options = builder.Options;
            Ctx = new MyDbContext(options);

            try
            {
                var tx = Ctx.Database.BeginTransaction(IsolationLevel.Serializable);
                var dirtyTests = Ctx.TestKeyValues.FirstOrDefault(x => x.Key == "DirtyTests");
                if (dirtyTests == null || int.Parse(dirtyTests.Value) > 0)
                {
                    throw new Exception("Database is dirty");
                }
                tx.Commit();
            }
            catch
            {
                Ctx.Database.EnsureDeleted();
            }

            var seeder = new MyDbSeeder(Ctx);
            seeder.Seed();
            _isInitialized = true;
        }

        public void Clear()
        {
            _isInitialized = false;
        }

        public void IncrementDecrementDirtyTests(int incrementDecrement)
        {
            var tx = Ctx.Database.BeginTransaction(IsolationLevel.Serializable);

            var dirtyTests = Ctx.TestKeyValues.FirstOrDefault(x => x.Key == "DirtyTests");
            dirtyTests ??= Ctx.TestKeyValues.Add(new TestKeyValue() { Key = "DirtyTests" }).Entity;
            dirtyTests.Value ??= "0";
            dirtyTests.Value = (int.Parse(dirtyTests.Value) + incrementDecrement).ToString();
            Ctx.SaveChanges();
            tx.Commit();
        }
    }

    [CollectionDefinition("Database collection")]
    public class DatabaseCollection : ICollectionFixture<DatabaseFixture>
    {
        // This class has no code, and is never created. Its purpose is simply
        // to be the place to apply [CollectionDefinition] and all the
        // ICollectionFixture<> interfaces.
    }
}
```

#Results#
As of this writing, we have 81 tests that interact with the database. I am using JetBrains Resharper as my test runner, with parallel tests enabled.

*It takes approx 12 seconds to run all 81 tests!*

It takes approx 20 seconds to run them not in parallel. I am not 100% sure parallel is going to work long term, still experimenting, feel free to experiment with that one too!