Database Testing Guide: Validate Data, Queries, and Jobs

An order API returned 201 Created, and the confirmation page showed £129.98. The nightly finance report showed £64.99. Investigation found two order-item rows, one payment row, and a report join that counted the payment once per item before applying a compensating division. It worked for two equal items and failed for mixed baskets. Every screen looked plausible; the persisted relationships exposed the defect.

Database testing connects an action to durable state and then to every consumer of that state. This guide uses PostgreSQL examples. The business patterns are portable, but date, constraint, and query-plan syntax should be adapted for another engine.

Map the transaction before writing queries

Begin with the business event, not the table list. For “paid order,” identify the API request, transaction boundary, tables written, generated keys, status transitions, outbox event, audit record, and report read model. Ask which component owns each field and which operations may be asynchronous.

A small state map prevents shallow assertions:

Document whether timestamps come from the application or database, whether money is stored in minor units or decimals, and whether deletes are physical or logical. A query can be syntactically correct while asserting the wrong model.

Create identifiable, isolated test data

Use unique markers such as DBT-0710-0042 in an allowed reference field, then retain the generated primary keys. Avoid searching by a common customer name or by “latest row.” Parallel tests make both unreliable.

Prefer API or service setup when the behavior under test includes application validation. Direct SQL setup is useful for rare states, but it can bypass defaults, events, encryption, and audit logic. If direct setup is approved, wrap it in a transaction and show the cleanup path.

BEGIN;

INSERT INTO customers (external_ref, email, status)
VALUES ('DBT-0710-0042', 'dbt+0042@example.test', 'ACTIVE')
RETURNING customer_id;

-- Run only test-environment actions that use the returned customer_id.
ROLLBACK;

The sql block is PostgreSQL. RETURNING should yield exactly one identifier, and ROLLBACK should leave no customer row. It is unsuitable when the application action uses a separate connection that must see the uncommitted data. In that case, create committed data and clean it through a supported fixture service.

Verify schema constraints as executable rules

Application validation is not a substitute for database integrity. Test NOT NULL, UNIQUE, foreign keys, check constraints, defaults, lengths, and numeric precision at the boundary that owns them. First inspect the actual schema so the expected behavior is based on deployed code.

For a payment table with amount_minor > 0 and a unique idempotency_key, try one violation at a time. Expected evidence is a rejected statement with the named constraint and no partial row. Then verify a valid insert uses the expected default status.

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public'
  AND table_name = 'payments'
ORDER BY constraint_name;

This sql query should list the deployed table constraints. Compare names and types with the migration under test. Do not make tests depend on automatically generated constraint names unless naming is part of the team convention.

Test Unicode, maximum lengths, decimal boundaries, null versus empty text, and timestamps around a date boundary. Database collation and time zone configuration can change results that appeared safe in an application unit test.

Assert CRUD effects and transactional atomicity

For each create, update, and delete path, check affected rows, unchanged rows, audit fields, related records, and external events. A success response with the correct primary row can still leave stale children or duplicate events.

Atomicity tests need a controlled failure inside the transaction. For example, submit an order whose second item violates a known inventory rule after the first item is staged. The expected result should be no order, no item, no payment, and no outbox event, unless the design explicitly records a failed attempt.

SELECT
  (SELECT count(*) FROM orders WHERE client_ref = 'DBT-ATOMIC-07') AS orders,
  (SELECT count(*) FROM order_items oi
     JOIN orders o USING (order_id)
    WHERE o.client_ref = 'DBT-ATOMIC-07') AS items,
  (SELECT count(*) FROM outbox_events
    WHERE aggregate_ref = 'DBT-ATOMIC-07') AS events;

The sql query should return one row containing 0, 0, 0 for an all-or-nothing design. If the event count is one, capture its type and payload before deciding whether it is a leak or a deliberate failure event.

Reconcile calculations at the correct grain

Aggregation defects often come from joining tables at different grains. Establish the grain of each source before calculating. An order is one row, items are many rows per order, and payment attempts can also be many rows per order. Joining all three directly can multiply values.

Reconcile each measure independently in common table expressions:

WITH item_totals AS (
  SELECT order_id, sum(quantity * unit_price_minor) AS items_minor
  FROM order_items
  GROUP BY order_id
), captured AS (
  SELECT order_id, sum(amount_minor) AS paid_minor
  FROM payments
  WHERE status = 'CAPTURED'
  GROUP BY order_id
)
SELECT o.order_id, o.total_minor, i.items_minor, p.paid_minor
FROM orders o
JOIN item_totals i USING (order_id)
JOIN captured p USING (order_id)
WHERE o.client_ref = 'DBT-0710-ORDER-17'
  AND (o.total_minor <> i.items_minor OR o.total_minor <> p.paid_minor);

This PostgreSQL sql query should return zero rows. Any row is a mismatch with three values ready for diagnosis. If taxes, shipping, refunds, or partial capture exist, model them explicitly rather than weakening the assertion.

Test jobs, concurrency, and retries

Scheduled jobs introduce watermark, locking, retry, and idempotency risks. Seed records just before, exactly at, and just after the selection boundary. Record the database time used by the job. Verify that a rerun processes missed work without applying completed work twice.

Concurrency cases should create a real race, not two sequential clicks. Launch two requests with the same business key, hold one transaction if the test harness permits it, and verify the final cardinality and user responses. A unique constraint may protect data while one client still receives an unhandled server error.

Observe locks and timing carefully. A slow query is not automatically a database defect; it may be waiting on an intentional lock. Capture query text, parameters, execution plan for a representative dataset, lock waits, and row counts. Never infer production performance from a tiny empty test database.

Include role boundaries in this layer. Connect as the same database role used by the application and prove that it can execute required routines but cannot read administrative or cross-tenant data. Then connect with a reporting role and confirm it cannot mutate operational tables. A test performed only as a database owner can hide missing grants, unsafe grants, and row-level policy defects. Record the effective role with the query evidence, especially after migrations that create new tables or sequences.

Treat migrations as reversible data changes

Test a migration on a production-shaped, masked snapshot or synthetic volume. Record the starting schema version and row counts, apply the migration once, restart it if documented as restartable, and validate transformed values. Then run application smoke tests against the migrated database.

Review criteria include null handling, default backfill, precision changes, index creation, lock duration, old application compatibility during rollout, and rollback or forward-fix procedure. Compare checksums only for columns expected to remain byte-equivalent. A normalized phone number should be checked against its transformation rule, not its old checksum.

After migration, search for unmapped categories and truncation:

SELECT legacy_status, count(*)
FROM customer_accounts
WHERE status_v2 IS NULL
GROUP BY legacy_status
ORDER BY count(*) DESC;

The sql query should return zero rows after a complete status backfill. Returned groups reveal exactly which legacy values the mapping missed.

Produce evidence that survives review

A database defect report needs environment, schema version, test marker, action timestamp, query text, parameters, actual rows, expected invariant, and cleanup state. Export only the columns needed to prove the issue, with secrets and personal data removed. Screenshots are less useful than copyable results, but a screenshot can preserve client settings or an execution plan visualization.

Before release, review whether all critical invariants passed, failures were reproduced from a clean state, migrations were tested at realistic shape, asynchronous jobs reached a terminal state, and direct test writes were cleaned. Database confidence comes from explaining the full state transition, not from running a large folder of unrelated SELECT statements.