> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/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](/docs/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.

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

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

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

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.

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

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.