Testing Database

Database Testing

Test database interactions, schemas, data integrity, and performance, coordinating with integration and data layer testing.

When to use me

Use this skill when:

• Developing applications with database dependencies
• Testing data persistence and retrieval
• Validating database schema migrations
• Ensuring data integrity and consistency
• Testing database performance and optimization
• Verifying transaction handling and rollbacks
• Testing backup and recovery procedures

What I do

• Schema and migration testing:

  • Test database schema changes
  • Validate migration scripts
  • Check backward compatibility
  • Test rollback procedures

• Data integrity testing:

  • Verify CRUD operations work correctly
  • Test transaction atomicity (ACID properties)
  • Check referential integrity and constraints
  • Validate data types and conversions

• Performance testing:

  • Query optimization and indexing
  • Connection pooling and resource management
  • Load testing with concurrent database access
  • Long-running transaction testing

• Integration coordination:

  • Coordinate with integration testing for data layer
  • Support unit testing with test database setup
  • Enable end-to-end testing with realistic data
  • Complement API testing with database validation

Examples

# Database schema testing
npm run test:database:schema       # Schema validation
npm run test:database:migrations   # Migration testing
npm run test:database:rollback     # Rollback procedure testing

# Data integrity testing
npm run test:database:crud         # CRUD operation testing
npm run test:database:transactions # Transaction testing
npm run test:database:constraints  # Constraint validation

# Performance testing
npm run test:database:performance  # Query performance
npm run test:database:load         # Concurrent load testing
npm run test:database:indexing     # Index optimization testing

# Integration with other tests
npm run test:integration -- --database # Integration tests with database
npm run test:e2e -- --data-persistence # E2E with data validation

# Test database setup
npm run test:database:setup        # Create test databases
npm run test:database:teardown     # Clean up test data
npm run test:database:fixtures     # Load test fixtures

Output format

Database Test Results:
──────────────────────────────
Database System: PostgreSQL 15
Test Environment: Isolated test database

Schema Testing:
  ✅ Current schema matches ORM definitions
  ✅ Migration scripts apply correctly
  ✅ Rollback scripts revert successfully
  ✅ Indexes created as expected

Data Integrity:
  ✅ CRUD operations: 100% success
  ✅ Transactions: ACID properties verified
  ✅ Constraints: Foreign keys, unique, check constraints enforced
  ✅ Data types: Proper type handling and conversion

Performance Testing:
  ✅ Query Optimization: 45/50 queries optimized
    ⚠️ 5 complex queries need indexing review
  ✅ Connection Pooling: 100 concurrent connections handled
  ✅ Load Testing: 1000 transactions/minute sustained
  ✅ Response Times: 95% < 100ms

Integration with Other Tests:
  - Unit tests: Use in-memory database or mocks
  - Integration tests: Use test database with transactions
  - API tests: Validate database interactions through API
  - E2E tests: Full data flow from UI to database
  - Performance tests: Include database performance metrics

Security Testing Integration:
  ✅ SQL injection prevention verified
  ✅ Access controls enforced at database level
  ✅ Sensitive data encryption tested
  ✅ Audit logging of database operations

Data Migration Testing:
  ✅ Migration from v1 to v2 successful
  ✅ Data loss: 0 records
  ✅ Downtime: 15 minutes (within SLA)
  ✅ Rollback tested and verified

Recommendation:
  - Review 5 complex queries for indexing opportunities
  - Implement connection health checks
  - Add more comprehensive backup/restore testing
  - Monitor database performance in production

Notes

• Use test databases separate from development/production
• Implement database transaction wrapping for test isolation
• Consider using database containers for consistent testing
• Test with realistic data volumes, not just small samples
• Include database performance in application performance testing
• Test backup and restore procedures regularly
• Consider database-specific features (triggers, stored procedures)
• Coordinate with DevOps for database infrastructure testing
• Document database testing strategy and coverage