Skip to content
End To End Tester
Beyond Functionalpractical

Database Testing

Testing migrations, constraints, queries and transactions — the layer everything else depends on, and the one most suites replace with a mock.

2 min read · updated 19 September 2026

The database is the layer every other layer depends on, and it is the layer most test suites replace with a mock. That trade is understandable and almost always wrong: a mocked repository cannot tell you that the column is too narrow, that the constraint fires, or that the query is correct.

#What to test

#Migrations

The highest-value database test, and the rarest.

csharp
[Fact]
public async Task Migration_012_backfills_currency_without_losing_rows()
{
    await using var db = await PostgresContainer.StartAsync();

    // Apply everything up to the migration under test.
    await Migrations.ApplyUpTo(db.ConnectionString, "011");

    // Seed data that looks like production, including the awkward cases.
    await db.ExecuteAsync("""
        INSERT INTO orders (id, reference, amount_cents) VALUES
          (gen_random_uuid(), 'REF-1', 1999),
          (gen_random_uuid(), 'REF-2', 0),
          (gen_random_uuid(), 'REF-3', NULL)
    """);

    await Migrations.ApplyUpTo(db.ConnectionString, "012");

    var rows = await db.QueryAsync<(string Reference, string Currency)>(
        "SELECT reference, currency FROM orders ORDER BY reference");

    rows.Count().ShouldBe(3);                       // nothing lost
    rows.ShouldAllBe(r => r.Currency == "GBP");     // backfilled, including the NULL row
}

A migration that works on an empty schema and fails on real data is the classic production incident. Seeding awkward rows — nulls, zeros, duplicates, the longest string anyone ever entered — before applying it is what catches it.

Where a migration is meant to be reversible, test the rollback too. Most are never tested and many do not work.

#Constraints

Constraints are the last line of defence. Prove they fire, and prove your code translates them.

java
@Test
void aDuplicateReferenceIsRejectedAsADomainError() {
    repository.save(new Order("REF-1", 4_000));

    assertThatThrownBy(() -> repository.save(new Order("REF-1", 5_000)))
        .isInstanceOf(DuplicateReferenceException.class);   // translated, not a raw SQLException
}

@Test
void anOrderCannotHaveANegativeAmount() {
    assertThatThrownBy(() -> jdbc.update(
        "INSERT INTO orders (id, reference, amount_cents) VALUES (?, ?, ?)",
        UUID.randomUUID(), "REF-2", -1))
        .isInstanceOf(DataIntegrityViolationException.class);
}

The second test bypasses the application entirely, on purpose: it proves the database itself would refuse bad data, which is what protects you from the script somebody runs at 3am.

#Types and precision

python
def test_money_round_trips_exactly(repository):
    # The value that reveals a DECIMAL(10,2) column or a float column.
    repository.save(Order(reference="REF-1", amount_cents=1_999_99))

    assert repository.find("REF-1").amount_cents == 1_999_99

def test_timestamps_keep_their_timezone(repository):
    placed = datetime(2026, 6, 15, 23, 30, tzinfo=timezone.utc)
    repository.save(Order(reference="REF-2", placed_at=placed))

    assert repository.find("REF-2").placed_at == placed   # fails on `timestamp` without tz

#Queries

Not that the ORM works — that your query returns the right rows.

typescript
it('orders-needing-review excludes cancelled and includes the boundary', async () => {
  await seed([
    anOrder({ reference: 'A', status: 'paid', amountCents: 100_000 }),   // at the threshold
    anOrder({ reference: 'B', status: 'paid', amountCents: 99_999 }),
    anOrder({ reference: 'C', status: 'cancelled', amountCents: 500_000 })
  ]);

  const results = await repository.needingReview({ thresholdCents: 100_000 });

  expect(results.map((o) => o.reference)).toEqual(['A']);
});

The boundary row is the point. A query with > instead of >= passes every test that does not include a value exactly on the threshold.

#Transactions and concurrency

csharp
[Fact]
public async Task Two_concurrent_reservations_cannot_both_take_the_last_item()
{
    await SeedStock("book-1", quantity: 1);

    var results = await Task.WhenAll(
        service.ReserveAsync("book-1", 1),
        service.ReserveAsync("book-1", 1));

    results.Count(r => r.Succeeded).ShouldBe(1);
    results.Count(r => !r.Succeeded).ShouldBe(1);
    (await StockFor("book-1")).ShouldBe(0);
}

This is the test that finds missing row locks and wrong isolation levels, and it is nearly impossible to write without a real database. It is also the kind of bug that produces oversold inventory and a very bad afternoon.

#Query performance

sql
-- Assert on the plan, not the wall-clock: timing on CI hardware is noise.
EXPLAIN (FORMAT JSON)
SELECT * FROM orders WHERE tenant_id = $1 AND status = 'paid' ORDER BY placed_at DESC LIMIT 20;
python
def test_the_orders_query_uses_the_index(connection):
    plan = connection.execute(
        "EXPLAIN (FORMAT JSON) SELECT * FROM orders "
        "WHERE tenant_id = %s AND status = 'paid' ORDER BY placed_at DESC LIMIT 20",
        ("t1",)
    ).fetchone()[0]

    assert "Seq Scan" not in json.dumps(plan)

A test that fails when a sequential scan appears is one of the cheapest production-performance safeguards available — and it catches the case where someone drops an index or adds a column to a WHERE clause.

#Isolation

Three approaches, all valid, covered in test data management:

  1. Transaction rollback — fastest and most complete; breaks if the code under test manages its own transactions.
  2. Unique scope per test — a tenant, prefix or schema. Works with any transaction strategy and is parallel-safe.
  3. Fresh database per worker — perfect isolation at the cost of container startup per worker. With Postgres, CREATE DATABASE … TEMPLATE makes a clean copy in milliseconds.

#Use the real engine

java
new PostgreSQLContainer<>("postgres:16-alpine")     // the version production runs

SQLite standing in for Postgres is a fake with a drift problem, and the drift is concentrated in exactly the areas where the bugs are: type coercion, collation and case sensitivity, constraint enforcement, isolation levels, JSON operators, window functions, and every vendor-specific function you have used.

Testcontainers starts the real engine in about a second and reuses it across a class. There is no longer a good reason to substitute.

Common questions

Can I use SQLite in tests instead of Postgres?
You can, and it will lie to you about types, collations, constraints, isolation levels, JSON behaviour and every non-standard function you use. Testcontainers made running the real engine cheap enough that the substitution is no longer worth its risk.
Should I test my migrations?
Yes, and it is the highest-value database test there is. Apply the migration to a database populated with realistic data, check the result, and — where the migration is meant to be reversible — apply the rollback and check that too. Most production migration incidents would have been caught by this.
How do I keep database tests isolated?
A transaction rolled back after each test is the simplest and fastest. Where the code manages its own transactions, give each test its own schema, tenant or data prefix instead.

Runnable samples for this page

last test results ↗

Working tests, not fragments — they run in CI on every push to 8exgh/endtoendtester-samples.

Was this page useful?