One PostgreSQL URI for Polars: toggle read-only and work with both ADBC and ConnectorX

Use one PostgreSQL URI in Polars by toggling default_transaction_read_only; apply a small options-strip to support both ADBC and ConnectorX engines safely.

When moving data between PostgreSQL and Polars, it’s tempting to standardize on a single connection URI and reuse it across engines. The sticking point is read-only enforcement via PostgreSQL options in the URI. The engine adbc accepts a URI with options like default_transaction_read_only=True, while connectorx doesn’t accept extra parameters. The goal is to keep one URI and flip read-only on or off without manually maintaining two versions.

Problem setup

Polars can read via pl.read_database_uri using engines connectorx or adbc, and write via DataFrame.write_database using engines adbc or sqlalchemy. A PostgreSQL URI with a read-only transaction flag looks like this:

"postgresql://scott:tiger@localhost:5432/mydatabase?options=-c%20default_transaction_read_only%3DTrue"

The essential part is default_transaction_read_only=True. The issue: adbc accepts this options segment, but connectorx does not accept setting extra parameters.

Minimal example that exposes the mismatch

import polars as pl

uri_with_ro = (
    r"postgresql://user:pass@localhost:5432/dbname"
    r"?options=-c%20default_transaction_read_only%3DTrue"
)

# This uses the same URI for different engines.
# adbc accepts the options segment.
pl.read_database_uri(
    query="SELECT 1",
    uri=uri_with_ro,
    engine="adbc",
)

# connectorx does not accept extra parameters in the URI.
pl.read_database_uri(
    query="SELECT 1",
    uri=uri_with_ro,
    engine="connectorx",
)

What’s actually going on

The behavior diverges between engines. adbc works with the URI that includes ?options=-c%20default_transaction_read_only%3D..., while connectorx doesn’t accept extra parameters. As a result, a single URI with the options segment is not portable across both engines without adjustment.

Practical fix: strip the options only for connectorx

The simplest way to unify usage is to assemble one canonical URI that includes the read-only switch, then programmatically remove the ?options portion only when the engine is connectorx. This keeps the engine-specific handling in one place and allows toggling read-only via a single flag.

import polars as pl

ro_flag = "False"  # or "True"
dsn_uri = (
    rf"postgresql://username:password@host:port/database"
    rf"?options=-c%20default_transaction_read_only%3D{ro_flag}"
)


def run_query_via_uri(sql: list[str] | str, dsn: str, backend: str, **extras):
    if backend == "connectorx":
        cut = dsn.find("?options", dsn.rfind("/"))
        dsn = dsn[:cut] if cut != -1 else dsn
    return pl.read_database_uri(query=sql, uri=dsn, engine=backend, **extras)


# Unified reads across engines
run_query_via_uri(
    "SELECT * FROM public.a_tbl",
    dsn=dsn_uri,
    backend="adbc",
)
run_query_via_uri(
    "SELECT * FROM public.a_tbl",
    dsn=dsn_uri,
    backend="connectorx",
)

# Unified writes across engines
# dataset is an existing Polars DataFrame
dataset.write_database(
    "public.a_tbl",
    connection=dsn_uri,
    engine="adbc",
    if_table_exists="append",
)
dataset.write_database(
    "public.a_tbl",
    connection=dsn_uri,
    engine="sqlalchemy",
    if_table_exists="append",
)

Why this matters

Using a single URI simplifies integration code and reduces the chance of configuration drift between read and write paths. With Polars, pl.read_database_uri is driven by the engine argument, and DataFrame.write_database can also switch engines. Centralizing how the connection string is adapted per engine keeps the calling code clean and avoids ad hoc branches scattered across the codebase.

Takeaways

If you need PostgreSQL read-only semantics in your connection string, adbc accepts the options segment directly, while connectorx does not accept extra parameters. Build one URI that encodes default_transaction_read_only and strip the options only for connectorx. This way you can toggle read-only with one switch and reuse the same string for reading via adbc or connectorx, and for writing via adbc or sqlalchemy, without juggling multiple connection templates.