Unit testing Dapper repositories

Dapper is a micro-ORM library which isvery simple and super fast. In our projects we use Dapper for the tasks where something likeEntityFramework or NHibernate would be an overkill.

Quite often the data access code is difficult to be unit tested. Objects likedatabase connections, commands, transactions and contexts are hard to mock, andthus the data access code is not easily isolated. Dapper relies heavily on SQLstatements inside C# code, which gives an extra complication. Some people wouldargue that unit tests are not warranted for data access layer, and integrationtests should be used instead. Let’s have a look at another possibility.

An Example of a Repository

Let’s say we have a simple class and we want to populate instances of this classfrom the database:

publicclassProduct{publicint Id {get;set; }publicstring Name {get;set; }publicstring Description {get;set; }}

To be able to use Dapper for data access, we need an instance ofIDbConnection.As we want to be able to mock the connection for unit tests, we need to createa factory interface to abstract it away:

publicinterface IDatabaseConnectionFactory{    IDbConnection GetConnection();}

Now the repository would get a connection from this factory and executeDapper queries on it:

publicclassProductRepository{privatereadonly IDatabaseConnectionFactory connectionFactory;public ProductRepository(IDatabaseConnectionFactory connectionFactory)    {this.connectionFactory = connectionFactory;    }public Task<IEnumerable<Product>> GetAll()    {returnthis.connectionFactory.GetConnection().QueryAsync<Product>("select * from Product");    }}

Testing Without a real Database

Here is my approach to testing the repository:

1. Use an in-memorySQLite3 database.
2. Create a table there and put some data in.
3. Run the repository against this database.
4. Compare the result to the expected values.

Here is a helper class which uses another micro-ORM libraryOrmLite to talkto SQLite database:

publicclassInMemoryDatabase{privatereadonly OrmLiteConnectionFactory dbFactory =new OrmLiteConnectionFactory(":memory:", SqliteOrmLiteDialectProvider.Instance);public IDbConnection OpenConnection() =>this.dbFactory.OpenDbConnection();publicvoid Insert<T>(IEnumerable<T> items)    {using (var db =this.OpenConnection())        {            db.CreateTableIfNotExists<T>();foreach (var itemin items)            {                db.Insert(item);            }        }    }}

And here is the test for ourProductRepository class:

[Test]publicasync Task QueryTest(){// Arrangevar products =new List<Product>    {new Product { ... },new Product { ... }    };var db =new InMemoryDatabase();    db.Insert(products);    connectionFactoryMock.Setup(c => c.GetConnection()).Returns(db.OpenConnection());// Actvar result =awaitnew ProductRepository(connectionFactoryMock.Object).GetAll();// Assert    result.ShouldBeEquivalentTo(products);}

Is It a Unit Test?

Well, not completely. This approach does not mock the database, but instead putsan in-memory database in place of the normal one. The problem is that we don’tcontrol all the details how it works, so it might not be as flexible as we need.For instance, SQLite type system is quite simplistic, so whileINT andBIGINTare different column types in SQL Server, they are the sameINTEGER type inSQLite. This can lead to false positive or false negative tests in edge cases.

Nevertheless, the concept is simple and requires very little amount of code,so it’s useful to have it in the toolbox anyway. The resulting tests are fast,have no external dependencies and are always consistent between multiple runs.That makes them better than real integration tests for the simple scenariosduring TDD development.

• Unit Testing
• Dapper
• ORM
• TDD