Database Testing

When a product behaves incorrectly, the UI or API is not always the cause. Problems often lie in data: an incomplete record, an unsaved update, inconsistent statuses, a broken relationship, an incorrect migration, or prohibited duplicates. Database testing is therefore a distinct layer of product quality, rather than "something for the DBA."

A good tester need not administer databases, but should understand that data follows its own rules and can become invalid regardless of how polished the interface looks.

What is database testing?

Database testing checks whether the system correctly writes, stores, reads, updates, and deletes data.

It goes beyond manual SQL queries and includes:

→What was actually saved after a user action.
→Whether data state matches expectations.
→Whether duplicates appeared.
→Whether relationships were lost.
→Whether integrity was violated.
→Whether transactions behave correctly.
→Whether migrations damage existing data.

An API returning 200 does not mean the database is correct. Sometimes the UI and API look normal while invalid internal state accumulates and causes problems later.

Why this matters to QA

Data is the product's foundation. It supports:

→The interface.
→Reports.
→Integrations.
→Notifications.
→Analytics.
→Billing.
→Access control.
→Background processes.

When the database stores inconsistent state, the surrounding functionality starts failing.

For QA, database testing is especially useful in scenarios involving:

→Complex business transitions.
→Status sequences.
→Background jobs.
→Integrations.
→Financial operations.
→Bulk changes.
→Migrations and schema releases.
→Suspected race conditions or lost updates.

What is usually checked?

→Data written after create, update, and delete operations.
→Agreement between stored data and UI and API promises.
→Correct relationships between entities.
→Absence of unnecessary or orphaned records.
→Enforcement of uniqueness constraints.
→Correct default values.
→Correct soft or hard deletion.
→Integrity after errors and rollback.
→Data state after background processing.
→Correct schema and data migrations.

QA does not need to query the database manually every time. However, data access provides strong diagnostic evidence in complex scenarios.

Common failure points

→The UI shows a created order, but some related records are missing.
→A user is deleted, leaving orphaned dependent entities.
→The API shows one status while the database contains another.
→A retried operation creates duplicates.
→A report displays incorrect numbers because of invalid data.
→A migration adds a column but leaves older records incompatible.
→A transaction fails, yet some changes are still saved.
→A concurrent update overwrites another user's changes.

These defects are hard to see on a single screen, but become clearer when QA can examine actual data state.

What matters to QA

→Check both the onscreen result and its source.
→Examine relationships.
→Consider transactional behavior.
→Remember background processing.

For a critical scenario, verify that data was actually created and is in the correct state.

Constraints and integrity

Constraints are one of the most useful database testing topics.

→Primary keys.
→Foreign keys.
→Unique constraints.
→Not-null constraints.
→Check constraints.
→Transaction isolation.
→Cascading update and deletion rules.

QA needs to understand what these protections do without becoming immersed in administration. Missing necessary constraints make inconsistent product state easier to create. When constraints exist, understand how they appear through APIs and business scenarios.

For example, if email must be unique, check both the form's error message and whether the database actually prevents two active users with the same key.

A practical example

Consider creating an order.

After success, the user sees an order in the interface. QA needs more evidence. Several things may be expected in the database:

→The main order record exists.
→Order items exist.
→The status is saved.
→The owner is recorded.
→Amounts are stored.
→Related entities are created or updated.
→No temporary drafts remain.
→Audit or event history is recorded correctly.

Now suppose one step fails. What should happen?

→No order should exist at all.
→Alternatively, an order may remain as draft/failed.
→Or some data may be retained for later recovery.

Database testing is especially important here. It checks whether the resulting state follows business rules, beyond simply whether an object exists.

Key takeaways for QA

→Database testing assesses data-state quality and involves more than SQL queries.
→UI and API behavior may look correct while underlying data is invalid.
→Relationships, constraints, transactions, and error consequences are particularly important.
→For critical scenarios, verify actual data state as well as the screen.
→QA need not be a DBA, but must recognize data as a separate layer of risk and quality.