How do you validate database state after an automated test runs?

A robust automated test has three DB checkpoints: before, during/after action, and teardown.

Pattern 1 — snapshot before, assert after:

-- Capture count before
SELECT COUNT(*) AS before_count FROM orders WHERE customer_id = 99;

-- (test action: submit a new order via UI or API)

-- Assert count increased by exactly 1
SELECT COUNT(*) AS after_count FROM orders WHERE customer_id = 99;
-- Diff: after_count - before_count should = 1

Pattern 2 — assert the specific row exists with correct values:

SELECT order_id, status, total_amount, customer_id
FROM   orders
WHERE  customer_id  = 99
  AND  status       = 'pending'
  AND  total_amount = 149.99
  AND  created_at   > NOW() - INTERVAL '1 minute';
-- Expect exactly 1 row

Pattern 3 — assert related rows were created (cascade effects):

-- After placing an order, verify the audit log entry was written
SELECT event_type, entity_id
FROM   audit_log
WHERE  entity_type = 'order'
  AND  entity_id   = :new_order_id
  AND  event_type  = 'created';

In test frameworks, these queries typically run in an @AfterEach / after() hook using a DB helper class, and they throw an assertion error if the expected row count or values don't match.