This database testing tutorial is for beginners and manual testers who want to check what happens behind the screen. Database testing verifies that data is stored, updated and retrieved correctly, and that the database rules (keys, constraints, triggers, procedures) work as designed.
What Is Database Testing?
Database testing (also called backend testing) checks the data layer of an application. When a user places an order in the UI, a database tester confirms the order row exists with the right values, the stock reduced, and no orphan or duplicate rows were created. It differs from ETL testing, which checks data moving between systems; see ETL vs database testing.
Types of Database Testing
| Type | What you check |
|---|---|
| Structural (schema) testing | Tables, columns, data types, lengths, keys, indexes match the design |
| Functional testing | CRUD operations, stored procedures, triggers, views return correct results |
| Data integrity testing | Primary keys, foreign keys, unique and check constraints |
| Non-functional testing | Performance, load, security and access permissions |
How Database Testing Is Done (Step by Step)
- Read the requirement and the data model (ER diagram).
- Get read access to the test database and a SQL client such as DBeaver or SQL Developer.
- Write test cases for each operation: create, read, update, delete.
- Perform the action in the application or API.
- Run SQL to verify the database state.
- Test negative cases: invalid data should be rejected by constraints.
- Log defects with the query, expected and actual result.
Database Testing Test Case Examples
| ID | Test case | Expected result |
|---|---|---|
| DB-01 | Register a new user | One row in users with correct email, hashed password, status ACTIVE |
| DB-02 | Register with an existing email | Insert rejected by unique constraint; no new row |
| DB-03 | Place an order with 2 items | 1 order row, 2 order_line rows, stock reduced by quantities |
| DB-04 | Cancel an order | Status CANCELLED, stock restored, updated_at changed |
| DB-05 | Delete a customer with orders | Blocked by foreign key, or soft-deleted as per design |
| DB-06 | Enter text longer than column length | Rejected or truncated as per requirement |
| DB-07 | Run monthly sales stored procedure | Totals match a manual SUM query |
| DB-08 | Update address | Audit/trigger table records old and new values |
SQL Checks You Will Use
-- schema check SELECT column_name, data_type, character_maximum_length FROM information_schema.columns WHERE table_name = 'orders'; -- duplicates SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; -- orphan rows SELECT l.order_id FROM order_lines l LEFT JOIN orders o ON o.order_id = l.order_id WHERE o.order_id IS NULL;
Practise more with the SQL test questions for testers.
Database Testing Tools
- SQL clients: DBeaver (free), SQL Server Management Studio, Oracle SQL Developer, pgAdmin.
- Automation: Python with pytest and SQLAlchemy, or JDBC in Java test frameworks.
- Performance: JMeter can send JDBC requests for load testing.
From Database Testing to ETL Testing
Database testing is the natural first step into data testing. ETL testing uses the same SQL skills on bigger questions: is the data complete and correct after it moves into a data warehouse? ETL Testing: From Beginners to Advanced takes manual and database testers through ETL and data warehouse testing, data quality checks, Python and AI tools.
Frequently Asked Questions
What is database testing?
Checking that an application stores, updates and retrieves data correctly and that database rules such as keys, constraints, triggers and stored procedures work as designed.
How is database testing done?
Perform an action in the application, then run SQL to verify the database state, including negative tests where constraints should reject bad data.
Is database testing functional or non-functional?
Both. CRUD, procedures and integrity checks are functional; performance and security checks are non-functional.
Which tools are used for database testing?
SQL clients such as DBeaver or SSMS, Python or Java for automation and JMeter for database load testing.
Asim Noaman Lodhi
Certified Google Partner · QA Consultant · 12+ Years IT
QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.