Lesson 1 of 85 min read
What database testing is and why the UI is not enough
A green screen that says 'Order placed' proves only that the UI showed a message. Database testing checks what was actually stored: the right rows, in the right tables, with the right values, and nothing extra. Many serious production bugs — double charges, lost orders, wrong balances, leaked personal data — are invisible in the UI and obvious in the database. This lesson explains what database testing covers and when a tester should look underneath the screen.
Key takeaways
- 1Database testing verifies stored data (values, relationships, history) and database behaviour (constraints, transactions, triggers, procedures, performance, access).
- 2Structural testing checks the schema itself: tables, columns, data types, keys, indexes and constraints match the design. Functional testing checks that application actions write, update and delete the correct data.
- 3Non-functional database testing covers performance (slow queries, missing indexes), security (permissions, SQL injection, sensitive data masking) and recovery (backups, rollbacks).
- 4Use it when the UI hides the truth: payments, balances, inventory counts, audit logs, soft deletes, background jobs, imports and data migrations.
- 5Testers usually get read-only access to a QA or staging database. Never run UPDATE or DELETE on a shared environment without agreement — and never on production.
- 6Tools you will meet: DBeaver, pgAdmin, MySQL Workbench, SQL Server Management Studio, and code-level checks in automation using JDBC, psycopg/pymysql or a Node database client.
The bug the UI could not show
Scenario: user clicks 'Pay' twice quickly on a slow network. UI result: 'Payment successful' (looks fine) Database check:
SELECT order_id, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
WHERE order_id = 'ORD-10421'
GROUP BY order_id;
order_id | payments | total
ORD-10421 | 2 | 3998.00 <- customer charged twice
Bug report title: Double click on Pay creates two payment rows and charges twice (expected one row per order; no unique constraint on payments.order_id)
Go deeper
Expert notes beyond the basics
- 01
Where database testing fits in the test pyramid
Database checks are not a separate phase; they sit inside your existing tests. In manual testing you add a 'DB verification' step to critical test cases. In API tests you call the endpoint and then query the table. In UI automation you use the database to create test data quickly (set up a user with 3 orders in milliseconds) and to verify results the screen doesn't show. Keep UI assertions for what users see and database assertions for what the business depends on.
- 02
Which flows deserve database checks
You cannot verify every screen in SQL, so pick by risk: anything involving money (payments, refunds, wallets, invoices), inventory and bookings, user identity and permissions, audit or compliance data, background jobs and imports, and any feature that writes to more than one table. A good rule: if a wrong value would need a data fix script in production, it deserves a database check in testing.
- 03
Relational vs NoSQL in one paragraph
This course uses relational databases (PostgreSQL, MySQL, SQL Server, Oracle) because they are the most common in QA jobs. The ideas carry over to NoSQL stores like MongoDB: you still verify that documents were created correctly, required fields exist, duplicates aren't created and related data stays consistent — but there are fewer database-enforced constraints, so the application must do more validation and you must test it harder.
- 04
Safe working habits
Use a read-only account for exploring. Use unique, recognisable test data. Wrap experimental changes in BEGIN … ROLLBACK. Add a LIMIT to large SELECTs on shared databases. Never copy production personal data into tickets or chat; mask it. These habits keep you trusted with database access — which many teams only give to careful testers.
Common mistakes to avoid
- - Trusting the success message without checking what was stored.
- - Running UPDATE or DELETE on a shared QA database without a transaction or agreement.
- - Using generic test data like 'test@test.com' and then checking someone else's row.
- - Only verifying visible fields and ignoring status, timestamps and audit columns.
- - Pasting real customer data from production into bug reports.
Try it yourself
Pick a 'Place order' feature in any e-commerce app. List at least six database checks you would perform after placing one order with two different products.
Write your answer on paper or in a sheet first, then compare.
Interview questions
How to answer like a confident QA engineer
- Q1
What is database testing and why is it needed if we already test the UI?
Model answer
Database testing verifies the data an application stores and how the database behaves — schema, constraints, CRUD results, transactions, triggers, procedures, performance and access. It's needed because the UI only shows what the developer chose to display. A screen can say 'saved' while the row has a wrong status, a missing foreign key, a duplicate, or an unmasked card number. In my testing I verify critical flows such as order placement or refunds with SQL: I check the expected rows exist, values match the input, related tables are updated together and nothing extra was created. That catches double inserts, lost updates and data corruption that UI testing alone would miss.
- Q2
What are the types of database testing?
Model answer
Structural testing checks the schema: tables, columns, data types, keys, indexes, constraints, triggers and procedures. Functional testing checks that application operations create, read, update and delete the correct data and follow business rules. Non-functional testing covers performance, load, security, access control, and backup and recovery.
- Q3
Which tools have you used for database testing?
Model answer
For manual checks I use a client such as DBeaver or pgAdmin to run SQL against the QA database. In automation I connect from the test framework — JDBC in Java, psycopg or pymysql in Python, or a Node client in Playwright tests — to set up data and assert stored results. For load I use the performance tool the team already uses, like JMeter or k6, while watching database metrics.
- Q4
Do testers need write access to the database?
Model answer
Usually read access is enough and safer. Write access on QA environments helps for fast data setup and negative testing, but it should be used inside transactions or with agreed data, never on production, and ideally through scripts or APIs so data stays consistent.
Knowledge check
+5 points
Which bug is MOST likely to be found only by database testing?