duckhaven-sql-connector

A PEP 249 (DB-API 2.0) Python client for DuckHaven's SQL session API.

It is a pure HTTP client of DuckHaven's public REST API: it authenticates with a DuckHaven Personal Access Token (dh_pat_…), opens a SQL session bound to one compute agent, runs statements against that session's persistent DuckDB connection, and fetches results. It never talks to a compute node directly and depends on no DuckHaven server internals.

This connector is the shared transport that dbt-duckhaven, the dlt duckhaven destination, a future CLI, and Airflow operators build on.

Install

pip install duckhaven-sql-connector
# optional extras:
pip install "duckhaven-sql-connector[arrow]"   # client-side Arrow tables
pip install "duckhaven-sql-connector[otel]"    # OpenTelemetry trace propagation

Usage

from duckhaven_sql_connector import connect

with connect(
    host="https://duckhaven.internal",
    workspace="analytics",
    token="dh_pat_…",
    catalog="sales",  # optional default catalog
    # agent="…-uuid-…",       # optional explicit compute (an agent UUID); omit to auto-pick
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT ? AS n", [1])  # qmark params, rendered safely client-side
        print(cur.description, cur.fetchall())

A runnable version is in examples/quickstart.py.

Note: The DuckHaven SQL session surface is disabled unless the operator sets SQL_SESSIONS_ENABLED=true on the server. Against a server with it off, opening a session raises an OperationalError.

Errors

Failures raise the standard PEP 249 exceptions, carrying the server's code/status_code/detail. Both of DuckHaven's error envelopes (api_version 1's {"detail": ...} and api_version 2's {"error", "message", "details"}) are accepted transparently — the connector works against either server generation without configuration.

• ProgrammingError — a rejected statement (statement_not_allowed), a denied grant, or a missing object.
• OperationalError — an unavailable/disconnected agent, a reaped or closed session (reconnect), a timeout, or the session surface being disabled. MaxRetryDurationError (a subtype) is raised when retries exhaust the configured time budget.
• InterfaceError — bad connection configuration or a malformed response.

Agent access denials

A DuckHaven server can restrict which agents a caller may target. Two shapes surface when agent is set, both as ProgrammingError:

• 403 agent_forbidden — you can see the agent but hold too low a tier to run on it. The raised error carries code="agent_forbidden".
• 404 Agent not found — an agent the server keeps restricted and you hold no grant on is invisible rather than forbidden, so it answers exactly as a deleted agent would. The error carries no code (api_version 1) or the generic code="not_found" (api_version 2, which derives it from the status rather than naming this case specifically). A mistyped agent UUID and a denied one are deliberately indistinguishable, so check whether you have been granted the agent before concluding the id is wrong.

Omitting agent auto-picks, and the server only considers agents you may use. On a server with restricted agents that means auto-pick can raise OperationalError ("no connected agent available") where the same call against an unrestricted deployment would connect.

Idempotent requests (poll/fetch/cancel) are retried on transient failures with capped exponential backoff; a server Retry-After header is honored, and retries are bounded by both a max-attempt count and a total-time budget (RetryPolicy.max_elapsed). Statement submits are never auto-retried.

Cold start

A DuckHaven deployment can run elastic compute, scaling to zero when nothing is running. Connecting then has to start an agent first, which takes seconds on a container host and up to a minute on a cloud one. connect() waits that out rather than failing: the server hands back the session before it is usable and the connector polls it to open, so the wait is invisible apart from a slower first connection.

connect(..., compute_wait=300.0)  # the default; 0 fails immediately instead

compute_wait is the total wall-clock budget for that wait. The default matches the server's own provisioning deadline, past which it gives up on the pending session — so waiting longer could not succeed. Exhausting the budget raises MaxRetryDurationError.

Waiting only happens while the server reports the session as still coming up. If compute cannot be started at all, or the agent never reports for duty, the server says so and connect() raises an OperationalError carrying the reason (compute_unavailable, provisioning_timeout) instead of waiting out the full budget. Against a server without elastic compute nothing changes: no compute is ever starting, so nothing is ever waited on.

Statement completion

Running a statement is asynchronous underneath — the server hands it to a compute agent and is told when it finishes. Rather than discover that by polling, execute() lets the server hold the submit call until the statement is done, so it returns as soon as the result exists instead of on the connector's next poll.

connect(..., statement_wait=None)  # the default: take the server's own budget

None sends nothing, so the deployment's SQL_STATEMENT_WAIT_TIMEOUT_S (10 seconds by default) applies and an operator's tuning is not overridden by a client with no opinion. A number overrides it for this connection; 0 asks the server never to hold the call, restoring the submit-then-poll behaviour of earlier connectors. It must stay under http_timeout, which is the socket deadline the held response has to arrive within, and the server caps it at SQL_STATEMENT_MAX_WAIT_TIMEOUT_S.

A statement that runs longer than the budget is not cancelled — the server hands it back still running and the connector polls it to completion as before, so the worst case is exactly the old behaviour. Against a server too old to know the field, nothing changes: it ignores it and the connector polls.

First page of rows

Running a statement used to cost two HTTP calls: the submit, and one to fetch rows. The second was unavoidable — PEP 249 requires cursor.description, and the column names come with the rows — so even SELECT 1, and even a statement whose rows you never read, paid a full round trip for it.

The server can now return the first page on the submit response, and the connector asks for it by default:

connect(..., first_page_limit=200)  # the default; 0 disables it

A statement whose result fits in that page costs one HTTP call. A larger result is unaffected in behaviour: the inlined page carries a cursor and paging continues from it exactly as before. The request is capped at your fetch_size, since asking for more rows than you will buffer is pointless, and the server caps it again on its side — this saves a round trip on rows you are about to read, it is not a bulk transport.

Against a server too old to support it, the response simply carries no page and the connector fetches rows the way it always did.

Column types

cursor.description carries the result's column types in PEP 249's type_code field, spelled the way DuckDB prints a logical type — the same string DESCRIBE returns, so it is self-describing for parameterized and nested types:

cur.execute("SELECT id, amount, created_at FROM sales.orders")
[(d[0], d[1]) for d in cur.description]
# [('id', 'BIGINT'), ('amount', 'DECIMAL(18,4)'), ('created_at', 'TIMESTAMP WITH TIME ZONE')]
cur.column_types  # the same types on their own

Both are None against a server (or agent) older than this field, so code that reads them should tolerate that.

Values are not re-typed to match. Results travel as JSON, so a DECIMAL or HUGEINT arrives as a float with its precision already lost, a BLOB as hex text, an INTERVAL as an ISO-8601 duration, and temporal types as ISO-8601 strings. The connector reports the true type but does not cast the value, because casting could not restore precision that was gone before the client saw it — it would only hide the loss.

Metadata

For relation introspection (as dbt and BI tools need), the cursor exposes metadata methods; fetch the rows as usual:

cur.tables(catalog="sales", schema_name="public")
for catalog, schema, name, table_type in cur.fetchall():
    ...

cur.columns(catalog="sales", schema_name="public", table_name="orders")
for catalog, schema, table, column, position, data_type, is_nullable in cur.fetchall():
    ...
# also: cur.catalogs(), cur.schemas(catalog=…)

Two things are worth knowing about how these work:

• columns() needs an exact table_name. It reports columns with DESCRIBE, which describes one relation. information_schema.columns is not usable: for an attached Iceberg table it returns a single placeholder row (__ / UNKNOWN) instead of the real columns, and inconsistently so — a table something has already touched in the session reports correctly while the rest do not — so it returns wrong data rather than failing. Use tables() to enumerate, then columns() per relation. data_type is DuckDB's spelling, the same vocabulary a query result reports.
• catalogs(), schemas() and tables() read DuckHaven's REST browse endpoints, not SQL. Engine-side enumeration is refused on any workspace with a scoped catalog attached, since the engine cannot filter those listings by grant; the REST endpoints can, and behave identically on open catalogs. They cost one request per catalog in scope, plus one per schema for tables(), so pass catalog= and schema_name= when you can.

Arrow results

With the arrow extra, fetch results as a pyarrow.Table:

cur.execute("SELECT * FROM sales.orders")
table = cur.fetch_arrow_table()

Observability

• otel extra — each request emits a client span and injects a W3C traceparent, so client spans join the DuckHaven server trace. It is a no-op when the extra isn't installed.
• Hooks — pass connect(..., hooks=Hooks(...)) to observe request timings, retries, and rows fetched without any OpenTelemetry dependency (a client library runs no metrics server).

Server version

conn.server_version() reports the server's release and API-contract version:

v = conn.server_version()
if v is None:
    ...  # server predates GET /api/version — assume the oldest supported behaviour
else:
    print(v.version, v.api_version)  # e.g. "1.4.0", 1

version is the build/release version; api_version is an integer bumped only on a breaking API change. It is a provenance and coarse-compatibility signal, not a feature list — an additive change (a new field, a newly admitted statement) moves neither — so it is for support and diagnostics rather than for gating behaviour. A server too old to expose the endpoint returns None.

Compatibility

The exact server endpoints and fields this client depends on are pinned in contract/duckhaven-openapi.subset.json and checked by the contract test. Regenerate it against a running server with make refresh-contract HOST=https://duckhaven.internal to detect API drift early.

License

Apache-2.0.