> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-revert-104359-revert-104251-parquet-single.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> ClickHouse SQLAlchemy and Alembic support

# SQLAlchemy support

ClickHouse Connect includes the `clickhousedb` SQLAlchemy dialect on top of the core driver. It supports SQLAlchemy 1.4.40 and later, including SQLAlchemy 2.x, with a focus on Core queries, ClickHouse DDL, reflection, and simple ORM inserts.

Install the SQLAlchemy dependencies with the package extra:

```bash theme={null}
pip install "clickhouse-connect[sqlalchemy]"
```

<h2 id="sqlalchemy-connect">
  Connect with SQLAlchemy
</h2>

Create an engine with either the `clickhousedb://` or `clickhousedb+connect://` URL form:

```python theme={null}
from sqlalchemy import create_engine, text

engine = create_engine(
    "clickhousedb://user:password@host:8123/mydb?compression=zstd"
)

with engine.connect() as conn:
    version = conn.execute(text("SELECT version()")).scalar_one()
    print(version)
```

URL query parameters can contain ClickHouse settings, ClickHouse Connect client options such as `compression`, `query_limit`, and timeouts, or HTTP/TLS options such as `ca_cert`. Prefix a ClickHouse setting with `ch_` to force it to be treated as a server setting when needed, for example `ch_http_max_field_name_size=99999`.

See [Connection arguments and settings](/integrations/language-clients/python/driver-api#connection-arguments) for the available client options.

<h3 id="sqlalchemy-per-query-settings">
  Per-query settings
</h3>

Pass ClickHouse settings through SQLAlchemy execution options. Settings can be set on an engine, connection, or statement. A statement value takes precedence over a connection or engine value with the same key.

```python theme={null}
from sqlalchemy import text

stmt = text("SELECT getSetting('max_threads')").execution_options(
    settings={"max_threads": 2}
)

with engine.connect() as conn:
    value = conn.execute(stmt).scalar_one()
```

<h3 id="sqlalchemy-per-query-read-formats">
  Per-query read formats
</h3>

Set ClickHouse read formats on an engine, connection, or statement through SQLAlchemy execution options with `query_formats`, with statement formats applied first so they override matching connection or engine keys and wildcards.

```python theme={null}
from sqlalchemy import text

stmt = text("SELECT user_uuid FROM users").execution_options(
    query_formats={"UUID": "string"}
)

with engine.connect() as conn:
    rows = conn.execute(stmt).all()
```

<h3 id="sqlalchemy-server-side-parameters">
  Server-side parameters
</h3>

SQLAlchemy normally renders client-side parameters. Opt in to ClickHouse server-side parameters when creating the engine:

```python theme={null}
engine = create_engine(
    "clickhousedb://user:password@host:8123/mydb",
    server_side_params=True,
)
```

In this mode every bound value must have a ClickHouse-compatible SQLAlchemy type. Supported `IN` lists become typed ClickHouse `Array` parameters. The compiler raises `CompileError` when it cannot derive a compatible type or safely process a bind.

Bind names must be ClickHouse ASCII BareWord names. Names that start and end with `$` are rejected because the core driver reserves them for raw binary query parameters.

<h2 id="sqlalchemy-core-queries">
  Core queries
</h2>

The dialect supports SQLAlchemy Core `SELECT` queries with joins, filters, ordering, limits and offsets, `DISTINCT`, and compound selects.

SQLAlchemy `union()`, `intersect()`, and `except_()` compile to ClickHouse `UNION DISTINCT`, `INTERSECT DISTINCT`, and `EXCEPT DISTINCT`. Their `union_all()`, `intersect_all()`, and `except_all()` counterparts compile to the corresponding `ALL` operators. This explicit mapping preserves SQLAlchemy's duplicate semantics regardless of ClickHouse set-operation defaults.

```python theme={null}
from sqlalchemy import MetaData, Table, select

metadata = MetaData(schema="mydb")
users = Table("users", metadata, autoload_with=engine)
orders = Table("orders", metadata, autoload_with=engine)
events = Table("events", metadata, autoload_with=engine)

stmt = (
    select(users.c.name, orders.c.product)
    .select_from(users.join(orders, users.c.id == orders.c.user_id))
    .order_by(users.c.name)
    .limit(10)
)

with engine.connect() as conn:
    rows = conn.execute(stmt).all()
```

Lightweight `DELETE` is supported and requires an explicit `WHERE` clause:

```python theme={null}
from sqlalchemy import delete

stmt = delete(users).where(users.c.name.like("%temporary%"))
with engine.connect() as conn:
    conn.execute(stmt)
```

<h3 id="sqlalchemy-literal-rendering">
  Literal rendering
</h3>

When SQLAlchemy inlines a bound value through `literal_binds` or `literal_execute`, the dialect uses ClickHouse quoting for generic string types and ClickHouse types. This also applies through `TypeDecorator` wrappers and `with_variant()` selections. String values retain percent signs and backslashes even when other bound parameters remain.

<h3 id="sqlalchemy-json-type-hints">
  JSON type hints
</h3>

Declare typed JSON paths with the `typed_paths` mapping. A path type can be a ClickHouse SQLAlchemy type class, a configured instance, or a ClickHouse type name string. Type name strings support types without a SQLAlchemy constructor, such as `Dynamic`, and can still be used for complex configured type expressions. They preserve names in a named `Tuple`.

Type name strings can contain configured nested JSON types such as ``Array(JSON(`child` UInt32))``. Recognized ClickHouse type names are case-insensitive in these strings and are emitted with their canonical capitalization. A string must contain one complete type expression. Trailing text and malformed nested JSON arguments are rejected.

An empty `Tuple()` is not supported as a JSON typed path because ClickHouse cannot serialize it through a JSON column's Native format. The core driver supports `Tuple()` in query and insert columns at any position, including nested in positional or named tuples, inside `Array`, and as `Nullable(Tuple())` where enabled by the server.

```python theme={null}
from sqlalchemy import Column, MetaData, Table

from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes import JSON, UInt32

events = Table(
    "events",
    MetaData(),
    Column(
        "payload",
        JSON(
            typed_paths={
                "event.id": UInt32,
                "details": "Tuple(id UInt32, label Nullable(String))",
                "attributes": "Variant(String, Array(String))",
            },
            max_dynamic_paths=256,
            max_dynamic_types=16,
            skip_paths=["internal.debug"],
            skip_regexps=[r"^private\."],
        ),
    ),
)
```

For simple Python identifier paths, keyword arguments are shorthand for `typed_paths`, for example `JSON(user_id=UInt32)`. Use `typed_paths` for dotted paths, spaces, backticks, `%2E` encoded dots, or names that match constructor options. A typed path named `SKIP` is supported through the mapping. Keys in `typed_paths` and values in `skip_paths` are decoded names. Leading or trailing backticks and double quotes are treated as literal path characters, not as pre-applied SQL quoting. Inside a raw type string, backticks and double quotes are ClickHouse identifier syntax.

Up to 1000 typed paths can be configured. `max_dynamic_paths` accepts 0 through 10000. `max_dynamic_types` accepts 0 through 254. These ranges also apply inside raw nested JSON type strings. Explicit server defaults of 1024 and 32 are omitted from generated DDL. Plain skip paths are deduplicated. Regular expression strings are not validated by Python because ClickHouse uses RE2 syntax. Duplicate regular expressions are preserved.

A plain skip path cannot be named exactly `REGEXP` because ClickHouse reserves that token for `SKIP REGEXP`. Names such as `REGEXP_foo` remain valid. In a raw JSON type string, a plain `SKIP` operand must be one ClickHouse identifier or a dot-separated compound identifier. An unquoted compound identifier cannot start with `REGEXP`; quote that first component when it is path data. `SKIP REGEXP` must have one single-quoted string literal. Quote identifier parts with backticks or double quotes when they contain spaces or punctuation. Raw JSON type hints support `Variant(...)`; standalone `Variant` has no public SQLAlchemy constructor. `Variant` members are ordered and deduplicated by the same canonical names used by ClickHouse.

The constructor orders arguments in the same canonical form returned by ClickHouse. Reflected types, SQLAlchemy type copies, and Alembic autogeneration preserve the configuration.

<h3 id="sqlalchemy-json-subcolumns">
  JSON subcolumns
</h3>

For a column declared or reflected as ClickHouse `JSON`, use square brackets to select one segment of a storage-backed subcolumn path at a time:

```python theme={null}
from sqlalchemy import Column, MetaData, Table, select

from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes import JSON, UInt32

events = Table(
    "events",
    MetaData(),
    Column("payload", JSON),
)

request_id = events.c.payload["context"]["request"].subcolumn(
    "id",
    type_=UInt32,
)

stmt = select(
    events.c.payload["severity"].label("severity"),
    request_id.label("request_id"),
)
```

`payload["severity"]` compiles to ClickHouse dotted identifier syntax. Each part is quoted separately, for example `` `events`.`payload`.`severity` ``. It reads ClickHouse's stored JSON subcolumn and does not call `getSubcolumn`. Chain `[]` or `.subcolumn()` once for each path segment. Each segment must be a non-empty string.

Passing `type_` to `.subcolumn()` wraps the dotted path in a SQL `CAST` and assigns that type to the SQLAlchemy expression. Without `type_`, `.subcolumn("segment")` behaves like `["segment"]`.

An untyped path has ClickHouse's `Dynamic` type. ClickHouse does not allow `Dynamic` values directly in `ORDER BY` or `GROUP BY`. Pass `type_` when a subcolumn is used there.

For statically typed code, import `json_subcolumn` from `clickhouse_connect.cc_sqlalchemy`. The helper also takes one segment at a time and preserves the Python result type from `type_`:

```python theme={null}
from clickhouse_connect.cc_sqlalchemy import json_subcolumn

context = json_subcolumn(events.c.payload, "context")
request = json_subcolumn(context, "request")
request_id = json_subcolumn(request, "id", type_=UInt32)
```

In this example, type checkers see `request_id` as `ColumnElement[int]`.

Each segment is quoted independently, including names with spaces or backticks. Backticks do not make a dot literal to ClickHouse JSON path handling. When `json_type_escape_dots_in_keys` is enabled, use ClickHouse's `%2E` encoding for literal dots in keys. Access a key named `a.b` as `payload["a%2Eb"]`, not `payload["a.b"]`.

<h3 id="sqlalchemy-query-extensions">
  ClickHouse query extensions
</h3>

Import `select` from `clickhouse_connect.cc_sqlalchemy` to expose typed ClickHouse methods to static type checkers. The standard `sqlalchemy.select` also has these methods at runtime.

```python theme={null}
from clickhouse_connect.cc_sqlalchemy import select

stmt = (
    select(events.c.user_id, events.c.event_type)
    .final()
    .prewhere(events.c.event_date >= "2026-01-01")
    .sample(0.1)
    .limit_by([events.c.user_id], 3)
)
```

The ClickHouse `Select` methods are:

| Method                                   | SQL feature                                                                      |
| ---------------------------------------- | -------------------------------------------------------------------------------- |
| `.final()`                               | `FINAL` for a table                                                              |
| `.sample(value)`                         | `SAMPLE`, using a fraction, row count, or expression                             |
| `.prewhere(expression)`                  | `PREWHERE`; repeated calls combine with `AND`                                    |
| `.limit_by(columns, limit, offset=None)` | `LIMIT ... BY`                                                                   |
| `.array_join(...)`                       | `ARRAY JOIN`                                                                     |
| `.left_array_join(...)`                  | `LEFT ARRAY JOIN`                                                                |
| `.ch_join(...)`                          | ClickHouse joins with `strictness`, `distribution`, `using`, and `cross` options |
| `.cte(name, materialized=True)`          | `WITH name AS MATERIALIZED (...)`                                                |

SQLAlchemy's `Select.with_hint()` is a table hint API. The ClickHouse dialect does not render table hints. An applicable wildcard or `clickhousedb` hint emits an `SAWarning` and leaves the generated SQL unchanged. Use `final()`, `sample()`, `prewhere()`, or `limit_by()` for those ClickHouse clauses.

`Select.with_statement_hint()` is a raw tail directive API. It appends the supplied text to the end of the `SELECT` without ClickHouse-specific validation. This remains available for trusted static SQL such as `SETTINGS max_threads=1`:

```python theme={null}
stmt = select(events.c.id).with_statement_hint("SETTINGS max_threads=1")
```

For ClickHouse settings, prefer execution options so the driver handles the settings separately from the SQL text:

```python theme={null}
stmt = select(events.c.id).execution_options(settings={"max_threads": 1})
```

For example, a ClickHouse `GLOBAL ANY LEFT JOIN` can be chained without nesting a custom `FromClause`:

```python theme={null}
stmt = (
    select(events.c.id, users.c.name)
    .select_from(events)
    .ch_join(
        users,
        events.c.user_id == users.c.id,
        isouter=True,
        strictness="ANY",
        distribution="GLOBAL",
    )
)
```

Use the explicit `Lambda` construct for ClickHouse higher-order functions:

```python theme={null}
from sqlalchemy import column, func

from clickhouse_connect.cc_sqlalchemy import Lambda, select

stmt = select(
    func.arrayMap(
        Lambda("x", column("x") * 2),
        events.c.metrics,
    ).label("doubled")
)
```

The standard SQLAlchemy `values()` construct compiles to ClickHouse's `VALUES` table-function syntax, including when used in a common table expression. The CTE form requires SQLAlchemy 2.0.42 or later, where `Values.cte()` was added.

<h3 id="sqlalchemy-materialized-ctes">
  Materialized CTEs
</h3>

By default ClickHouse inlines a common table expression, so a CTE referenced more than once has its body executed once per reference. Pass `materialized=True` to `.cte()` to emit `WITH <name> AS MATERIALIZED (...)`, which computes the body once:

```python theme={null}
from sqlalchemy import func

from clickhouse_connect.cc_sqlalchemy import select

ranked = (
    select(book.c.book_id, func.row_number().over(order_by=book.c.score.desc()).label("result_rank"))
    .where(book.c.genre == "sci-fi")
    .order_by(book.c.score.desc())
    .limit(100)
    .cte("ranked", materialized=True)
)

stmt = (
    select(book.c.book_id, ranked.c.result_rank)
    .select_from(book)
    .ch_join(ranked, book.c.book_id == ranked.c.book_id, strictness="ANY")
    .where(book.c.book_id.in_(select(ranked.c.book_id)))
    .execution_options(settings={"enable_materialized_cte": 1, "enable_analyzer": 1})
)
```

The server materializes the CTE only when the keyword is present, `enable_materialized_cte=1`, and the analyzer is enabled. Set `enable_materialized_cte` on the statement, connection, or engine as shown in [Per-query settings](#sqlalchemy-per-query-settings). The analyzer is enabled by default on every server that supports this feature, so setting `enable_analyzer=1` explicitly is defensive. `enable_materialized_cte` is an experimental ClickHouse setting. With `enable_materialized_cte=0` or `enable_analyzer=0`, the query succeeds and returns the same rows. ClickHouse silently ignores `MATERIALIZED` and inlines the CTE again, so a forgotten setting costs performance without raising anything. Materialized CTEs require ClickHouse 26.3 or later. Older servers reject the keyword as a syntax error.

For a statement built with the standard `sqlalchemy.select`, use the module-level `cte()` instead. It takes the statement as its first argument and otherwise mirrors `Select.cte()`:

```python theme={null}
from sqlalchemy import select as sa_select

from clickhouse_connect.cc_sqlalchemy import cte

ranked = cte(sa_select(book.c.book_id), "ranked", materialized=True)
```

The keyword renders only on the ClickHouse dialect, so a statement shared with another backend compiles unchanged there.

ClickHouse does not support recursive materialized CTEs. The SQLAlchemy helpers raise `ValueError` when `recursive=True` and `materialized=True` are both set.

<h2 id="sqlalchemy-ddl-reflection">
  DDL and reflection
</h2>

ClickHouse Connect provides ClickHouse data types, table engines, dictionary constructs, database DDL, and table reflection.

Standalone `Variant` columns reflect through an internal SQLAlchemy type, and Alembic autogenerate preserves their canonical raw type names without repeated type changes. `Geometry` and `MultiPoint` columns reflect as public SQLAlchemy types.

```python theme={null}
import sqlalchemy as db
from sqlalchemy import MetaData

from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes import DateTime64, String, UInt32
from clickhouse_connect.cc_sqlalchemy.ddl.custom import CreateDatabase
from clickhouse_connect.cc_sqlalchemy.ddl.tableengine import MergeTree

with engine.connect() as conn:
    conn.execute(CreateDatabase("example_db", exists_ok=True))

    metadata = MetaData(schema="example_db")
    events = db.Table(
        "events",
        metadata,
        db.Column("id", UInt32, primary_key=True),
        db.Column("user", String),
        db.Column("created_at", DateTime64(3)),
        MergeTree(order_by="id"),
    )
    events.create(conn)

    reflected = db.Table("events", MetaData(schema="example_db"), autoload_with=conn)
    assert reflected.engine is not None
```

Reflected columns carry `server_default` for `DEFAULT` expressions and dialect-specific attributes such as `clickhouse_codec`, `clickhouse_ttl`, `clickhouse_materialized`, and `clickhouse_alias` when present.

String values in `DEFAULT`, `MATERIALIZED`, `ALIAS`, and `TTL` clauses use ClickHouse string escaping. The same escaping applies to table, dictionary, and column comments, including comments emitted by Alembic.

MergeTree key arguments such as `order_by`, `partition_by`, `primary_key`, `sample_by`, and `ttl` accept SQLAlchemy column and SQL expressions as well as plain strings.

<h2 id="sqlalchemy-inserts">
  Inserts and basic ORM use
</h2>

Core inserts and simple ORM models are supported. Prefer Core inserts for bulk data paths.

```python theme={null}
with engine.connect() as conn:
    conn.execute(
        events.insert(),
        [
            {"id": 13, "user": "user_1"},
            {"id": 79, "user": "user_2"},
        ],
    )
```

```python theme={null}
import sqlalchemy as db
from sqlalchemy import MetaData
from sqlalchemy.orm import Session, declarative_base

from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes import String, UInt32
from clickhouse_connect.cc_sqlalchemy.ddl.tableengine import MergeTree

Base = declarative_base(metadata=MetaData(schema="example_db"))


class User(Base):
    __tablename__ = "users"
    __table_args__ = (MergeTree(order_by=["id"]),)

    id = db.Column(UInt32, primary_key=True)
    name = db.Column(String)


Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(User(id=13, name="user_1"))
    session.bulk_save_objects([User(id=79, name="user_2")])
    session.commit()
```

<h2 id="sqlalchemy-alembic">
  Alembic migrations
</h2>

ClickHouse Connect includes Alembic integration for ClickHouse schema migrations. Install it with:

```bash theme={null}
pip install "clickhouse-connect[alembic]"
```

Import `clickhouse_connect.cc_sqlalchemy.alembic` in Alembic's `env.py` to register the dialect integration. Autogenerate supports common table evolution, including table creation and removal, column add/alter/drop, defaults, and comments. Use manual operations for table and column renames. Review every generated migration before applying it.

ClickHouse-specific `op.*` helpers cover:

* Data skipping indexes, including add, materialize, and drop operations.
* Projections, including add, materialize, and drop operations.
* MergeTree table setting modification and reset.
* Materialized view creation and removal.
* Dictionary creation, removal, and reload.

ClickHouse data skipping indexes are not SQLAlchemy indexes. `Index`, `Column(index=True)`, `op.create_index`, and `op.drop_index` are rejected to avoid partial or incorrect DDL. Use `op.add_clickhouse_index` and `op.drop_clickhouse_index`.

See the complete [Alembic worked example](https://github.com/ClickHouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/alembic/WORKED_EXAMPLE.md). Users migrating from `clickhouse-sqlalchemy` should also read the [migration guide](https://github.com/ClickHouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/MIGRATING_FROM_CLICKHOUSE_SQLALCHEMY.md).

<h2 id="scope-and-limitations">
  Scope and limitations
</h2>

* ClickHouse does not provide traditional transactions through this HTTP dialect. `engine.begin()` and `Session.commit()` organize Python-side work, but commit and rollback are no-ops on the server.
* `UPDATE`, two-phase transactions, sequences, `RETURNING`, and advanced isolation levels are not implemented by the dialect. Use explicit ClickHouse SQL for server mutations when needed.
* `Column(..., primary_key=True)` supplies SQLAlchemy object identity. It does not create a server-side uniqueness constraint. Define sorting and optional primary-key expressions through the table engine.
* Traditional foreign-key, unique-constraint, and standard index metadata are not available because ClickHouse does not enforce those constraints.
* ORM relationship management, unit-of-work updates, cascades, and eager or lazy relationship loading are outside the supported ORM scope.