ClickHouse SQLAlchemy
SQLAlchemy writes queries. hypequery defines your metrics.
If you use SQLAlchemy with ClickHouse, you already have a good way to build SQL. What you don’t have is one shared definition of revenue, scoped to the right customer. hypequery adds that layer next to SQLAlchemy, not instead of it.
Replaces SQLAlchemy?
No, it sits beside it
Driver
clickhouse-connect
Adds
Measures, validation, tenancy
Query builders don’t hold meaning
SQLAlchemy can express any aggregation, but it doesn’t know which one is “revenue”. Every caller rebuilds it.
Each query re-applies the rules
Tenant filters, completed-order conditions, and timezone bucketing are repeated in every statement.
Callers can ask for anything
An API built on a query builder needs its own allow-list of fields, filters, and limits. That’s a second definition to maintain.
Side by side
Write SQL when you need it. Name measures when they’re shared.
Keep SQLAlchemy for one-off and operational queries. Put the numbers your product, reports, and agents share into datasets.
- Measures and dimensions are defined once and reviewed as code
- Callers pick fields by name; anything undeclared is rejected
- The tenant filter is added for you and can’t be widened from a query
- Python has no hypequery query builder, so SQLAlchemy stays your tool for raw SQL
compare.py
The same report, two ways
orders_table is a SQLAlchemy Table; orders is a hypequery dataset.
One engine, one config
Point hypequery at your engine’s server
executor_from_engine reads your engine’s URL and connects hypequery to the same ClickHouse server, with its own HTTP clients, so closing one never affects the other. Works with clickhousedb:// and clickhouse+http:// engines.
hypequery sends values as ClickHouse server-side parameters, which only the HTTP interface carries, so clickhouse+native:// engines are refused with an explanation. Use an HTTP-interface URL for hypequery, usually port 8123 or 8443.
hypequery does not read SQLAlchemy Table metadata yet. Generate datasets from your live ClickHouse schema with hypequery generate datasets instead.
engine.py
hypequery and SQLAlchemy on one server
The same URLs also work directly with ClickHouseConnection.from_url().
Where teams usually get stuck
Questions teams ask
clickhouse-sqlalchemy vs hypequery
clickhouse-sqlalchemy is a dialect for building SQL. hypequery is a semantic layer for shared measures. Many teams want both.
SQLAlchemy ORM for ClickHouse analytics
ORMs map rows to objects. Analytics wants aggregations by dimension, which is what datasets describe.
Using one ClickHouse config
executor_from_engine(engine) points hypequery at the server your clickhousedb:// or clickhouse+http:// engine uses.
Generate models from SQLAlchemy?
Not today. Datasets are generated from the live ClickHouse schema, or written by hand.
Further reading
Go deeper where it actually helps
Next step
Move one shared number into a dataset
Find the aggregation you copy most often, define it as a measure, and query it by name.
Open source, and it runs in your own backend. Want it hosted?