Use SQLite Intentionally for Lightweight Tests and Understand Provider-Behavior Differences

Learning outcomes

1. SQLite is a real relational engine—but still a different engine

SQLite gives a lightweight test a real SQL parser, constraints, transactions, indexes and provider translation. That makes it far more useful than EF InMemory for relational behavior. But Chapter 20 already established that SQLite has dynamic typing, no schemas/sequences, different DDL, different date/decimal semantics, different collations/functions, and a single-file locking model. Use it for the contracts it actually implements.

2. In-memory SQLite exists only while at least one connection stays open

The connection string Data Source=:memory: creates an in-memory database associated with that connection. If a fixture opens a connection, creates/migrates the database, disposes it, and then constructs a new connection for the test, the schema is gone.

public sealed class SqliteServiceHubFixture : IAsyncDisposable{    private readonly SqliteConnection _connection =        new("Data Source=:memory:");    public async Task InitializeAsync()    {        await _connection.OpenAsync();        await using var db = CreateContext("tenant-a");        await db.Database.MigrateAsync();        await SeedAsync(db);    }    public ServiceHubContext CreateContext(string tenantId)    {        var options = new DbContextOptionsBuilder<ServiceHubContext>()            .UseSqlite(_connection)            .EnableDetailedErrors()            .Options;        var db = new ServiceHubContext(options);        db.AssignTenant(tenantId);        return db;    }    public async ValueTask DisposeAsync() => await _connection.DisposeAsync();}

This uses the Chapter 22 options-only constructor and overwrites tenant state through AssignTenant for every test context. The fixture therefore exercises the same production model and tenant filter instead of maintaining a simplified test-only model.

3. Deliberate failure: close the connection and the database disappears

await using (var setup = new SqliteConnection("Data Source=:memory:")){    await setup.OpenAsync();    // migrate/create schema} // database is destroyed hereawait using var test = new SqliteConnection("Data Source=:memory:");await test.OpenAsync();// SELECT -> "no such table"

The repair is either one long-lived fixture connection or a shared-memory URI with deliberate keeper-connection semantics. The simple course path uses one open connection per fixture.

4. Use migrations when the migration lifecycle is part of the contract

EnsureCreated is valid for prototypes and some isolated tests, but it bypasses the normal migrations history/snapshot path. A migration smoke test should call MigrateAsync against a fresh disposable database and then assert schema/data invariants.

await using var db = fixture.CreateContext("tenant-a");await db.Database.MigrateAsync();var applied = await db.Database.GetAppliedMigrationsAsync();Assert.Contains(applied, m => m.Contains("AddTenantLifecycleColumns"));

If the test only needs model-created tables and explicitly does not test migrations, EnsureCreated can be documented as a test convenience—but do not mix it into a normal migration database and call that migration verification.

5. In-memory versus temporary-file SQLite

6. Foreign-key and tenant-defense verification

await using var db = fixture.CreateContext("tenant-a");// Seed a tenant-a WorkOrder and a tenant-b Technician.// Then deliberately create the cross-tenant relationship in a low-level test path.var ex = await Assert.ThrowsAsync<DbUpdateException>(    () => db.SaveChangesAsync());Assert.Contains("FOREIGN KEY", ex.InnerException?.Message ?? ex.Message,    StringComparison.OrdinalIgnoreCase);

This is strong evidence for the SQLite constraint created by the Chapter 22 mapping. It still does not prove SQL Server row-level security, PostgreSQL collations, or MySQL generated-column behavior.

7. Maintain a provider-fidelity checklist beside SQLite tests

8. Mandatory lab: one in-memory fixture, one file fixture

1. Create an in-memory SQLite fixture that keeps one connection open and applies the current migrations.
2. Verify tenant filtering, a unique constraint, a tenant-aware FK, and one transaction rollback.
3. Close the connection and prove a new :memory: connection no longer has the schema.
4. Create a uniquely named temporary file database, open two contexts/connections, and run the Chapter 13 two-writer concurrency test.
5. Record the native sqlite_version(), EF/SQLite provider 10.0.11, OS, and connection mode.
6. Delete the temporary file in cleanup.

9. Production judgment and bridge

SQLite is the course’s mandatory free/local relational test path because it is fast and real, not because it is identical to every server engine. Keep a production-provider test suite for behaviors where engine/provider differences matter. Lesson 4 builds that suite using disposable production-like database instances and explicit isolation.

Authoritative references

• SQLite in-memory testing — EF Core guidance for keeping the SQLite connection open
• Microsoft.Data.Sqlite in-memory databases — connection lifetime and shared-cache considerations
• SQLite provider limitations — type, schema, sequence, migration, and query limitations
• EF Core migrations — migration lifecycle and applied history
• EF Core concurrency — two-context optimistic concurrency behavior
• Choosing a testing strategy — why SQLite is still a database fake for other production providers