What is database testing and what do you verify?

Database testing involves verifying data integrity, data validity, and database performance to ensure the application’s data layer functions correctly.

Key Areas to Test

1. Data Integrity:

• Verify data is correctly inserted, updated, and deleted
• Check foreign key relationships
• Validate constraints (NOT NULL, UNIQUE, CHECK)
• Verify triggers execute correctly

2. Data Validity:

• Verify correct data types
• Check data format (dates, emails, phone numbers)
• Validate data ranges
• Ensure no orphan records

3. Schema Validation:

• Verify table structures
• Check column names and data types
• Validate indexes
• Verify stored procedures and functions

4. CRUD Operations:

• Create: Verify INSERT operations
• Read: Verify SELECT queries return correct data
• Update: Verify UPDATE operations modify correct records
• Delete: Verify DELETE operations remove correct records

Common SQL Queries for Testing

1. Verify Record Count:

SELECT COUNT(*) FROM users WHERE status = 'active';

2. Check for Duplicates:

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

3. Verify Data Integrity:

-- Check for orphan records
SELECT * FROM orders
WHERE user_id NOT IN (SELECT id FROM users);

4. Validate Date Ranges:

SELECT * FROM orders
WHERE order_date > delivery_date;

5. Check NULL Values:

SELECT * FROM users
WHERE email IS NULL OR email = '';

Performance Testing

• Query execution time
• Index effectiveness
• Database response under load
• Connection pooling

Tools for Database Testing

• SQL queries (manual testing)
• Database management tools (MySQL Workbench, pgAdmin)
• Automated testing frameworks (DBUnit, SQLUnit)
• Performance tools (JMeter for DB load testing)