Testing Dapper and Raw SQL

In this lesson, you will learn how to verify the integrity of your hand-optimized data access layer using integration tests that execute against a real database instance.

When using Dapper, you aren't just writing C#; you are writing raw SQL. Because Dapper bypasses the abstraction layers of an ORM like EF Core, the compiler cannot verify if your SQL syntax is valid or if your mapping profiles match the database schema. To catch errors like mismatched column names or incorrect parameter naming, your test suite must perform "round-trip" integration tests.

The Strategy: Ephemeral Databases

Since these tests require a real database, the biggest challenge is preventing test data pollution. Using the "Managing Test Data Lifecycles" concept we covered earlier, you should spin up a fresh, containerized database (e.g., via Testcontainers) for each test run.

Consider this example of an integration test for a Dapper repository method:

[Fact]
public async Task GetUserById_ReturnsCorrectUser_WhenQueryIsValid()
{
    // Arrange: Use Testcontainers to ensure a clean, isolated database
    var connection = await _dbContainer.GetConnectionAsync();
    var repo = new UserRepository(connection);
    await connection.ExecuteAsync("INSERT INTO Users (Id, Name) VALUES (1, 'Alice')");

    // Act
    var user = await repo.GetUserByIdAsync(1);

    // Assert
    Assert.NotNull(user);
    Assert.Equal("Alice", user.Name);
}

Why Integration over Mocks?

You might be tempted to mock IDbConnection, but that is a dangerous anti-pattern here. Mocking a connection interface requires you to essentially "re-implement" the database logic inside your test mocks. If you change your SQL query in the repository, your mocks will still pass, giving you a false sense of security while the actual application crashes in production.

By writing integration tests, you validate that your Dapper type-mapping handles the database driver's data types correctly and that your raw SQL remains performant and syntactically sound. If the query fails, the test fails—exactly what you want in a high-quality data access layer.

Key Takeaway: Never mock Dapper connections; use ephemeral containers to run your raw SQL against a real database to guarantee that your optimized queries actually work.