Metadata-Version: 2.4
Name: sqlalchemy-monetdb-adbc
Version: 0.3.8
Summary: SQLAlchemy dialect for MonetDB over ADBC
Project-URL: Homepage, https://github.com/wlaur/sqlalchemy-monetdb-adbc
Project-URL: Repository, https://github.com/wlaur/sqlalchemy-monetdb-adbc
Project-URL: Issues, https://github.com/wlaur/sqlalchemy-monetdb-adbc/issues
Author-email: William Laurén <lauren.william.a@gmail.com>
License-Expression: MIT
License-File: LICENSE
Keywords: adbc,arrow,database,dialect,monetdb,sqlalchemy
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Typing :: Typed
Requires-Python: >=3.13
Requires-Dist: adbc-driver-manager>=1.11
Requires-Dist: adbc-driver-monetdb>=0.12.3
Requires-Dist: numpy>=2.1
Requires-Dist: pyarrow>=25
Requires-Dist: pydantic>=2
Requires-Dist: sqlalchemy>=2.0.42
Requires-Dist: tzdata>=2025.2; sys_platform == 'win32'
Description-Content-Type: text/markdown

# sqlalchemy-monetdb-adbc

A SQLAlchemy dialect for MonetDB backed by
[`adbc-driver-monetdb`](https://github.com/wlaur/adbc-driver-monetdb).

The package is pure Python. It gives SQLAlchemy and Arrow-native ADBC operations a
single MonetDB connection and transaction boundary.

## Status

The dialect covers SQL compilation, types, reflection, transactions, and the ORM,
and is validated against a live MonetDB server.

### Dialect compliance suite

SQLAlchemy's own dialect suite runs from `suite/` (see Development) and reports
no failures: 1237 passed, 16 xfailed, 388 skipped. Each xfail is a MonetDB or
driver limitation, listed with its reason in `suite/known_failures.py`. They are
marked xfail rather than skipped so that the tests still run, and report XPASS
if a future release gains the behaviour:

- MonetDB infers no type for a bare parameter, so `WHERE ? = ?` is rejected
  outright. A cast resolves it, but SQLAlchemy emits the parameter alone. This
  is most of the remainder.
- Untyped parameters also change arithmetic: `SELECT ? / ?` with `(15, 10)`
  returns `1`, not `1.5`, and dividing an untyped parameter by a decimal is
  read as interval arithmetic.
- No lastrowid, which is why generated values come back through `RETURNING`.
- `RETURNING *` needs an explicit column list.
- JSON is normalized on input, so a document does not round-trip byte for byte.
- Some identifiers legal elsewhere are rejected, such as one containing `%`.

Common table expressions are fully supported, including recursive CTEs, CTEs
over `VALUES`, and CTEs driving `UPDATE`/`DELETE`. MonetDB's `WITH` accepts only
`SELECT` or `VALUES`, so a CTE cannot itself be an `INSERT`/`UPDATE`/`DELETE`.

Regular expression matching is not available: MonetDB's `~` is `mbr_contains`,
a geometry operator, so `regexp_match()` raises rather than producing SQL that
means something else. `regexp_replace()` works.

The dialect requires `adbc-driver-monetdb` 0.12.3 or newer. This version safely
falls back to typed literal execution when MonetDB cannot execute a prepared
query over remote tables while retaining prepared execution for normal query
shapes. The driver also coalesces producer batches into adaptive byte-budgeted
COPY windows, streams bounded uploads, and prevents client-side ingest failures
from committing partial appends. For an append to a constrained table, its
default `auto` policy uses bounded staging and one target insert so primary-key
validation does not repeat for every physical upload window. Applications can
select the diagnostic `direct` policy through
`adbc.monetdb.constrained_append` when server-side staging is unsuitable; see
the driver documentation for its transaction, permission, and MonetDB-version
behavior. The driver also reports truthful row counts, caches prepared
statements per connection, executes one-row bound DML without a savepoint, and
returns small results from MonetDB's initial reply. Append columns prefer exact
names before falling back to case-insensitive matching, preserving distinct
quoted identifiers such as `"x"` and `"X"`. One
`DRIVER-WORKAROUND` remains in the source for upstream Apache Arrow ADBC
behavior: ADBC always returns an Arrow stream, so the DB-API layer reports an
empty `description` rather than `None` for statements that produce no result
set, and SQLAlchemy needs `None` to decide that a statement returned no rows.

### MonetDB behaviors worth knowing

- A stock login lands in the `sys` schema, where system views already occupy
  ordinary table names such as `users` and `columns`. Create and use a schema of
  your own.
- Bulk reflection with `Inspector.get_table_names()` and
  `Inspector.get_view_names()` excludes MonetDB-owned system objects, including
  when `schema="sys"` is explicit. Named reflection such as `has_table()` and
  `get_columns()` can inspect those objects. Query the MonetDB catalog directly
  when a complete system inventory is required.
- Self-referential foreign keys are added by `ALTER TABLE` after the table
  exists, because MonetDB cannot declare them inline. MonetDB then enforces
  them one statement at a time rather than at statement end, so on such a table
  `DELETE FROM t` and `TRUNCATE t` are both rejected, and a multi-row `INSERT`
  cannot reference a row added by the same statement. Declare the foreign key
  with `ondelete="CASCADE"` if you need bulk deletes, or clear the referencing
  column first:

  ```python
  connection.execute(update(tree).values(parent_id=None))
  connection.execute(delete(tree))
  ```
- Indexes carry no ordering, so `ASC`/`DESC` on an index expression is dropped.
- MonetDB rejects the all-zero (nil) UUID, `00000000-0000-0000-0000-000000000000`,
  even as a plain literal, because it uses that value as its internal `NULL` for
  the `uuid` type. A `Uuid` column therefore cannot store `uuid.UUID(int=0)`.
- A `DECIMAL` declared without precision means `DECIMAL(18, 3)`. The dialect
  always renders that explicitly: MonetDB drops the connection when a
  *parameter* is cast to an unsized `DECIMAL`, which is what SQLAlchemy emits
  for true division.
- A `Numeric` column accepts `float`, `int` and `Decimal` in the same
  `executemany`. The dialect prepares the statement to obtain its exact Arrow
  parameter schema and normalises mixed Python numeric values before building
  that batch.
- `INET` parameters bind as strings. Selected `INET` columns are cast to
  `VARCHAR(128)` because MonetDB does not expose the native type through its
  binary result protocol.
- A `UNIQUE` constraint is backed by an index of the same name, so it is
  reflected both by `get_unique_constraints()` and by `get_indexes()`.

## Installation

```console
uv add sqlalchemy-monetdb-adbc
```

PyArrow is a required dependency because the dialect's row-conversion layer and
public Arrow helpers use concrete `pyarrow` types. Polars is not required by the
dialect; install it only when using the Polars examples or integration.

## Usage

Existing SQLAlchemy MonetDB URLs work unchanged:

```python
from sqlalchemy import create_engine

engine = create_engine(
    "monetdb://monetdb:monetdb@localhost:50000/demo",
)

with engine.connect() as connection:
    rows = connection.exec_driver_sql("SELECT 1").all()
```

The explicit `monetdb+adbc://` form resolves to this dialect. It can coexist
with `sqlalchemy-monetdb` when every URL is explicit:
`monetdb+adbc://` selects this package and `monetdb+pymonetdb://` selects the
pymonetdb-backed dialect. Both packages register bare `monetdb://`, so its winner is
entry-point-order dependent while both are installed.

TLS URLs work with either the unchanged `monetdbs://` form or the explicit
`monetdbs+adbc://` form. Both preserve the secure driver URI.

The driver accepts the same URI query options after the database name:

```python
engine = create_engine(
    "monetdb://monetdb:monetdb@localhost:50000/demo?client_application=my_app",
)
```

## Arrow and Polars

Rows are converted to Python objects only if you ask for them, and that
conversion is done a column at a time through NumPy rather than element by
element. Ordinary row-returning queries with meaningful result sizes avoid
per-cell Arrow scalar boxing.

PyArrow and NumPy are runtime dependencies for the dialect's Arrow-native
result layer and optimized Arrow-to-Python object conversion. Polars is
optional. Arrow-native reads and writes do not convert through NumPy.

The driver removes the avoidable fixed costs found in the release review:
one-row parameterized DML no longer adds a savepoint pair, and results of up to
100 rows no longer force a second fetch. The driver also batches `executemany`
parameters into one multi-row statement when MonetDB can preserve the DB-API
semantics, and repeated single-batch appends no longer create redundant
savepoints. Tiny row-oriented SELECTs still carry the fixed cost of receiving
a canonical Arrow stream across the native boundary; use the Arrow helpers
below when keeping the result columnar matters.

To skip the conversion entirely and keep data columnar, run the query on the
same connection and take Arrow back:

```python
import polars as pl

from sqlalchemy_monetdb_adbc import (
    fetch_arrow_table,
    ingest_arrow,
    open_arrow_batch_reader,
)

statement = select(trades.c.id, trades.c.symbol).where(trades.c.symbol == "AAPL")

with Session(engine) as session:
    table = fetch_arrow_table(session.connection(), statement)  # pyarrow.Table
    df = pl.from_arrow(table)

    # streaming, for results that should not be materialized at once
    with open_arrow_batch_reader(session.connection(), statement) as reader:
        for batch in reader:
            ...

    ingest_arrow(session.connection(), trades, table, mode="append")
```

These run on the ADBC session backing the SQLAlchemy connection, so they see
that connection's uncommitted work, take part in its transaction, and roll back
with it.

Appending to an existing table matches the stream's columns to the destination's
by name, so the stream may present them in any order and may supply a subset; a
column it omits takes that column's `DEFAULT`. The driver owns that contract and
applies it identically on both of its ingest routes, so the dialect does not
reorder or null-fill anything itself.

For a large incremental write, pass one `pyarrow.RecordBatchReader` to one
`ingest_arrow` call. The reader remains streaming while the driver keeps one
operation-level transaction scope. Passing a SQLAlchemy `Table` uses its name
and translated schema. With `create=True`, the helper emits normal dialect DDL
first, including primary keys, nullability, constraints, custom types, and
schema translation:

```python
ingest_arrow(connection, trades, reader, create=True)
```

For a Parquet source, use the driver's bounded row-group stream, re-exported by
the dialect:

```python
from sqlalchemy_monetdb_adbc import ParquetArrowStream, ingest_arrow

with ParquetArrowStream("trades.parquet") as stream:
    ingest_arrow(connection, trades, stream, mode="append")
    assert stream.rows_read == stream.num_rows
```

`ParquetArrowStream` decodes one physical row group at a time, uses an owned
PyArrow file handle instead of a whole-file memory map, and bounds each emitted
batch by rows and estimated bytes. Its `epoch_columns` mapping can reinterpret
nullable integer timestamps or dates batch by batch without an eager Polars
conversion. Explicit `row_groups` preserve their requested order and reject
duplicates rather than ingesting the same physical group twice. For non-Parquet Polars lazy pipelines,
`adbc-driver-monetdb[polars]` provides `PolarsArrowStream`.
Backpressure bounds its handoff queue, but Polars 1.43 can decode ahead
proportional to a Parquet dataset; prefer `ParquetArrowStream` for files.

Advanced ADBC statement options can be passed without moving ingest policy
into the dialect:

```python
ingest_arrow(
    connection,
    trades,
    reader,
    statement_options={"adbc.monetdb.ingest_atomicity": "savepoint"},
)
```

The driver's default constrained-append mode stages bounded COPY windows and
performs one final target move. A producer or constraint failure therefore
preserves earlier SQLAlchemy work. A client-side failure after completed
*direct* target COPY windows leaves the transaction readable but makes commit
fail. SQLAlchemy deactivates a transaction after any failed commit, so the
dialect rolls the physical ADBC transaction back before propagating that
commit error; the pool and SQLAlchemy then agree that no transaction remains.
Calling `rollback()` instead of attempting commit also recovers normally.

`pl.read_database` can use that same session too. Pass it the underlying
ADBC connection owned by SQLAlchemy:

```python
import polars as pl

from sqlalchemy_monetdb_adbc import raw_adbc_connection

with Session(engine) as session:
    session.execute(insert(trades), {"id": 1, "symbol": "AAPL"})

    df = pl.read_database(
        query="SELECT id, symbol FROM trades ORDER BY id",
        connection=raw_adbc_connection(session.connection()),
    )

    session.commit()
```

Do not close the returned ADBC connection or change its transaction state;
SQLAlchemy owns both. Passing a URL to `pl.read_database` instead would
open a separate session and would not see uncommitted work.

Do not pass the raw ADBC connection to `DataFrame.write_database`: Polars'
ADBC write path commits the connection after ingesting, which would also
commit any pending SQLAlchemy work. Use the transaction-aware helper for
columnar writes:

```python
ingest_arrow(session.connection(), "trades", df, mode="append")
```

A SQLAlchemy statement is compiled for you, bind parameters included. Arrow
helpers run the same bind processors as normal SQLAlchemy execution, including
JSON codecs, `PydanticJSON`, temporal normalization, custom type decorators,
`NULL`, and expanding parameters. Explicit `parameters` are accepted for a
plain SQL string; they cannot override values already bound into a compiled
SQLAlchemy statement. `raw_adbc_connection()` returns the underlying ADBC
connection if you need the driver API directly.

The driver prefetches large results by default. Closing a partially consumed
result—such as calling `.first()`—waits briefly and then lets an in-flight fetch
finish without killing the pooled session. The next statement on that
connection can wait for that fetch or its configured timeout. Set
`read_prefetch=false` in the connection URI when prompt pool reuse matters more
than fetch/decode overlap:

```python
engine = create_engine("monetdb://localhost/demo?read_prefetch=false")
```

The driver marks terminal client transport failures with the structured ADBC
detail `adbc.monetdb.connection_terminal=true`. The dialect invalidates those
sessions in `QueuePool` and `StaticPool`, while server SQLSTATE timeout or
cancellation errors remain reusable when MonetDB kept the session open.

## JSON

A `JSON` column round-trips Python objects, as SQLAlchemy specifies: the driver
returns the document as a string and SQLAlchemy deserializes it, honouring
`json_serializer` and `json_deserializer` on `create_engine`.

```python
engine = create_engine("monetdb://...", json_deserializer=orjson.loads)
```

MonetDB validates and normalizes JSON on input, so a round-tripped document
keeps its values but not its original whitespace or key order.

Path indexing works as on other backends, including nested keys, array indexes
and the `as_string()`/`as_integer()` accessors. A path that matches nothing
returns `None`:

```python
select(t.c.doc["title"])  # 'hello'
select(t.c.doc[("sub", "k")])  # 'v'
select(t.c.doc[("arr", 1)])  # 20
select(t.c.doc["missing"])  # None
```

Whatever Python object you store is serialized, and you get that same object
back. Note that a `str` is itself a valid JSON value, so passing pre-serialized
text to a `JSON` column stores a JSON *string*, not an object:

```python
connection.execute(insert(t), [{"payload": {"a": 1}}])  # stored as {"a":1}
connection.execute(insert(t), [{"payload": '{"a": 1}'}])  # stored as "{\"a\": 1}"
```

This is SQLAlchemy's behavior on every backend, not a MonetDB quirk. To store
JSON text you already hold, use a `Text` column, or a type that controls the
codec as below.

### Storing a Pydantic model

`PydanticJSON` stores a Pydantic model in a MonetDB `JSON` column. The model is
serialized straight to JSON text and parsed straight back with
`model_validate_json`, so no intermediate `dict` is built in either direction:

```python
from pydantic import BaseModel, ConfigDict
from sqlalchemy import Integer, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from sqlalchemy_monetdb_adbc import PydanticJSON


class Content(BaseModel):
    model_config = ConfigDict(frozen=True)

    title: str
    views: int


class Base(DeclarativeBase):
    pass


class Article(Base):
    __tablename__ = "article"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    content: Mapped[Content] = mapped_column(PydanticJSON(Content))
```

The attribute is the model itself, with no conversion at the call site:

```python
article: Article = session.scalars(select(Article)).one()
article.content.title  # Content instance, never a dict
```

This avoids deserializing to an intermediate `dict` before validation. A plain
`JSON` column cannot avoid the dict: by the time the attribute is read,
SQLAlchemy has already deserialized and the original text is gone.

#### Declare the model frozen

As with any `JSON` column, SQLAlchemy does not track mutation *inside* the
value, so an in-place edit is silently not persisted. Declaring the model
`frozen=True`, as above, turns that silent loss into a `ValidationError`, and
makes the model hashable. Persist a change by assigning a new value:

```python
article.content = article.content.model_copy(update={"views": 2})
```

Freezing is a property of your model, so the type cannot impose it; if you need
mutation to be tracked instead, use `sqlalchemy.ext.mutable`.

## Alembic

Importing the dialect registers a MonetDB migration implementation, so Alembic
works without further configuration. Schema operations, autogenerate, and
`PydanticJSON` columns are covered by the test suite.

No configuration is needed: importing the dialect, which `create_engine` does
for you, registers the implementation. This works in offline mode too.

Changing a column's type is supported. MonetDB spells it
`ALTER TABLE t ALTER COLUMN c <type>`, without the `TYPE` keyword most backends
use. It refuses to alter a column that other objects depend on, such as one
carrying a primary key, and says so.

A `PydanticJSON` column autogenerates as `sa.JSON()`, since a migration
describes the database schema. Rendering the model instead would make
migrations import application code and break as soon as that model moved.

## Development

Python 3.13 or newer and `uv` are required.

```console
uv sync --all-groups
uv run ruff check .
uv run ruff format --check .
uv run pyright
uv run pytest
uv build
```

Integration tests and the dialect compliance suite need a server. `compose.yaml`
pins the same native ARM64 MonetDB image the driver is developed against:

```console
docker compose up --wait
MONETDB_TEST_URI=monetdb://monetdb:monetdb@localhost:50001/test uv run pytest tests
uv run pytest suite -o addopts=""
docker compose down -v
```

`uv run pytest` alone skips every test that needs a server, so the unit gate
runs without one.

## License

MIT
