Dev: Faster Database Tests

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

Travis Marble
Travis Marble
Co-Founder / Development
Dev: Faster Database Tests

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).

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.

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

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!