SQL for Testers: Prove the API Actually Wrote to the DB

The idea, in one line

After the API call, query the database directly and confirm the record really landed. That check is called a database assertion, and it needs just a few SQL moves.

The SQL moves worth knowing

• JOIN — stitches rows from two tables on a matching key, like orders to the customer who placed them
• GROUP BY — collapses rows into buckets so COUNT, SUM and AVG can summarize each bucket, like orders per customer
• WHERE — filters rows before grouping; HAVING — filters the groups after
• subquery — a SELECT nested inside another, answering a question in stages (like "users who placed more orders than average")

See it work

Here's a reporting query that uses several of those moves together, plus the small pattern you'll drop into a test.

-- Orders per paying customer, busiest first
SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.name
HAVING COUNT(o.id) > 1
ORDER BY order_count DESC;

-- In a pytest test, confirm the API's write really landed:
-- cur.execute("SELECT title FROM posts WHERE id = ?", (new_id,))
-- row = cur.fetchone()
-- assert row is not None and row[0] == "qa"

Read the query top to bottom: join customers to their orders, keep only paid ones, count them per customer, keep customers with more than one, and sort by the count. The test snippet below it just looks up the id you created and checks the row exists.

Advanced — the testing pattern and staying safe

For testing, the shape is small: POST through the API, then run a parameterized SELECT for the id you just created and assert exactly one row came back with the values you expected.

Grounded in the SQL standard as documented in the PostgreSQL docs (SELECT, JOIN, GROUP BY)