Deterministic Fixtures and Test Data

Prefer data with a purpose

Each fixture row should support a scenario: the expected result, a boundary, a rejection, or an interaction. Use explicit keys and stable values so expected outcomes do not depend on random ordering or another test's state.

INSERT INTO customers (customer_id, name, active)
VALUES
  (9101, 'Active Customer', true),
  (9102, 'Inactive Customer', false);

INSERT INTO orders (order_id, customer_id, status, total, created_at)
VALUES
  (9201, 9101, 'paid', 10.00, TIMESTAMPTZ '2026-01-01 00:00:00+00'),
  (9202, 9101, 'pending', 0.00, TIMESTAMPTZ '2026-01-02 00:00:00+00');

Include important boundaries

• No matching rows and exactly one match.
• Minimum, maximum, zero, and just-outside allowed values.
• NULL versus empty string where the schema permits both.
• Duplicate keys and missing foreign-key parents.
• Timestamp exactly at a boundary and immediately before/after it.
• Unicode, long text, and case behavior where relevant.
• Multiple tenants/users to test isolation of reads and writes.

Use a fixed clock or injected time for expiry and date-window tests. Avoid dependence on the machine's local timezone.

Control fixture relationships and cleanup

Insert parent rows before dependent rows and remove child data before parents when not using transaction rollback. Use a unique run ID if a shared test database must host concurrent jobs. Keep fixtures minimal; large production-like datasets belong in a separately controlled performance test.

BEGIN;
INSERT INTO customers (customer_id, name) VALUES (9103, 'Fixture');
INSERT INTO orders (order_id, customer_id, status, total)
VALUES (9203, 9103, 'paid', 15.00);
-- Exercise assertions, then ROLLBACK in this isolated test.
ROLLBACK;

Keep test data safe

Use synthetic records wherever possible. If masked data is approved, verify masking, access, retention, and export controls. Test logs, snapshots, failure artifacts, and CI caches also need data-handling controls. Never store credentials or personal information in fixture files.