README

phpClickHouse-laravel

Laravel adapter for PHP ClickHouse tooling:

https://github.com/smi2/phpClickHouse — HTTP transport and query execution
https://github.com/oralunal/clickhouse-builder — fluent query builder (fork of glushkovds/ClickhouseBuilder, itself a fork of the-tinderbox/ClickhouseBuilder)

Features

Eloquent-flavored BaseModel (create, save, insertBulk, insertAssoc, where, pagination)
PhpClickHouseLaravel\Migration base class for ClickHouse DDL migrations (single-node and cluster)
Query builder integration with settings(), chunk(), and ClickHouse-specific grammar
Column casts (currently boolean) applied on insert
Model events: creating, created, saved
Retry-on-network-error support (retries config key)
Buffer engine support via $tableForInserts / $tableSources
In-memory buffered inserts: accumulate rows with Model::buffer() and send them as a single HTTP request with Model::flushBuffer() (auto-flushed on script shutdown)
OPTIMIZE, TRUNCATE, ALTER TABLE ... DELETE, ALTER TABLE ... UPDATE helpers
Multi-instance and cluster-mode connections with active-node rotation
Publishable default config — .env is enough for most setups

Underneath, smi2/phpClickHouse handles HTTP transport (curl-only, no PDO). More: https://github.com/smi2/phpClickHouse#features

Prerequisites

PHP 8.5+
Laravel 13+
ClickHouse server 24.x (older 20+ versions usually work but are no longer tested)

Installation

1. Install via composer:

composer require oralunal/phpclickhouse-laravel

The service provider is registered automatically via Laravel package auto-discovery. If you have auto-discovery disabled, add PhpClickHouseLaravel\ClickhouseServiceProvider::class to bootstrap/providers.php (Laravel 11+) or config/app.php (Laravel 10 and below).

2. Configure the connection.

The simplest setup — just set these in your .env:

CLICKHOUSE_HOST=localhost
CLICKHOUSE_PORT=8123
CLICKHOUSE_DATABASE=default
CLICKHOUSE_USERNAME=default
CLICKHOUSE_PASSWORD=
# only if you use an https connection
CLICKHOUSE_HTTPS=true

The service provider merges sensible defaults into config('database.connections.clickhouse') for you. No config edits needed for a single-node setup.

If you want to customize defaults beyond what env vars cover, publish the config:

php artisan vendor:publish --tag=clickhouse-config

That drops a config/clickhouse.php into your app. Values you set there override the packaged defaults, and you can add further connections to the same file. Alternatively, you can define the connection yourself in config/database.php, which outranks both:

'clickhouse' => [
    'driver' => 'clickhouse',
    'host' => env('CLICKHOUSE_HOST'),
    'port' => env('CLICKHOUSE_PORT', '8123'),
    'database' => env('CLICKHOUSE_DATABASE', 'default'),
    'username' => env('CLICKHOUSE_USERNAME', 'default'),
    'password' => env('CLICKHOUSE_PASSWORD', ''),
    'timeout_connect' => env('CLICKHOUSE_TIMEOUT_CONNECT', 2),
    'timeout_query' => env('CLICKHOUSE_TIMEOUT_QUERY', 2),
    'https' => (bool) env('CLICKHOUSE_HTTPS', null),
    'retries' => env('CLICKHOUSE_RETRIES', 0),
    'settings' => [ // optional
        'max_partitions_per_insert_block' => 300,
    ],
    'fix_default_query_builder' => true,
],

Usage

You can use smi2/phpClickHouse directly:

/** @var \ClickHouseDB\Client $db */
$db = DB::connection('clickhouse')->getClient();
$statement = $db->select('SELECT * FROM summing_url_views LIMIT 2');

More about $db: https://github.com/smi2/phpClickHouse/blob/master/README.md

Or use the Eloquent-like ORM

1. Add a model:

<?php

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class MyTable extends BaseModel
{
    // Optional. Derived from class name when omitted: MyTable => my_tables.
    protected $table = 'my_table';
}

2. Add a migration:

<?php

class CreateMyTable extends \PhpClickHouseLaravel\Migration
{
    public function up()
    {
        static::write('
            CREATE TABLE my_table (
                id UInt32,
                created_at DateTime,
                field_one String,
                field_two Int32
            )
            ENGINE = MergeTree()
            ORDER BY (id)
        ');
    }

    public function down()
    {
        static::write('DROP TABLE my_table');
    }
}

Or use the Schema Builder:

<?php

use PhpClickHouseSchemaBuilder\Expression;
use PhpClickHouseSchemaBuilder\Tables\MergeTree;

class CreateMyTable extends \PhpClickHouseLaravel\Migration
{
    public function up()
    {
        static::createMergeTree('my_table', fn(MergeTree $table) => $table
            ->columns([
                $table->uInt32('id'),
                $table->datetime('created_at', 3)->default(new Expression('now64()')),
                $table->string('field_one'),
                $table->int32('field_two'),
            ])
            ->orderBy('id')
        );
    }

    public function down()
    {
        static::write('DROP TABLE my_table');
    }
}

3. Insert data.

One row:

$model = MyTable::create(['model_name' => 'model 1', 'some_param' => 1]);
# or
$model = MyTable::make(['model_name' => 'model 1']);
$model->some_param = 1;
$model->save();
# or
$model = new MyTable();
$model->fill(['model_name' => 'model 1', 'some_param' => 1])->save();

Bulk insert:

# Non-assoc
MyTable::insertBulk([['model 1', 1], ['model 2', 2]], ['model_name', 'some_param']);
# Assoc
MyTable::insertAssoc([['model_name' => 'model 1', 'some_param' => 1], ['some_param' => 2, 'model_name' => 'model 2']]);

4. Query builder:

$rows = MyTable::select(['field_one', new RawColumn('sum(field_two)', 'field_two_sum')])
    ->where('created_at', '>', '2020-09-14 12:47:29')
    ->groupBy('field_one')
    ->settings(['max_threads' => 3])
    ->getRows();

Known issues

Some of the problems are described here.

Advanced usage

Columns casting

Before insertion, the column is converted to the data type specified in $casts. This only applies to inserts, not selects. Supported: boolean.

Casts apply to insertAssoc() / buffer() by column name, and to insertBulk() by matching $casts keys against the $columns list you pass.

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class MyTable extends BaseModel
{
    /**
     * The columns that should be cast.
     *
     * @var array
     */
    protected $casts = ['some_bool_column' => 'boolean'];
}
// Then you can insert the data like this:
MyTable::insertAssoc([
    ['some_param' => 1, 'some_bool_column' => false],
]);

Events

Events are dispatched under the same names as Eloquent model events, but only a subset is fired, and which ones depends on how you insert:

Call	Events fired
`MyTable::create([...])`	`creating`, `saved`, `created`
`MyTable::make([...])->save()`	`saved`

Returning false from a creating listener cancels create(). save() does not fire creating, so it cannot be cancelled that way. Observers and the $dispatchesEvents map are Eloquent-only and are not supported.

Retries

You can retry requests on non-200 responses (e.g., transient network errors).

In .env:

CLICKHOUSE_RETRIES=2

retries is optional; default is 0 (a single attempt, no retries). 1 means one attempt + one retry on error (two total).

Working with huge rows

Chunk results like in Laravel:

// Split the result into chunks of 30 rows
$rows = MyTable::select(['field_one', 'field_two'])
    ->chunk(30, function ($rows) {
        foreach ($rows as $row) {
            echo $row['field_two'] . "\n";
        }
    });

Buffer engine for insert queries

See https://clickhouse.tech/docs/en/engines/table-engines/special/buffer/

<?php

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class MyTable extends BaseModel
{
    // Optional; derived from class name when omitted.
    protected $table = 'my_table';
    // All inserts go to $tableForInserts, selects read from $table.
    protected $tableForInserts = 'my_table_buffer';
}

If you also want to read from the buffer table, set its name as $table:

<?php

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class MyTable extends BaseModel
{
    protected $table = 'my_table_buffer';
}

In-memory buffered inserts

Different from the Buffer table engine above — this is a process-local row buffer kept in PHP memory. Useful when you want to coalesce many small writes into a single HTTP request without setting up a Buffer table on the ClickHouse side.

MyTable::buffer(['model_name' => 'model 1', 'some_param' => 1]);
MyTable::buffer(['model_name' => 'model 2', 'some_param' => 2]);
// ... add as many as you like, possibly from different code paths ...

MyTable::flushBuffer(); // single insertAssocBulk HTTP request

buffer() accepts either a single associative row or an array of rows:

MyTable::buffer([
    ['model_name' => 'model 1', 'some_param' => 1],
    ['model_name' => 'model 2', 'some_param' => 2],
]);

The cast pipeline used by insertAssoc() is applied at buffer time, so $casts keeps working for buffered rows too.

If you forget to call flushBuffer(), the package flushes every model's buffer automatically at script shutdown (via Laravel's Application::terminating() hook plus a register_shutdown_function fallback for non-HTTP scripts). Errors during auto-flush are logged via report() rather than thrown, since the response has typically already been sent.

If a manual flushBuffer() call fails (network error, schema mismatch, etc.), the exception bubbles up and the buffer is preserved so you can retry:

try {
    MyTable::flushBuffer();
} catch (\Throwable $e) {
    // rows are still in MyTable::getBufferedRows() — fix the issue and retry
}

Each model class has its own buffer keyed by class name, so different models can buffer concurrently without interfering. Available helpers:

Method	Purpose
`MyTable::buffer($rowOrRows)`	Append a row (or rows) to the buffer
`MyTable::flushBuffer()`	Send the buffer; returns `Statement` or `null` if empty
`MyTable::bufferCount()`	Number of rows currently buffered for this model
`MyTable::getBufferedRows()`	Snapshot of buffered rows (debug / inspection)
`MyTable::clearBuffer()`	Discard the buffer without sending
`BaseModel::flushAllBuffers(silent: false)`	Flush every model that has buffered rows

OPTIMIZE Statement

See https://clickhouse.com/docs/en/sql-reference/statements/optimize/

MyTable::optimize($final = false, $partition = null);

TRUNCATE Statement

Remove all data from a table:

MyTable::truncate();

Deletions

See https://clickhouse.com/docs/en/sql-reference/statements/alter/delete/

MyTable::where('field_one', 123)->delete();

Using the buffer engine with OPTIMIZE / ALTER TABLE DELETE:

<?php

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class MyTable extends BaseModel
{
    // SELECT and INSERT on $table
    protected $table = 'my_table_buffer';
    // OPTIMIZE, TRUNCATE, and where()->update() / where()->delete() on $tableSources
    protected $tableSources = 'my_table';
}

Updates

See https://clickhouse.com/docs/en/sql-reference/statements/alter/update/

MyTable::where('field_one', 123)->update(['field_two' => 'new_val']);
// or an expression
MyTable::where('field_one', 123)
    ->update(['field_two' => new RawColumn("concat(field_two,'new_val')")]);

Helpers for inserting different data types

// Array data type
MyTable::insertAssoc([
    ['id' => 1, 'field_one' => 'str', 'field_array' => new InsertArray(['a', 'b'])],
]);

insertAssoc() takes column => value rows. For positional rows, use insertBulk() with an explicit column list:

MyTable::insertBulk([[1, 'str', new InsertArray(['a', 'b'])]], ['id', 'field_one', 'field_array']);

Working with multiple ClickHouse instances in a project

config/clickhouse.php is a map from connection name to connection config. The service provider merges every entry into config('database.connections.<name>'), so you can declare additional ClickHouse connections alongside the default one in a single file.

1. Publish the config if you haven't already:

php artisan vendor:publish --tag=clickhouse-config

Then add a second connection in config/clickhouse.php:

return [
    'clickhouse' => [
        // ... default connection
    ],

    'clickhouse2' => [
        'driver' => 'clickhouse',
        'host' => env('CLICKHOUSE2_HOST', '127.0.0.1'),
        'port' => env('CLICKHOUSE2_PORT', '8123'),
        'database' => 'default',
        'username' => 'default',
        'password' => '',
        'timeout_connect' => 2,
        'timeout_query' => 2,
        'https' => false,
        'retries' => 0,
        'fix_default_query_builder' => true,
    ],
];

Precedence, highest first: config/database.php's connections array, then your published config/clickhouse.php, then the packaged defaults. So adding the same shape to config/database.php works too, and overrides both.

2. Add a model pointing at it:

<?php

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class MyTable2 extends BaseModel
{
    protected $connection = 'clickhouse2';

    protected $table = 'my_table2';
}

3. Add a migration bound to that connection:

<?php

return new class extends \PhpClickHouseLaravel\Migration
{
    protected $connection = 'clickhouse2';

    public function up()
    {
        static::write('CREATE TABLE my_table2 ...');
    }

    public function down()
    {
        static::write('DROP TABLE my_table2');
    }
};

Cluster mode

Important!

Each ClickHouse node must share the same database name, username, and password.
Reads and writes go to the first reachable node.
Migrations execute on all nodes. If any node is unreachable, the migration throws.
ReplicatedMergeTree uses the {replica} and {shard} macros — those must be defined on each ClickHouse server (in config.xml or config.d/*.xml), not in this package. Example config:
```
<macros>
    <shard>01</shard>
    <replica>clickhouse01</replica>
</macros>
```
See tests/docker/clickhouse01/config.xml in this repo for a working example, or the ClickHouse docs: https://clickhouse.com/docs/en/operations/settings/settings#server_settings-macros

Your config/database.php should look like:

'clickhouse' => [
    'driver' => 'clickhouse',
    'cluster' => [
        [
            'host' => 'clickhouse01',
            'port' => '8123',
        ],
        [
            'host' => 'clickhouse02',
            'port' => '8123',
        ],
    ],
    // Optional. When set, Migration::createMergeTree() adds
    // ON CLUSTER '<name>' to the DDL it compiles. It must match a cluster
    // declared in your ClickHouse server config (remote_servers).
    // Without it the table is still created on every node, because
    // migrations are dispatched to each node in turn.
    // If you set it, also call ->ifNotExists() in createMergeTree() — see below.
    'cluster_name' => 'company_cluster',
    'database' => env('CLICKHOUSE_DATABASE', 'default'),
    'username' => env('CLICKHOUSE_USERNAME', 'default'),
    'password' => env('CLICKHOUSE_PASSWORD', ''),
    'timeout_connect' => env('CLICKHOUSE_TIMEOUT_CONNECT', 2),
    'timeout_query' => env('CLICKHOUSE_TIMEOUT_QUERY', 2),
    'https' => (bool) env('CLICKHOUSE_HTTPS', null),
    'retries' => env('CLICKHOUSE_RETRIES', 0),
    'settings' => [ // optional
        'max_partitions_per_insert_block' => 300,
    ],
    'fix_default_query_builder' => true,
],

With cluster_name set, add ->ifNotExists() to createMergeTree():

static::createMergeTree('my_table', fn(MergeTree $table) => $table
    ->ifNotExists()
    ->columns([...])
    ->orderBy('id')
);

Migrations are dispatched to each node in turn, and ON CLUSTER already creates the table on every node from the first dispatch — so without IF NOT EXISTS the second node's identical statement fails with TABLE_ALREADY_EXISTS.

Migration:

<?php

return new class extends \PhpClickHouseLaravel\Migration
{
    public function up()
    {
        static::write("
            CREATE TABLE my_table (
                id UInt32,
                created_at DateTime,
                field_one String,
                field_two Int32
            )
            ENGINE = ReplicatedMergeTree('/clickhouse/tables/default.my_table', '{replica}')
            ORDER BY (id)
        ");
    }

    public function down()
    {
        static::write('DROP TABLE my_table');
    }
};

You can read the current node and rotate to the next:

$row = new MyTable();
echo $row->getThisClient()->getConnectHost();
// will print 'clickhouse01'
$row->resolveConnection()->getCluster()->slideNode();
echo $row->getThisClient()->getConnectHost();
// will print 'clickhouse02'

Contributing

The package is developed against Orchestra Testbench with a local ClickHouse in Docker. To run the test suite locally:

docker compose -f docker-compose.test.yaml up -d
composer install
composer test

See docs/howto_run_local_test.md for prerequisites, cluster-test notes, and using vendor/bin/testbench / Laravel Boost during development.

See CONTRIBUTING.md for branch and commit conventions, the CHANGELOG.md policy, and the release process.

oralunal / phpclickhouse-laravel

Maintainers

Package info

Statistics

Security