Metadata-Version: 2.5
Name: duckhaven-sql-connector
Version: 0.10.0
Summary: DB-API 2.0 client for DuckHaven's SQL session/statement API
Project-URL: Homepage, https://github.com/tamasmrtn/duckhaven-clients
Project-URL: Source, https://github.com/tamasmrtn/duckhaven-clients
Author: DuckHaven
License-Expression: Apache-2.0
License-File: LICENSE
Keywords: connector,dbapi,duckdb,duckhaven,sql
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Topic :: Database :: Front-Ends
Classifier: Typing :: Typed
Requires-Python: >=3.11
Requires-Dist: httpx>=0.28
Provides-Extra: arrow
Requires-Dist: pyarrow>=26.0.1; extra == 'arrow'
Provides-Extra: otel
Requires-Dist: opentelemetry-api>=1.45.2; extra == 'otel'
Requires-Dist: opentelemetry-instrumentation-httpx>=0.48b0; extra == 'otel'
Description-Content-Type: text/markdown

# duckhaven-sql-connector

A [PEP 249 (DB-API 2.0)](https://peps.python.org/pep-0249/) Python client for
[DuckHaven](https://github.com/tamasmrtn/duckhaven-clients)'s SQL 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. In [`auto` mode](#sessions-or-one-shot-queries) it opens no session and runs each
statement as a one-shot query instead. 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

```sh
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

```python
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
        print(cur.description, cur.fetchall())
```

A runnable version is in [`examples/quickstart.py`](examples/quickstart.py).

Parameters use `?` placeholders. Against a DuckHaven with `api_version` 3 or later they are
sent as typed values alongside the SQL and the server writes them in: a `Decimal` keeps its
precision, a `datetime` is a timestamp (with time zone when aware), `bytes`, `UUID`s and lists
of one type are supported. Against an older server the connector renders them into the SQL
itself, quoted and escaped.

> **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`. Connect with `mode="auto"` to run queries there
> anyway; see [Sessions or one-shot queries](#sessions-or-one-shot-queries).

## Sessions or one-shot queries

By default a connection is one SQL session: `connect()` opens it, every statement runs on
it, and state carries from one statement to the next, as in a local DuckDB connection. dbt
and dlt need exactly that. A session needs the server's SQL session surface switched on, and
it holds a place on its agent for as long as it is open.

`mode="auto"` opens no session. Each statement runs as a one-shot query, so the connector
works on a default deployment where sessions are off, and an idle connection holds nothing
on the server. This suits notebooks, scripts and BI tools that mostly read:

```python
conn = connect(host=..., workspace="analytics", token=..., catalog="sales", mode="auto")
```

A statement that sets state for later ones opens a session the first time one is needed,
and the connection then stays on it. Those statements are `SET`, `RESET`, `USE`, `PRAGMA`,
`ATTACH`, `DETACH` and `CREATE [OR REPLACE] TEMP …`, and `stage_files()` opens one too.
The connector recognises them by their first keywords, after any comments. Anything else
runs one-shot, where the server refuses a statement it cannot run there with a clear
error. If sessions are off on the server, the statement that needs one raises
`NotSupportedError` and the connection keeps working for everything else.

`catalog`, `agent`, `timeout` and `use_cache` apply to one-shot queries as they do to a
session. `schema` does too, but only on a server whose `server_version().features` lists
`query_schema`: an older server would ignore the field and resolve names in its default
schema. Against such a server, a connection with `schema` set opens a session for its first
statement and `USE`s the schema there, as `session` mode does. The schema is never
created, so a statement on one that does not exist raises `ProgrammingError`. A one-shot
query in a scoped catalog also needs a grant on the catalog, the schema or a table in it.
A one-shot query is held on the server until it finishes, up to `statement_wait` seconds,
or 30 when that is `None`, and never longer than half of `http_timeout`. `first_page_limit`
only applies to session statements. If compute has to start, a one-shot query waits for it
as part of its `timeout`.

One-shot queries are slower per statement. Each one opens a new DuckDB connection on the
agent and attaches the workspace's catalogs. On a 2-vCPU agent we measured about 300 ms
for `SELECT 1`, against about 100 ms for a session statement. For many small queries in a
row, a session is faster.

## Errors

Failures raise the standard [PEP 249 exceptions](https://peps.python.org/pep-0249/#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, a
  missing object, or a statement that failed when it ran.
- `OperationalError` — an unavailable/disconnected agent, a reaped or closed session
  (reconnect), a timeout, or the session surface being disabled. Also an agent the server
  will not use: `runtime_retired` (its DuckDB runtime has been retired), `runtime_unsupported`
  (the runtime is not one the server recognises) and `agent_sandbox_unverified` (the agent
  could not lock its configuration, which a session requires). These describe the
  deployment rather than your SQL, so naming another agent, or none, can succeed. The same
  goes for compute that cannot take the run: `agent_required`, `no_compute_available`,
  `default_compute_unavailable` and `compute_queue_full`. A statement that was cancelled
  (`code="cancelled"`) or whose agent disconnected while it ran is an `OperationalError`
  too.
  `MaxRetryDurationError` (a subtype) is raised when retries exhaust the configured time
  budget.
- `IntegrityError` — the `query_id` you chose is already taken (`query_id_conflict`).
- `InterfaceError` — bad connection configuration, a rejected token, or a malformed
  response.
- `NotSupportedError` — in `auto` mode, a statement that needs a session on a server with
  SQL sessions off.

### 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.

```python
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.

```python
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:

```python
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.

## Result cache

DuckHaven answers a read from its result cache when nothing the query reads has changed
since an identical query ran, so a repeated dashboard or health-check query can come back
without any compute running. This is on by default on the server and needs nothing from the
connector: a cached statement looks exactly like any other (same rows, same
`cursor.description`, same `rowcount`), and costs the same single HTTP call.

The server decides what is eligible. Among the things it never serves from the cache: writes
and DDL, queries using `now()`, `random()` and similar, views, `information_schema`, table
functions that read outside the catalog such as `read_parquet`, statements inside a
transaction, and anything after the session has created a temporary object or attached a
catalog. See the DuckHaven documentation for the full list.

Turn it off when you are measuring something it would short-circuit — a benchmark that
repeats its queries would otherwise time the cache:

```python
connect(..., use_cache=False)  # None (the default) takes the server's own setting
```

`False` is sent on session open and on every statement; the result still refreshes the
cache for everyone else. A server that predates the cache ignores the field.

## 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:

```python
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 converted to match. Results travel as JSON, and the connector uses each column's
type to hand back the Python value PEP 249 callers expect:

| DuckDB type | Python value |
|---|---|
| `DECIMAL(p,s)` | `decimal.Decimal` |
| every integer type, `HUGEINT`, `UBIGINT` and `BIGNUM` included | `int` |
| `DATE`, `TIME`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE` | `date`, `time`, `datetime` (aware for `WITH TIME ZONE`) |
| `UUID` | `uuid.UUID` |
| `BLOB` | `bytes` |
| lists, structs and maps | converted member by member |

Other types (`VARCHAR`, `DOUBLE`, `BOOLEAN`, `INTERVAL`, `UNION`, `ENUM`, …) arrive as
JSON gives them. A `BLOB` nested inside a list or struct is passed through as the server
encoded it.

> **Exactness depends on the server.** A DuckHaven with `api_version` 3 or later sends
> `DECIMAL` and 64-bit-and-wider integers as exact strings, so they convert exactly. An
> older server sends them as JSON numbers that have already lost precision past about
> 16 digits; the connector converts what it gets but cannot restore what is gone. Against a
> server or agent that sends no `column_schema`, values are passed through unconverted.

To map a type to another type system, parse it with `duckhaven_sql_connector.types.parse_type`.
It keeps what the string says: a decimal's precision and scale, an enum's values, a fixed
array's size, and the members of lists, structs, maps and unions:

```python
from duckhaven_sql_connector.types import parse_type

parse_type("DECIMAL(18,4)")  # DuckType(name='DECIMAL', fields=[], params=(18, 4), size=None)
parse_type("INTEGER[3]").size  # 3; "INTEGER[]" parses as a LIST instead
```

The PEP 249 type objects compare by base type, so `NUMBER == "DECIMAL(18,4)"` and
`DATETIME == "TIMESTAMP WITH TIME ZONE"` both hold.

## Metadata

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

```python
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.

`conn.metadata()` returns the same picture in one request: the catalogs, schemas and tables
the token may see, from DuckHaven's metadata report. With `columns=True` each table also
lists its columns' `name`, `type` and `nullable`, without running a statement on an agent.
Narrow it with `catalog=`, `schema=` or `table=`, because the server caps how many tables it
reports columns for in one call:

```python
for catalog in conn.metadata(catalog="sales", schema="analytics", columns=True):
    for schema in catalog["schemas"]:
        for table in schema["tables"]:
            print(table["name"], [(c["name"], c["type"]) for c in table["columns"]])
```

On a scoped catalog it lists the tables you hold any grant on, including those where you
hold only the metadata tier: you see them, but reading their rows is refused.

## Cancelling and query ids

Every statement gets an id before it is sent, and `cur.query_id` holds it. Pass your own
with `cur.execute(sql, query_id=…)`. Because the id is known before the server answers,
another thread can stop a statement while `execute()` is still blocked, either with
`cur.cancel()` or with `conn.cancel_query(query_id)`, which needs no session:

```python
import threading
import uuid

run_id = str(uuid.uuid4())
cur = conn.cursor()
worker = threading.Thread(target=cur.execute, args=(long_sql,), kwargs={"query_id": run_id})
worker.start()
...  # later, when the user presses stop:
cur.cancel()  # or conn.cancel_query(run_id); the worker's execute() raises OperationalError
```

The server records a run a moment after its submit arrives. `cur.cancel()` retries briefly
if it gets there first; `conn.cancel_query()` raises `ProgrammingError` (404) instead, so
call it once the run exists. A statement that already finished is left as it is. A server
that predates chosen ids picks its own; `cur.query_id` switches to that one when the submit
returns, so cancelling during the submit only takes effect once the submit has returned.
Ids are unique across the whole server, so choose a random one (`uuid.uuid4()`) for each
statement. Reusing an id raises `IntegrityError` (`query_id_conflict`), and that includes the
id of a submit the server refused, for example with a 503: retry with a new id. Any member of
the workspace can cancel any run in it.

## Tags

Tags are labels recorded on each statement and shown in DuckHaven's query History, to tell
apart the traffic of different tools, jobs or notebook cells. Set them for the connection,
and add to them per statement:

```python
conn = connect(..., tags={"app": "forecast", "env": "prod"})
conn.cursor().execute("SELECT …", tags={"step": "load"})  # records app, env and step
```

At most 20 per statement, with keys of letters, digits and `_ . : -`. A server that
predates tags ignores them.

## Checking a connection

`conn.ping()` checks that the connection can still run statements without running one. It
asks the server whose token this is, and on a connection with a session, that the session
is still open. It raises `OperationalError` for a session that ended (and marks the
connection dead) and `InterfaceError` for a token that was revoked or expired.

## Arrow results

With the `arrow` extra installed, against a DuckHaven with `api_version` 3 or later, results
are read as an Arrow stream instead of JSON pages: one request per result, read one record
batch at a time, with every value keeping its type. Nothing changes for your code. Rows from
`fetchone`/`fetchall` are the same Python values either way (see [Column types](#column-types)),
and `fetch_arrow_table` hands over the Arrow data without a round trip through Python:

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

A small result (up to 256 KiB as Arrow, by the server's default) comes back on the statement
response itself, so the statement costs one HTTP call; a larger one is streamed by a second
call. Against a server that does not offer this yet, every result takes that second call.

Without the extra, or against an older server, results are paged as JSON and
`fetch_arrow_table` builds the table from those rows. Two types read differently over Arrow:
an `INTERVAL` arrives as Parquet's raw 12-byte interval, and a `UNION` as a struct of its
members.

## 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:

```python
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, v.features)  # e.g. "1.4.0", 3, {"query_schema"}
```

`version` is the build/release version; `api_version` is an integer bumped only on a
breaking API change. An additive change (a new field, a newly admitted statement) moves
neither, so both are for support and diagnostics rather than for gating behaviour.
`features` is the exception: it names the few additions an older server would ignore
silently rather than refuse, which a client has to ask about before relying on them. It is
empty against a server that predates the list. 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`](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.
