Testing Overview

Relevant source files

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

• README.md
• pyproject.toml
• tests/conftest.py
• tests/test_integration.py
• tests/test_state.py
• tests/test_conninfo.py
• tests/test_backwards_compat.py
• tests/test_meltano.py

The testing infrastructure for the meltano-state-backend-postgresql project is designed to ensure robust state management within PostgreSQL environments. The suite covers unit testing of the PostgreSQLStateStoreManager logic, integration testing against live database containers, and backwards compatibility verification with legacy Meltano system database schemas.

The project maintains a 100% code coverage requirement as enforced by its configuration. It utilizes a combination of pytest for execution, testcontainers for ephemeral infrastructure, and tox for multi-version orchestration.

Sources: pyproject.toml:227, tests/test_integration.py:38-41

Test Infrastructure and Tooling

The testing environment is orchestrated through several key tools and configurations defined in the project's build system.

Tool	Purpose	Configuration File
pytest	Primary test runner and framework	pyproject.toml
tox	Multi-environment testing (Python 3.10-3.14)	pyproject.toml
testcontainers	Management of temporary PostgreSQL Docker instances	tests/conftest.py
coverage	Strict 100% code coverage enforcement	pyproject.toml
Ruff	Linting and static analysis	pyproject.toml

Sources: pyproject.toml:57-75, pyproject.toml:210-230, tests/conftest.py:23-27

Test Execution Flow

The following diagram illustrates how tests are orchestrated from the developer environment to execution:

The flow ensures that linting, typing, and coverage metrics are satisfied before a build is considered successful.
Sources: pyproject.toml:240-285, README.md:93-103

Unit Testing and Mocking

Unit tests focus on the internal logic of the PostgreSQLStateStoreManager without requiring a live database. This is achieved by mocking psycopg connection objects and cursors.

Mocking Strategy

The tests/conftest.py file provides fixtures that patch psycopg.connect. The subject fixture returns a manager instance paired with a mocked cursor, allowing tests to verify:

• SQL query construction and formatting.
• Parameter serialization (e.g., JSON encoding of state objects).
• Correct handling of database returns, including NULL values.

@pytest.fixture
def mock_connection() -> Generator[tuple[mock.MagicMock, mock.Mock, mock.Mock], None, None]:
    """Patch psycopg.connect and yield (mock_connect, mock_conn, mock_cursor)."""
    with mock.patch("psycopg.connect") as mock_connect:
        mock_conn = mock.Mock()
        mock_cursor = mock.Mock()
        # ... context manager setup ...
        mock_connect.return_value = mock_conn
        yield mock_connect, mock_conn, mock_cursor

Sources: tests/conftest.py:44-58, tests/test_state.py:16-36

Integration Testing

Integration tests verify the backend's behavior against a real PostgreSQL instance. These tests are parameterized to cover various combinations of schemas and table names.

PostgreSQL Container Setup

A module-scoped fixture starts a postgres:16 container using testcontainers. This ensures a clean, isolated database for the duration of the test module.

Coverage Areas

Integration tests specifically validate:

• Connection Parsing: Verification that ports, SSL modes, and schemas are correctly extracted from connection URIs or keyword arguments.
• State Persistence: Testing set, get, delete, and clear_all operations.
• Pattern Matching: Ensuring glob-style patterns (e.g., job_*) are correctly translated to SQL LIKE patterns.
• Advisory Locking: Testing the acquire_lock mechanism, which utilizes PostgreSQL's pg_try_advisory_lock for concurrency control.

Sources: tests/test_integration.py:118-185, tests/test_conninfo.py:18-50, tests/test_state.py:117-128

Backwards Compatibility

A dedicated suite ensures the new PostgreSQL backend can interact with data structured by Meltano's legacy system database schema.

Compatibility Logic

The tests perform the following sequence:

1. Initialize a standard Meltano project fixture.
2. Run MigrationService to create legacy-style tables in a specific PostgreSQL schema.
3. Write state using the legacy systemdb backend.
4. Switch the project configuration to use the meltano-state-backend-postgresql backend.
5. Attempt to read the state and verify it matches the legacy data.

This confirms that the backend can operate as a drop-in replacement or co-exist with existing database-stored state.
Sources: tests/test_backwards_compat.py:30-101

Configuration and Environment Testing

Tests in tests/test_meltano.py verify that Meltano settings and environment variables are correctly mapped to the backend properties.

Setting Validation

The suite checks that settings like host, port, database, and sslmode are correctly identified by the Meltano settings manager and that their corresponding environment variables (e.g., MELTANO_STATE_BACKEND_POSTGRESQL_HOST) follow the expected naming convention.

Sources: tests/test_meltano.py:13-64

Summary of Testing Constraints

Feature	Requirement / Constraint
OS Support	Integration tests are restricted to Linux when running in CI environments.
Meltano Version	Testing includes compatibility logic for Meltano versions older than 4.2.0.
Coverage	Must maintain 100% line and branch coverage.
Python Compatibility	Tests must pass on all versions from 3.10 through 3.14.

Sources: tests/test_integration.py:32-35, pyproject.toml:225-230, pyproject.toml:290-295