> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-vortex-format.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-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-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 |

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.x, where `Values.cte()` was added).

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