SQL and the data layer
Reading the database is how you confirm what the UI claimed, seed a scenario, and find the row that made the test flake. Joins, transactions, isolation levels. Absent from most QA roadmaps and present in almost every real testing job.
Most QA roadmaps skip SQL entirely, and almost every real testing job needs it within the first month. Reading the database directly is how you confirm what the UI claimed instead of trusting a screen that might just be caching stale data. It is how you seed a scenario that would take twenty manual clicks to set up through the app, and it is how you find the one row that made a test flake when the UI gives you no clue at all. This post covers joins, transactions, and isolation levels, the parts of SQL that come up constantly in testing and rarely get taught anywhere else.
Reading the database beats trusting the screen
A UI can show a cached value, a value from a stale API response, or a value the frontend computed incorrectly from correct backend data. Querying the database directly answers a simpler question: what is actually stored right now. This distinction matters most when a bug report says "the order total is wrong" and the real question is whether the total in the database is wrong or the total the page rendered is wrong. Those are different bugs with different owners.
SELECT o.id, o.total_cents, SUM(li.price_cents * li.quantity) AS computed_total
FROM orders o
JOIN line_items li ON li.order_id = o.id
WHERE o.id = 4821
GROUP BY o.id, o.total_cents;If total_cents matches computed_total, the bug is in the frontend's rendering of a correct value. If they diverge, the bug is upstream in whatever wrote the order row, and no amount of frontend debugging will find it.
Joins are how you connect what the UI already connects visually
A join combines rows from two or more tables based on a related column, which is exactly what a page does visually when it shows an order alongside its customer's name and shipping address, data that likely lives in three separate tables. An INNER JOIN returns only rows with a match on both sides; a LEFT JOIN keeps every row from the first table even when there is no match on the second, which is the join to reach for when checking whether something is missing rather than wrong.
Transactions and why a flaky test sometimes sees half a write
A transaction groups multiple writes so they either all succeed or all roll back together, and this matters for testing because a test reading mid-transaction can see an inconsistent, temporary state that no user ever actually experiences. The isolation level controls exactly what a concurrent reader can see: READ COMMITTED, the common default, means a reader never sees uncommitted changes, but it can still see different results if it queries the same row twice while another transaction commits in between.
A QA engineer on a payments team once debugged a test that intermittently reported a customer's balance as briefly negative during a transfer between two accounts. It turned out the application correctly wrapped the debit and credit in one transaction. A health-check job outside the test queried the balance table on a fixed interval and occasionally caught a state that only existed for a few milliseconds inside the transaction boundary of an unrelated concurrent test run. The fix was moving the health check to run against a read replica with REPEATABLE READ isolation, which meant it always saw a consistent snapshot instead of a mid-transaction sliver.
Using SQL to seed and to diagnose
Seeding through the UI is slow and brittle if the goal is a specific data shape, ten orders in exactly the right states, a user account past a trial expiry date, a product with zero remaining stock. Inserting rows directly, or running a seed script that does the same, sets up the scenario in milliseconds and skips whatever manual clicking the UI would otherwise require.
Four SQL habits pay off constantly in day-to-day testing:
- Query the database directly whenever a bug report is ambiguous about whether the UI or the backend is wrong.
- Use
LEFT JOINwhen checking for missing related data, sinceINNER JOINsilently excludes exactly the rows you are looking for. - Seed edge-case data with direct inserts instead of clicking through the UI for every test scenario.
- Understand your test database's isolation level before trusting a query that runs concurrently with other writes, especially inside a Docker Compose database service shared across parallel test runs.
FAQ
Questions people ask
Do I need to know SQL to be an effective tester, or is the UI enough?
The UI is enough until the first ambiguous bug report or the first scenario that would take twenty manual clicks to set up. SQL closes both gaps fast, and it comes up constantly in real testing jobs.
What is the difference between `INNER JOIN` and `LEFT JOIN` for testing purposes?
INNER JOIN only returns rows with a match in both tables, silently dropping anything missing a relation. LEFT JOIN keeps every row from the first table, which is what you want when hunting for missing data.
Why would a test see inconsistent data if the application uses transactions correctly?
A reader querying at the wrong isolation level, or querying between two reads inside its own logic, can still catch a transient state from a concurrent write. Match the isolation level to what the test actually needs to see.
Is seeding data with direct SQL inserts safe for a shared test database?
Yes, as long as the inserted data is clearly scoped to the test run, usually with a unique identifier or a cleanup step afterward, so it does not leak into or affect other tests running in parallel.