PHPackages                             rasuvaeff/clickhouse-toolkit - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. rasuvaeff/clickhouse-toolkit

ActiveLibrary[Database & ORM](/categories/database)

rasuvaeff/clickhouse-toolkit
============================

Framework-agnostic ClickHouse toolkit for PHP: parameterized query builder, data reader, batch writer, DDL builder, partition manager, mutation builder, and migration runner.

v1.4.0(2w ago)12563BSD-3-ClausePHPPHP 8.3 - 8.5CI passing

Since May 30Pushed 1w agoCompare

[ Source](https://github.com/rasuvaeff/clickhouse-toolkit)[ Packagist](https://packagist.org/packages/rasuvaeff/clickhouse-toolkit)[ Docs](https://github.com/rasuvaeff/clickhouse-toolkit)[ RSS](/packages/rasuvaeff-clickhouse-toolkit/feed)WikiDiscussions master Synced 1w ago

READMEChangelogDependencies (55)Versions (8)Used By (3)

ClickHouse Toolkit
==================

[](#clickhouse-toolkit)

[![Latest Stable Version](https://camo.githubusercontent.com/ea12047bb21ce01db1cfc5dad438e18dd221fe5a6a53db321de3b969ac53ce52/68747470733a2f2f706f7365722e707567782e6f72672f7261737576616566662f636c69636b686f7573652d746f6f6c6b69742f76)](https://packagist.org/packages/rasuvaeff/clickhouse-toolkit)[![Total Downloads](https://camo.githubusercontent.com/14f361f0ad7fe377252fc860e4d758400c7eb918d0d28de75dab74e1ae568e9c/68747470733a2f2f706f7365722e707567782e6f72672f7261737576616566662f636c69636b686f7573652d746f6f6c6b69742f646f776e6c6f616473)](https://packagist.org/packages/rasuvaeff/clickhouse-toolkit)[![Build](https://github.com/rasuvaeff/clickhouse-toolkit/actions/workflows/build.yml/badge.svg)](https://github.com/rasuvaeff/clickhouse-toolkit/actions/workflows/build.yml)[![Static analysis](https://github.com/rasuvaeff/clickhouse-toolkit/actions/workflows/static-analysis.yml/badge.svg)](https://github.com/rasuvaeff/clickhouse-toolkit/actions/workflows/static-analysis.yml)[![Psalm level](https://camo.githubusercontent.com/68f7f31799f2b93c710b14ba3877072e7fe07ec9d7cee3fdf67e14beab3e1b6f/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f7073616c6d2d6c6576656c5f312d626c75652e737667)](https://github.com/rasuvaeff/clickhouse-toolkit/actions/workflows/static-analysis.yml)[![PHP](https://camo.githubusercontent.com/ce51cbede88cef8a2a0d5cebf7d9ee8a53e122c930776a163107ec2b81d7d721/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f646570656e64656e63792d762f7261737576616566662f636c69636b686f7573652d746f6f6c6b69742f706870)](https://packagist.org/packages/rasuvaeff/clickhouse-toolkit)[![License](https://camo.githubusercontent.com/6cb285b57819f8de0acfb34923298f4f569f962544e8fe35331da2d163f4e485/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d4253442d2d332d2d436c617573652d626c75652e737667)](LICENSE.md)

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`](https://github.com/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`](https://github.com/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`](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`](AGENTS.md).

Table of contents
-----------------

[](#table-of-contents)

- [Requirements](#requirements)
- [Installation](#installation)
- [Quick start](#quick-start)
- [Components](#components)
    - [ClickHouseConfig & ClickHouseClientFactory](#clickhouseconfig--clickhouseclientfactory)
    - [ClickHouseQueryBuilder & WhereClause](#clickhousequerybuilder--whereclause)
    - [ClickHouseFilterVisitor](#clickhousefiltervisitor)
    - [ClickHouseDataReader](#clickhousedatareader)
    - [ClickHouseKeysetReader](#clickhousekeysetreader)
    - [ClickHouseBatchWriter](#clickhousebatchwriter)
    - [ClickHouseTableBuilder](#clickhousetablebuilder)
    - [ClickHousePartitionManager](#clickhousepartitionmanager)
    - [ClickHouseMutationBuilder](#clickhousemutationbuilder)
    - [ClickHouseDataType](#clickhousedatatype)
    - [ClickHouseMigrationRunner](#clickhousemigrationrunner)
    - [ClickHouseMigrationGenerator & status()](#clickhousemigrationgenerator--status)
    - [Console commands](#console-commands)
    - [Interfaces](#interfaces)
    - [Timezone handling](#timezone-handling)
- [Dependency injection](#dependency-injection)
- [Security notes](#security-notes)
- [What is intentionally not included](#what-is-intentionally-not-included)
- [Examples](#examples)
- [Development](#development)
- [License](#license)

Requirements
------------

[](#requirements)

RequirementVersionPHP`^8.3`A PSR-18 HTTP client + PSR-17 factoriesany implementationClickHouse servertested 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](https://docs.php-http.org/en/latest/discovery.html), or you can inject your own.

Installation
------------

[](#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
-----------

[](#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
----------

[](#components)

### `ClickHouseConfig` & `ClickHouseClientFactory`

[](#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`

[](#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
    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
) {}
```

MethodReturnsDescription`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` filterRendered asNotes`All`empty `WHERE``None``0`matches nothing`Equals``field = {p0:Type}``GreaterThan` / `GreaterThanOrEqual``field > / >= {p0:Type}``LessThan` / `LessThanOrEqual``field < /  T::UInt64])
    ->withMandatoryFilter(new Equals('tenant_id', $tenantId));

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

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

[](#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`

[](#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`

[](#clickhousekeysetreader)

Streams a large result set with **bounded memory** using keyset (seek) pagination. Each page is a normal query — `WHERE  >  ORDER BY  LIMIT ` — 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`

[](#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 — 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](https://clickhouse.com/docs/en/optimize/asynchronous-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`

[](#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`

[](#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`

[](#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`

[](#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.

```
use Rasuvaeff\ClickHouseToolkit\ClickHouseMigrationRunner;

$runner = new ClickHouseMigrationRunner(
    client: $client,
    migrationsPath: __DIR__ . '/migrations',
    logger: $logger, // optional PSR-3
);

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

Tracking table (created automatically):

```
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 &amp; 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.

### `ClickHouseMigrationGenerator` &amp; `status()`

[](#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 `_migrations` 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, 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)
}
```

StateMeaning`Applied`File exists, checksum matches the stored one.`Pending`File exists, not recorded in `_migrations` yet.`Missing`Recorded in `_migrations`, 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

[](#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`).

CommandWrapsDescription`clickhouse:migrations:generate ``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.Register them in your Symfony Console `Application`:

```
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`](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`](examples/framework-integrations.md).

### `ClickHouseDataType`

[](#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

[](#interfaces)

InterfaceMethod(s)Purpose`ClickHouseMigrationRunnerInterface``run(): list`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`](examples/EventReader.php)).`ClickHouseFilterVisitor``visit*()` per filter typeSQL generation for each filter type. Implemented by `ClickHouseSqlFilterVisitor`. Inject a custom implementation via `withVisitor()`.### Timezone handling

[](#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
--------------------

[](#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`](examples/di-container.php) for a runnable plain-PHP container wiring.

Security notes
--------------

[](#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
----------------------------------

[](#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](#quick-start)) if you need timeouts, retry policies or circuit breakers.
- Framework bootloaders/service providers (wire it in your app — see [Dependency injection](#dependency-injection)).

Examples
--------

[](#examples)

Runnable, self-contained examples live in [`examples/`](examples/):

FileServer?Shows[`query-builder.php`](examples/query-builder.php)noEvery supported filter/sort/select/count/distinct — prints the generated SQL.[`di-container.php`](examples/di-container.php)noWiring the toolkit into a PSR-11 container.[`client.php`](examples/client.php)yesBuilding a client and running a query.[`run-migrations.php`](examples/run-migrations.php) + [`migrations/`](examples/migrations)yesApplying `*.sql` migrations idempotently.[`generate-migration.php`](examples/generate-migration.php)noCreating a new migration file with `ClickHouseMigrationGenerator`.[`migrations-status.php`](examples/migrations-status.php)yesReporting migration state via `ClickHouseMigrationRunner::status()`.[`console-application.php`](examples/console-application.php)yesWiring the three Symfony Console commands into an `Application`.[`batch-writer.php`](examples/batch-writer.php)yesBatched inserts via `ClickHouseBatchWriter`.[`reader.php`](examples/reader.php) + [`EventReader.php`](examples/EventReader.php)yesA `ClickHouseReaderInterface` implementation with row mapping.[`data-reader.php`](examples/data-reader.php)yesImmutable `ClickHouseDataReader` (paginator-ready).See [`examples/README.md`](examples/README.md) for how to run them.

Development
-----------

[](#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
-------

[](#license)

BSD-3-Clause. See [LICENSE.md](LICENSE.md).

###  Health Score

48

—

FairBetter than 94% of packages

Maintenance97

Actively maintained with recent releases

Popularity18

Limited adoption so far

Community12

Small or concentrated contributor base

Maturity56

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~6 days

Total

7

Last Release

16d ago

PHP version history (2 changes)v1.0.0PHP ^8.3

v1.2.0PHP 8.3 - 8.5

### Community

Maintainers

![](https://www.gravatar.com/avatar/b0812d5572a7041dfe36e222d295b2e6dc55833a605350fcde58a51a5965ed30?d=identicon)[rasuvaeff](/maintainers/rasuvaeff)

---

Top Contributors

[![rasuvaeff](https://avatars.githubusercontent.com/u/1352718?v=4)](https://github.com/rasuvaeff "rasuvaeff (32 commits)")

---

Tags

analyticsbatchclickhousedatabaseolapphpquery-buildertoolkityiiyii3consolemigrationdatabasegeneratorbatchclickhouseyiianalyticsquery buildertoolkityii3data readerolap

###  Code Quality

TestsPHPUnit

Static AnalysisPsalm, Rector

Code StylePHP CS Fixer

Type Coverage Yes

### Embed Badge

![Health badge](/badges/rasuvaeff-clickhouse-toolkit/health.svg)

```
[![Health](https://phpackages.com/badges/rasuvaeff-clickhouse-toolkit/health.svg)](https://phpackages.com/packages/rasuvaeff-clickhouse-toolkit)
```

###  Alternatives

[flow-php/flow

PHP ETL - Extract Transform Load - Data processing framework

85036.3k](/packages/flow-php-flow)[tempest/framework

The PHP framework that gets out of your way.

2.2k34.4k16](/packages/tempest-framework)[telnyx/telnyx-php

Official Telnyx PHP SDK — APIs for Voice, SMS, MMS, WhatsApp, Fax, SIP Trunking, Wireless IoT, Call Control, and more. Build global communications on Telnyx's private carrier-grade network.

36789.4k2](/packages/telnyx-telnyx-php)[sylius/sylius

E-Commerce platform for PHP, based on Symfony framework.

8.5k5.9M754](/packages/sylius-sylius)[typo3/cms

TYPO3 CMS is a free open source Content Management Framework initially created by Kasper Skaarhoj and licensed under GNU/GPL.

1.2k1.9M122](/packages/typo3-cms)[matomo/matomo

Matomo is the leading Free/Libre open analytics platform

21.7k38.9k](/packages/matomo-matomo)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)