Metadata-Version: 2.5
Name: duckhaven-sql-connector
Version: 0.8.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.0; extra == 'arrow'
Provides-Extra: otel
Requires-Dist: opentelemetry-api>=1.45.1; 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 **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

```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, rendered safely client-side
        print(cur.description, cur.fetchall())
```

A runnable version is in [`examples/quickstart.py`](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](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, or a
  missing object.
- `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.
  `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.

```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` and `UBIGINT` 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.

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

## Arrow results

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

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

```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)  # 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`](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.
