ClickHouse support¶

alchemiq provides a first-class ClickHouse integration under the [clickhouse] extra: an annotation-first model base, three MergeTree-family engine descriptors, a typed repository, buffered inserts, and a custom migration runner (Alembic does not support ClickHouse).

pip install "alchemiq[clickhouse]"

ClickHouse and PostgreSQL have separate SQLAlchemy metadata and mapper registries, so the two never interfere.

Declaring a model¶

Subclass ClickHouseModel and set Meta.engine to one of the three supported engine descriptors. order_by is required - ClickHouse uses the ORDER BY key for sorting and (in ReplacingMergeTree) for deduplication:

import datetime as dt
from alchemiq.clickhouse import ClickHouseModel, MergeTree, ReplacingMergeTree
from alchemiq.clickhouse.types import DateTime64, UInt32

class PageView(ClickHouseModel):
    event_time: dt.datetime = DateTime64(3)
    user_id: int = UInt32()

    class Meta:
        engine = MergeTree(order_by=("event_time", "user_id"))

Engine descriptors¶

Engine	Use case
`MergeTree`	General-purpose append-only analytics
`ReplacingMergeTree`	Deduplication by ORDER BY key; required for soft-delete
`AggregatingMergeTree`	Aggregate-function columns

All three share the same keyword arguments:

Parameter	Description
`order_by`	Tuple of column names or a SQL expression string (required)
`partition_by`	Optional partition expression
`primary_key`	Optional PRIMARY KEY (subset of ORDER BY)
`ttl`	Optional TTL expression, e.g. `"event_time + INTERVAL 90 DAY"`
`sample_by`	Optional SAMPLE BY expression
`settings`	Optional dict of ENGINE-level settings

ReplacingMergeTree also accepts version (column name for dedup version) and is_deleted (column name for tombstone flag).

Soft-delete models¶

Set Meta.soft_delete = True with ReplacingMergeTree. alchemiq injects is_deleted, _version, and deleted_at columns automatically:

class Document(ClickHouseModel):
    key: int = UInt32()
    body: str

    class Meta:
        soft_delete = True
        engine = ReplacingMergeTree(order_by=("key",))

SELECT ... FINAL retains the row with the highest _version and filters out rows where is_deleted=1. Use repo.with_deleted() or repo.only_deleted() to bypass the filter.

Creating and dropping tables¶

from alchemiq.clickhouse import create_clickhouse_tables, drop_clickhouse_tables

await create_clickhouse_tables()   # CREATE TABLE IF NOT EXISTS for every model
await drop_clickhouse_tables()     # DROP TABLE IF EXISTS (reverse order)

ClickHouseRepository¶

ClickHouseRepository is the data-access surface for one ClickHouse model. Instantiate it directly or subclass with a type parameter:

from alchemiq.clickhouse import ClickHouseRepository

# direct
repo = ClickHouseRepository(PageView)
await repo.insert(PageView(event_time=dt.datetime.now(dt.UTC), user_id=42))

# typed subclass
class PageViewRepo(ClickHouseRepository[PageView]):
    pass

rows = await PageViewRepo().filter(user_id=42).order_by("event_time").all()

Filtering¶

Q objects work the same way as for PostgreSQL:

from alchemiq import Q

rows = await repo.filter(Q(user_id__gt=100)).order_by("-event_time").limit(50).all()

Insert methods¶

Method	Description
`insert(*objs)`	Insert one or more model instances immediately
`bulk_insert(objs)`	Optimised batch insert
`buffered(...)`	Returns a `BufferedInserter` context manager

Buffered insertion¶

BufferedInserter accumulates rows in memory and flushes to ClickHouse when max_rows is reached or every flush_interval seconds:

async with repo.buffered(max_rows=1000, flush_interval=5.0) as buf:
    for event in events:
        await buf.add(event)
# all rows flushed on exit

Raw SQL¶

repo.raw executes a literal SQL string and returns results as plain dicts (or as model instances when as_model=True):

rows = await repo.raw(
    "SELECT region, sum(amount) AS total FROM _sale GROUP BY region ORDER BY region"
)
# rows == [{"region": "EU", "total": 30}, ...]

instances = await repo.raw(
    "SELECT * FROM page_view WHERE user_id = {uid:UInt32}",
    params={"uid": 42},
    as_model=True,
)

Soft-delete on ClickHouse¶

ClickHouseRepository.delete(**lookups) is supported for models with Meta.soft_delete = True. It does not issue a SQL DELETE - instead it appends a tombstone row (same ORDER BY key, is_deleted=1); a subsequent SELECT ... FINAL collapses the key to the latest version and hides the row. The lookups must supply every column in the ORDER BY key, or UnsupportedOperationError is raised eagerly before any IO.

repo = ClickHouseRepository(Document)
await repo.insert(Document(key=1, body="hello"))
await repo.delete(key=1)    # tombstone inserted; row hidden under FINAL
await repo.restore(key=1)   # un-delete (live marker with is_deleted=0)
await repo.cleanup()        # OPTIMIZE ... FINAL CLEANUP - physically drop tombstones

delete raises UnsupportedOperationError if the model is not soft-delete (there is no physical/hard row DELETE on ClickHouse).

Unsupported operations¶

ClickHouse is append-only. The following methods raise UnsupportedOperationError:

update(...) and bulk_update(...) - ClickHouse has no row UPDATE
get_or_create(...) and update_or_create(...) - ClickHouse has no upsert; use insert / bulk_insert

Outbox integration¶

ClickHousePublisher is a Publisher adapter that writes outbox messages directly into a ClickHouse table in batches. Wire it into a Relay the same way as any other publisher:

from alchemiq import Relay
from alchemiq.clickhouse import ClickHousePublisher

relay = Relay(ClickHousePublisher(EventRepo), batch_size=500)
await relay.run()

Migrations¶

alchemiq ships its own migration runner for ClickHouse (Alembic does not support it). Migrations are Python classes with up and down methods. Applied revisions are stored in a _alchemiq_migrations MergeTree table in ClickHouse itself.

CLI commands¶

alchemiq makemigrations --db clickhouse
alchemiq migrate --db clickhouse
alchemiq rollback --db clickhouse
alchemiq history --db clickhouse    # list migrations with applied markers
alchemiq showsql --db clickhouse    # print DDL for pending migrations (reads history, runs nothing)

alchemiq history reads the _alchemiq_migrations table and prints each revision with a [x] (applied) or [ ] (pending) marker.

alchemiq showsql connects to ClickHouse to read the applied-revision history and prints the DDL SQL for pending migrations without executing it.

PostgreSQL-only features¶

The following alchemiq features are not available for ClickHouse:

Feature	Reason
`UnitOfWork` / transactions	ClickHouse has no ACID transactions
Optimistic locking (`Meta.versioned`)	Requires row-level UPDATE
Physical/hard row `DELETE`	Append-only storage (soft-delete via tombstone is supported)
`QuerySet.explain()` (PostgreSQL EXPLAIN)	PG-specific; use CH `EXPLAIN` syntax via `repo.raw`
Alembic migrations	Alembic has no ClickHouse dialect
Native upsert	ClickHouse has no upsert; use `insert` / `bulk_insert`
Model signals on write	No ORM-level flush