Isolated db testing

in Software Engineering, Testing

Automated tests should run in isolation, be repeatable, and importantly, fast.

Trying to achieve those three things when doing database integration testing is difficult - even more so when you use XUnit which automatically executes tests in parallel.

In the past I've achieved this many ways:

  1. Create database, insert test data, do test, tear down database, and repeat for each test. (Very slow! Can't run tests in parallel.)
  2. Tear down and recreate database at the start of the test run, insert test data, do tests. (Faster, but you need to ensure your assertions are valid - you can't just assert a table has 5 records because another test, which happens to be running at the same time, just inserted another record.)
  3. A variation on the above - assume the database is already exists, insert test data, do test, clean up after yourself. (Faster than the second option, but it suffers from the same issues. Also, is your "tidy up code" right - a test may fail and leave the database in an unknown state.)
  4. SQLite. (There are many limitations, plus I want my database engine to be like the one I'm running in Production which is usually MS SQL Server.)

Is there a better way?

I should be able to spin up separate instances of LocalDb and attach a model database.

Each test then has its own database engine and database to test against. You don't have to worry about cleaning up, because the instance, along with all files associated with it, can be destroyed once the process ends.

How?

You've already got the necessary tools if you've got VS2010 with data tools installed. SqlLocalDb has everything you need to create and destroy instances. However, the functionality is been wrapped within a helpful API SqlLocalDbApi - a Nuget package by Martin Costello.

Using this package, you can easily spin up a new LocalDb instance and attach a database, insert test data (or start with a model already containing test data), do your test and you're done - you don't have to create and maintain any clean up code because the instance is destroyed automatically:

(The video isn't very interesting - and its a little fuzzy - but it demonstrates the creation/destruction of a LocalDb instance and the cleaning up after itself.)

Show me the code

I've created a sample here: https://github.com/code-computerlove/localdb-test-database.

It consists of two assemblies:

  1. TestDatabase - copies your model database to the LocalDb instances directory, as each instance needs its own *.mdf file, and sets the primary data file to the new copy.
  2. Sample - a simple XUnit test that initializes an instance of TestDatabase using the sample database.