Test SQL Reads and Data Changes

Test SELECT results as a contract

Check selected fields, row values, duplicates, NULL behavior, filters, joins, and stable ordering. Add an explicit order when result sequence matters; SQL does not guarantee order without ORDER BY.

SELECT order_id, status, total
FROM orders
WHERE customer_id = 9101
ORDER BY order_id;
-- Assert exact expected rows:
-- (9201, 'paid', 10.00)
-- (9202, 'pending', 0.00)

Include a customer with no orders and verify the intended behavior of an outer join or empty aggregate.

Test inserts and updates

Assert returned keys and the persisted row. Also test duplicate keys, invalid values, repeated requests, and update predicates that should match zero rows.

INSERT INTO orders (order_id, customer_id, status, total)
VALUES (9204, 9101, 'pending', 20.00)
RETURNING order_id, status, total;

UPDATE orders
SET status = 'paid'
WHERE order_id = 9204 AND status = 'pending'
RETURNING order_id, status;
-- Assert one affected row and the expected final state.

Test deletes with exact affected rows

Before a destructive statement, validate the candidate set. In a test, assert exactly which rows were removed and which related rows remain or cascade, as specified by the schema.

DELETE FROM orders
WHERE order_id = 9204
RETURNING order_id;
-- Assert exactly one returned ID: 9204.
-- Query afterward to assert the row is absent.

Use parameterized application queries

Exercise the real data-access layer with bound parameters rather than string concatenation. Test values containing quotes, Unicode, wildcard characters, and SQL-looking input; verify they are treated as data and cannot widen the result set.

Keep a failing test's SQLSTATE and sanitized parameters in diagnostics, not secrets or unrestricted user data. Run the query against the actual supported engine.