README

Lightweight, framework-agnostic ClickHouse helpers for PHP applications.

$qb = new ClickHouseQueryBuilder(allowedFields: ['id', 'status'], fieldTypes: ['id' => T::UInt64]);
$where = $qb->buildWhere(new Equals('status', 'active'));
$sql = $qb->buildSelect(table: 'events', where: $where->sql, limit: 20);

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.
ClickHouseKeysetReader — bounded-memory streaming of large result sets via keyset pagination.
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.
ClickHouseMigrationGenerator — creates new migration files with auto-incremented numeric prefixes.
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. Projects using the llm/skills Composer plugin also get this package's agent skill synced into .agents/skills/ automatically on install.

Requirements
Installation
Quick start
Components
Dependency injection
Security notes
What is intentionally not included
Examples
Development
License

Requirements

Requirement	Version
PHP	`^8.3`
A PSR-18 HTTP client + PSR-17 factories	any implementation
ClickHouse server	tested against 23.x – 26.x over the HTTP interface (port `8123`)

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

composer require rasuvaeff/clickhouse-toolkit

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

composer require guzzlehttp/guzzle
# or: composer require symfony/http-client nyholm/psr7

Quick start

use Rasuvaeff\ClickHouseToolkit\ClickHouseClientFactory;
use Rasuvaeff\ClickHouseToolkit\ClickHouseConfig;
use Rasuvaeff\ClickHouseToolkit\ClickHouseDataType as T;
use Rasuvaeff\ClickHouseToolkit\ClickHouseQueryBuilder;
use SimPod\ClickHouseClient\Format\JsonEachRow;
use Yiisoft\Data\Reader\Filter\In;
use Yiisoft\Data\Reader\Sort;

// 1. Build a client.
$client = (new ClickHouseClientFactory(new ClickHouseConfig(
    host: 'clickhouse',
    port: 8123,
    database: 'app',
    username: 'default',
    password: '',
)))->create();

// 2. Build a safe, parameterized query from user-supplied filters.
$qb = new ClickHouseQueryBuilder(
    allowedFields: ['id', 'status', 'created_at'],
    fieldTypes: ['id' => T::UInt64, 'created_at' => T::DateTime],
    defaultSort: 'id DESC',
);

$where = $qb->buildWhere(new In('status', ['active', 'pending']));
$orderBy = $qb->buildOrderBy(Sort::only(['created_at'])->withOrder(['created_at' => 'desc']));
$sql = $qb->buildSelect(table: 'events', columns: ['id', 'status'], where: $where->sql, orderBy: $orderBy, limit: 20);

// 3. Execute.
$output = $where->isEmpty()
    ? $client->select($sql, new JsonEachRow())
    : $client->selectWithParams($sql, $where->params, new JsonEachRow());

foreach ($output->data as $row) {
    // ...
}

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.

final readonly class ClickHouseConfig
{
    public function __construct(
        public string $host = '127.0.0.1',
        public int $port = 8123,
        public string $database = 'default',
        public string $username = 'default',
        public string $password = '',
        public bool $secure = false,   // true -> https://
    ) {}

    public function baseUri(): string; // e.g. "http://127.0.0.1:8123"
}

use Rasuvaeff\ClickHouseToolkit\ClickHouseClientFactory;
use Rasuvaeff\ClickHouseToolkit\ClickHouseConfig;

// Auto-discovers an installed PSR-18 client + PSR-17 factories:
$client = (new ClickHouseClientFactory(new ClickHouseConfig(
    host: 'ch.internal',
    secure: true,     // https
)))->create();

$client->executeQuery('SELECT 1');

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

use GuzzleHttp\Client;

$factory = new ClickHouseClientFactory(
    config: new ClickHouseConfig(host: 'ch.internal', secure: true),
    httpClient: new Client(['timeout' => 10.0]),
    // requestFactory / streamFactory / uriFactory are optional (auto-discovered when null)
);

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

public function __construct(
    private array $allowedFields,            // list<string>
    private array $fieldTypes = [],          // field => ClickHouse type, default "String" (use ClickHouseDataType constants)
    private string $defaultSort = '', // no ORDER BY by default; pass e.g. 'id DESC' for stable pagination
    private ?FilterInterface $mandatoryFilter = null,
    private ?string $serverTimezone = null,  // IANA timezone; DateTime values are converted before formatting
) {}

Method	Returns	Description
`buildWhere(FilterInterface $filter)`	`WhereClause`	`{sql, params}`; `sql` is empty when nothing matched.
`buildOrderBy(?Sort $sort)`	`string`	ORDER BY fragment (allow-list-checked), or `defaultSort`; empty string means no `ORDER BY`.
`buildSelect(string $table, array $columns = [], string $where = '', ?string $orderBy = null, ?int $limit = 20, int $offset = 0)`	`string`	`columns` empty → `SELECT *`; empty order → no `ORDER BY`; `limit` null → no LIMIT/OFFSET.
`buildCount(string $table, string $where = '')`	`string`	`SELECT count() AS cnt FROM ...`.
`buildDistinct(string $table, string $column)`	`string`	`SELECT DISTINCT col FROM ... ORDER BY col`.

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

Supported filters

`yiisoft/data` filter	Rendered as	Notes
`All`	empty `WHERE`
`None`	`0`	matches nothing
`Equals`	`field = {p0:Type}`
`GreaterThan` / `GreaterThanOrEqual`	`field > / >= {p0:Type}`
`LessThan` / `LessThanOrEqual`	`field < / <= {p0:Type}`
`EqualsNull`	`field IS NULL`	no params
`In`	`field IN ({p0:Type}, {p1:Type}, …)`	empty values → `0` (match nothing)
`Between`	`field BETWEEN {p0:Type} AND {p1:Type}`
`Like`	`field ILIKE {p0:String}` (or `LIKE` if `caseSensitive`)	non-string fields are wrapped in `toString(field)`; empty values are dropped; value bound + wildcard-escaped; honours `LikeMode` Contains/StartsWith/EndsWith
`Not`	`NOT (...)`	dropped if the inner filter is empty
`AndX` / `OrX`	`(a AND/OR b …)`	empty sub-filters skipped

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.

$qb = ClickHouseQueryBuilder::create(['id', 'status'], ['id' => T::UInt64])
    ->withMandatoryFilter(new Equals('tenant_id', $tenantId));

$where = $qb->buildWhere($userFilter); // (tenant_id = {p0:...}) AND (<user filter>)

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, …).

use Rasuvaeff\ClickHouseToolkit\ClickHouseRawFilter;

$where = $qb->buildWhere(new ClickHouseRawFilter('toDate(created_at) = {d:Date}', ['d' => '2024-01-01']));

Full read + count cycle

use Yiisoft\Data\Reader\Filter\AndX;
use Yiisoft\Data\Reader\Filter\Equals;
use Yiisoft\Data\Reader\Filter\GreaterThanOrEqual;

$where = $qb->buildWhere(new AndX(
    new Equals('status', 'active'),
    new GreaterThanOrEqual('user_id', 1000),
));

$selectSql = $qb->buildSelect(table: 'events', columns: ['id', 'status'], where: $where->sql, limit: 50);
$countSql  = $qb->buildCount(table: 'events', where: $where->sql);

$rows  = $client->selectWithParams($selectSql, $where->params, new JsonEachRow())->data;
$total = (int) ($client->selectWithParams($countSql, $where->params, new JsonEachRow())->data[0]['cnt'] ?? 0);

`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:

use Rasuvaeff\ClickHouseToolkit\ClickHouseFilterVisitor;
use Rasuvaeff\ClickHouseToolkit\ClickHouseQueryBuilder;

$qb = ClickHouseQueryBuilder::create(['id'], ['id' => 'UInt64'])
    ->withVisitor(new MyCustomVisitor());

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

use Rasuvaeff\ClickHouseToolkit\ClickHouseDataReader;
use Rasuvaeff\ClickHouseToolkit\ClickHouseDataType as T;
use Rasuvaeff\ClickHouseToolkit\ClickHouseQueryBuilder;
use Yiisoft\Data\Reader\Filter\Equals;
use Yiisoft\Data\Reader\Sort;

$reader = new ClickHouseDataReader(
    client: $client,
    table: 'events',
    queryBuilder: new ClickHouseQueryBuilder(
        allowedFields: ['id', 'type', 'created_at'],
    fieldTypes: ['id' => T::UInt64, 'created_at' => T::DateTime],
        defaultSort: 'id DESC',
    ),
    mapper: static fn (array $row): array => ['id' => (int) $row['id'], 'type' => (string) $row['type']],
    columns: ['id', 'type'],
);

$page = $reader
    ->withFilter(new Equals('type', 'click'))
    ->withSort(Sort::only(['id'])->withOrder(['id' => 'desc']))
    ->withLimit(20)
    ->withOffset(40);

$total = $page->count();   // ignores limit/offset
$rows  = $page->read();    // mapped values

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.

read() / getIterator() materialize the whole result in memory. To iterate a large result set with bounded memory, use ClickHouseKeysetReader below.

`ClickHouseKeysetReader`

Streams a large result set with bounded memory using keyset (seek) pagination. Each page is a normal query — WHERE <key> > <last-seen> ORDER BY <key> LIMIT <pageSize> — so the whole result is never loaded at once and, unlike LIMIT/OFFSET, deep pages stay cheap (a primary-index range scan instead of skipping rows). The query builder's mandatory (tenant/ACL) filter and the allow-list apply on every page.

use Rasuvaeff\ClickHouseToolkit\ClickHouseKeysetReader;
use Rasuvaeff\ClickHouseToolkit\ClickHouseQueryBuilder;
use Rasuvaeff\ClickHouseToolkit\ClickHouseDataType as T;
use Yiisoft\Data\Reader\Filter\Equals;

$reader = new ClickHouseKeysetReader(
    client: $client,
    table: 'events',
    queryBuilder: new ClickHouseQueryBuilder(
        allowedFields: ['id', 'status'],
        fieldTypes: ['id' => T::UInt64],
    ),
    mapper: static fn (array $row): int => (int) $row['id'],
    keyColumns: ['id' => T::UInt64],   // ordered map column => ClickHouse type
    columns: ['id', 'status'],          // key columns are added automatically
    pageSize: 1000,
    filter: new Equals('status', 'active'),
);

foreach ($reader->stream() as $id) {   // one page in memory at a time
    // ...
}

The key columns must form a unique, ascending total order — for a non-unique sort column add a unique tie-breaker, expressed as a column tuple compared with ClickHouse tuple comparison:

keyColumns: ['created_at' => T::DateTime, 'id' => T::UInt64],
// boundary: (created_at, id) > ({ck0:DateTime}, {ck1:UInt64})

Otherwise rows sharing a boundary key can be skipped. Key columns must be non-nullable. Boundary parameters use reserved ck0, ck1, … names — keep them clear of any ClickHouseRawFilter in your base filter.

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

use Rasuvaeff\ClickHouseToolkit\ClickHouseBatchWriter;

$writer = new ClickHouseBatchWriter(
    client: $client,
    table: 'events',
    columns: ['id', 'type', 'user_id', 'created_at'],
    batchSize: 1000,
);

$writer->write($rows); // $rows: iterable<array<string, mixed>> — a generator keeps memory flat

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

For high-throughput ingestion, pass ClickHouse query settings — they are applied to every batch INSERT. For example, offload buffering to the server with async inserts:

$writer = new ClickHouseBatchWriter(
    client: $client,
    table: 'events',
    columns: ['id', 'type', 'user_id', 'created_at'],
    batchSize: 10_000,
    settings: ['async_insert' => 1, 'wait_for_async_insert' => 0],
);

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

use Rasuvaeff\ClickHouseToolkit\ClickHouseDataType as T;
use Rasuvaeff\ClickHouseToolkit\ClickHouseTableBuilder;

ClickHouseTableBuilder::create($client, 'events')
    ->ifNotExists()
    ->column('id', T::UInt64)
    ->column('created_at', T::DateTime)
    ->engine('MergeTree()')
    ->partitionBy('toYYYYMM(created_at)')
    ->primaryKey('id')
    ->orderBy('(id, created_at)')
    ->execute();

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.

use Rasuvaeff\ClickHouseToolkit\ClickHousePartitionManager;

$pm = new ClickHousePartitionManager($client);

foreach ($pm->getPartitions('events') as $p) {
    // ['partition' => '202401', 'partition_id' => '202401', 'rows' => 12345, 'bytes' => 987654]
}

$pm->dropPartition('events', '202401');
$pm->detachPartition('events', '202401');
$pm->attachPartition('events', '202401');
$pm->freezePartition('events', '202401');
$pm->clearColumnInPartition('events', '202401', 'payload');
$pm->movePartition('events', 'events_archive', '202401');     // MOVE … TO TABLE
$pm->replacePartition('events', 'events_mirror', '202401');   // REPLACE … FROM

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

use Rasuvaeff\ClickHouseToolkit\ClickHouseMutationBuilder;

$mb = new ClickHouseMutationBuilder($client);

$mb->update('events', 'status = {st:String}', 'id = {id:UInt64}', ['st' => 'archived', 'id' => 42]);
$mb->delete('events', 'created_at < {cutoff:DateTime}', ['cutoff' => '2023-01-01 00:00:00']);

$mb->waitForMutations('events', timeout: 30.0); // poll system.mutations until done -> bool

foreach ($mb->getMutations('events') as $m) {
    // ['mutation_id' => '...', 'command' => '...', 'is_done' => true, 'parts_to_do' => 0, 'latest_fail_reason' => '']
}

$mb->killMutation('events', $mutationId);

`ClickHouseMigrationRunner`

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

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.
Parameterisable — {{key}} tokens are replaced from $placeholders before the file is hashed and executed, so a package can ship DDL whose table names the application configures. An unresolved token is an error, not a query sent to the server.
Relocatable bookkeeping — $migrationsTable renames the tracking table itself.

use Rasuvaeff\ClickHouseToolkit\ClickHouseMigrationRunner;

$runner = new ClickHouseMigrationRunner(
    client: $client,
    migrationsPath: __DIR__ . '/migrations',
    logger: $logger, // optional PSR-3
    placeholders: ['events_table' => 'my_events'], // optional; replaces {{events_table}}
    migrationsTable: '_migrations', // optional; the bookkeeping table
);

$applied = $runner->run(); // list<string> of files applied this call

Choosing the bookkeeping table

Two situations need a name other than the default:

Adopting the runner where _migrations already exists with a different schema — a home-grown (name, applied_at) table, say. The runner's own CREATE TABLE IF NOT EXISTS finds the table and does nothing, then the first read fails on the missing checksum column. That happens before any migration file is read, so the repair cannot ship as a migration. Point the runner at a fresh name instead, let it re-apply the (idempotent) migrations, and drop the old table whenever convenient.
Two applications sharing one ClickHouse database — give each its own bookkeeping table.

The name is interpolated into SQL, not bound, so it must be a plain identifier (/^[A-Za-z_]\w*\z/); anything else throws an InvalidArgumentException from the constructor. A db-qualified analytics._migrations is refused as well: the backticks wrap the whole string, so it would name one table containing a dot. Select the database on the client instead.

The checksum covers the resolved SQL, not the raw file. Two consequences, both deliberate:

switching a shipped migration from a literal name to {{placeholder}} is invisible to installations using the default value — the resolved text is byte-identical to what they applied;
changing a value after a migration has been applied is reported as a divergence instead of quietly creating a second table. Create the new table yourself, or drop the bookkeeping row, then re-run.

Placeholder values are interpolated verbatim into DDL. They come from your configuration, not from user input — validate them with Identifier::assertPlain() if they can ever originate elsewhere.

Tracking table (created automatically, named by $migrationsTable):

CREATE TABLE IF NOT EXISTS `_migrations` (
    name String, checksum String, applied_at DateTime64(6) DEFAULT now64(6)
) ENGINE = ReplacingMergeTree(applied_at) ORDER BY name

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

`ClickHouseMigrationGenerator` & `status()`

Two helpers that round out the migration workflow:

ClickHouseMigrationGenerator creates a new migration file with the next sequential numeric prefix. It is a plain filesystem helper — no ClickHouse client required.
ClickHouseMigrationRunner::status() reports the state of every migration file relative to the bookkeeping table.

use Rasuvaeff\ClickHouseToolkit\ClickHouseMigrationGenerator;

$generator = new ClickHouseMigrationGenerator(__DIR__ . '/migrations');

$path = $generator->generate('add events index');
// Creates migrations/003_add_events_index.sql (003 = highest existing prefix + 1)
// with a header comment; write your DDL below the header.

The description is sanitised to a slug (lowercase, non-alphanumeric runs collapse to _, trimmed). Prefix width matches the widest existing one and grows past 999 (999 → 1000).

use Rasuvaeff\ClickHouseToolkit\ClickHouseMigrationState;

$statuses = $runner->status(); // list<ClickHouseMigrationStatus>, sorted by name

foreach ($statuses as $status) {
    // $status->name       — '001_create_events.sql'
    // $status->state      — ClickHouseMigrationState::Applied
    // $status->checksum   — sha1 of the current file (null for Missing)
    // $status->appliedAt  — stored applied_at string (null for Pending)
}

State	Meaning
`Applied`	File exists, checksum matches the stored one.
`Pending`	File exists, not recorded in the bookkeeping table yet.
`Missing`	Recorded in the bookkeeping table, but the source file was removed.
`Diverged`	File exists and was recorded, but the checksum no longer matches (or conflicting checksums were recorded).

Unlike run(), status() never throws on divergence — it surfaces the anomaly through the Diverged state.

Console commands

Three Symfony Console commands wrap the migration API for CLI use. They live in Rasuvaeff\ClickHouseToolkit\Command and require symfony/console (^7.2, listed in require).

Command	Wraps	Description
`clickhouse:migrations:generate <description>`	`ClickHouseMigrationGenerator::generate()`	Creates `NNN_description.sql` with the next prefix. Exit `2` on invalid description, `1` on filesystem failure.
`clickhouse:migrations:status`	`ClickHouseMigrationRunner::status()`	Prints a table of migrations + state counts. Exit `1` when any `Missing` or `Diverged` exists.
`clickhouse:migrations:migrate`	`ClickHouseMigrationRunner::run()`	Applies pending migrations, one line per file. Idempotent.

use Rasuvaeff\ClickHouseToolkit\ClickHouseMigrationGenerator;
use Rasuvaeff\ClickHouseToolkit\ClickHouseMigrationRunner;
use Rasuvaeff\ClickHouseToolkit\Command\ClickHouseMigrationsGenerateCommand;
use Rasuvaeff\ClickHouseToolkit\Command\ClickHouseMigrationsRunCommand;
use Rasuvaeff\ClickHouseToolkit\Command\ClickHouseMigrationsStatusCommand;
use Symfony\Component\Console\Application;

$application = new Application('clickhouse-migrations');
$application->addCommands([
    new ClickHouseMigrationsGenerateCommand(new ClickHouseMigrationGenerator($migrationsPath)),
    new ClickHouseMigrationsStatusCommand($runner),
    new ClickHouseMigrationsRunCommand($runner),
]);
$application->run();

A runnable wiring example is in examples/console-application.php.

To wire the API directly into Yii3, Symfony or Laravel (container binding + your own console command), see examples/framework-integrations.md.

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

use Rasuvaeff\ClickHouseToolkit\ClickHouseDataType as T;

T::UInt64;                                  // 'UInt64'
T::nullable(T::String);                     // 'Nullable(String)'
T::array(T::nullable(T::String));           // 'Array(Nullable(String))'
T::map(T::String, T::UInt64);               // 'Map(String, UInt64)'
T::decimal(10, 2);                          // 'Decimal(10, 2)'
T::dateTime64(3, 'UTC');                    // "DateTime64(3, 'UTC')"
T::enum8(['active' => 1, 'inactive' => 2]); // "Enum8('active' = 1, 'inactive' = 2)"

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

Interfaces

Interface	Method(s)	Purpose
`ClickHouseMigrationRunnerInterface`	`run(): list<string>`	Implemented by `ClickHouseMigrationRunner`.
`ClickHouseWriterInterface`	`write(iterable $rows): void`	Implemented by `ClickHouseBatchWriter`.
`ClickHouseReaderInterface`	`findByFilters(...)`, `countByFilters(...)`	A simpler reader contract than `DataReaderInterface`; implement it per table when you don't need the full reader (see `examples/EventReader.php`).
`ClickHouseFilterVisitor`	`visit*()` per filter type	SQL generation for each filter type. Implemented by `ClickHouseSqlFilterVisitor`. Inject a custom implementation via `withVisitor()`.

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

$qb = new ClickHouseQueryBuilder(
    allowedFields: ['created_at'],
    fieldTypes: ['created_at' => T::DateTime],
    serverTimezone: 'UTC',
);

// A DateTimeImmutable in Europe/Moscow (+03:00) will be formatted as UTC.
$where = $qb->buildWhere(new Equals('created_at', new \DateTimeImmutable('2024-06-15 15:00:00+03:00')));
// params: ['p0' => '2024-06-15 12:00:00']

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

Dependency injection

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

use Rasuvaeff\ClickHouseToolkit\ClickHouseClientFactory;
use Rasuvaeff\ClickHouseToolkit\ClickHouseConfig;
use Rasuvaeff\ClickHouseToolkit\ClickHouseMigrationRunner;
use Rasuvaeff\ClickHouseToolkit\ClickHouseMigrationRunnerInterface;
use SimPod\ClickHouseClient\Client\ClickHouseClient;
use SimPod\ClickHouseClient\Client\PsrClickHouseClient;

return [
    ClickHouseConfig::class => static fn (): ClickHouseConfig => new ClickHouseConfig(
        host: $_ENV['CLICKHOUSE_HOST'] ?? 'clickhouse',
        port: (int) ($_ENV['CLICKHOUSE_PORT'] ?? 8123),
        database: $_ENV['CLICKHOUSE_DB'] ?? 'app',
        username: $_ENV['CLICKHOUSE_USER'] ?? 'default',
        password: $_ENV['CLICKHOUSE_PASSWORD'] ?? '',
    ),

    PsrClickHouseClient::class => static fn (ClickHouseClientFactory $f): PsrClickHouseClient => $f->create(),
    ClickHouseClient::class => PsrClickHouseClient::class, // toolkit classes type-hint the interface

    ClickHouseMigrationRunnerInterface::class => static fn (ClickHouseClient $client): ClickHouseMigrationRunner => new ClickHouseMigrationRunner(
        client: $client,
        migrationsPath: dirname(__DIR__) . '/resources/clickhouse-migrations',
    ),
];

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).
Migration rollback / down-migrations. ClickHouse DDL (ALTER ... DELETE) is often irreversible, so rollback creates a false sense of safety. Use forward-fix migrations with idempotent DDL instead.
Connection pooling or retries. Inject your own PSR-18 client (see Quick start) if you need timeouts, retry policies or circuit breakers.
Framework bootloaders/service providers (wire it in your app — see Dependency injection).

Examples

Runnable, self-contained examples live in examples/:

File	Server?	Shows
`query-builder.php`	no	Every supported filter/sort/select/count/distinct — prints the generated SQL.
`di-container.php`	no	Wiring the toolkit into a PSR-11 container.
`client.php`	yes	Building a client and running a query.
`run-migrations.php` + `migrations/`	yes	Applying `*.sql` migrations idempotently.
`generate-migration.php`	no	Creating a new migration file with `ClickHouseMigrationGenerator`.
`migrations-status.php`	yes	Reporting migration state via `ClickHouseMigrationRunner::status()`.
`console-application.php`	yes	Wiring the three Symfony Console commands into an `Application`.
`batch-writer.php`	yes	Batched inserts via `ClickHouseBatchWriter`.
`reader.php` + `EventReader.php`	yes	A `ClickHouseReaderInterface` implementation with row mapping.
`data-reader.php`	yes	Immutable `ClickHouseDataReader` (paginator-ready).

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

Development

composer install
composer build       # validate + normalize + require-checker + cs + psalm + testo
composer test        # testo Unit + Integration suites
composer cs:fix      # apply php-cs-fixer
composer psalm       # static analysis (errorLevel=1)

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

CLICKHOUSE_HOST=127.0.0.1 CLICKHOUSE_PASSWORD=… vendor/bin/testo --suite=Integration

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

License

BSD-3-Clause. See LICENSE.md.

rasuvaeff / clickhouse-toolkit

Package info

Security

Statistics

README

Table of contents

Requirements

Installation

Quick start

Components

`ClickHouseConfig` & `ClickHouseClientFactory`

`ClickHouseQueryBuilder` & `WhereClause`

`ClickHouseFilterVisitor`

`ClickHouseDataReader`

`ClickHouseKeysetReader`

`ClickHouseBatchWriter`

`ClickHouseTableBuilder`

`ClickHousePartitionManager`

`ClickHouseMutationBuilder`

`ClickHouseMigrationRunner`

Choosing the bookkeeping table

`ClickHouseMigrationGenerator` & `status()`

Console commands

`ClickHouseDataType`

Interfaces

Timezone handling

Dependency injection

Security notes

What is intentionally not included

Examples

Development

License

rasuvaeff / clickhouse-toolkit

Package info

Security

Statistics

README

Table of contents

Requirements

Installation

Quick start

Components

ClickHouseConfig & ClickHouseClientFactory

ClickHouseQueryBuilder & WhereClause

ClickHouseFilterVisitor

ClickHouseDataReader

ClickHouseKeysetReader

ClickHouseBatchWriter

ClickHouseTableBuilder

ClickHousePartitionManager

ClickHouseMutationBuilder

ClickHouseMigrationRunner

Choosing the bookkeeping table

ClickHouseMigrationGenerator & status()

Console commands

ClickHouseDataType

Interfaces

Timezone handling

Dependency injection

Security notes

What is intentionally not included

Examples

Development

License

`ClickHouseConfig` & `ClickHouseClientFactory`

`ClickHouseQueryBuilder` & `WhereClause`

`ClickHouseFilterVisitor`

`ClickHouseDataReader`

`ClickHouseKeysetReader`

`ClickHouseBatchWriter`

`ClickHouseTableBuilder`

`ClickHousePartitionManager`

`ClickHouseMutationBuilder`

`ClickHouseMigrationRunner`

`ClickHouseMigrationGenerator` & `status()`

`ClickHouseDataType`