Automate Database Integration Tests

This profile guides the design and maintenance of database integration tests for relational and NoSQL systems. Its focus is verifying actual database interactions, data integrity, query behavior, migration transformations, and integration scenarios across services. It combines isolation and data-management principles with concrete Python and Java examples.

Repository tests examine ORM mappings, joins, custom queries, and access patterns. Cross-service tests inspect persistent state and published events after a workflow runs. Migration tests begin with data in the old schema, apply a transformation, and assert the resulting fields. Performance scenarios exercise bulk operations and concurrent writes, with counts and elapsed time used as observable outputs.

Use it when building an integration suite, improving flaky fixtures, checking a migration's data effects, or testing repository error behavior. Isolate parallel tests with separate database instances or schemas, appropriate transaction rollback, minimal stable seed data, and reusable data builders.

The examples need adaptation to the application's actual transaction and database behavior. They illustrate session-scoped containers, individual session cleanup, committed repository operations, and an in-memory H2 configuration alongside PostgreSQL examples. Those different setups should not be treated as interchangeable proof for a target database. Example time limits are scenario assertions rather than universal database performance promises.

Inputs include the database engine, ORM or access layer, test framework, schema and migrations, realistic test records, and the workflow being verified. Outputs are fixtures, test environments, and assertions about records, relationships, errors, events, and timing. The Python container setup uses these imports:

import pytest
from testcontainers.postgres import PostgresContainer
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Setup patterns include Docker Compose services for PostgreSQL and Redis, JPA repository testing with TestEntityManager, and SQLAlchemy checks of related users, profiles, and orders. An order-processing example verifies reduced inventory, a pending payment, and an OrderCreated event. Migration coverage checks that splitting a full name preserves first name, last name, and email.

Data cleanup can use rollback or explicit cleanup, while snapshots support scenarios that require a complex initial state. Parallel execution needs isolated instances, and connection pools and warm-up strategies help make performance measurements more consistent. Monitor test duration and investigate slow queries.

Failure coverage includes constraint violations, transaction rollback under errors, connection failures and recovery, and deadlock detection and resolution. Concurrency checks exercise optimistic-locking conflicts and assert aggregate write counts. These scenarios extend the suite beyond checking that a happy-path query returns one expected row.