Why Database Testing Matters

I have seen QA teams test the UI for months and still miss critical bugs. Why? Because some bugs only exist in the database. The UI is like a pretty mask. It can lie to you. The database does not lie.

Real Bugs That UI Testing Misses

Bug 1: Data Corruption — Silent Killer

A salary field is defined as DECIMAL(8,2). Maximum value: 999999.99. An employee earns 1,50,000 per month. The UI accepts the input. The backend silently truncates it to 99,999.99. The employee gets paid less. Nobody notices for three months.

-- Find employees whose salary looks suspiciously capped
SELECT employee_id, full_name, salary
FROM employees
WHERE salary = 999999.99;

-- Better: Check if the column can actually hold the expected range
DESCRIBE employees;

Bug 2: Wrong Calculations in the Database

A shopping cart shows total = Rs 1,500. But the database stored Rs 1,485 because a trigger applied a 1% loyalty discount that the UI did not know about. Now the invoice says one thing, the payment processes another amount. Nightmare.

-- Verify order total matches sum of items
SELECT o.order_id,
       o.total_amount AS stored_total,
       SUM(oi.quantity * oi.unit_price) AS calculated_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount != SUM(oi.quantity * oi.unit_price);

Bug 3: Orphan Records — Data Without a Parent

A user deletes their account from the UI. The users table row is gone. But their orders, reviews, and payment records still exist — pointing to a user_id that no longer exists. These orphan records break reports, analytics, and sometimes the entire application.

-- Find orders that belong to deleted users
SELECT o.order_id, o.user_id, o.order_date, o.total_amount
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;

Bug 4: Duplicate Records

User clicks the Submit button twice. The UI shows one order. The database has two identical orders. The customer gets charged twice. This happens when there is no UNIQUE constraint or idempotency check.

-- Find duplicate orders (same user, same amount, within 5 seconds)
SELECT user_id, total_amount, COUNT(*) AS duplicate_count
FROM orders
WHERE order_date > NOW() - INTERVAL '1 day'
GROUP BY user_id, total_amount
HAVING COUNT(*) > 1;