Integration Testing

Relevant source files

The following files were used as context for generating this wiki page:

• tests/test_integration.py
• tests/conftest.py
• tests/test_backwards_compat.py
• pyproject.toml
• src/meltano_state_backend_postgresql/backend.py
• README.md

Integration testing in the meltano-state-backend-postgresql project ensures that the state backend correctly interacts with a live PostgreSQL instance. These tests validate the full lifecycle of state management, including table creation, state persistence, retrieval, locking mechanisms, and compatibility with existing Meltano system database schemas.

The test suite utilizes testcontainers to spin up ephemeral PostgreSQL instances, providing a clean, isolated environment for every test execution. This allows for testing complex scenarios such as schema-qualified table names and search path variations that unit tests with mocks cannot fully cover.

Sources: tests/test_integration.py:38-40, tests/conftest.py:24-28, README.md:92-101

Test Environment Architecture

The integration testing infrastructure relies on pytest fixtures to manage the lifecycle of the database container and the PostgreSQLStateStoreManager.

Container Management

A module-scoped fixture postgres_container initializes a PostgresContainer using the postgres:16 image. This container provides the network bridge and credentials required for the backend to establish a real connection.

Sources: tests/conftest.py:24-28, tests/test_integration.py:46-48

Manager Setup and Parametrization

The manager fixture in the integration suite is heavily parametrized to test various database configurations. It handles the dynamic creation and destruction of schemas to test how the backend resolves table identifiers.

The diagram shows the lifecycle of the integration test environment, from container startup to schema cleanup.
Sources: tests/test_integration.py:42-70, tests/conftest.py:24-28

Core Integration Scenarios

The integration tests cover the primary functional requirements of the state backend against the database.

State Persistence and Retrieval

Tests verify that MeltanoState objects (containing partial_state and completed_state) are correctly serialized to JSON and stored in the PostgreSQL TEXT columns. It also validates that retrieving a non-existent state returns None and that null values in the database are correctly handled as empty dictionaries in Python.

Test Case	Description	Source
test_set_and_get_state	Basic CRUD operation for state.	tests/test_integration.py:93-119
test_update_existing_state	Verifies ON CONFLICT logic updates existing keys.	tests/test_integration.py:121-133
test_null_state_values	Ensures NULL database columns map to {}.	tests/test_integration.py:192-203

Advisory Locking

A critical component of the integration tests is validating PostgreSQL advisory locks. The test_acquire_lock test ensures that the backend can successfully obtain and release a lock using a 64-bit integer key derived from the state_id.

Sources: tests/test_integration.py:185-190, src/meltano_state_backend_postgresql/backend.py:328-360

Table and Schema Resolution

The backend must correctly handle various ways of defining the state table location. Tests use the _assert_table_identifier helper to verify the generated SQL identifiers.

Resolution Logic

The backend supports three primary methods of location resolution:

1. Default: Uses the state table in the default search_path.
2. Explicit Schema/Table: Uses a specific schema and table name provided via settings.
3. Search Path Options: Uses the options=-csearch_path=... parameter in the connection URI.

This diagram illustrates how the PostgreSQLStateStoreManager determines whether to use a qualified or unqualified table identifier based on configuration.
Sources: tests/test_integration.py:72-88, src/meltano_state_backend_postgresql/backend.py:171-182

Backwards Compatibility Testing

The project includes a specific integration suite to ensure compatibility with Meltano's legacy systemdb schema.

1. Migration Execution: Uses MeltanoState and MigrationService to apply standard Meltano migrations to a fresh schema.
2. Cross-Backend Verification: Sets state using the standard SQLAlchemy-based systemdb manager and then attempts to retrieve it using the new PostgreSQLStateStoreManager.
3. Schema Isolation: Validates that state is correctly isolated between the public schema and custom schemas specified in the connection URI's search_path.

Sources: tests/test_backwards_compat.py:34-92

Tooling and Automation

The integration tests are integrated into the project's development workflow as defined in pyproject.toml.

• Environment: Tests are restricted to Linux in CI environments because of testcontainers requirements.
• Dependencies: The tests dependency group includes testcontainers[postgres], pytest, and meltano[postgres].
• Execution: Tests are typically run via tox or pytest.

Sources: pyproject.toml:61-67, tests/test_integration.py:34-37, README.md:92-101

Conclusion

Integration testing provides the definitive validation for the meltano-state-backend-postgresql plugin. By simulating real-world database interactions—including complex schema configurations, concurrent locking, and legacy migrations—the test suite ensures that the backend remains a reliable and compatible replacement for the default Meltano state storage mechanism.