Automate Database Integration Tests

This skill provides expertise in database integration testing, with deep knowledge of testing strategies, frameworks, and best practices for relational and NoSQL databases, designing reliable test suites that verify database interactions, data integrity, performance, and cross-system integration scenarios. Core testing principles cover test isolation and data management - database-per-test (separate instances or schemas for parallel execution), transaction rollback (wrapping tests in rolled-back transactions for clean state), test data builders (reusable factories for consistent test data), and seed data management (minimal stable reference data kept separate from test-specific data) - and integration boundaries: repository layer testing (ORM mappings, query logic, data access patterns), cross-service integration (database interaction between microservices), migration testing (schema changes and data transformations), and performance integration (database performance under realistic load).

Test environment setup covers containerized database testing using Testcontainers - a session-scoped fixture spinning up a real PostgreSQL container, running migrations, and a per-test session fixture that rolls back after each test to maintain isolation - and a Docker Compose test environment defining isolated test database and cache (Redis) services on non-default ports. Repository integration testing is demonstrated in both Java (a @DataJpaTest using an in-memory H2 database, testing custom repository query methods with @Transactional @Rollback, and a concurrent-update test verifying optimistic locking throws the expected exception) and Python/SQLAlchemy (testing a repository method with joined entities across User/Profile/Order, and a bulk-operations performance test measuring execution time for a bulk insert of 1000 records).

Use this skill when writing integration tests that verify real database interactions - repository query logic, migrations, concurrent update handling, or bulk operation performance - against a real database instance rather than mocks. It is well suited to applications with meaningful ORM query logic or cross-service database interactions to validate. It is not meant for pure unit tests with no real database interaction (use mocks for those), or for testing business logic unrelated to data access patterns.

Input: the application's repository/ORM layer, migration scripts, and the specific query or concurrency behavior to validate.

Output: an isolated, containerized test environment and integration tests covering repository queries, migrations, concurrency, and bulk performance. Example Testcontainers isolation pattern:

@pytest.fixture
def db_session(db_container):
    engine = create_engine(db_container.get_connection_url())
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        yield session
        session.rollback()
    finally:
        session.close()

Works with Testcontainers (PostgreSQL), Docker Compose for test environments, JPA/Hibernate with Spring's @DataJpaTest and H2 for Java, and SQLAlchemy for Python.

Backend engineers writing integration tests for repository/ORM layers, and teams that need real-database test isolation (Testcontainers, transaction rollback) rather than mocked data access tests.