Skip to main content
ClickHouse Connect includes the clickhousedb SQLAlchemy dialect on top of the core driver. The synchronous dialect supports SQLAlchemy 1.4.40 and later, including SQLAlchemy 2.x, with a focus on Core queries, ClickHouse DDL, reflection, and simple ORM inserts. The async dialect requires SQLAlchemy 2.0.44 or later. Install the SQLAlchemy dependencies with the package extra:

Connect with SQLAlchemy

Create an engine with either the clickhousedb:// or clickhousedb+connect:// URL form:
Use these explicit URL forms when clickhouse-sqlalchemy is also installed. Importing clickhouse_connect.cc_sqlalchemy leaves that package’s clickhouse:// registration in place. If another installed provider prevents our alias registration, the import emits a UserWarning that recommends an explicit ClickHouse Connect URL. Existing runtime registrations are left untouched without a warning. If no other provider claims clickhouse://, this import makes it available as a ClickHouse Connect compatibility alias.

ClickHouse session IDs

Each pooled connection in either the synchronous or async dialect generates a distinct ClickHouse session ID by default. When requests for that connection reach the same ClickHouse server process, settings changed with SET and temporary tables persist for that connection. Named-session state and same-session overlap checks are process-local. On one server process, an overlapping request for the same user and session ID is rejected immediately with server code 373 instead of being queued. If you configure a fixed session_id, use pool_size=1, max_overflow=0 or serialize access before requests reach ClickHouse. In ClickHouse Cloud or other load-balanced deployments, requests with the same session ID can reach different servers, so do not rely on a fixed session_id as distributed state or as a distributed mutex.

Async connections

The async dialect requires SQLAlchemy 2.0.44 or later and uses the native ClickHouse Connect AsyncClient. Install its dependencies and create an async engine with the clickhousedb+async:// URL:
Results are buffered. Server-side cursors are disabled, so AsyncConnection.stream() raises InvalidRequestError. AsyncSession.stream() is accepted by SQLAlchemy, but the dialect buffers the full result before returning it. Use the native AsyncClient streaming methods for large results. The raw native client is available as driver_connection while its SQLAlchemy connection is checked out:
Do not use the SQLAlchemy connection concurrently with its raw client. Finish raw-client streams before leaving the SQLAlchemy connection block, and do not keep the raw client after the connection returns to the pool. SQLAlchemy owns the borrowed client’s lifecycle, so never call client.close() or any of its private lifecycle methods. SQLAlchemy’s pool owns connection concurrency. Each pooled connection owns one native async client and defaults its aiohttp connector limits to one connection and one connection per host. Set connector_limit, connector_limit_per_host, or keepalive_timeout in the URL or connect_args to override those transport settings. With pool_pre_ping=True, SQLAlchemy checks reused connections with SELECT 1 when a pooled connection is checked out. Async SQLAlchemy executemany inserts currently send one HTTP request for each parameter set instead of using the driver’s Native bulk insert protocol. Use this path only for small batches. For bulk data, use the pool-owned driver_connection access pattern above and await client.insert() before returning the SQLAlchemy connection to the pool. Because async executemany uses query parameter binding, naive datetime values follow naive_datetime_binding, not the naive_datetime_insert setting used by synchronous Native executemany. Typed SQLAlchemy DateTime64 binds preserve fractional seconds with both client-side and server-side parameters. Untyped %s or %(name)s parameters passed to exec_driver_sql() retain the default whole-second formatting for naive datetime values. Use timezone-aware values for unambiguous timezone behavior. Use client.insert() for Native bulk semantics. Create and dispose an async engine in the event loop where it is used. Return every checked-out connection, then await engine.dispose() during shutdown and before using the engine from another event loop. If the engine’s owning loop has already closed, await engine.dispose() in the current loop before reuse. aiohttp may still report an unclosed transport when cleanup begins only after the owning loop has closed, so dispose before transfer when possible. pool_pre_ping=True is not a replacement for disposal when moving a pooled async engine between event loops. To share one engine across event loops without retaining loop-bound connections, configure poolclass=NullPool. If disposal runs while a connection is still checked out, the dialect closes that connection when it is returned or garbage collected. Do not call engine.sync_engine.dispose() from synchronous code. SQLAlchemy cannot await async connection cleanup there and may log the error instead of closing pooled transports. 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. Run synchronous SQLAlchemy helpers such as DDL and inspection through AsyncConnection.run_sync():

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.

Per-query read formats

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.

Error handling

Errors raised by the driver through a SQLAlchemy connection use the DB-API classes exported from clickhouse_connect.dbapi. They are the same class objects as the corresponding classes in clickhouse_connect.driver.exceptions, so SQLAlchemy wraps them in the matching sqlalchemy.exc.DBAPIError subclass. StreamFailureError is an OperationalError and is wrapped as sqlalchemy.exc.OperationalError. If a table does not exist, column inspection and table reflection raise sqlalchemy.exc.NoSuchTableError. The exception also inherits from sqlalchemy.exc.NoResultFound so existing handlers still catch it. Its message is Table <database>.<table_name> does not exist. SQL execution follows the driver’s HTTP retry policy. Native bulk inserts use the insert retry policy. If caller cancellation can interrupt an explicit AsyncConnection.invalidate(), run invalidation in an owned task and wait for that task before propagating cancellation. This lets SQLAlchemy finish its connection-record bookkeeping:
Do not use the connection while its invalidation task is still running. If a direct await connection.invalidate() is cancelled and connection.invalidated remains false, await connection.invalidate() again to finish cleanup before using or closing the connection.

Server-side parameters

SQLAlchemy normally renders client-side parameters. Opt in to ClickHouse server-side parameters when creating the engine:
Use the same server_side_params=True argument with create_async_engine() for the async dialect. 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.

Core queries

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.
Lightweight DELETE is supported and requires an explicit WHERE clause:

Literal rendering

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. Python datetime values with a ClickHouse DateTime64 SQLAlchemy type retain their microseconds in client-side parameters and inline literals, including nullable values and values nested in arrays and tuples. ClickHouse applies the declared precision. Python datetime provides up to six fractional digits. Plain DateTime values retain whole-second formatting. For a text() statement, supply the type explicitly with bindparam("ts", type_=DateTime64(6)) to preserve fractional seconds. A pandas.Timestamp value bound with DateTime64(9) retains its nanoseconds in parameters and inline literals. SQLAlchemy column types must match the server schema. Declaring DateTime64 over a server DateTime column renders fractional seconds and can raise conversion errors on insert and in IN comparisons. On SQLAlchemy 2.x, inline literals of generic sqlalchemy.ARRAY types containing ClickHouse Tuple items need dimensions=1, or the appropriate higher dimension count for nested arrays, so SQLAlchemy treats each tuple as one item. SQLAlchemy 1.4 does not support inline literals for generic ARRAY types. Typed named Tuple parameters accept dictionaries whose keys exactly match the declared fields. The client uses the declaration’s field order for bound parameters and inline literals, including named tuples inside arrays and other tuples. This applies to synchronous and asynchronous inserts, predicates, and both parameter modes. Missing or extra fields raise ProgrammingError in query parameter binding. Native bulk inserts retain their existing handling. Dictionaries passed to untyped exec_driver_sql() parameters keep the configured JSON or map formatting. For named tuples inside Map values, set common.set_setting("dict_parameter_format", "map"). This also preserves fractional seconds for typed DateTime64 values inside maps in bound parameters and inline literals. If a named datetime parameter is reused, every occurrence needs a compatible DateTime64 bind type to preserve fractions. An untyped occurrence or a conflicting type keeps whole-second formatting. Set type_=DateTime64(6) on each bindparam, or use distinct parameter names with the appropriate types. The same applies to a reused bind name whose named Tuple occurrences declare different field names. The client falls back to positional handling for that name, so a dictionary value is not reordered and the server rejects it. Use distinct parameter names or pass a positional tuple.

Named tuples

Declare named tuple fields with an elements mapping. Keys must be non-empty strings, and values can be ClickHouse SQLAlchemy type classes or configured instances. Fields retain the mapping’s iteration order, and names are quoted automatically.
Use Tuple(UInt32, String) or Tuple(elements=[UInt32, String]) for unnamed tuples. Positional elements and the elements keyword cannot be combined.

JSON type hints

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

JSON subcolumns

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:
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_:
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"].

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.
The ClickHouse Select methods are: 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:
For ClickHouse settings, prefer execution options so the driver handles the settings separately from the SQL text:
For example, a ClickHouse GLOBAL ANY LEFT JOIN can be chained without nesting a custom FromClause:
Use the explicit Lambda construct for ClickHouse higher-order functions:
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.

Materialized CTEs

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

DDL and reflection

ClickHouse Connect provides ClickHouse data types, table engines, dictionary constructs, database DDL, and table reflection. Inspector.get_table_names() includes all view kinds for compatibility. MetaData.reflect() includes them by default, and only can select a view by name. Use Inspector.get_view_names() for non-materialized views and Inspector.get_materialized_view_names() for materialized views. Both methods accept schema and work on SQLAlchemy 1.4.40 and later, including 2.x. Inspector.get_view_definition() returns the stored SELECT of a view or materialized view and raises NoSuchTableError for tables and missing names. A reflected view has no engine. Its Table carries clickhousedb_table_type set to view, materialized_view, window_view, or live_view. Nullable() and LowCardinality() from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes accept a ClickHouse SQLAlchemy type class or instance and preserve its concrete type for type checkers. For example, Nullable(String) returns a String instance and can be passed directly to Column. Type checkers infer list[String] for [Nullable(String)]. If the list also holds other ClickHouse types, annotate it as list[ChSqlaType] and import ChSqlaType from clickhouse_connect.cc_sqlalchemy.datatypes.base. 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.
Reflected columns carry server_default for DEFAULT expressions. Reflected SQLAlchemy objects use dialect-specific options such as clickhousedb_codec, clickhousedb_ttl, clickhousedb_materialized, and clickhousedb_alias when present. The public dictionaries returned by Inspector.get_columns() retain the corresponding clickhouse_* keys. In these dictionaries, server_default, clickhouse_materialized, clickhouse_alias, and clickhouse_ttl are SQL strings. To build a Column from them, wrap each one in sqlalchemy.text(). Use clickhousedb_* for new table and column declarations. Driver-generated metadata and Alembic revisions use these names. ClickHouse Connect also reads explicit legacy clickhouse_* options, but SQLAlchemy validates them against the dialect that owns clickhouse. In a mixed install, ClickHouse Connect options such as clickhouse_engine, clickhouse_ttl, clickhouse_settings, clickhouse_table_type, and clickhouse_dictionary_* can raise ArgumentError. Rename them to clickhousedb_*. ClickHouse Connect reads defaults only from clickhousedb. Register them with Table.argument_for("clickhousedb", ...) or Column.argument_for("clickhousedb", ...). Alembic operation arguments such as op.add_column(..., clickhouse_settings={...}) keep their existing names. 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. Memory(), Log(), StripeLog(), TinyLog(), Null(), and Set() accept zero arguments and round-trip through Alembic autogeneration. The existing dictionary argument remains supported. Use settings={...} to supply engine settings. SummingMergeTree and ReplicatedSummingMergeTree accept an optional, keyword-only columns argument. Existing positional arguments keep their meaning, so SummingMergeTree("id") still sets ORDER BY id.
Pass a string, a SQLAlchemy column, a mapped column attribute, or a non-empty list or tuple of those values. List and tuple string items are quoted as identifiers. A scalar string supplies raw SQL, such as "delta" or "(delta, n_tx)". The server requires identifiers for these columns. Omit columns to let ClickHouse select the columns to sum. Reflection and Alembic autogeneration preserve an explicit column list.

Inserts and basic ORM use

Core inserts and simple ORM models are supported. For the synchronous dialect, prefer Core executemany inserts for compatible bulk data paths. For async bulk inserts, use the native AsyncClient.insert() path described in Async connections.
For the synchronous dialect, plain Core executemany inserts generated by the SQLAlchemy compiler use one Native bulk insert. Async executemany sends one request per parameter set, as described in Async connections. Raw SQL and inserts with expressions or other semantics that cannot be routed safely preserve the original SQL and execute once for each parameter set. If a later parameter set fails, rows written by earlier parameter sets remain committed. Explicit multi-row insert(events).values([...]) statements work with dictionary rows, tuples in table column order, and per-row SQL expressions. Pandas to_sql(method="multi") uses this form. It inserts the rows but returns 0 because textual INSERT statements report a row count of 0 through the DB-API cursor. SQLAlchemy determines the column list from the first row. Extra dictionary keys in later rows and tuple values outside that selected column list are ignored. A later row missing a selected value fails compilation. Give every row the same columns. With the default HTTP form limits in ClickHouse 26.4 and newer, server_side_params=True is suitable only for small explicit batches, below about 1000 bind values with headroom for other fields. Server configuration can raise this ceiling. For large plain batches with the synchronous dialect, pass rows as the second argument to execute() so the driver can use its Native bulk insert path. For async bulk data, await the native AsyncClient.insert() method.

Views and SQL functions

Alembic provides explicit create, replace, and drop helpers for ordinary views and SQL user-defined functions:
Both create helpers accept if_not_exists=False and or_replace=False. Passing both as True raises ValueError. The SQL bodies are strings passed through to ClickHouse. Names are quoted as identifiers unless already quoted; a function name is one identifier, even if it contains a dot. SQL functions are server-global, so the function helpers and inspection methods have no schema argument. Use globally unique or versioned function names across databases and migration streams. These operations support online execution and offline SQL rendering, including async SQLAlchemy connections through run_sync. or_replace=True explicitly requests replacement. ClickHouse can replace an existing table or materialized view with an ordinary view of the same name, discarding the prior object’s data. DROP VIEW rejects base tables but can drop materialized views. IF NOT EXISTS leaves an existing definition unchanged and does not verify it. Replacement and guarded creation cannot infer a safe downgrade; write it explicitly using the previous definition. Drops also need explicit downgrade definitions. Restoring a definition does not restore discarded data. SQL function replacement requires both CREATE FUNCTION and DROP FUNCTION privileges. ClickHouse expands SQL UDF calls when storing a view definition. Replacing a SQL UDF does not update existing views that use it; recreate or explicitly alter those views to use the new definition. Inspect ordinary views with the existing get_view_names(schema=...) and get_view_definition(name, schema=...) methods. SQL function inspection is specific to the ClickHouse dialect:
Function inspection includes only SQLUserDefined functions, excluding built-in and executable functions. Names are sorted. The definition is the full stored CREATE FUNCTION statement, not just its lambda body; missing names raise sqlalchemy.exc.NoResultFound. Catalog access errors propagate. Metadata inspection remains text even when user query formats request bytes. These helpers do not add declarative view or function entities, autogenerate comparison, or dependency ordering. Keep explicit migration operations in dependency order and use render_as_batch=False when rendering them.

Data skipping indexes

Declare ClickHouse data skipping indexes as table arguments. This API requires SQLAlchemy, and works without Alembic:
Index expressions and types are raw ClickHouse SQL. Definitions are immutable and names must be unique within a table. Declarations are stored in the clickhousedb_indexes table option, which also accepts a sequence of ClickHouseIndex definitions. Table.to_metadata() preserves them. CREATE TABLE renders them inline. if_not_exists, first, and after_index remain available to imperative ALTER helpers and do not affect inline creation or schema comparison. Inspector.get_clickhouse_indexes() returns replayable definitions for explicit indexes. Reflected expressions use a tuple(...) wrapper to preserve ClickHouse’s index expression list, including nested tuples. Server-created implicit indexes are excluded using the catalog’s creation field. Catalog errors and unknown classifications raise an error. The catalog must expose creation. ClickHouse release notes document this field in 26.1.1.912 and the 25.12.2.54 backport. Compatibility is checked by the actual query rather than by a version string. Generic Inspector.get_indexes() keeps returning an empty list. Table(..., autoload_with=engine) does not read the skip-index catalog. To copy both columns and indexes explicitly:

Projections

Declare SELECT projections as table arguments, without requiring Alembic:
select is the raw SQL inside the projection’s parentheses. Definitions are immutable and projection names must be unique within a table. The clickhousedb_projections table option also accepts a sequence of ClickHouseProjection definitions. Table.to_metadata() preserves declarations, and CREATE TABLE renders them inline. Dictionaries reject projection declarations. if_not_exists, first, and after_projection remain available for imperative ALTER helpers and do not affect inline creation or comparison. Imports from clickhouse_connect.cc_sqlalchemy.alembic and .alembic.operations, including the existing positional constructor arguments, remain supported. Inspector.get_clickhouse_projections() returns the stored SELECT definitions, preserving wildcards, aliases, grouping, and ordering. It refuses projection-level WITH SETTINGS and index-based projection forms because the SELECT-only API cannot preserve those definitions completely. Catalog failures also raise an error. Ordinary Table(..., autoload_with=engine) does not read the projection catalog. To copy columns and projections explicitly:
Inspection requires a visible target table and access to the database, table, name, type, query, and settings fields in system.projections. ClickHouse added this catalog in 24.9.1.3278. Its presence alone does not establish comparison support. The driver checks capabilities through the catalog and normalization queries.

Alembic migrations

ClickHouse Connect includes Alembic integration for ClickHouse schema migrations. Install it with:
For migrations through the async dialect, install both extras:
Create an async Alembic project, then replace its generated environment with the ClickHouse-aware example:
The generated alembic.ini uses script_location = %(here)s/alembic. Keep that setting when the migration directory is named alembic, or update it to the directory passed to alembic init. Replace alembic/env.py with the checked-in async Alembic env.py example, then set sqlalchemy.url in alembic.ini. 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, comments, and MergeTree table settings. Use manual operations for table and column renames. Review every generated migration before applying it. On ClickHouse Cloud, add alter_sync=2 to the migration engine URL so later reflection sees completed schema changes across replicas. This matters when a connection is replaced or autogenerate runs immediately after an upgrade. select_sequential_consistency=1 covers data reads, not schema changes. Version-table inserts bypass insert deduplication so a downgrade can restore a previously recorded revision. Generated offline SQL uses SETTINGS deduplicate_insert = 'disable', which requires that setting on the target server. Online migrations use the legacy insert_deduplicate = 0 setting when the server lacks the newer setting. Ordinary table inserts keep their configured deduplication behavior. Autogenerate compares the declared table engine with the server for tables present in both. Automatic migration of changes to engine family, positional engine arguments, ORDER BY, PRIMARY KEY, PARTITION BY, SAMPLE BY, and table TTL is unsupported, so a difference raises UnsupportedTableChangeError instead of writing an empty or misleading revision. Write an explicit migration after reviewing the server’s ALTER support and the data affected. The exception carries a drifts tuple of TableOptionDrift records with the table, the option, and both spellings. Constructing the exception requires at least one drift, otherwise it raises ValueError. Expressions are compared after normalization, and when the text still differs the server’s formatQuerySingleLine settles spellings such as INTERVAL 30 DAY against toIntervalDay(30). Quoted engine arguments compare their decoded string contents exactly, including whitespace and punctuation. TTL clauses are formatted as ALTER TABLE t MODIFY TTL ..., preserving actions such as DELETE WHERE, TO VOLUME, and RECOMPRESS. MergeTree and the Cloud Shared* and Replicated* variants count as one family, and replication path arguments are not compared. Declared engine settings that differ from the server render as op.modify_clickhouse_table_settings(...), with a downgrade that restores or resets each setting. Values and types from system.merge_tree_settings resolve defaults and equivalent boolean and numeric spellings; string settings keep their string meaning. Settings the model does not declare are preserved, including settings removed from the model after an earlier migration. Reflection cannot distinguish those settings from server-injected settings, so autogenerate never resets them automatically. Use op.reset_clickhouse_table_settings(...) explicitly when a reset is intended. Read-only settings such as index_granularity and index_granularity_bytes are compared and refused like keys. Pass clickhousedb_compare_table_options=False to context.configure() to skip the comparison. Reflected views and materialized views are not tables. include_object excludes them, so autogenerate never proposes drop_table for a view that is missing from the metadata. Manage them with the ordinary view or materialized view helpers. op.alter_column(..., server_default=None) emits MODIFY COLUMN ... REMOVE DEFAULT, which is the only form the server honors. The server rejects the statement when the column has no DEFAULT or uses MATERIALIZED or ALIAS, so only request removal when a default exists. A type change alone keeps the existing default. The dialect column options follow the same rule: pass clickhousedb_codec=None, clickhousedb_ttl=None, clickhousedb_alias=None, or clickhousedb_materialized=None to remove the property, or a value to set it. Setting or removing these properties alone does not require existing_type. Use text(...) for TTL, alias, and materialized SQL expressions, for example clickhousedb_ttl=text("ts + INTERVAL 7 DAY"). Options that are not passed stay unchanged. Both clickhousedb_ and legacy clickhouse_ names are accepted; when both names are passed to alter_column, the explicit clickhousedb_ value takes precedence, including None. When metadata omits an AggregateFunction state version, autogenerate accepts the existing reflected version. For example, AggregateFunction(uniq, UInt32) matches reflected AggregateFunction(1, uniq, UInt32). This also applies inside nested types. Changes to the aggregate function, its parameters, or its argument types still produce a type change. An explicit metadata version must match the reflected version. Legacy reflected types without a version match explicit version 0. Omitting the version does not request an upgrade of an existing state format after a server upgrade. Declare an explicit version when you intend to change the state format. Alembic’s migration functions remain synchronous. An async environment creates an AsyncEngine, opens an AsyncConnection, and passes the synchronous migration function to await connection.run_sync(...). Offline migrations call context.configure(url=..., literal_binds=True, dialect_opts={"paramstyle": "named"}) directly and do not create an engine. The checked-in async Alembic env.py example includes both paths and reads the connection URL through Alembic’s standard sqlalchemy.url configuration. It keeps the ClickHouse Alembic hooks and options from the worked example, including include_object, make_include_name(...), clickhouse_writer, and version_table. Do not use engine.sync_engine to run or dispose async migrations. 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.
  • Ordinary view and SQL function creation, replacement, and removal.
  • Materialized view creation and removal.
  • Dictionary creation, removal, and reload.
Upgrade note: Alembic compares explicit skip indexes and SELECT projections by default. Objects created only by earlier imperative migrations can generate drops when absent from managed table metadata. Before upgrading, declare them with ClickHouseIndex or ClickHouseProjection, exclude unmanaged objects with include_name or include_object, or disable the relevant comparison in context.configure():
Each option skips its catalog reads and table-drop downgrade snapshots. Edits generate separate drop and add operations. Downgrade restores definitions, but neither direction restores prior materialization. Replacing a projection discards its existing projection parts. Review that rebuild cost and run MATERIALIZE PROJECTION explicitly when existing parts need the replacement immediately. Index materialization also remains explicit. Declare skip indexes with ClickHouseIndex for autogenerate support, or use the existing op.add_clickhouse_index and op.drop_clickhouse_index helpers in explicit revisions. Imports from clickhouse_connect.cc_sqlalchemy.alembic and .alembic.operations remain supported. Standard SQLAlchemy Index, Column(index=True), op.create_index, and op.drop_index remain rejected. For managed tables, declarations own the explicit live index set by default. An undeclared explicit index produces a drop operation, including indexes previously created only by imperative migrations. A changed expression, index type, parameter, or effective granularity produces a separate drop followed by an add. Downgrade restores the prior definition. Operations remain visible to process_revision_directives before rendering. Policy hooks should pair index and projection edits by schema, table, and object name. Do not rely on adjacency. Neither direction restores prior materialization, and no MATERIALIZE INDEX is generated. Materialize explicitly if existing parts need the index immediately. Generate index additions, removals, and edits with render_as_batch=False. Their helpers do not support Alembic’s deferred batch queue, so render_as_batch=True raises an error during generation. Inline indexes in table creation and table-drop downgrade snapshots remain supported. The same restriction applies to other custom ClickHouse operations inside a batch block, including table settings, projections, and materialization. Generate these operations outside batch mode. For physical batch recreation with op.batch_alter_table(), pass a complete copy_from Table containing the columns, engine, and clickhousedb_indexes. Use the explicit reflection example above to load both columns and indexes, or supply complete metadata for offline migrations. Automatic online recreation reads the index catalog at execution time and refuses tables with explicit indexes before creating or dropping tables. The autogenerate comparison opt-out does not bypass this guard. Standard include_name and include_object hooks receive type_="index". Name filters apply to both reflected and declared candidates so an excluded name remains unmanaged. Object filters receive the usual reflected and compare_to values. Their index objects expose the definition fields and .table. For example, the standard Alembic unmanaged-object recipe, reflected and compare_to is None, protects imperative-only indexes. Table filters are respected before index catalog reads. Inline indexes on newly created tables are also filtered before policy hooks. Comparison uses server formatting and analysis to account for ClickHouse spellings and defaults. Expression analysis uses the current table, server functions, and settings. It requires the analyzer and the expected EXPLAIN output. These capabilities were verified against ClickHouse 26.8, with source review at 26.6. The catalog field alone does not establish comparison support on an older server. Changed ALIAS definitions that affect an index require an explicit column migration before index comparison. An index edit can reference a physical column added in the same revision. If analysis reports that scheduled new column as an unknown identifier, autogenerate validates the full expression with the added columns available, then emits the column addition before the index drop and add. Other unknown identifiers, including typos and columns excluded by filters, still raise IndexComparisonError. Empty type parentheses and case differences, such as minmax(), MinMax(), and minmax, compare equally. Omitted type arguments are not expanded for comparison: changing between bloom_filter and bloom_filter(0.025) can produce a one-time drop and add even though the server uses that default. Comparison does not establish equivalence for arbitrary SQL expressions. When normalization succeeds but the resulting forms differ, autogenerate generates a replacement even if the expressions return the same values. Review such changes or manage those indexes with explicit migrations and filters. If catalog access, expression resolution, or the expected normalization output is unavailable, autogenerate raises IndexComparisonError, carrying the table, index, declared and reflected definitions, and the reason. Review edits carefully. The drop and add are separate, nontransactional statements. A failed add can leave the old index absent.

Projection autogenerate

Alembic compares SELECT projections on managed tables by default. Declarations own the live projection set. Undeclared projections produce drops, and changed definitions produce separate drop and add operations. On each table, autogenerate orders projection drops, column additions and alterations, index operations, projection additions, then column drops. Alembic appends column drops after the table comparators finish. Pair edits by schema, table, and projection name in policy hooks. Other operations can appear between the drop and add. Downgrades restore the previous definitions, including projections on recreated dropped tables. Operations expose complete definitions to process_revision_directives before rendering. No MATERIALIZE PROJECTION is generated, and neither upgrade nor downgrade restores previous part materialization. Materialize explicitly when existing parts need the projection immediately. For AggregatingMergeTree and other engines with nonstandard merge behavior, ClickHouse rejects ADD PROJECTION when deduplicate_merge_projection_mode='throw'. Set it explicitly to drop or rebuild before applying projection additions or replacements, including replacements of projections originally declared inline. include_name and include_object receive type_="projection". Name filters apply to both reflected and declared names. Object filters receive definitions with .table, reflected, and compare_to. The usual reflected and compare_to is None recipe protects imperative-only projections. Table filters run before catalog reads, and inline definitions on newly created tables are filtered before policy hooks. Unsupported definitions on filtered projections or unrelated tables do not block comparison. Comparison uses the server’s full projection grammar formatter and the case-insensitive function names and aliases in system.functions, following ClickHouse’s CREATE normalization. It preserves SELECT structure, including wildcards and aggregate expressions, rather than analyzing an ordinary SELECT against the live table. Formatting, identifier quoting, and registered case-insensitive function spellings compare equally. Case-sensitive names such as sumIf and SumIf remain distinct. When the formatted definitions differ, comparison also checks the parsed syntax tree, which ignores grouping parentheses. Redundant parentheses and operator and function spellings such as value + 13 and plus(value, 13) compare equally, because ClickHouse versions and ClickHouse Cloud store redundant parentheses inconsistently. Other structural differences generate drop and add operations. Comparison does not prove arbitrary SQL equivalence or validate column dependencies. A replacement can reference a physical column added earlier in the same revision. Review dependencies, including changed ALIAS columns, before applying it. Invalid dependencies fail when the DDL executes, and a failed add can leave the old projection absent because drop and add are separate nontransactional statements. Missing catalog fields, catalog access errors, unavailable formatting or function metadata, projection-level WITH SETTINGS, and index-based forms raise ProjectionComparisonError with the table, projection, declared and reflected definitions, and reason. A failure to normalize raises this diagnostic instead of generating a replacement. Filter unsupported projections or manage them with explicit migrations. If query_masking_rules changes the SELECT text returned by the catalog, comparison and replay may be unreliable. Use explicit migrations, filters, or the comparison opt-out for affected projections. Function normalization requires the name, is_aggregate, case_insensitive, and alias_to fields in system.functions. Support is checked by actual catalog and formatter queries rather than version strings. Generate projection additions, removals, and edits with render_as_batch=False. Inline CREATE definitions and table-drop downgrade snapshots remain supported. For physical batch recreation, provide complete copy_from metadata containing the columns, engine, and clickhousedb_projections. Automatic online recreation reads the projection catalog and refuses tables with projections before DDL. The comparison opt-out does not bypass that execution guard. See the complete Alembic worked example. Users migrating from clickhouse-sqlalchemy should also read the migration guide.

Classify generated operations before rendering

All 21 ClickHouse migration operation classes are public from clickhouse_connect.cc_sqlalchemy.alembic and its operations module. Use isinstance to inspect them in process_revision_directives or register them with Alembic’s Rewriter.rewrites. The old underscore names remain aliases of the same classes, so existing registrations and imports keep working. The public inspection fields match constructor arguments. Table operations expose table_name, schema, and clickhouse_settings. Single index operations expose name. Additions also expose expression, type_, granularity, if_not_exists, first, and after_index. Projection additions expose select and after_projection instead of index expression, type, and granularity fields. Drops expose if_exists and, for single indexes or projections, existing_index or existing_projection when a prior definition is available. Materializations expose if_exists and partition. Grouped additions store indexes or projections as tuples of ClickHouseIndex or ClickHouseProjection. Grouped drops store names as a tuple. Inspect every member of a grouped operation. ModifyClickHouseTableSettingsOp exposes settings and optional existing_settings. ResetClickHouseTableSettingsOp exposes a tuple of names. These table settings differ from the query-level clickhouse_settings mapping. Create view and function operations expose name, select or expression, if_not_exists, and or_replace. Views also expose schema. Materialized-view creation exposes name, schema, to_table, to_schema, select, and if_not_exists. Dictionary creation exposes name, schema, a columns tuple, primary_key, source, layout, lifetime, comment, if_not_exists, and clickhouse_settings. View, function, materialized-view, and dictionary drops expose name and if_exists, plus schema and clickhouse_settings where accepted by the constructor. Dictionary reload exposes name and schema. SQL fragments remain strings. Mappings retain their supplied values. Reverse operations can require prior definitions or raise NotImplementedError. A reversible definition does not restore materialized data. This example is an application policy, not a driver safety guarantee. It permits only an explicitly chosen index shape or table setting, sends replacements and unknown operations for review, and rejects standalone index drops, standalone projection drops, and destructive built-in operations. Change these rules for your deployment. Even creation needs field inspection: or_replace=True on a view can discard a colliding table’s data, and raw SQL bodies and dictionary sources need review.
Pass process_revision_directives=review_revision to context.configure() in place of clickhouse_writer. The hook logs findings for both directions and gates revision generation on upgrade findings. Downgrade findings require a separate review before executing a downgrade. They do not block writing an approved upgrade. The hook traverses nested containers and pairs replacements by kind, schema, table, and name, regardless of adjacency. Grouped additions and all unrecognized operations default to review. This recipe supports one engine and one operation stream per direction. Using list roots in the classifier does not make clickhouse_writer a multi-engine hook. An empty generated tree proves only that enabled, supported comparisons found no changes. Views, functions, dictionaries, and other unsupported comparisons still need explicit migrations.

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.