Unit Testing Dapper with Inline Queries

Question

I know there are several question similar to mine.

• Dapper: Unit Testing SQL Queries
• Testing Dapper Queries

butI don't think both of above question has clear answer that fit my requirement.

Right now I develop a new WebAPI project and split between WebAPI project and DataAccess technology. I not have a problem test the Controller for WebAPI since I can mock the data access class.

But for DataAccess class that's a different stories, since I'm using Dapper with inline queries in it, I'm a bit confuse how can I test it by using Unit Test. I've asked some of my friends and they prefer to do Integration test instead of Unit Test.

What I want to know is, is it possible to unit test the DataAccess class that use Dapper and Inline queries in it.

Let's say I have a class like this (this is a generic repository class, since a lot of the codes have similar queries differentiate by table name and field)

public abstract class Repository<T> : SyncTwoWayXI, IRepository<T> where T : IDatabaseTable
{
       public virtual IResult<T> GetItem(String accountName, long id)
       {
            if (id <= 0) return null;

            SqlBuilder builder = new SqlBuilder();
            var query = builder.AddTemplate("SELECT /**select**/ /**from**/ /**where**/");

            builder.Select(string.Join(",", typeof(T).GetProperties().Where(p => p.CustomAttributes.All(a => a.AttributeType != typeof(SqlMapperExtensions.DapperIgnore))).Select(p => p.Name)));
            builder.From(typeof(T).Name);
            builder.Where("id = @id", new { id });
            builder.Where("accountID = @accountID", new { accountID = accountName });
            builder.Where("state != 'DELETED'");

            var result = new Result<T>();
            var queryResult = sqlConn.Query<T>(query.RawSql, query.Parameters);

            if (queryResult == null || !queryResult.Any())
            {
                result.Message = "No Data Found";
                return result;
            }

            result = new Result<T>(queryResult.ElementAt(0));
            return result;
       }

       // Code for Create, Update and Delete
  }

And the implementation for above code is like

public class ProductIndex: IDatabaseTable
{
        [SqlMapperExtensions.DapperKey]
        public Int64 id { get; set; }

        public string accountID { get; set; }
        public string userID { get; set; }
        public string deviceID { get; set; }
        public string deviceName { get; set; }
        public Int64 transactionID { get; set; }
        public string state { get; set; }
        public DateTime lastUpdated { get; set; }
        public string code { get; set; }
        public string description { get; set; }
        public float rate { get; set; }
        public string taxable { get; set; }
        public float cost { get; set; }
        public string category { get; set; }
        public int? type { get; set; }
}

public class ProductsRepository : Repository<ProductIndex>
{
   // ..override Create, Update, Delete method
}

Answer 1

Here is our approach:

1. First of all, you need to have an abstraction on top of IDbConnection to be able to mock it:

   public interface IDatabaseConnectionFactory
   {
       IDbConnection GetConnection();
   }
   

2. Your repository would get the connection from this factory and execute the Dapper query on it:

   public class ProductRepository
   {
       private readonly IDatabaseConnectionFactory connectionFactory;
   
       public ProductRepository(IDatabaseConnectionFactory connectionFactory)
       {
           this.connectionFactory = connectionFactory;
       }
   
       public Task<IEnumerable<Product>> GetAll()
       {
           return this.connectionFactory.GetConnection().QueryAsync<Product>(
               "select * from Product");
       }
   }
   

3. Your test would create an in-memory database with some sample rows and check how the repository retrieves them:

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

4. I guess there are multiple ways to implement such in-memory database; we used OrmLite on top of SQLite database:

   public class InMemoryDatabase
   {
       private readonly OrmLiteConnectionFactory dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteOrmLiteDialectProvider.Instance);
   
       public IDbConnection OpenConnection() => this.dbFactory.OpenDbConnection();
   
       public void Insert<T>(IEnumerable<T> items)
       {
           using (var db = this.OpenConnection())
           {
               db.CreateTableIfNotExists<T>();
               foreach (var item in items)
               {
                   db.Insert(item);
               }
           }
       }
   }
   

Answer 2

I adapted what @Mikhail did because I had issues when adding the OrmLite packages.

internal class InMemoryDatabase
{
    private readonly IDbConnection _connection;

    public InMemoryDatabase()
    {
        _connection = new SQLiteConnection("Data Source=:memory:");
    }

    public IDbConnection OpenConnection()
    {
        if (_connection.State != ConnectionState.Open)
            _connection.Open();
        return _connection;
    }

    public void Insert<T>(string tableName, IEnumerable<T> items)
    {
        var con = OpenConnection();

        con.CreateTableIfNotExists<T>(tableName);
        con.InsertAll(tableName, items);
    }
}

I've created a DbColumnAttribute so we can specify a specific column name for a classes property.

public sealed class DbColumnAttribute : Attribute
{
    public string Name { get; set; }

    public DbColumnAttribute(string name)
    {
        Name = name;
    }
}

I added some IDbConnection extensions for the CreateTableIfNotExists and InsertAll methods.

This is very rough so I've not mapped types correctly

internal static class DbConnectionExtensions
{
    public static void CreateTableIfNotExists<T>(this IDbConnection connection, string tableName)
    {
        var columns = GetColumnsForType<T>();
        var fields = string.Join(", ", columns.Select(x => $"[{x.Item1}] TEXT"));
        var sql = $"CREATE TABLE IF NOT EXISTS [{tableName}] ({fields})";

        ExecuteNonQuery(sql, connection);
    }

    public static void Insert<T>(this IDbConnection connection, string tableName, T item)
    {
        var properties = typeof(T)
            .GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .ToDictionary(x => x.Name, y => y.GetValue(item, null));
        var fields = string.Join(", ", properties.Select(x => $"[{x.Key}]"));
        var values = string.Join(", ", properties.Select(x => EnsureSqlSafe(x.Value)));
        var sql = $"INSERT INTO [{tableName}] ({fields}) VALUES ({values})";

        ExecuteNonQuery(sql, connection);
    }

    public static void InsertAll<T>(this IDbConnection connection, string tableName, IEnumerable<T> items)
    {
        foreach (var item in items)
            Insert(connection, tableName, item);
    }

    private static IEnumerable<Tuple<string, Type>> GetColumnsForType<T>()
    {
        return from pinfo in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
            let attribute = pinfo.GetCustomAttribute<DbColumnAttribute>()
            let columnName = attribute?.Name ?? pinfo.Name
            select new Tuple<string, Type>(columnName, pinfo.PropertyType);
    }

    private static void ExecuteNonQuery(string commandText, IDbConnection connection)
    {
        using (var com = connection.CreateCommand())
        {
            com.CommandText = commandText;
            com.ExecuteNonQuery();
        }
    }

    private static string EnsureSqlSafe(object value)
    {
        return IsNumber(value)
            ? $"{value}"
            : $"'{value}'";
    }

    private static bool IsNumber(object value)
    {
        var s = value as string ?? "";

        // Make sure strings with padded 0's are not passed to the TryParse method.
        if (s.Length > 1 && s.StartsWith("0"))
            return false;

        return long.TryParse(s, out long l);
    }
}

You can still use it the same way as @Mikhail mentions in Step 3.

Answer 3

I would like add another perspective on this problem and a solution that takes a different approach to solving it.

Dapper can be considered as a dependency on the repository class as it is an external codebase that we have no control over. Therefore testing it is not really in the realm of responsibility for Unit Testing (More in line with integration testing as you mentioned).

With that said, we cannot really mock Dapper directly because it is really just an extension method set on the IDbConnection interface. We could mock all of the System.Data code until we get down to the IDbCommand where Dapper really does its work. That however would be a lot of work, and in most cases not worth the effort.

We instead can create a simple IDapperCommandExecutor mock-able interface:

public interface IDapperCommandExecutor
{
    IDbConnection Connection { get; }

    T Query<T>(string sql, object? parameters = null);

    // Add other Dapper Methods as required...
}

This interface then can simply be implemented with Dapper:

public class DapperCommandExecutor : IDapperCommandExecutor
{
    public DapperCommandExecutor(IDbConnection connection)
    {
        Connection = connection;
    }

    IDbConnection Connection { get; }

    T Query<T>(string sql, object? parameters = null) 
        => Connection.QueryAsync<T>(sql, parameters);

    // Add other Dapper Methods as required...
}

Then all you would have to do is change the following:

var queryResult = sqlConn.Query<T>(query.RawSql, query.Parameters);

to

var queryResult = commandExecutor.Query<T>(query.RawSql, query.Parameters);

Then in your testing, you can create a mocked Command Executor

public class MockCommandExecutor : Mock<IDapperCommandExecutor>
{

    public MockCommandExecutor()
    {
        // Add mock code here...
    }

}

In summary, we do not need to test the Dapper library, it can, for unit testing, be mocked in. This mocked Dapper Command Executor will reduce the additional dependency requirement for an in-memory database and can reduce the complexity of your tests.