Lesson 1 of 85 min read
Why testers need SQL
The screen can lie; the database is where the truth is saved. SQL lets you check what the app really stored.
Key takeaways
- 1Verify that a sign-up, order or payment was actually saved with the right values.
- 2Find or create test data quickly instead of clicking through many screens.
- 3Most QA job descriptions list SQL, and interviews often include a short query task.
Real example
The order page shows 'Order placed'. You run:
SELECT id, status, total
FROM orders
WHERE customer_email = 'asha@test.dev'
ORDER BY created_at DESC;
The row shows status = 'pending' and total = 0. That's a real bug the UI hid.
Go deeper
Expert notes beyond the basics
- 01
Why the UI and the database can disagree
A page can show 'Payment successful' from a cached response or a client-side assumption, while the actual row in the orders or payments table never got written, or got written with the wrong status. This happens when an API call times out after the UI already redirected, or when a background job that updates status fails silently. A tester who only trusts the screen will sign off a broken flow. Running SELECT status, total, updated_at FROM payments WHERE order_id = 501; after every UI action closes that gap and should become a habit for any checkout, refund or registration flow you test.
- 02
Read-only access and why it matters
Most QA teams are given a read-only database user or a reporting replica, not full write access to production or even to shared test environments. This protects real data from accidental UPDATE or DELETE mistakes while still letting you verify results. If you only have SELECT permission, you cannot create test data directly in the table; instead you create it through the application or API, then verify it with SQL. Always confirm which environment you are connected to (check the host name or a harmless SELECT CURRENT_DATABASE();) before running anything, because the same query against production instead of staging can have very different consequences.
- 03
SQL in API testing, not just UI testing
When you test a REST or GraphQL API, the response JSON can look correct while the underlying write is incomplete, for example a 201 Created returned for an order but the order_items rows never inserted because of a transaction rollback. Pair every write-call test with a quick SQL check of the affected tables. This is especially valuable for negative tests: if an API call is supposed to fail validation, confirm with SQL that no row was created at all, not just that the response was an error code.
- 04
Typical tools you will actually use
You rarely open a raw terminal to run SQL at work. Most testers use a GUI client such as DBeaver, TablePlus, pgAdmin (PostgreSQL) or MySQL Workbench, or query straight from a Postman/Insomnia pre-request script against a test API, or use a BI tool like Metabase for one-off checks. Command-line clients (psql, mysql) are useful for quick checks over SSH or in CI pipelines. Whatever the tool, the SQL itself barely changes; learning standard SQL once lets you move between MySQL, PostgreSQL and SQL Server test environments with only small syntax adjustments.
Common mistakes to avoid
- - Trusting a UI success message without checking that the record was actually saved with the right values.
- - Running SQL against the wrong environment because the connection was never double-checked.
- - Assuming a non-zero HTTP status code from an API means nothing was written to the database.
- - Not knowing whether your database account is read-only, and attempting writes that could corrupt shared test data.
QA data checks after a UI action
Run one of these right after any create/update/delete action in the app.
| Check | SQL | What a problem looks like |
|---|---|---|
| Record was created | SELECT * FROM orders WHERE id = 501; | No row returned, or row has placeholder/default values |
| Correct status saved | SELECT status FROM orders WHERE id = 501; | Status is 'pending' when UI showed 'confirmed' |
| Right environment | SELECT current_database(); | Query points at production instead of staging |
| No accidental duplicate | SELECT COUNT(*) FROM orders WHERE customer_id = 1 AND created_at > NOW() - INTERVAL '1 minute'; | Count is 2 after a single click |
Try it yourself
A tester submits a sign-up form and the UI shows 'Welcome, Asha!'. Write the SQL check you would run to confirm the account was created correctly, and list two things in the result that would indicate a bug even though the UI looked fine.
Write your answer on paper or in a sheet first, then compare.
Interview questions
How to answer like a confident QA engineer
- Q1
How do you use SQL as a tester?
Model answer
To confirm that actions in the UI or API were saved correctly, to find and prepare test data, and to check things like duplicates, missing values and wrong totals. I mostly use read-only SELECT queries on test environments.
- Q2
What is the difference between DELETE and TRUNCATE?
Model answer
DELETE removes rows one at a time, can use a WHERE clause, fires triggers, and can be rolled back inside a transaction. TRUNCATE removes all rows at once, cannot use WHERE, usually cannot be rolled back (or only within the same transaction in PostgreSQL), and resets auto-increment counters. As a tester I use DELETE with WHERE to clean up specific test rows, and avoid TRUNCATE on shared tables.
- Q3
What is the difference between a database and a schema?
Model answer
A database is the overall container of data on a server. A schema is a logical grouping of tables, views and other objects inside a database, often used to separate modules or teams, for example a 'billing' schema and an 'inventory' schema in the same database. MySQL treats 'database' and 'schema' as the same thing, while PostgreSQL and SQL Server treat schema as a namespace inside a database.
- Q4
Why would a tester prefer a replica or staging database over production for queries?
Model answer
Running ad-hoc or exploratory SELECTs on production can add load to a system serving real users and risks accidentally running a destructive query against real customer data. A replica or staging database lets testers verify behaviour freely, and any accidental heavy query does not affect real customers.
Knowledge check
+5 points
Why does a tester check the database after a UI action?