SQLAlchemy Support
ClickHouse Connect includes a SQLAlchemy dialect (clickhousedb
) built on top of the core driver. It targets SQLAlchemy Core APIs and supports SQLAlchemy 1.4.40+ and 2.0.x.
Connect with SQLAlchemy
Create an engine using either clickhousedb://
or clickhousedb+connect://
URLs. Query parameters map to ClickHouse settings, client options, and HTTP/TLS transport options.
Notes on URL/query parameters:
- ClickHouse settings: pass as query parameters (for example,
use_skip_indexes=0
). - Client options:
compression
(alias forcompress
),query_limit
, timeouts, and more. - HTTP/TLS options: options for the HTTP pool and TLS (for example,
ch_http_max_field_name_size=99999
,ca_cert=certifi
).
See Connection arguments and Settings in the sections below for the full list of supported options. These can also be supplied via the SQLAlchemy DSN.
Core queries
The dialect supports SQLAlchemy Core SELECT
queries with joins, filters, ordering, limits/offsets, and DISTINCT
.
Lightweight DELETE
with a required WHERE
clause is supported:
DDL and reflection
You can create databases and tables using the provided DDL helpers and type/engine constructs. Table reflection (including column types and engine) is supported.
Reflected columns include dialect-specific attributes such as clickhousedb_default_type
, clickhousedb_codec_expression
, and clickhousedb_ttl_expression
when present on the server.
Inserts (Core and basic ORM)
Inserts work via SQLAlchemy Core as well as with simple ORM models for convenience.
Scope and limitations
- Core focus: Enable SQLAlchemy Core features like
SELECT
withJOIN
s (INNER
,LEFT OUTER
,FULL OUTER
,CROSS
),WHERE
,ORDER BY
,LIMIT
/OFFSET
, andDISTINCT
. DELETE
withWHERE
only: The dialect supports lightweightDELETE
but requires an explicitWHERE
clause to avoid accidental full-table deletes. To clear a table, useTRUNCATE TABLE
.- No
UPDATE
: ClickHouse is append-optimized. The dialect does not implementUPDATE
. If you need to change data, apply transformations upstream and re-insert, or use explicit text SQL (for example,ALTER TABLE ... UPDATE
) at your own risk. - DDL and reflection: Creating databases and tables is supported, and reflection returns column types and table engine metadata. Traditional PK/FK/index metadata is not present because ClickHouse does not enforce those constraints.
- ORM scope: Declarative models and inserts via
Session.add(...)
/bulk_save_objects(...)
work for convenience. Advanced ORM features (relationship management, unit-of-work updates, cascading, eager/lazy loading semantics) are not supported. - Primary key semantics:
Column(..., primary_key=True)
is used by SQLAlchemy for object identity only. It does not create a server-side constraint in ClickHouse. DefineORDER BY
(and optionalPRIMARY KEY
) via table engines (for example,MergeTree(order_by=...)
). - Transactions and server features: Two-phase transactions, sequences,
RETURNING
, and advanced isolation levels are not supported.engine.begin()
provides a Python context manager for grouping statements but performs no actual transaction control (commit/rollback are no-ops).