Test Database Strategies — Isolation, Seeding and Cleanup

Test database isolation strategy determines whether tests interfere with each other and how fast the test suite runs. The gold standard for integration tests is: each test starts with a known database state and cannot be affected by other tests. There are three practical approaches for the BlogApp: SQLite in-memory (fastest, some limitations), LocalDB with reset (full compatibility), and Respawn (most robust for shared databases).

Database Isolation Strategies #

// ── APPROACH 1: SQLite In-Memory (fastest, some limitations) ──────────────
// Pro: millisecond database creation, no cleanup needed
// Con: no SQL Server-specific features (sequences, rowversion, RCSI)
services.AddDbContext<AppDbContext>(opts =>
    opts.UseSqlite("Data Source=:memory:;Cache=Shared"));
// One connection must stay open to keep in-memory DB alive:
// var keepAlive = new SqliteConnection("Data Source=:memory:;Cache=Shared");
// keepAlive.Open();  // keep alive for the test session

// ── APPROACH 2: LocalDB with unique name per test class ──────────────────
// Pro: full SQL Server compatibility
// Con: ~500ms to create database; must delete after
services.AddDbContext<AppDbContext>(opts =>
    opts.UseSqlServer($@"Server=(localdb)\mssqllocaldb;Database={_dbName};
                         Trusted_Connection=true;Encrypt=false"));
// In DisposeAsync:
// await db.Database.EnsureDeletedAsync();

// ── APPROACH 3: Respawn (recommended for shared test database) ────────────
// Install: dotnet add package Respawn
// Pro: very fast reset (~10ms vs 500ms to recreate), shared database,
//      allows running against a persistent LocalDB or Azure SQL
// Con: needs a connection to reset, slightly more setup

public class BlogAppFactory : WebApplicationFactory<Program>, IAsyncLifetime
{
    private Respawner? _respawner;
    private IDbConnection? _conn;

    public async Task InitializeAsync()
    {
        // Create schema once
        using var scope = Services.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        await db.Database.EnsureCreatedAsync();

        // Configure Respawn
        _conn = new SqlConnection(
            db.Database.GetConnectionString()!);
        _conn.Open();

        _respawner = await Respawner.CreateAsync(_conn, new RespawnerOptions
        {
            TablesToIgnore = ["__EFMigrationsHistory"],
            DbAdapter      = DbAdapter.SqlServer,
        });
    }

    // Call this between test runs to reset data
    public async Task ResetDatabaseAsync()
    {
        await _respawner!.ResetAsync(_conn!);
        await SeedBaseTestDataAsync();  // re-seed the minimum required data
    }

    public new async Task DisposeAsync()
    {
        _conn?.Dispose();
        await base.DisposeAsync();
    }
}

// ── Using Respawn in test class ───────────────────────────────────────────
public class PostsIntegrationTests : IClassFixture<BlogAppFactory>, IAsyncLifetime
{
    private readonly BlogAppFactory _factory;

    public PostsIntegrationTests(BlogAppFactory factory) => _factory = factory;

    // Reset database before each test class run (IAsyncLifetime on test class)
    public async Task InitializeAsync() => await _factory.ResetDatabaseAsync();
    public Task DisposeAsync() => Task.CompletedTask;

    [Fact]
    public async Task Test_StartsWith_KnownCleanState() { /* ... */ }
}

// ── DatabaseSeeder — reusable test data setup ─────────────────────────────
public static class DatabaseSeeder
{
    public static async Task SeedPostsAsync(AppDbContext db, int count = 5)
    {
        var posts = Enumerable.Range(1, count).Select(i => new Post
        {
            Title       = $"Test Post {i}",
            Slug        = $"test-post-{i}",
            Body        = "This is a test post body with enough content.",
            AuthorId    = "admin-test-user-id",
            Status      = i % 2 == 0 ? "draft" : "published",
            IsPublished = i % 2 != 0,
            PublishedAt = i % 2 != 0 ? DateTime.UtcNow.AddDays(-i) : null,
        }).ToList();

        db.Posts.AddRange(posts);
        await db.SaveChangesAsync();
    }
}

Strategy Comparison #

Common Mistakes #

Mistake 1 — Forgetting TablesToIgnore for migration history (Respawn deletes it) #

❌ Wrong — Respawn without TablesToIgnore = ["__EFMigrationsHistory"]; migration history deleted; next EF migration thinks schema is empty.

✅ Correct — always exclude __EFMigrationsHistory and static lookup tables from Respawn’s reset.

Mistake 2 — Using EnsureCreated instead of Migrate in test setup (incomplete schema) #

❌ Wrong — EnsureCreated() creates schema from current model, ignoring any migration-specific SQL (views, custom indexes).

✅ Correct — db.Database.MigrateAsync() applies all migrations including custom SQL; tests run against production-identical schema.