Español

Don't Trust In-Memory Database Tests

Your in-memory database tests are lying to you. An in-memory fake never translates your queries into the SQL dialect your production database speaks — it evaluates them as plain in-process operations. So a query can pass every test on your machine and still fail, or silently return different rows, the first time it hits the real engine.

Why green tests break in production

An ORM query is really two programs: the query you wrote, and the SQL your provider translates it into. An in-memory fake runs only the first one. Everything that lives in the translation layer goes untested:

• Collation and comparison rules. SQL Server compares strings case-insensitively by default; an in-memory collection (and SQLite) compares case-sensitively. A lookup that finds "Alice" in tests misses "alice" in production — or the reverse.
• Provider-specific functions. A call like SQL Server's EF.Functions.DateDiffDay translates fine on the real provider and simply fails on a fake.
• Raw SQL. In-memory fakes can't execute it at all, so any hand-written query is a blind spot.
• Transactions. The EF Core in-memory provider ignores them — a rollback test passes without ever rolling anything back.
• Migrations and schema. A fake has no schema to migrate, so constraint, index, and column-type mistakes surface only at deploy time.

Microsoft's own EF Core docs are blunt about it: using the in-memory provider as a test fake is "highly discouraged," and it's kept only for legacy applications.

The fix: test the risky queries against a real engine

"Real database tests are slow and painful" was true in 2015. Today the setup cost is one container:

// Testcontainers: a real PostgreSQL, per test run, auto-cleaned
var pg = new PostgreSqlBuilder().Build();
await pg.StartAsync();
var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseNpgsql(pg.GetConnectionString())
    .Options;

Testcontainers (available for .NET, Java, Go, Node, Python, and more) boots the same engine you run in production inside Docker, hands your tests a connection string, and tears it down afterwards. The EF Core team's own suite runs 30,000+ tests against real SQL Server in a few minutes, on every commit — a local database with a reasonable dataset is fast, because everything stays on one machine.

Isolation is the real design work, not speed. The standard recipe: create and seed the database once per test run (a shared fixture), wrap each data-modifying test in a transaction you never commit, and give tests that must commit their own database plus a cleanup step.

Rules of thumb

Power-user notes

• If you must have a double, stub above the ORM. A repository layer that returns IEnumerable lets you stub query outputs instead of re-executing query operators in memory — the only double that doesn't re-test the translation layer.
• SQLite in-memory is the least-bad fake, not a real test. It's still a different dialect with different collation and function support; queries that pass on it can fail on your production engine.
• Reset fast between committing tests. Deleting and reseeding via the ORM is slow; a raw DELETE FROM per table, or a dedicated reset library like Respawn, keeps real-database suites quick.
• Match versions. Test against the same major version you deploy — a translation bug fixed in one engine release can still bite the one you actually run.

Keep in-memory (or better, stubbed repositories) for speed on pure logic — but prove every risky query, migration, and transaction against the real engine before it ships.

Resources

• Choosing a testing strategy — EF Core docs — the comparison table of in-memory vs. SQLite vs. real database, and why in-memory is "highly discouraged."
• Testing against your production database system — EF Core docs — fixtures, transaction rollback isolation, and cleanup patterns.
• Testcontainers — Getting started — real services in Docker containers for tests, with per-language modules.
• Avoid In-Memory Databases for Tests — Jimmy Bogard — the false-confidence argument, from the author of Respawn and AutoMapper.
• SQLite in-memory databases — how the :memory: mode actually works, if you use SQLite as a fake.