Getting Started

Minimal Setup

Installation

composer require bavix/laravel-clickhouse

Register Service Provider Add to config/app.php under providers:
```
Bavix\LaravelClickHouse\ClickHouseServiceProvider::class,
```

Configure Database Connection Add a ClickHouse connection in config/database.php:

'clickhouse' => [
    'driver' => 'clickhouse',
    'host' => env('CLICKHOUSE_HOST', 'localhost'),
    'port' => env('CLICKHOUSE_PORT', 9000),
    'database' => env('CLICKHOUSE_DATABASE', 'default'),
    'username' => env('CLICKHOUSE_USERNAME', 'default'),
    'password' => env('CLICKHOUSE_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'strict' => true,
    'engine' => 'MergeTree',
    'options' => [
        'compression' => true,
    ],
],

Set Default Connection (Optional) In .env:
```
DB_CONNECTION=clickhouse
```

First Use Case Create a model extending Bavix\LaravelClickHouse\Eloquent\Model:

use Bavix\LaravelClickHouse\Eloquent\Model;

class User extends Model
{
    protected $table = 'users';
    protected $connection = 'clickhouse';
}

Run migrations (adjust syntax for ClickHouse):

Schema::connection('clickhouse')->create('users', function (Blueprint $table) {
    $table->string('id')->primary();
    $table->string('name');
    $table->timestamp('created_at');
});

Implementation Patterns

Core Workflows

Model Definition Extend Bavix\LaravelClickHouse\Eloquent\Model for ClickHouse-specific models.

class AnalyticsEvent extends Model
{
    protected $connection = 'clickhouse';
    protected $table = 'events';
    protected $primaryKey = 'event_id';
    public $incrementing = false; // ClickHouse often uses UUIDs/strings as PKs
}

Querying with Eloquent Use standard Eloquent methods, but optimize for ClickHouse:

// Time-based queries (ClickHouse excels here)
$events = AnalyticsEvent::where('event_time', '>', now()->subDays(7))
    ->orderBy('event_time', 'desc')
    ->get();

// Grouping/aggregations
$dailyMetrics = AnalyticsEvent::selectRaw('
    toDate(event_time) as date,
    count(*) as total_events
')
->groupBy('date')
->get();

Migrations ClickHouse requires explicit engine definitions (e.g., MergeTree):

Schema::connection('clickhouse')->create('logs', function (Blueprint $table) {
    $table->string('id')->primary();
    $table->text('message');
    $table->timestamp('created_at');
    $table->engine('MergeTree() ORDER BY (created_at)');
});

Batch Inserts Leverage ClickHouse’s bulk-insert capabilities:

$events = collect(range(1, 1000))->map(fn($i) => [
    'event_id' => Str::uuid(),
    'user_id' => 1,
    'event_time' => now()->subHours($i),
]);

AnalyticsEvent::insert($events->toArray());

Materialized Views Create ClickHouse-specific materialized views:

Schema::connection('clickhouse')->createView('daily_summary', function (Blueprint $table) {
    $table->viewDefinition('
        SELECT
            toDate(event_time) as date,
            count(*) as total_events,
            uniq(user_id) as unique_users
        FROM events
        GROUP BY date
    ');
});

Raw SQL for Advanced Queries Use DB::connection('clickhouse')->select() for complex queries:

$results = DB::connection('clickhouse')->select('
    SELECT
        user_id,
        count(*) as event_count,
        avg(event_duration) as avg_duration
    FROM events
    WHERE event_time > now() - INTERVAL 30 DAY
    GROUP BY user_id
    HAVING event_count > 10
');

Integration Tips

Hybrid Architectures Use ClickHouse for analytics/read-heavy workloads and PostgreSQL/MySQL for transactions:

// Transactional data (PostgreSQL)
$user = User::create(['name' => 'John']);

// Analytics data (ClickHouse)
AnalyticsEvent::create([
    'user_id' => $user->id,
    'event_type' => 'signup',
    'event_time' => now(),
]);

Event Sourcing Store events in ClickHouse for replayability:

class Event extends Model
{
    protected $table = 'events';
    protected $connection = 'clickhouse';
    protected $dates = ['occurred_at'];
}

// Append-only writes
Event::create(['aggregate_id' => $id, 'event_data' => $data, 'occurred_at' => now()]);

Caching Queries Cache frequent aggregations:

$cacheKey = 'daily_metrics_' . now()->format('Y-m-d');
$metrics = Cache::remember($cacheKey, now()->addDay(), function () {
    return AnalyticsEvent::dailyMetrics()->get();
});

Scheduled Jobs Run ClickHouse-specific maintenance:

// Optimize tables (ClickHouse-specific)
Schema::connection('clickhouse')->statement('OPTIMIZE TABLE events FINAL');

Gotchas and Tips

Pitfalls

No Transactions ClickHouse lacks ACID transactions. Avoid relying on DB::transaction() for ClickHouse operations. Workaround: Use application-level retries or external coordination (e.g., Saga pattern).

Primary Key Constraints ClickHouse requires explicit ORDER BY in MergeTree tables. Omitting it causes errors.

// ❌ Fails
Schema::create('bad_table', function (Blueprint $table) {
    $table->string('id')->primary();
    // Missing ORDER BY clause!
});

// ✅ Works
Schema::create('good_table', function (Blueprint $table) {
    $table->string('id')->primary();
    $table->engine('MergeTree() ORDER BY (id)');
});

UUIDs as Primary Keys ClickHouse’s UUID type is not auto-incrementing. Use String + UUID generation:
```
$model->id = Str::uuid();
$model->save();
```
Soft Deletes ClickHouse lacks deleted_at support. Use a is_deleted boolean or partition tables by deletion status.

Case Sensitivity Table/column names are case-sensitive. Stick to lowercase or quote identifiers:

// ✅ Explicit quoting
$results = DB::select("SELECT * FROM `MyTable` WHERE `Name` = ?", ['John']);

DateTime Handling ClickHouse uses DateTime64 for timestamps. Ensure Laravel’s Carbon instances are compatible:

// Convert Carbon to ClickHouse-compatible string
$model->event_time = $carbonInstance->toDateTimeString();

Pagination Limits ClickHouse’s LIMIT behaves differently with OFFSET. Use LIMIT ... OFFSET cautiously or prefer skip()/take():

// Prefer
$items = Model::skip(10)->take(20)->get();

// Avoid for large offsets
$items = Model::offset(100000)->limit(20)->get();

Debugging

Query Logging Enable ClickHouse query logging in config/database.php:
```
'clickhouse' => [
    'driver' => 'clickhouse',
    'log_queries' => true, // Add this
    // ...
],
```
Logs appear in storage/logs/laravel.log.
Connection Issues Verify ClickHouse server is running and accessible:
```
telnet localhost 9000
```
Check Laravel’s .env for correct credentials.

Schema Mismatches Use Schema::hasTable() to check table existence:

if (!Schema::connection('clickhouse')->hasTable('users')) {
    Schema::connection('clickhouse')->create('users', ...);
}

Performance Bottlenecks Profile slow queries with ClickHouse’s EXPLAIN:

Laravel Clickhouse Laravel Package