Data Access Integration Testing with Testcontainers

I test my repositories by default on all my new and old projects and it proved it so useful that I decided to write about it.

Overview

Testcontainers for .NET makes it trivial to spin up production‑like database instances for tests, so you can run migrations and exercise real SQL without polluting developer machines or CI runners.

This post shows a pragmatic approach for .NET teams: run real databases in disposable containers (DotNet.Testcontainers), apply your migrations, exercise repositories (EF Core, Dapper, raw SQL), and assert behavior including roles, views, and stored procedures. Examples are drawn from a real test fixture pattern and include C# you can copy and adapt.

Why test repositories

A colleague of mine asked:

So do you test the EF's job which is already well tested by Microsoft?

No! Integration tests for repositories or data access are not about mocking the database — they are about validating the contract between your code (SQL or LINQ), your ORM configuration, and the actual database engine.

Data Access related exception are one of the most common runtime errors.

• You test the contract, not the implementation. Your code (LINQ or raw SQL), ORM configuration, and the physical schema must agree. ORMs like EF Core cannot guarantee that your entity configuration matches the actual SQL schema at runtime — only the database can validate that.
• Migrations are code too. Running migrations in tests catches syntax errors, permission problems, and ordering issues before CI or production.
• Frameworks change. Major EF Core upgrades often introduce breaking behavior. Running your repository tests against a real engine makes upgrades observable and safe.
• Security and DB objects matter. Roles, grants, views, and stored procedures are part of your runtime contract and can be tested the same way as code.

Testcontainers pattern and example fixture

public class DatabaseTestFixture : IAsyncLifetime
{
    private PostgreSqlContainer? _pgsqlContainer;

    public string AppConnectionString =>
        _pgsqlContainer is null ? throw new InvalidOperationException("_pgsqlContainer is not initialized")
        : $"Host={_pgsqlContainer.Hostname};Port={_pgsqlContainer.GetMappedPublicPort(5432)};Database=postgres;Username=testuser;Password=testuser";

    public async Task InitializeAsync()
    {
        _pgsqlContainer = new PostgreSqlBuilder()
            .WithImage("docker.io/postgres:14.17")
            .WithCleanUp(true)
            .Build();

        await _pgsqlContainer.StartAsync();
        await MigrateAsync();        
    }

    // you can optionally run thin before each test to ensure your DB is not affected by other tests
    public async Task Reset()
    {
      _pgsqlContainer.ResetDatabase(); 
      await MigrateAsync();
    }

    public async Task MigrateAsync()
    {
        var migrationRunner = new MigrationRunner(_pgsqlContainer.GetConnectionString());
        migrationRunner.MigrateAsync();

        await _pgsqlContainer.ExecScriptAsync(
		"""
        CREATE USER testuser WITH LOGIN PASSWORD 'testuser';"
        GRANT approle TO testuser; --approle created in migrations
        """);
    }

    public async Task DisposeAsync()
    {
        if (_pgsqlContainer is not null) {
            await _pgsqlContainer.DisposeAsync();
        }
    }
}

[Collection(nameof(IntegrationTestCollection))]
public class BlogsRepositoryTests
{
    private readonly DatabaseTestFixture _fixture;

    public BlogsRepositoryTests(DatabaseTestFixture fixture) => _fixture = fixture;

    [Fact]
    public async Task InsertAndReadBlog()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseNpgsql(_fixture.AppConnectionString)
            .Options;

        await using var context = new AppDbContext(options);
        var repo = new BlogsRepository(context);

        var blog = new Blog {
            Title = "Hello",
        };

        var created = await repo.AddAsync(blog);

        // it might be good idea to use separate dbcontext for read to emulate real scenarios
        var read = await repo.GetByIdAsync(created.Id);

        read.Should().NotBeNull();
        read.Title.Should().Be("Hello");
    }
}

[Fact]
public async Task ApplicationCannotDropBlogsTable()
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseNpgsql(fixture.AppConnectionString)
        .Options;

    await using var ctx = new AppDbContext(options);

    // Act
    Func<Task> act = async () => await ctx.Database.ExecuteSqlRawAsync("DROP TABLE public.blogs;");

    // Assert: Postgres should reject the operation (permission denied)
    await Assert.ThrowsAsync<PostgresException>(act);
}

When this is useful

At first, this test might not look very useful, but it guarantees that:

1. Migrations has run successfully including any future migrations
2. The queries generated by EF or any custom SQL is valid
3. Makes sure that the approle created in Migration has all required privileges
4. Additionally, it makes it future proof to any changes in repository, breaking changes in ORM, allow you to replace ORM or even database.

Consider scenario, where you need to add Tags property.
You add public string[] Tags {get; set;} = [] property to Blogs class and write migration

ALTER TABLE blogs
ADD COLUMN tags jsonb DEFAULT '[]'::jsonb;Q

The tests are now red because EF Core don't know to store string[] property and you need to configure it:

modelBuilder.Entity<Blog>(b =>
        {
            b.Property(x => x.Tags)
             .HasColumnType("jsonb")
             .HasConversion(
                 v => JsonSerializer.Serialize(v, JsonSerializerOptions.Default),
                 v => JsonSerializer.Deserialize<string[]>(v, JsonSerializerOptions.Default) ?? Array.Empty<string>()
             );
        });

Consider scenario, where you need to write complex SQL query. Using TestContainer you can test the query just like any other code, making it's refactoring much easier: prepare data, execute query, assert on result.

Consider another scenario, where you write and optimize complex Linq query. It's good idea to examine the generated SQL. It's very simple if you can invoke the query from Test Explorer. You don't need to assert the generated SQL, you just want an easy way to manually examine it and assert it's behavior.

You can write generic test that validates all DbContext entities. You can make sure that your DBContext does not produce any warnings.

Benefits recap

• Confidence when deploying schema changes.
• Early detection of migration and mapping errors.
• Faster optimization cycles because you can reproduce and measure real SQL.
• Reduced coupling to a specific ORM or DB by validating behavior.
• Easier upgrades for EF Core and database engines.
• Improved maintainability by enabling refactoring and easier upgrades for for ORM and database engines.

Final word

I once had to upgrade an EF6 codebase to EF Core — it was painful. In another project the upgrade wasn’t feasible at all and we had to rewrite entire app from scratch.

If that sounds familiar, take this to heart: today’s shiny frameworks will eventually age. Design your application and data access for longevity so schema and library changes become manageable instead of catastrophic.