Download the PHP package rasuvaeff/clickhouse-toolkit without Composer

ClickHouse Toolkit

Lightweight, framework-agnostic ClickHouse helpers for PHP applications.

• ClickHouseClientFactory + ClickHouseConfig — build a configured client over any PSR-18 HTTP client (auto-discovered or injected; HTTP/HTTPS).
• ClickHouseQueryBuilder — turn yiisoft/data filters and sort into safe, parameterized SQL.
• ClickHouseFilterVisitor + ClickHouseSqlFilterVisitor — extensible visitor for SQL generation per filter type.
• ClickHouseDataReader — an immutable DataReaderInterface ready for yiisoft/data paginators.
• ClickHouseBatchWriter — buffered, batched inserts.
• ClickHouseTableBuilder — fluent CREATE TABLE DDL.
• ClickHousePartitionManager — list / drop / detach / attach / move / freeze partitions.
• ClickHouseMutationBuilder — async ALTER … UPDATE/DELETE with mutation tracking.
• ClickHouseMigrationRunner — idempotent, checksum-verified *.sql migrations.
• ClickHouseDataType — type-name constants and factories for parametric/nested types.

Built on top of simpod/clickhouse-client. The query/reader pieces integrate with the yiisoft/data reader abstractions, so they slot naturally into Yii3 admin grids and paginated APIs, but nothing here requires the full framework.

Using an AI coding assistant? llms.txt is a compact, self-contained reference of the whole public API plus copy-paste recipes — drop it into the model's context. Contributors: see AGENTS.md.

Table of contents

• Requirements
• Installation
• Quick start
• Components
  • ClickHouseConfig & ClickHouseClientFactory
  • ClickHouseQueryBuilder & WhereClause
  • ClickHouseFilterVisitor
  • ClickHouseDataReader
  • ClickHouseBatchWriter
  • ClickHouseTableBuilder
  • ClickHousePartitionManager
  • ClickHouseMutationBuilder
  • ClickHouseDataType
  • ClickHouseMigrationRunner
  • Interfaces
  • Timezone handling
• Dependency injection
• Security notes
• What is intentionally not included
• Examples
• Development
• License

Requirements

The toolkit depends only on interfaces (psr/http-client, psr/http-factory, psr/log, php-http/discovery, simpod/clickhouse-client, yiisoft/data) — not on any concrete HTTP client. It auto-discovers an installed PSR-18 client/PSR-17 factories via php-http/discovery, or you can inject your own.

Installation

You also need a PSR-18 client and PSR-17 factories if your project doesn't already ship one, e.g.:

Quick start

Components

ClickHouseConfig & ClickHouseClientFactory

ClickHouseConfig holds connection settings; ClickHouseClientFactory turns it into a SimPod\ClickHouseClient\Client\PsrClickHouseClient. The HTTP client and PSR-17 factories are auto-discovered (or injected). The endpoint is an absolute URI built from the config; authentication and database are sent via X-ClickHouse-* headers (an AuthenticatingHttpClient decorator), so credentials never appear in the URL.

To control timeouts, retries or TLS, build your own PSR-18 client and inject it (along with the PSR-17 factories you want):

ClickHouseQueryBuilder & WhereClause

Translates yiisoft/data filters and sort into parameterized ClickHouse SQL. The builder is the security boundary: only fields present in allowedFields are emitted in WHERE and ORDER BY; anything else is silently dropped. Comparison values become bound parameters with unique keys (p0, p1, …), so the same field may appear multiple times without collisions.

WhereClause is a small DTO: public string $sql, public array $params, and isEmpty(): bool.

Supported filters

DateTimeInterface values are normalized to Y-m-d H:i:s; bool to 0/1.

Mandatory filters (tenant / owner / ACL)

The builder is fluent and immutable. withMandatoryFilter() attaches an always-applied filter that is AND-combined with the user filter and bypasses the allow-list (its fields need not be in allowedFields; identifiers are still validated). This is the safe way to enforce access constraints — the user filter can only narrow within it.

Raw expressions

ClickHouseRawFilter is a FilterInterface that emits a raw SQL fragment for things the typed filters can't express. The SQL is trusted (never from user input); values go in $params using {name:Type} placeholders whose names must not clash with the builder's auto keys (p0, p1, …).

Full read + count cycle

ClickHouseFilterVisitor

The query builder delegates SQL generation to a visitor. ClickHouseFilterVisitor is the interface with a visit*() method per filter type; ClickHouseSqlFilterVisitor is the default implementation. Use dispatch(FilterInterface $filter, int &$index, bool $trusted) to route any filter to the right method.

Implement ClickHouseFilterVisitor and inject via withVisitor() to customise SQL generation:

ClickHouseDataReader

An immutable Yiisoft\Data\Reader\DataReaderInterface backed by a ClickHouse table. Filtering, sorting and pagination are delegated to the query builder; rows are mapped to your value type by a supplied mapper. It plugs straight into yiisoft/data paginators (OffsetPaginator, KeysetPaginator).

Implements read(), readOne(), count(), getIterator(), and the immutable withFilter/withSort/withLimit/withOffset (+ getters). With no limit set, read() omits LIMIT and returns the full result.

ClickHouseBatchWriter

Buffers rows and inserts them in fixed-size batches. Each row is projected onto the declared columns (extra keys dropped, missing keys → null), so loosely-shaped associative rows are fine. Failures are wrapped in ClickHouseWriteException.

Implements ClickHouseWriterInterface (write(iterable $rows): void).

ClickHouseTableBuilder

Fluent CREATE TABLE builder. build() returns the SQL; execute() runs it via the client. The table name and column names are validated identifiers; column types, the engine, and the ORDER BY / PARTITION BY / PRIMARY KEY expressions are emitted verbatim — DDL is developer-authored, so keep them trusted.

build()/execute() throw if no columns or no engine were set.

ClickHousePartitionManager

Manages MergeTree partitions through ALTER TABLE … PARTITION. Partition operations can't use bound parameters, so a partition is addressed by its id (from getPartitions()) and emitted as an escaped PARTITION ID '…'; table and column names are validated identifiers.

ClickHouseMutationBuilder

Submits and tracks mutations — ALTER TABLE … UPDATE/DELETE, the only way to modify or delete existing rows. Mutations are asynchronous. The $set and $condition fragments are trusted (developer-authored); pass user values as bound {name:Type} parameters (ClickHouse supports parameters in ALTER).

ClickHouseMigrationRunner

Applies *.sql files from a directory in filename order, recording each applied file with a content checksum in a _migrations table.

• Idempotent — already-applied files are skipped.
• Tamper-evident — if an already-applied file's contents changed, a ClickHouseMigrationException is thrown instead of silently diverging.
• One statement per file — contents are sent as a single query (no naive ; splitting).
• Optional PSR-3 logging — pass a LoggerInterface to log applied/skipped files.

Tracking table (created automatically):

Name files so lexicographic order equals execution order, e.g. 001_create_events.sql, 002_add_index.sql.

Concurrency & partial failure. ClickHouse has no transactions and the runner uses no distributed lock: the applied-list is read, then each file is executed and recorded separately. Two runners started at once may both run the same pending file, and if a file's DDL succeeds but the _migrations insert does not, the next run repeats it. Run migrations from a single deploy step, prefer idempotent DDL (CREATE TABLE IF NOT EXISTS, ALTER TABLE ... ADD COLUMN IF NOT EXISTS), and wrap run() in an external lock if you need stronger guarantees.

ClickHouseDataType

Type-name constants and factories so type definitions are self-documenting and typo-proof. Types are plain strings, usable anywhere one is expected (ClickHouseTableBuilder columns, ClickHouseQueryBuilder field types).

Composite types (Enum, timezone-qualified DateTime) are for column definitions, not query-parameter types.

Interfaces

Timezone handling

ClickHouseQueryBuilder accepts an optional serverTimezone (IANA name, e.g. "UTC", "Europe/Moscow"). When set, DateTimeInterface filter values are converted to that timezone before being formatted as Y-m-d H:i:s. This applies to filters whose value is a DateTimeInterface object (Equals, comparisons, Between); In values are scalar/string values and are passed as provided. Without serverTimezone, the object's own timezone is used (backward compatible).

Fluent: $qb->withServerTimezone('UTC') returns a new instance.

Dependency injection

Any PSR-11 container works. Example using Yiisoft DI definitions (Yii3):

See examples/di-container.php for a runnable plain-PHP container wiring.

Security notes

• Allow-list enforcement. ClickHouseQueryBuilder only emits allow-listed fields in WHERE and ORDER BY (each allowedFields entry is validated as an identifier at construction). Pass user-controlled filter/sort objects straight through — unknown fields are dropped.
• Disallowed user filters are silently dropped (widening, not narrowing). For mandatory tenant/owner/ACL constraints do not rely on user filters — use withMandatoryFilter(), which is always applied and AND-combined so the user filter can only narrow within it.
• Bound parameters. All comparison/In/Between/Like values are passed as ClickHouse bound parameters ({pN:Type}) with unique keys; values are never concatenated into SQL.
• Like escaping. Like values are wildcard-escaped (addcslashes($value, '%_\\')) and bound as a parameter — the quote is not escaped (it lives in the parameter, not the SQL). Empty Like values are dropped. Non-string fields are compared as toString(field) LIKE/ILIKE {pN:String} so user filters cannot make ClickHouse reject numeric/date columns.
• Table/column names passed to buildSelect/buildCount/buildDistinct and the columns projection are not escaped, but they are validated as plain SQL identifiers (db.table allowed); a malformed identifier throws InvalidArgumentException. Still pass trusted, plain identifiers — the validator rejects raw expressions (toDate(x) AS d), so build those yourself.
• Pagination. buildSelect rejects negative limit/offset with InvalidArgumentException.
• orderBy passed to buildSelect, and the constructor's non-empty defaultSort, are trusted raw ORDER BY fragments — not validated. Use buildOrderBy() output (allow-list-checked) or a hard-coded constant; never build them from untrusted input. The default defaultSort is empty, so generic builders do not assume an id column; set one explicitly for stable pagination.
• fieldTypes type tokens are validated (allowing parametric types like Array(Nullable(String))) so they can't break out of the {name:Type} placeholder. They are developer configuration, not user input.
• Credentials travel in X-ClickHouse-* headers, not the URL.

What is intentionally not included

• Concrete readers/writers for specific tables (row shapes are app-specific — use ClickHouseDataReader with a mapper, or implement ClickHouseReaderInterface).
• A migration generator or rollback/down migrations.
• Connection pooling or retries.
• Framework bootloaders/service providers (wire it in your app — see Dependency injection).

Examples

Runnable, self-contained examples live in examples/:

See examples/README.md for how to run them.

Development

Integration tests in tests/Integration/ run end-to-end against a real server and are skipped unless CLICKHOUSE_HOST is set:

CI runs composer build on PHP 8.3, 8.4, and 8.5.

License

BSD-3-Clause. See LICENSE.md.