Database Testing

What it is

Database testing verifies the persistence layer directly: that the schema matches expectations, constraints actually constrain, queries return correct results, and migrations upgrade and roll back without data loss.

It has three distinct layers that are often conflated:

1
schema      → tables, columns, types, indexes, constraints
2
data        → seed rows, transforms, referential integrity, business rules in SQL
3
migration   → forward upgrade, backward rollback, zero-downtime safety

A test suite should treat each layer on its own: a schema test proves structure, a data test proves correctness of the rows, and a migration test proves the transition between versions.

When to use it / when to avoid it

Use it for

Constraints and invariants (uniqueness, foreign keys, check rules, cascade behaviour)
Analytical transforms and models (dbt, stored procedures, views)
Migrations: forward, rollback, and behaviour on existing data
Query correctness for reports where a silent wrong answer is costly

Avoid it when

The concern is business logic already covered by a unit or integration test
You only need to verify an ORM mapping — a repository integration test is cheaper
You want to assert on vendor internals rather than observable data behaviour

Frameworks & tools

Tool	Ecosystem	Notes
Testcontainers	Multi (Java, Go, Node, Python, .NET)	Throwaway real database per run
dbt tests	SQL	`unique`, `not_null`, `relationships`, custom generic tests
pgTAP	PostgreSQL	Unit tests for schema, functions and RLS inside the DB
Flyway / Liquibase	Java / Multi	Versioned migrations with validation
Alembic	Python	Migration framework for SQLAlchemy
tSQLt	SQL Server	Unit testing framework for T-SQL
DbUnit / Respawn	.NET / Multi	Seed datasets, reset database state fast
Great Expectations	Python	Data-quality expectations across pipelines

Quick setup

1
npm install -D vitest testcontainers pg

1
import { PostgreSqlContainer } from '@testcontainers/postgresql';
2
const pg = await new PostgreSqlContainer('postgres:16').start();

1
pip install pytest testcontainers psycopg[binary] dbt-core

1
go get github.com/testcontainers/testcontainers-go
2
go get github.com/jackc/pgx/v5

1
mvn test

1
<dependency>
2
  <groupId>org.testcontainers</groupId>
3
  <artifactId>postgresql</artifactId>
4
  <version>1.20.1</version>
5
  <scope>test</scope>
6
</dependency>

1
dotnet add package Testcontainers.PostgreSql
2
dotnet add package Respawn

Example

1
it('enforces unique email', async () => {
2
  await db.query("insert into users(email) values ('a@x.com')");
3
  await expect(
4
    db.query("insert into users(email) values ('a@x.com')"),
5
  ).rejects.toThrow(/duplicate key/);
6
});

1
def test_foreign_key_is_enforced(db):
2
    with db.cursor() as cur:
3
        try:
4
            cur.execute("insert into orders(user_id) values (999999)")
5
        except Exception as exc:
6
            assert "foreign key" in str(exc).lower()
7
        else:
8
            raise AssertionError("FK not enforced")

1
models:
2
  - name: orders
3
    columns:
4
      - name: order_id
5
        tests: [unique, not_null]
6
      - name: user_id
7
        tests:
8
          - relationships:
9
              to: ref('users')
10
              field: id

1
func TestMigrationRoundTrip(t *testing.T) {
2
  up(t)   // apply migration 003
3
  assertColumn(t, "orders", "currency")
4
  down(t) // roll back to 002
5
  assertNoColumn(t, "orders", "currency")
6
}

1
@Test
2
void detectsMissingDependency() {
3
  assertThatThrownBy(() ->
4
    jdbcTemplate.update("insert into orders(user_id) values (999999)")
5
  ).hasMessageContaining("foreign key");
6
}

1
[Fact]
2
public async Task EnforcesUniqueEmail()
3
{
4
    await _conn.ExecuteAsync("insert into users(email) values ('a@x.com')");
5
    await Assert.ThrowsAnyAsync<Exception>(() =>
6
        _conn.ExecuteAsync("insert into users(email) values ('a@x.com')"));
7
    await _respawn.ResetAsync(_conn);
8
}

Anti-patterns & best practices

One database per run. Testcontainers or a throwaway schema beats a shared dev database.
Test migrations both ways. Every up needs a tested down; a rollback that fails is an outage.
Never mutate a shared fixture file blindly. Use builders or per-test seeds so the suite is order-independent.
Assert on observable data, not vendor internals. Query results and constraints are the contract, not page layout.
Cover the empty and boundary cases. Zero rows, nulls, maximum lengths and duplicate inserts catch real bugs.
Populate the suite with realistic volumes for query tests where performance or index usage matters.