Connection Info Parsing

Relevant source files

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

• src/meltano_state_backend_postgresql/backend.py
• tests/test_conninfo.py
• README.md
• pyproject.toml
• tests/test_meltano.py
• tests/test_integration.py

Connection Info Parsing is the mechanism by which the meltano-state-backend-postgresql extension resolves database connectivity details. It transforms a combination of a base connection URI and optional explicit configuration parameters into a standardized PostgreSQL connection string compatible with the psycopg library. This process ensures that Meltano can reliably establish a connection to the state storage database regardless of whether configuration is provided as a single string or via individual environment variables.

The system handles URI parsing, parameter overrides, default value application, and validation of mandatory credentials such as the username, password, and database name. It also manages schema and table identification, ensuring that Meltano state data is directed to the correct database objects.
Sources: src/meltano_state_backend_postgresql/backend.py:126-160, README.md:46-88

Connection String Generation

The core logic for generating connection information resides in the conninfo_from_params function. This utility takes a base URI and a set of optional keyword arguments (host, port, database, user, password, sslmode) to produce a final conninfo string.

Parsing Logic and Overrides

1. URI Decomposition: The base URI is first converted into a dictionary of parameters using conninfo_to_dict. Sources: src/meltano_state_backend_postgresql/backend.py:137
2. Explicit Overrides: If specific parameters (like host or user) are provided as individual arguments, they overwrite the values extracted from the URI. Sources: src/meltano_state_backend_postgresql/backend.py:138-149
3. Validation: The system checks for the presence of mandatory fields: user, password, and dbname. If any are missing, a MissingStateBackendSettingsError is raised. Sources: src/meltano_state_backend_postgresql/backend.py:152-160, tests/test_conninfo.py:105-132
4. Defaults: Default values are applied for host (localhost) and sslmode (prefer) if not otherwise specified. Sources: src/meltano_state_backend_postgresql/backend.py:163-164

Data Flow for Connection Info

This diagram illustrates how configuration inputs are merged and validated to create the final connection string.

Configuration Parameters

The backend supports multiple configuration options, which can be set via meltano.yml, environment variables, or the URI query string.

Connection Settings Table

Parameter	Env Variable	Default	Description
host	MELTANO_STATE_BACKEND_POSTGRESQL_HOST	localhost	PostgreSQL server hostname
port	MELTANO_STATE_BACKEND_POSTGRESQL_PORT	5432	PostgreSQL server port
user	MELTANO_STATE_BACKEND_POSTGRESQL_USER	N/A	PostgreSQL username (Required)
password	MELTANO_STATE_BACKEND_POSTGRESQL_PASSWORD	N/A	PostgreSQL password (Required)
database	MELTANO_STATE_BACKEND_POSTGRESQL_DATABASE	N/A	PostgreSQL database name (Required)
sslmode	MELTANO_STATE_BACKEND_POSTGRESQL_SSLMODE	prefer	SSL connection mode
schema	MELTANO_STATE_BACKEND_POSTGRESQL_SCHEMA	search_path	Target database schema
table	MELTANO_STATE_BACKEND_POSTGRESQL_TABLE	state	Table name for state storage

Sources: src/meltano_state_backend_postgresql/backend.py:46-105, tests/test_meltano.py:34-66, README.md:83-91

Schema and Table Identification

The backend manages how tables are identified in SQL queries. This is distinct from connection info parsing but depends on the parsed parameters.

Table Identifier Logic

The PostgreSQLStateStoreManager determines the table_identifier during initialization. If a schema is provided (either via kwargs or a setting), the identifier is qualified (e.g., "myschema"."state"). If no schema is provided, the table name is used unqualified, relying on the PostgreSQL search_path.

Special handling exists for the search_path when provided in the URI options:

• If ?options=-csearch_path=myschema is in the URI, it is passed verbatim to the connection.
• The table_identifier remains unqualified in this case to allow the server-side search_path to resolve the location.
• Explicit schema parameters always take priority and result in a qualified identifier.

Implementation Details

Connection Factory

The connection is established lazily via the connection property. It uses the parsed conninfo and sets autocommit=True to ensure state updates are persisted immediately without manual transaction management.

# From src/meltano_state_backend_postgresql/backend.py:219-228
@property
def connection(self) -> psycopg.Connection[TupleRow]:
    if self._connection is not None:
        return self._connection

    self._connection = psycopg.connect(self.conninfo, autocommit=True)
    return self._connection

Sources: src/meltano_state_backend_postgresql/backend.py:219-228

URI Options Forwarding

The parser ensures that PostgreSQL-specific options, such as sslmode or complex options strings (e.g., search_path), are correctly forwarded to the underlying psycopg driver. This allows users to configure advanced PostgreSQL features directly through the state_backend.uri.
Sources: tests/test_conninfo.py:30-37, tests/test_conninfo.py:58-67

Summary

Connection Info Parsing in the PostgreSQL state backend provides a flexible configuration layer. By supporting both standard connection URIs and granular settings, it accommodates various deployment environments—from local development using defaults to production environments requiring strict SSL configurations and custom schemas. The robust validation logic prevents runtime failures by ensuring all necessary credentials are present before attempting a database connection.