Database Testing Foundations and Strategy

What does database testing prove?

Database tests validate the contract between stored data, SQL, schema rules, application code, and operational procedures. Use PostgreSQL-style examples in this course; adapt types, isolation behavior, and tooling to the database engine and version under test.

• Schema: migrations create the intended tables, keys, indexes, and constraints.
• Data: valid writes persist, invalid writes are rejected, and relationships stay consistent.
• Queries: filters, joins, aggregates, and ordering return the expected rows.
• Application: code handles database results, errors, transactions, and retries correctly.
• Operations: backup restoration, performance limits, access controls, and deployment procedures are tested.

Choose tests at the right level

1. Unit: fast tests for query-building or domain logic using a controlled substitute when appropriate.
2. Database integration: execute real SQL and migrations against an isolated instance of the target engine.
3. End-to-end: exercise a real application workflow through its normal interfaces.
4. Operational: verify restore, failover, load, access, and deployment behavior using controlled environments.

A mock cannot prove that a real engine parses a query, enforces a constraint, or applies a migration correctly. Keep the important integration checks against the actual supported database.

Write an observable test

Each test should state its setup, action, expected result, and cleanup. Assert business outcomes, not incidental row ordering or internal implementation details.

-- Setup a known customer and order in a disposable test database.
INSERT INTO customers (customer_id, name)
VALUES (901, 'Test Customer');

INSERT INTO orders (order_id, customer_id, status, total)
VALUES (9901, 901, 'paid', 25.00);

-- Exercise the same query shape used by the application.
SELECT customer_id, COUNT(*) AS paid_orders, SUM(total) AS paid_total
FROM orders
WHERE customer_id = 901 AND status = 'paid'
GROUP BY customer_id;
-- Assert: customer_id=901, paid_orders=1, paid_total=25.00

Risk-first test planning

Prioritize money movement, permissions, data loss, tenant isolation, migrations, concurrency, and recovery. Define acceptance criteria, supported engine/version, test data classification, environment owner, cleanup method, and required evidence before implementation.