Software Testing Database Testing

Every order, payment, and profile that a user creates ends up in a database. A screen can show a success message while the data underneath is wrong, missing, or duplicated. Database testing verifies that stored data stays accurate, consistent, and secure. Testers look behind the screen to confirm what really happened.

Why It Matters

• Wrong data leads to wrong invoices, reports, and decisions.
• Lost records damage customer trust.
• Slow queries make pages crawl.
• Weak access rules expose private information.

The Big Picture

  USER INTERFACE  --->  BUSINESS LOGIC  --->  DATABASE
   (what you see)       (rules and code)     (stored facts)

  Database testing verifies the right-hand side.

Key Areas

Data Integrity

Data integrity means stored data follows the rules. Primary keys stay unique, foreign keys point to existing records, and required fields never stay empty. A tester tries to insert a duplicate id or an order for a customer that does not exist and confirms that the database rejects it.

Data Accuracy

Values entered on screen must match values in tables. A tester enters a price of 49.99 and queries the table to confirm that the same number appears, with no rounding or truncation.

CRUD Operations

CRUD stands for Create, Read, Update, and Delete. Each screen action maps to one of them, and each needs a database check.

User Action	Database Operation	What to Verify
Register account	INSERT	A new row exists with correct values
View profile	SELECT	Screen data equals table data
Change address	UPDATE	Only the intended row and column change
Close account	DELETE	Row and related rows disappear or archive correctly

Transactions

A transaction groups several steps into one unit. The group must finish completely or not at all. A bank transfer subtracts from one account and adds to another. A failure between the two steps must restore the original balances.

  START TRANSACTION
    Step 1: Account A  -  500
    Step 2: Account B  +  500
  COMMIT  (both steps saved)     or     ROLLBACK  (both steps undone)

ACID Properties

• Atomicity: all steps succeed or none do.
• Consistency: data moves from one valid state to another.
• Isolation: simultaneous transactions do not disturb each other.
• Durability: saved data survives a crash.

Example: Online Order Test

1. Place an order for two items through the website.
2. Query the orders table and confirm one new row with the right customer id.
3. Query the order items table and confirm two rows linked to that order.
4. Check that the stock table reduced each item count by one.
5. Cancel the order and confirm that stock returns to the original numbers.

  SELECT order_id, customer_id, total
  FROM   orders
  WHERE  order_id = 5021;

Other Checks

Stored Procedures and Triggers

Testers run each stored procedure with valid, invalid, and boundary inputs. Triggers, which fire automatically after an insert or update, need checks that they run at the right moment and nowhere else.

Performance of Queries

Testers measure how long important queries take on large data sets. Missing indexes often cause slow results.

Security

Testers confirm that users see only their own records, that passwords sit in hashed form, and that the application rejects injected SQL text in input fields.

Migration Testing

Moving data from an old system to a new one demands row counts, totals, and sample record comparisons before and after the move.

Tools and Skills

• SQL knowledge for SELECT, JOIN, and aggregate queries
• Database clients such as MySQL Workbench, DBeaver, or pgAdmin
• Test data generators and masking tools
• Automation libraries that run queries from test scripts

Good Practices

• Test on a copy of the database, never on production.
• Back up the data before destructive tests.
• Mask personal data in copies.
• Compare results with a trusted source such as a requirement or reference report.

A tester who can read the tables sees the truth behind every screen and catches defects that surface testing never reveals.