How to unit test your database code when using ServiceStack OrmLite

servicestackI’ve been working extensively with ServiceStack lately and I really like what they’ve done. As it contains the full stack for building HTTP based services from text serializers to development tools to a micro ORM it’s very easy to fall into the pit of success. I don’t mind picking and choosing these things for my self, however it’s nice that is doesn’t require any brain power making choices. Plus you can always be assured that the tools you get in the box is quick to get started with, and has all the performance you’ll ever need. They might be too simplistic for your needs but then it’s nice to know that its usually very simple to replace them with something else.

Let me tell you a story.

I was cruising along doing my thing, test-driving my code and was feeling pretty good about myself. I was hitting a very high code coverage as I was only writing a little bit of test code, a little bit of production code, and refactored as I went along. Now, I hit a pain point which showed itself to be very difficult to get under test. It was the few lines where I actually called the database through OrmLite’s extension methods.

OrmLite is a micro ORM and is part of the basic ServiceStack package. It is a set of extension methods on top of System.Data.IDbConnection which makes it very easy to use at the same time giving you direct access to the underlying APIs. It consists of operations like Insert<T>, Update<T>, Select<T> etc. and is really easy to get started with.

Anyway, as OrmLite is only extension methods on IDbConnection I though it’d be simple as cake to fake it out such that I could get my database calls under test as well.

Turns out it was way more difficult than I had anticipated. After a little while I gave up and left a total of four lines uncovered (one line per entity).

The other day I was looking through the ServiceStack documentation after something totally unrelated and stopped at a couple of lines that caught my attention.

// Use in-memory Sqlite DB instead
var dbFactory = new OrmLiteConnectionFactory(
    ":memory:", false, SqliteOrmLiteDialectProvider.Instance);


So, it was possible to use an in memory database instance instead of the SQL Server provider I was using in production. Very interesting. Could I utilize this in my unit tests to get that last mile?

Lets see. I quickly wrote a little unit test to try it out.

   1: [TestClass]
   2: public class EntityRepositoryTests
   3: {
   4:     Entity expectedEntity;
   6:     [TestMethod]
   7:     public void GetAll_All_ReturnsAllFromDatabase()
   8:     {
   9:         var dbFactory = new OrmLiteConnectionFactory(
  10:             connectionString: ":memory:",
  11:             autoDisposeConnection: false,
  12:             dialectProvider: SqliteOrmLiteDialectProvider.Instance);
  14:         using (var db = dbFactory.OpenDbConnection())
  15:         {
  16:             db.CreateTable<Entity>();
  17:             expectedEntity = new Entity
  18:             {
  19:                 Id = 1,
  20:                 Name = "foo"
  21:             };
  22:             db.Insert(expectedEntity);
  23:         }
  25:         var repository = new EntityRepository(dbFactory);
  27:         var result = repository.GetAll().ToList();
  29:         Assert.That(result, Is.EqualTo(new [] { expectedEntity }));
  30:     }
  31: }


Sqlite is not included in the standard ServiceStack package, however it’s simply a matter of running a command in the Nuget Package Manager Console:

   1: PM> Install-Package ServiceStack.OrmLite.Sqlite32

for the test project and SqliteOrmLiteDialectProvider lights up.

When this was done NCrunch automagically picked it up and everything turned green immediately. Nice!


Running the test with MSTest was a little trickier as it turned out. Sqlite drops an interop dll in the test project as a resource which MSTest couldn’t pick up. To get that to work I needed to add the dll as a deployment file in the local test settings. When I did that, the whole thing even ran in the continuous integration build in TeamCity.

Awesome day at work. Hope you’ve found this tip useful. Cheers!

Comments (1) -

  • mleyzaola

    3/16/2013 12:14:08 AM | Reply

    Awesome finding. Thanks for sharing!

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.