Database & SQL Testing Fundamentals

1 The Hook — Why This Matters

A junior tester in Wellington was testing a government funding application form. She filled it out perfectly, hit Submit, saw a green success message. The stakeholder later reported that applications were being stored with NULL phone numbers even though the form required them.

When she opened a database client and ran SELECT * FROM applications WHERE phone_number IS NULL, she found 47 of them. The form validated the field on the frontend and showed a success message, but the backend was inserting empty values because a trigger silently replaced null phone numbers with empty strings, then the database constraint checked too late.

She had tested the form's UI. She had not tested what actually arrived in the database. That is the difference between testing the interface and testing the data flow. Every junior tester must learn to verify the database directly.

2 The Rule — The One-Sentence Version

Never assume the database saved the data correctly just because the UI showed a success message. Query the database directly to verify.

The UI is the frontend's story about what happened. The database is the truth. Backend processing, triggers, stored procedures, and data transformations can silently corrupt, drop, or misformat data after the form is submitted. Your job is to verify the truth.

3 The Analogy — Think Of It Like...

Analogy

A courier delivery confirmation.

The courier app says "Package delivered at 2:47pm" and you see a green checkmark. That is the UI. But did the driver actually leave the package at the right address? Did they drop it in the rain? Did they deliver to a neighbor by mistake? The green checkmark is the system's claim. The actual package in your hands (or missing from your hands) is the truth. Database testing is opening your door and checking whether the package is actually there and undamaged.

4 Watch Me Do It — Step by Step

Here is a complete workflow: testing a form submission from UI to database verification. Follow this pattern every time you need to confirm data was saved.

Identify what you're testing What data should the database store? For a "Create Customer" form, you need to verify: first name, last name, email, phone, registration date, status flag. Know the table name (customers) and the key columns.
Submit the form with known test data Use distinctive values you can search for: TestCustomer_20260424_001 instead of "John Smith". This makes the database query unambiguous. Submit the form and note the timestamp.
Connect to the database Use DBeaver (free GUI) or command-line tools like mysql or psql. Ask your team for the connection string. Format: host:port, database name, username, password.
Write and run a SELECT query Search for your test data:
SELECT * FROM customers WHERE first_name = 'TestCustomer_20260424_001';
This returns the row(s) the database actually stored.

Compare UI claim vs database truth What did the form say you submitted? What does the database actually contain? Do they match?

Field	UI showed	Database contains	Match?
first_name	TestCustomer_20260424_001	TestCustomer_20260424_001	Yes
email	test@resync.nz	test@resync.nz	Yes
phone	021 555 0123	(empty / NULL)	No - Bug!
created_at	2026-04-24	2026-04-24 14:47:32.000	Yes

Check for data type mismatches Did you submit a phone number as text but the database stored it as a number? Did dates get formatted differently (DD/MM/YYYY vs YYYY-MM-DD)? These are common silent bugs.
Verify constraints and relationships If the form created a customer and an associated order, query both tables:
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.first_name = 'TestCustomer_20260424_001';

Three practical SQL queries every junior tester needs:

Query 1: Verify a specific record exists with exact values SELECT * FROM orders WHERE order_id = 12345 AND customer_email = 'john@example.com' AND total_amount = 99.95;

Query 2: Count records created after a specific time SELECT COUNT(*) FROM orders WHERE created_at > '2026-04-24 14:00:00' AND status = 'pending';

Query 3: Find NULL or empty values (data integrity check) SELECT * FROM customers WHERE phone_number IS NULL OR phone_number = '' OR email IS NULL;

Query 4: Check inventory decreased when an order was placed

SELECT p.product_id, p.name, p.stock_quantity FROM products p WHERE p.product_id IN (SELECT product_id FROM order_items WHERE order_id = 12345);

Common data issues to check for:

Issue	How to detect	Example query
NULL when required	Check for IS NULL	`WHERE email IS NULL`
Wrong date format	Visual inspection	Submitted: 24/04/2026, Stored: 2026-04-24 (both valid)
Timezone mismatch	Compare created_at vs submitted time	UI shows 2pm, DB shows 4pm (likely +2 hours UTC offset issue)
Trimming/padding	Use LENGTH() function	`SELECT LENGTH(phone_number) FROM customers`
Case changes	Check UPPER/LOWER	Submitted: "John", Stored: "JOHN" (case conversion)
Truncation	Compare lengths	Submitted: 50 chars, Stored: 20 chars (field too small)

Pro tip: Save your SQL queries in a text file as you write them. Label them by test case: query_form_submit_01.sql, query_order_inventory_02.sql. This creates a repeatable test suite. Next time you test a form update, you can re-run the same queries to check for regressions.

5 When to Use It / When NOT to Use It

✅ Use DB testing when...

Testing form submissions or data creation
Verifying inventory or stock changes
Checking order processing and fulfillment
Testing payment or financial transactions
Verifying data cleanup (deletes, archiving)
Testing multi-step processes that touch multiple tables
Confirming constraints are enforced (unique, not null, foreign keys)

❌ Don't use DB testing when...

Testing read-only display pages (no data mutation)
Only testing visual UI rendering
The database is in production and you cannot write test data
You do not have database access credentials
The system uses caching; DB query might not reflect real-time state

6 Common Mistakes — Don't Do This

🚫 Trusting the UI success message alone

I used to think: If the UI says "Data saved successfully," the data is in the database.
Actually: The success message is what the frontend code was told by the backend. It does not mean data was actually persisted. Triggers, stored procedures, constraints, and async operations can fail silently after the success message appears. Always query the database.

🚫 Using generic test data

I used to think: I'll just use "John Smith" and "test@example.com" like everyone else does.
Actually: Generic data makes your queries ambiguous. When you search for "John Smith," you get 47 matches from other testers. Use unique, timestamped test data: TestJun_20260424_1530_001. This makes your database queries unambiguous and repeatable.

🚫 Ignoring data type and format changes

I used to think: As long as the data appears in the database, the test passes.
Actually: Silent data transformations are common: phone numbers losing spaces, dates reformatting, NULL becoming empty string, booleans becoming integers. These cause downstream bugs in reports, exports, and integrations. Always compare the exact value you submitted with what the database contains.

🚫 Not checking related tables

I used to think: If the order record is in the database, the test is complete.
Actually: Orders have line items. Line items update inventory. Inventory changes trigger fulfillment workflows. Test the entire data chain, not just the primary record. Use JOINs to verify relationships.

7 Now You Try — Event Registration Form

🎯 Interactive Exercise

Scenario: You've submitted an event registration form with these values:

First name: TestAttendee_20260424
Email: testattendee@resync.nz
Phone: 021 555 0199
Event ID: 5

Your task: Write a SQL query to verify this registration was stored correctly in the event_registrations table. Then write a second query to check that the attendance count for Event 5 increased by 1.

Suggested queries:

SELECT * FROM event_registrations

WHERE first_name = 'TestAttendee_20260424'

AND email = 'testattendee@resync.nz'

AND phone = '021 555 0199'

AND event_id = 5;

What this does: It finds your exact registration record. If it returns 0 rows, the data never saved. If it returns your record, check each field to ensure there are no formatting changes (spaces removed, NULL values, etc.)

SELECT COUNT(*) as attendance_count FROM event_registrations

WHERE event_id = 5;

What this does: Counts total registrations for Event 5. Compare this before and after your submission to verify it increased by exactly 1. If the count is wrong, check for duplicate insertions, trigger failures, or cascade deletes.

8 Self-Check — Can You Actually Do This?

Click each question to reveal the answer. If you got all three, you're ready to test database operations.

Q1. Why should you use unique, timestamped test data instead of generic names like "John Smith"?

Because generic data produces ambiguous database queries. When you search for "John Smith," you might get 47 matches from other testers or previous test runs. Unique, timestamped data like TestJun_20260424_1530 makes your queries unambiguous and ensures you're verifying YOUR submission, not someone else's. It also makes queries repeatable and auditable.

Q2. You submit a phone number "021 555 0123" via a form. The database query shows it was stored as "0215550123" (spaces removed). Is this a bug?

Not necessarily—but it is a data transformation that must be intentional. Check: Did the specification say the database should store phone numbers without spaces? If yes, this is correct behavior. If no, this is a silent bug: the form accepted a formatted number but the database stored it differently, which could break downstream systems expecting the same format. The key is that you detected the mismatch by comparing UI to DB.

Q3. A form submission shows a green "Success" message, but your database query finds 0 rows. What should you do?

This is a critical bug. The frontend lied. Start debugging: (1) Check if you queried the right table and with the right WHERE clause. (2) Check if there are multiple databases or environments and you're querying the wrong one. (3) Check if there is an insert trigger that rolls back on error (check trigger logs). (4) Check if the backend logged an error (check server logs). (5) Log a bug: "Form shows success but data not persisted." This is a data loss bug that affects all users, not just your test.

9 Interview Prep — Know These Cold

These are questions Kiwi employers ask junior testers about database testing.

Q1. Why must you test the database directly instead of trusting the UI?

The UI is the frontend's claim about what happened. The database is the truth. Backend processing, triggers, stored procedures, and data transformations can happen after the UI shows a success message. Silent data loss, format changes, and constraint violations are common. Querying the database is the only way to verify the actual state.

Q2. What tools do you use to access a database?

For a GUI, I use DBeaver (free, supports MySQL, PostgreSQL, SQL Server, etc.). For command-line, I use mysql, psql, or sqlplus depending on the database. For quick checks, I sometimes use the browser's Network tab to inspect API responses. The key is knowing the connection string (host, port, database, username, password).

Q3. What's the difference between NULL and an empty string? Why does it matter?

NULL means the value is not set (missing). An empty string "" is a zero-length string (something was set, but it's blank). They behave differently in queries: WHERE phone IS NULL finds NULLs; WHERE phone = '' finds empty strings. In business logic, NULL often means "not provided" while empty string means "provided but blank." A form field that allows empty strings but rejects NULL could silently accept incomplete data. Always check which one you have.

Q4. You discover that a date was stored in the database as 2026-04-24 but the form showed 24/04/2026. Is this a bug?

Not necessarily a bug—but it is a transformation. Check the specification: does the system require ISO format (YYYY-MM-DD) in the database and display format (DD/MM/YYYY) on the UI? If yes, this is correct. If no, this is a silent transformation that could break downstream systems expecting the original format. The key is detecting and documenting the difference so stakeholders can verify if it's intentional.

10 Link to Reference

Want the full SQL reference, DBeaver setup guide, and advanced database testing patterns?

→ Database Testing — Full Reference

Includes SQL fundamentals, DBeaver configuration, connection strings, and a complete matrix of test cases for common database scenarios.

11 Next Step

You now know how to verify data at the database layer. The next skill is understanding how data flows through multiple systems and checking for consistency across them.

Practice: Customer registration → Next topic: API Testing Basics → All junior learning