How do I unit test Dapper?

[Almenon]

The Dapper readme has no info on unit testing, and suprisingly there is not much tutorials online either.

The two main unit test libraries I was able to find were:

• DapperWrapper (not updated since 2014!!! Does it still work?)
• Moq.Dapper (requires .net core v2, but many people are still on VS2015+.net 4.6.2 or lesser)

When I google "unit test dapper" my top three results are:

• https://mikhail.io/2016/02/unit-testing-dapper-repositories <- requires in-memory db
• this stackoverflow <- also requires in-memory db
• https://www.intertech.com/Blog/unit-test-dapper-with-dapperparameters/ <- 2015 article promoting a library with only three stars

The dapper repo, meanwhile, appears to have integration tests using a actual database. While having a database for integration tests is ideal, it should also be possible to mock the connection so a database is not required.

[mgravell]

Frankly, I'm of the opinion that pure unit tests would be essentially useless here - most of the interesting problems are due to how it actually works with real providers, and as such I'm not massively inclined to invest much time trying to make large chunks of it "pure" unit tests. I'm also not sure it would serve a real purpose - the integration tests don't take very long to run.

So: what actual real problem are you trying to solve here? Do you have a specific intent in mind? Having pure tests just for the sake of it isn't actually a useful goal. I kinda don't care much whether I can successfully talk to mocks - I care that I can talk to a database.

Perhaps an in-process database might be worth exploring, but...

[Almenon]

Oh, I'm not talking about unit testing dapper itself. I'm talking about how to unit test in projects that use dapper

[billrob]

@Almenon You can wrap the dapper calls with your own interface. We have IDbExecutor that wraps the functions of dapper that we need. So at run time the DapperDbExecutor is the concrete used by real code and we mock IDbExecutor for our unit tests.

[Manirajss]

@Almenon I have used Sqlite to test queries in my project which written using Dapper. You may have small drawbacks such as sqlite is not supporting schemas and some syntax difference in sql functions.

[iby-dev]

I am looking for a dapper inmemory provider implementation - i am looking to move away from traditional pure unit tests just like Marc says. EFCore has some great new features but for dapper news is a little thin on the ground. I don't want to Mock anything, I want to spin up an inmemory db and then fire my DAL code at it and then create some scenarios around it. As Marc says, this more useful to me than the countless thousands of traditional tests I have done over the years. Any one has ideas on this please get in touch.

[nrjohnstone]

I agree with Marc that unit tests are not appropriate at this level.

If you are using an architectural style such as ports and adapters, then Dapper is an implementation detail of the particular adapter for a given port, in this case interfaces on the "persistence" port. As such, all of your "unit" testing should be done against an in memory implementation of your persistence interface.

Personally I suggest to people that they should forget about unit testing Dapper, it's a wrapper around database interaction and the only way to test that reliably is with integration tests otherwise all you are potentially testing and assert is strings and calls to mocks, which is next to useless and is binding intimately with your implementation details, which leads to a nightmare when refactoring anything.

The value of most unit tests is against your domain/core logic, keep them there and implement in memory implementations of your persistence interfaces.

For more concrete examples, have a look here

https://github.com/nrjohnstone/ports-adapters-examples/tree/master/example01

[codeapologist]

I have created a light abstraction library to help facilitate unit testing. As previously mentioned, this is not intended to test your queries with Dapper, but to provide a way to mock Dapper in your unit tests. I plan to keep it maintained and ensure it passes all of Dapper's integration tests. Link: DataAbstractions.Dapper

[julealgon]

Frankly, I'm of the opinion that pure unit tests would be essentially useless here - most of the interesting problems are due to how it actually works with real providers, and as such I'm not massively inclined to invest much time trying to make large chunks of it "pure" unit tests. I'm also not sure it would serve a real purpose - the integration tests don't take very long to run.

So: what actual real problem are you trying to solve here? Do you have a specific intent in mind? Having pure tests just for the sake of it isn't actually a useful goal. I kinda don't care much whether I can successfully talk to mocks - I care that I can talk to a database.

@mgravell while I can see where you are coming from and agree to some extent with the mindset, you should seriously reconsider not forcing it on consumers of the library as some companies have strict rules on unit test coverage and this can lead to severe difficulties when testing with Dapper.

Even if you do not think unit testing is beneficial from your perspective, it would be great if the library was written in a more testable way by exposing a few interfaces on top of the static extension methods. There is no need to get rid of the extension methods of course, this would just be a different way for tapping into the same functionality. Think AutoMapper here: it has all the static classes, but it also exposes the IMapper interface, which can very easily be mocked.

This allows the consumers of the library to decide if they want to go for full unit test testability (by injecting IMapper) or the simpler Mapper static class approach and rely on integration/functional tests to validate the behavior.

TL;DR I agree with the issue proposed here regardless of my opinion about the "usefulness" of unit tests. The client should decide how useful it is to them to have those pure unit tests.

[nrjohnstone]

That fact that AutoMapper made the bad mistake of putting an interface over something that essentially just copies property A to property B, to me demonstrates how far down the path of crazy a certain view of unit testing is.

The IMapper was not a bad design decision in itself, but what it allows people to do now is couple to the internal details of a Class about the fact that "mapping" takes place and the signatures that are used to do the mapping.

The very fact that a misguided proportion of the "unit" testing crowd believe that coupling a test fixture to every class in your solution is a great thing makes me believe that Dapper should resist doing this because at least it prevents people doing this.

Unit Test != Class Testing

My main reason for posting is I'm passionate about the great micro-orm that Dapper is and don't want to see it providing tools that directly encourage bad practices such as what AutoMapper has done.

[reckface]

I always recommend and use dapper for data access where inline SQL is already extensively used, and that's because it's a solid proven library. I don't need or want to test dapper. What would be nice is a way to ensure that the objects/parameters passed to dapper match what the database is expecting. If you've worked with large legacy codebases there's a tendency for stored procedure parameters to change independent of the .net code, and there's no way to find out until runtime. I need a way to verify the parameter object contains the parameters in the stored procedure metadata, or in the inline query as part of the test suite.

var items = connection.Query<Item>(@"Select Name, Id, Quantity 
                                 From SomeTable 
                                 WHERE Name like @name 
                                 AND Quantity > @quantity", new { Name, Quantity}); 

// the tests
parameterObjectProperties.Should().Contain("name", "quantity");

[2piix]

I'm just a lowly data analyst, but I have some testing needs (which are probably closer to integration testing) as well that are very much unmet by Dapper. (No offense, it's a great project)

Here's my scenario: I need to repeatably run a query to make sure that it always returns known good data (where the known good data is manually pulled out of the live database). I'm not interested in setting up "scenarios" or "mocking". I just want to run the query and make sure that, when tested against a live clone of production, the data we pull matches known good data. If we find a bug, we add a test case and work on the query until all the tests pass. (You know, like "unit testing", but in an RO environment instead of an isolated, fake one. The unit I'm testing is the actual SQL query.)

So, I found myself writing a lot of C# (not one of my strengths), when I thought, "Surely I can't be the only person who uses Dapper to write queries and tests, there must be something out there for me!" Nope. :-)

[MovGP0]

for unit testing, I've implemented a custom base interface for DBContext types:

    public interface IDbContext
    {
        Task<int> SaveChangesAsync(CancellationToken cancellationToken);

        Task<IDbContextTransaction> BeginTransactionAsync(CancellationToken cancellationToken);

        IDbConnection GetDbConnection();

        // define Dapper methods here
        Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
    }

Then I implement the context like this:

    public interface IMyContext : IDbContext
    {
        // ...
    }

    public sealed class MyContext : DbContext, IMyContext
    {
        public MyContext(DbContextOptions<MyContext> options) : base(options)
        {
        }

        public Task<IDbContextTransaction> BeginTransactionAsync(CancellationToken cancellationToken) => Database.BeginTransactionAsync(cancellationToken);
        public IDbConnection GetDbConnection() => Database.GetDbConnection();

        // wrap Dapper methods
        public Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return GetDbConnection().ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
        }

        // ...

Now i can easily mock everything:

var substitute = Substitute.For<IMyContext>();

[MovGP0]

@Almenon I have used Sqlite to test queries in my project

well, that's technically an Integration Test and not an Unit Test. Note the difference.

[michelvd]

I don't think you have to unit test the Dapper calling code all the time, and of course you don't have to test the internal working of Dapper, but if you have a complex query or one with some parameters, it isn't weird to test if that goes well? Or if a method has 2 queries in it, to unit test that the data returned from query a is properly set to the parameter in query 2?

I don't think you can assume that all code is tidy and solid and all, some code just isn't and especially then you want to write a unit test.

In these days it strikes me as strange that a library has multiple untestable methods, because they are implemented as extension methods and/or static methods.

Then saying: "you shouldn't be testing any code that is using Dapper' is not the solution imo.

[JibSail]

I have a model with some troublesome getters and setters.

It would be nice to be able to test how they behave with Dapper; Considering the value add is preventing breaking code changes in the model.

Dapper.Moq doesn't seem like it would help in this case.

I guess I could try mocking the actual db connection... I'll let you know how that goes.

[dlidstrom]

I've (partly) solved this by customizing the IDbConnection. Here's a sample that maps the query string to DataSet, effectively simulating database access. All taken care of by a custom IDbConnection. I am not sure this works all the way, you'll have to try it with your queries and see for yourself.

Click to expand!

```csharp async Task Main() { var dataSets = new Dictionary { // provide queries and the expected results, this is your stub { "select top 2 * from hotel", new List { new Hotel { Code = "H-123", HotelId = Guid.NewGuid() }, new Hotel { Code = "H-456", HotelId = Guid.NewGuid() }, }.ToDataSet() }, { "select * from customer", new List { new Customer { Id = 1, Name = "Daniel" } }.ToDataSet() } }; // the connection is the extension point, setup in your tests var conn = new MyDbConnection(dataSets); // probably your application code starts like this, pass the connection // to whatever you use (IoC, etc) conn.Open(); (await conn.QueryAsync("select top 2 * from hotel")).Dump(); conn.Query("select * from customer").Dump(); } // Define other methods and classes here class Hotel { public string Code { get; set; } public Guid HotelId { get; set; } } class Customer { public int Id { get; set; } public string Name { get; set; } } public static class MyExtensions { public static DataSet ToDataSet(this IList list) { Type elementType = typeof(T); DataSet ds = new DataSet(); DataTable t = new DataTable(); ds.Tables.Add(t); // add a column to table for each public property on T foreach (var propInfo in elementType.GetProperties()) { Type colType = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType; t.Columns.Add(propInfo.Name, colType); } // go through each property on T and add each value to the table foreach (T item in list) { DataRow row = t.NewRow(); foreach (var propInfo in elementType.GetProperties()) { row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value; } t.Rows.Add(row); } return ds; } } private class MyDbConnection : IDbConnection { private readonly Dictionary dataSets; public MyDbConnection(Dictionary dataSets) { this.dataSets = dataSets; } public string ConnectionString { get => ""; set => throw new NotImplementedException(); } public int ConnectionTimeout => throw new NotImplementedException(); public string Database => throw new NotImplementedException(); public ConnectionState State => ConnectionState.Open; public IDbTransaction BeginTransaction() { throw new NotImplementedException(); } public IDbTransaction BeginTransaction(System.Data.IsolationLevel il) { throw new NotImplementedException(); } public void ChangeDatabase(string databaseName) { throw new NotImplementedException(); } public void Close() { throw new NotImplementedException(); } public IDbCommand CreateCommand() { return new CommandWrapper(new MyCommand(dataSets)); } public void Dispose() { throw new NotImplementedException(); } public void Open() { } private class MyCommand : IDbCommand { private readonly Dictionary dataSets; private DataSet currentDataSet; public MyCommand(Dictionary dataSets) { this.dataSets = dataSets; } public IDbConnection Connection { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public IDbTransaction Transaction { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public string CommandText { get { throw new NotImplementedException(); } set { if (dataSets.TryGetValue(value, out currentDataSet) == false) { throw new Exception($"No DataSet configured for query '{value}', update your test setup"); } } } public int CommandTimeout { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public CommandType CommandType { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public IDataParameterCollection Parameters => throw new NotImplementedException(); public UpdateRowSource UpdatedRowSource { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public void Cancel() { throw new NotImplementedException(); } public IDbDataParameter CreateParameter() { throw new NotImplementedException(); } public void Dispose() { } public int ExecuteNonQuery() { throw new NotImplementedException(); } public IDataReader ExecuteReader() { return currentDataSet.CreateDataReader(); } public IDataReader ExecuteReader(CommandBehavior behavior) { return currentDataSet.CreateDataReader(); } public object ExecuteScalar() { throw new NotImplementedException(); } public void Prepare() { throw new NotImplementedException(); } } private class CommandWrapper : DbCommand { private readonly MyCommand inner; public CommandWrapper(MyCommand inner) { this.inner = inner; } public override string CommandText { get => throw new NotImplementedException(); set => inner.CommandText = value; } public override int CommandTimeout { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public override CommandType CommandType { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public override bool DesignTimeVisible { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public override UpdateRowSource UpdatedRowSource { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } protected override DbConnection DbConnection { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } protected override DbParameterCollection DbParameterCollection => throw new NotImplementedException(); protected override DbTransaction DbTransaction { get => throw new NotImplementedException(); set => throw new NotImplementedException(); } public override void Cancel() { throw new NotImplementedException(); } public override int ExecuteNonQuery() { throw new NotImplementedException(); } public override object ExecuteScalar() { throw new NotImplementedException(); } public override void Prepare() { throw new NotImplementedException(); } protected override DbParameter CreateDbParameter() { throw new NotImplementedException(); } protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) { return new MyDbDataReader(inner.ExecuteReader()); } private class MyDbDataReader : DbDataReader { private readonly IDataReader dataReader; public MyDbDataReader(IDataReader dataReader) { this.dataReader = dataReader; } public override int Depth => throw new NotImplementedException(); public override int FieldCount => dataReader.FieldCount; public override bool HasRows => throw new NotImplementedException(); public override bool IsClosed => throw new NotImplementedException(); public override int RecordsAffected => throw new NotImplementedException(); public override object this[int ordinal] => dataReader[ordinal]; public override object this[string name] => throw new NotImplementedException(); public override bool GetBoolean(int ordinal) { throw new NotImplementedException(); } public override byte GetByte(int ordinal) { throw new NotImplementedException(); } public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length) { throw new NotImplementedException(); } public override char GetChar(int ordinal) { throw new NotImplementedException(); } public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length) { throw new NotImplementedException(); } public override string GetDataTypeName(int ordinal) { throw new NotImplementedException(); } public override DateTime GetDateTime(int ordinal) { throw new NotImplementedException(); } public override decimal GetDecimal(int ordinal) { throw new NotImplementedException(); } public override double GetDouble(int ordinal) { throw new NotImplementedException(); } public override IEnumerator GetEnumerator() { throw new NotImplementedException(); } public override Type GetFieldType(int ordinal) { return dataReader.GetFieldType(ordinal); } public override float GetFloat(int ordinal) { throw new NotImplementedException(); } public override Guid GetGuid(int ordinal) { throw new NotImplementedException(); } public override short GetInt16(int ordinal) { throw new NotImplementedException(); } public override int GetInt32(int ordinal) { throw new NotImplementedException(); } public override long GetInt64(int ordinal) { throw new NotImplementedException(); } public override string GetName(int ordinal) { return dataReader.GetName(ordinal); } public override int GetOrdinal(string name) { throw new NotImplementedException(); } public override string GetString(int ordinal) { throw new NotImplementedException(); } public override object GetValue(int ordinal) { throw new NotImplementedException(); } public override int GetValues(object[] values) { throw new NotImplementedException(); } public override bool IsDBNull(int ordinal) { throw new NotImplementedException(); } public override bool NextResult() { return dataReader.NextResult(); } public override bool Read() { return dataReader.Read(); } } } } ```

The output:

[mmulhearn]

Unit testing with Dapper is definitely something that needs to be implemented. My approach tends to be abstracting dependencies out as much as possible so you aren't tied to as many dependencies as possible. As such, my database access is through a custom ISqlClient and Dapper is used in a concrete implementation of ISqlClient. This does make things more complicated and generic, but this would allow us to move from Dapper to another ORM should Dapper discontinue or we find an irreconcilable issue with Dapper.

With this approach, Dapper data splitting is done as generically as possible. What I'd like to do is mock out the sql connection and a sql command so it returns a mocked data reader that pretends to be the result from the database. That would allow me to unit test the map delegate of SqlMapper.QueryAsync<T> and verify that my code is handling the data splits correctly.

The issue is that Dapper moves from interfaces (IDbConnection and IDbCommand) to unmockable concretes (DbConnection and DbCommand) so mocking out the connection and command fail as the interfaces don't cast.

I'm still looking around the web for solutions, but there may be none at this point in time.

[losito105]

@Almenon You can wrap the dapper calls with your own interface. We have IDbExecutor that wraps the functions of dapper that we need. So at run time the DapperDbExecutor is the concrete used by real code and we mock IDbExecutor for our unit tests.

We do something similar on my project w/ different implementations of a shared database accessor interface for each of our services. These can easily be mocked, and their methods stubbed using standard Moq. In line queries are another story though...