URI-based Configuration

Relevant source files

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

• README.md
• src/meltano_state_backend_postgresql/backend.py
• tests/test_conninfo.py
• tests/test_integration.py
• fixtures/project/meltano.yml
• pyproject.toml

URI-based configuration in meltano-state-backend-postgresql allows users to define the connection parameters for the PostgreSQL state backend using a standard connection string. This approach simplifies configuration by centralizing credentials and connection details into a single setting, while still allowing for fine-grained overrides through individual settings.

Sources: README.md:23-28, src/meltano_state_backend_postgresql/backend.py:126-155

URI Structure and Parsing

The backend accepts a standard PostgreSQL connection URI. The primary entry point for processing this configuration is the conninfo_from_params function, which merges URI components with explicit keyword arguments to produce a final connection string compatible with the psycopg library.

URI Format

The expected URI format is:
postgresql://<user>:<password>@<host>:<port>/<database>?options=-csearch_path%3D<schema>

Sources: README.md:27-29, src/meltano_state_backend_postgresql/backend.py:126-133

Connection Resolution Logic

The system follows a specific precedence when resolving connection details:

1. Explicit Overrides: Parameters passed as keyword arguments to the PostgreSQLStateStoreManager (e.g., via Meltano settings).
2. URI Components: Parameters parsed directly from the state_backend.uri.
3. Defaults: Hardcoded fallbacks (e.g., localhost for host, prefer for SSL mode).

The following flowchart illustrates the processing of connection information:

Schema and Table Identification

The URI and configuration settings jointly determine where the state data is stored in the database. A key feature of the URI-based configuration is the ability to set the PostgreSQL search_path via query parameters.

Table Resolution

• Default Table: If no table is specified, the system defaults to state.
• Schema Qualification: If a schema is explicitly provided in the settings, the table identifier is qualified (e.g., "myschema"."state").
• Search Path: If the schema is provided via URI options (e.g., ?options=-csearch_path%3Dmyschema), the table identifier remains unqualified (e.g., "state"), relying on the connection's search_path.

Configuration Options

Users can configure the backend through the state_backend block in meltano.yml or via environment variables.

Key Settings Table

Setting	Type	Default	Description
state_backend.uri	String	N/A	Full PostgreSQL connection URI
state_backend.postgresql.host	String	localhost	Database server hostname
state_backend.postgresql.port	Integer	5432	Database server port
state_backend.postgresql.user	String	N/A	Authentication username (Required)
state_backend.postgresql.password	String	N/A	Authentication password (Required)
state_backend.postgresql.database	String	N/A	Target database name (Required)
state_backend.postgresql.schema	String	None	Schema for state table (uses search_path if null)
state_backend.postgresql.sslmode	String	prefer	SSL connection security level
state_backend.postgresql.table	String	state	Name of the table for state storage

Sources: src/meltano_state_backend_postgresql/backend.py:53-113, README.md:46-61, pyproject.toml:35-43

Integration Example

In a standard Meltano environment, the URI can be combined with SSL settings:

state_backend:
  uri: postgresql://my_user:my_password@localhost:5432/my_database?options=-csearch_path%3Dmy_schema
  postgresql:
    sslmode: prefer

Sources: README.md:37-41, fixtures/project/meltano.yml:7-11

Implementation Details

The core logic for handling these configurations resides in src/meltano_state_backend_postgresql/backend.py.

Connection Normalization

The conninfo_from_params function uses psycopg.conninfo utilities to ensure the URI and parameters are valid before attempting a connection. This prevents malformed connection strings from causing silent failures during the initialization of the PostgreSQLStateStoreManager.

def conninfo_from_params(
    uri: str,
    *,
    host: str | None = None,
    port: int | None = None,
    database: str | None = None,
    user: str | None = None,
    password: str | None = None,
    sslmode: str | None = None,
) -> str:
    params: dict[str, str | int | None] = conninfo_to_dict(uri)
    # ... applies overrides ...
    return make_conninfo(conninfo="", **params)

Sources: src/meltano_state_backend_postgresql/backend.py:126-163

Backward Compatibility

The system is designed to work with both URI-based configurations and legacy individual settings. In test_backwards_compat.py, the system demonstrates that it can correctly identify state even when shifting from the default Meltano system database to a dedicated PostgreSQL state backend by properly resolving the schema via the connection URI.

Sources: tests/test_backwards_compat.py:75-103

Summary

URI-based configuration provides a flexible and standard way to connect Meltano to a PostgreSQL state store. By leveraging the psycopg library's connection string parsing, the backend supports complex connection options (like sslmode and search_path) while maintaining compatibility with Meltano's individual setting definitions.