Skip to main content
Software Testing – Complete Beginner to Advanced Guide
CHAPTER 14 Intermediate

Database Testing

Updated: May 16, 2026
30 min read

# CHAPTER 14

Database Testing

1. Introduction

The User Interface is a temporary illusion; it just paints pictures on a screen. The API is a transient messenger; it forgets data the moment the HTTP connection closes. The Database is the truth. If an application has a beautiful UI and a fast API, but quietly corrupts data when saving it to the database, the business will fail. Database Testing is the rigorous process of bypassing the application layer and interacting directly with the database engine to ensure data integrity, structural consistency, and correct execution of CRUD operations. In this chapter, we will master Database Testing, learning how to validate schemas, execute SQL verification queries, and test risky data migrations.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Define Database Testing and understand its critical importance in QA.
  • Validate Structural Data Integrity (Schemas, Constraints, Triggers).
  • Test the four fundamental CRUD operations.
  • Understand the mechanics and risks of Data Migration testing.
  • Utilize raw SQL queries to verify backend data matches frontend UI states.

3. What is Database Testing?

Database testing is a form of Backend / White-Box testing. QA engineers connect directly to the database (using tools like MySQL Workbench, DBeaver, or pgAdmin) to verify that the application logic accurately manipulated the underlying data structure.
  • The Core Rule: You cannot trust the UI. The UI might say "Order Saved Successfully," but if you look in the database, the orders table might be empty, or the total_price might be calculated incorrectly. You must verify the truth at the source.

4. Structural Integrity Validation

Before testing the data, QA must test the structure that holds the data.
  • Data Types & Lengths: If a column is defined as VARCHAR(50), the QA tester must try to save a 51-character string via the API to ensure the database correctly rejects it.
  • Foreign Key Constraints: If you try to delete a User who has active Orders, the database should block the deletion (or cascade it), rather than leaving "Orphaned" orders in the system.
  • Null / Not Null: Verifying that critical fields (like email) throw a database error if the application attempts to save a NULL value.

5. Testing CRUD Operations

CRUD stands for Create, Read, Update, Delete. QA verifies the exact SQL flow.
  1. 1. Create: Use the UI to create a user. Run SELECT * FROM users; to verify the row exists and the data matches exactly.
  1. 2. Read: Manually insert a row into the database using SQL. Reload the UI. Does the UI accurately display the data you just injected?
  1. 3. Update: Use the UI to change a user's name. Run a SELECT query to ensure the specific row was updated, and no other rows were accidentally altered.
  1. 4. Delete: Use the UI to delete the user. Run a SELECT query to verify the row was completely removed (or "Soft Deleted" by setting a flag).

6. Data Migration Testing

This is the most dangerous event in a database's life.
  • The Scenario: The company is upgrading from an old legacy database (System A) to a new modern database schema (System B). Millions of rows of data must be moved.
  • QA Testing: QA must write scripts to count the rows in System A, and verify the exact same number of rows made it to System B. They must verify that complex data transformations (e.g., converting "Firstname Lastname" into two separate columns) occurred flawlessly across all records.

7. Visual Learning: The UI-to-DB Verification Flow

txt
12345678 
[ 1. Action (UI) ] -> Tester clicks "Submit Order" for $50.00.
       |
[ 2. API Call ]    -> API returns 200 OK.
       |
[ 3. DB Verification ] -> Tester opens MySQL Workbench.
       |                  Runs: SELECT * FROM orders ORDER BY id DESC LIMIT 1;
       |                  Checks: Does 'total_amount' exactly equal 50.00?
       |                  Checks: Is 'status' set to 'PENDING'?

8. Best Practices

  • ACID Property Validation: For critical systems (like banking), QA must test transactions. If money is deducted from Account A, but the server crashes *before* it is added to Account B, the database must roll back the entire transaction. It cannot leave the system in a half-finished state.

9. Common Mistakes

  • Testing in Production: NEVER run raw SQL testing queries against a live production database. A simple typo in a DELETE or UPDATE statement (forgetting the WHERE clause) will instantly destroy the company's live data. Always test on isolated Staging or QA databases.

10. Mini Project: Database Verification

Scenario: The developer created a feature to "Deactivate User." Your Testing Workflow:
  1. 1. Log into the test database. Find your test user.
SELECT id, isactive FROM users WHERE email = 'test@qa.com'; (Result: isactive = 1).
  1. 2. Go to the application UI and click the "Deactivate" button for that user.
  1. 3. The UI says "Deactivated!"
  1. 4. Return to the database. Rerun the exact same SQL query.
  1. 5. Assertion: If isactive still equals 1, log a critical bug. The UI lied. If it equals 0, the test passes.

11. Practice Exercises

  1. 1. Define the acronym "CRUD" and explain how a QA engineer validates each letter using raw SQL queries.
  1. 2. What is a Foreign Key constraint, and why is it important for QA to test that they are working correctly?

12. MCQs with Answers

Question 1

A QA Engineer uses the UI to update a customer's address. The UI shows the success message. The QA Engineer then logs directly into the SQL database to verify that the customeraddress column actually contains the new data. What principle does this demonstrate?

Question 2

During a massive system upgrade, 5 million user records are moved from an old Oracle database to a new PostgreSQL database schema. The QA team runs complex queries to ensure every single record transferred without corruption. What type of testing is this?

13. Interview Questions

  • Q: Explain why a QA Engineer must have a working knowledge of SQL (SELECT, JOIN, WHERE). How does this skill elevate them from a "Manual Tester" to a "QA Engineer"?
  • Q: What is a "Soft Delete" in database architecture? How would you verify a "Soft Delete" using SQL compared to a "Hard Delete"?
  • Q: Explain ACID properties in the context of database testing. How do you test if a multi-step transaction rolls back correctly on failure?

14. FAQs

Q: Do I need to be a DBA (Database Administrator) to do database testing? A: No. You do not need to know how to cluster servers, manage backups, or tune hardware. You only need to know how to write basic to intermediate Data Manipulation Language (DML) queries (SELECT, INSERT, UPDATE, DELETE, JOIN) to verify business logic.

15. Summary

In Chapter 14, we bypassed the illusions of the User Interface and sought truth directly at the source. We learned that Database Testing requires technical QA skills to write SQL queries and validate the structural integrity of the application. We mapped the UI actions directly to their underlying CRUD database operations, ensuring that the data saved matches the data submitted. Finally, we recognized the high-stakes risk of Data Migration, emphasizing that protecting the persistence layer is the most critical QA responsibility.

16. Next Chapter Recommendation

We have tested every piece of the software manually and automatically. Now, we must orchestrate all of these tests to run automatically in the cloud. Proceed to Chapter 15: CI/CD and DevOps Testing.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Mobile Application Testing
Next Chapter CI/CD and DevOps Testing

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 5

🎥 Related Videos 1