Skip to main content

Common Mistakes in Database Testing

Prerequisites: You should already understand every module in Sections 1–5 of this path. Leads to: After this, you'll be ready for the Database Testing Capstone.

Every module in this path named mistakes specific to its own concern. This module steps back and asks which mistakes recur across concerns, visible only once you've seen the whole path. These aren't new rules — they're the handful of underlying habits that, left unchecked, undermine almost any concern this path taught.

Pattern 1: Trusting the Interface Instead of Verifying the Data Directly​

A team considers a feature verified once the UI or API reports success, without ever querying the actual stored data.

How this leads to an escaped defect: What is Database Testing?'s own opening example — a fund-transfer feature that displayed a correct confirmation while a duplicate Transactions row silently double-debited a customer's account, invisible to every UI and API check.

Prevention: for any feature writing to shared or financial state, run a direct query against the affected rows as a standard part of test design — not an optional extra step reserved for when something already looks wrong.

Pattern 2: Verifying the Intended Row Changed Without Confirming Nothing Else Did​

A team tests an Update or Delete by checking that the target row is now correct, and stops there.

How this leads to an escaped defect: CRUD Validation's address-update example — an Update feature correctly changed the intended customer's row while silently corrupting a different customer's data too, a scope bug invisible to any test that only checked the row it meant to change.

Prevention: treat "no unintended row also changed" as its own explicit test case for every Update and Delete, verified with a direct query scoped to exclude the intended target.

Pattern 3: Trusting a Documented Constraint Instead of Testing It Directly​

A team assumes a constraint is enforced because a design document or schema diagram says it should be.

How this leads to an escaped defect: Constraints, Keys, and Relationships's email-uniqueness example — a UNIQUE constraint assumed to exist because the design document said so was never actually applied, discovered only after a bulk-import tool bypassed the UI and created two customers sharing an email address.

Prevention: test every documented constraint with a direct database write attempting to violate it, bypassing the UI entirely — a UI passing tells you nothing about whether the database itself enforces the rule.

Pattern 4: Testing Only Clean, Uninterrupted, Single-Operation Runs​

A team's test suite exercises every feature under ideal conditions — one operation at a time, never interrupted, never overlapping with another.

How this leads to an escaped defect: Transactions, Locks, and Concurrency's joint-account example — a withdrawal feature that passed every single-session test allowed two simultaneous withdrawals to both succeed against the same balance, a lost-update defect structurally invisible to any test that never ran two operations against the same data at once.

Prevention: for any feature touching shared or contested data, deliberately design at least one interrupted or concurrent test case — this defect class doesn't appear under any other condition.

Pattern 5: Testing a Trigger or Audit Trail Through Only One Write Path​

A team verifies a trigger fires correctly through the primary, most obvious flow, and assumes that coverage extends to every other way the same event can occur.

How this leads to an escaped defect: Stored Procedures, Views, and Triggers's KYC audit-trigger example — a compliance-critical trigger, verified only through the standard UI flow, silently failed to fire on a bulk batch job's different write pattern, producing months of untracked, unaudited compliance-relevant changes.

Prevention: map every distinct code path that can produce a trigger's triggering event — UI, batch job, admin tool, migration script — and test each independently, not just the one that's easiest to reach.

Pattern 6: Testing Performance and Security Only at Convenient, Small Scale​

A team's test accounts and datasets are small and tidy, and security checks stop at whatever the UI's own validation shows.

How this leads to an escaped defect: Database Performance Testing's transaction-history example — an N+1 query pattern invisible at a 3-record test scale caused a fifteen-second load time for a real customer with years of history, undetected until realistic data volume finally exposed it.

Prevention: test performance-sensitive features at realistic, not minimal, data volume, and verify security-relevant claims (privilege scope, injection resistance, data-at-rest protection) with a direct database-level check, not just observed UI behavior.

Every pattern above has the same underlying shape: a shortcut that feels reasonable in the specific moment it's taken — trusting a green UI checkmark because querying the data directly takes an extra minute, testing only the row you changed because checking every other row feels excessive, assuming a constraint holds because someone documented that it should. Recognizing the temptation itself as a signal worth pausing on, not just knowing the list of patterns, is what actually prevents them from recurring.

Key Takeaways​

  • Six recurring patterns cut across this path's individual modules: trusting the interface over direct data verification, checking only the intended row, trusting documented constraints without direct verification, testing only clean single-operation runs, testing a trigger through only one path, and testing performance/security only at convenient scale.
  • Each pattern is a shortcut that feels reasonable in the specific moment it's taken — recognizing the moment of temptation is the actual skill, more than memorizing the list.
  • Every pattern traces back to a real, worked defect example from earlier in this path — these are patterns that have already produced real, specific consequences throughout this path's own teaching examples.
  • Prevention in every case is a specific, learnable habit, not a vague call for more thoroughness.

What You Just Learned​

  • Six mistake patterns that recur across this path's individual concerns, not within any single one
  • How each pattern, left unchecked, produced a real, specific defect in this path's own worked examples
  • The specific prevention habit for each pattern
  • Why recognizing the moment of temptation to take a shortcut matters more than memorizing a list of mistakes

Next: Database Testing Capstone

Interview Questions​

Q1: What's a common mistake you've seen (or made) in database testing, and how would you prevent it?

What to look for: A specific, real pattern (not a generic "not testing enough") with a concrete prevention habit attached — ideally one of this module's six, explained in the candidate's own words, showing genuine understanding rather than a memorized list.

Q2: Why might a feature that passes every UI and API test still have a real, serious data-layer defect?

What to look for: A candidate who directly names Pattern 1 — that an interface only reports what it was built to report, and a duplicated, corrupted, or inconsistent underlying write can exist entirely undetected by any test that only checks the interface.

Glossary​

Escaped Defect: A defect that reached production despite testing having occurred, typically traceable to a specific gap in test design or execution rather than an absence of testing effort.

Scope Bug: A defect where an operation's condition matches more or fewer rows than intended, causing it to affect data it wasn't meant to touch.

Quick Revision​

Remember these five points:

✓ Verify data directly with a query — an interface passing tells you nothing about the underlying data's correctness.

✓ For every Update/Delete, confirm unrelated rows stayed untouched, not just that the intended row is correct.

✓ Test every documented constraint with a direct database write — don't trust documentation alone.

✓ Deliberately test interrupted and concurrent scenarios — this defect class doesn't appear under clean, single-operation conditions.

✓ Test triggers, performance, and security at realistic scale and across every write path, not just the most convenient one.