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:

pip install "clickhouse-connect[sqlalchemy]"

Connect with SQLAlchemy

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

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 for the available client options.

Per-query settings

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.

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

Server-side parameters

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

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.

Core queries

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

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:

from sqlalchemy import delete

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

ClickHouse query extensions

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.

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:

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:

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

DDL and reflection

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

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.

Inserts and basic ORM use

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

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

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

Alembic migrations

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

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. Users migrating from clickhouse-sqlalchemy should also read the migration guide.

Scope and limitations

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.

Home

JavaScript

Python

Go

Java

C#

Rust

C++

C

Third-party clients

Connect with SQLAlchemy

Per-query settings

Server-side parameters

Core queries

ClickHouse query extensions

DDL and reflection

Inserts and basic ORM use

Alembic migrations

Scope and limitations

​Connect with SQLAlchemy

​Per-query settings

​Server-side parameters

​Core queries

​ClickHouse query extensions

​DDL and reflection

​Inserts and basic ORM use

​Alembic migrations

​Scope and limitations

Connect with SQLAlchemy

Per-query settings

Server-side parameters

Core queries

ClickHouse query extensions

DDL and reflection

Inserts and basic ORM use

Alembic migrations

Scope and limitations