Clickhouse Builder Laravel Package

Getting Started

1. Install the package and, for Laravel/Lumen, its ClickHouse client dependency:

   composer require glushkovds/clickhouse-builder
   

2. In Laravel, register the service provider (auto-discovered on modern Laravel) and publish the config:

   php artisan vendor:publish --provider="Glushkovds\ClickHouseBuilder\ClickHouseBuilderServiceProvider"
   

   Then fill in config/clickhouse.php (HTTP host, port 8123, database, user, password). Lumen requires the provider and config to be wired manually in bootstrap/app.php.

3. Look first at src/ClickHouseBuilder.php and the service provider — the package is small enough that the source is the documentation. Skim the README and any tests/ directory for real query shapes before writing your own.

4. First use case — pull an aggregation for a dashboard:

   use Glushkovds\ClickHouseBuilder\ClickHouseBuilder;
   
   $rows = app(ClickHouseBuilder::class)
       ->select('event_date', 'count() AS total')
       ->from('events')
       ->where('event_date', '>=', '2026-01-01')
       ->groupBy('event_date')
       ->orderBy('event_date')
       ->get();
   

Start with a single read-only reporting table before introducing inserts or schema management.

Implementation Patterns

• Wrap it in a repository. Inject the builder (or the container binding) into a StatsRepository / EventQuery class rather than calling app(...) from controllers. This gives you one place to add logging, timeouts, and result casting.

• Never share builder state. If you bind it as a singleton, fluent calls accumulate. Create a fresh instance per query, or clone before extending:

  $base = app(ClickHouseBuilder::class)->from('events')->where('tenant_id', $tenantId);
  $daily = (clone $base)->select('toDate(ts) AS d', 'count() AS c')->groupBy('d')->get();
  

• Use toSql() for debugging and tests. Assert generated SQL in unit tests without a live server; run integration tests against a ClickHouse container in CI.

• Bind parameters properly. Prefer where('col', '=', $value) or explicit bindings over string interpolation — ClickHouse errors are cryptic when values are mis-escaped.

• Batch inserts. Build rows in chunks (10k–100k) and use the builder's insert helper or insert ... VALUES, rather than row-by-row writes.

• Page large reads by time or LIMIT/OFFSET. ClickHouse will happily return millions of rows; enforce limit in the repository and iterate by date partitions.

• Keep analytics separate from Eloquent. Don't try to mirror models. Use the ClickHouse builder for reads, then write aggregated results back into MySQL for the app to consume.

• Support multiple clusters by resolving a connection/config per environment (e.g. clickhouse.analytics, clickhouse.logs) and injecting the right one.

Gotchas and Tips

• Dialect differences bite. No UPDATE/DELETE (only ALTER TABLE ... UPDATE/DELETE mutations), no transactions, no foreign keys, FINAL, SAMPLE, and SETTINGS clauses. If a fluent method doesn't exist, drop to selectRaw/raw and test the SQL in clickhouse-client.
• Binding style matters. ClickHouse's HTTP interface supports {param:Type} placeholders; PDO style differs. Confirm which style the package emits — mixing them produces "Substitution not found" errors.
• Mutable builder singletons are the most common bug source. Symptom: a later query silently inherits a previous where or orderBy. Always start from a clean builder.
• Port confusion: 8123 is HTTP (used by the PHP client), 9000 is native. Pointing at the wrong one yields opaque connection errors.
• Types are strings. 64-bit integers and DateTime values often arrive as strings; cast with (int) / Carbon::parse in your mapper rather than trusting the driver.
• NULL semantics. whereNull only works on Nullable(...) columns; on non-nullable columns ClickHouse returns defaults. Verify schema before filtering.
• Timezone drift. ClickHouse stores timestamps in the server or column timezone; pass ISO strings consistently and don't mix Carbon objects with raw strings unless the builder formats them.
• Small ecosystem risk. Two stars, zero dependents, but an active release cadence (latest 2026-04-19). Pin the version in composer.json, read the source when in doubt, and keep a raw-SQL escape hatch so you're not blocked by a missing method.
• Debugging workflow: dd($builder->toSql(), $builder->getBindings()), then paste into clickhouse-client. Server-side error messages are detailed and usually point at the exact clause.
• Extension point: subclass the builder or register macros (if supported) for repeated clauses like whereTenant(), whereDateRange() — this keeps the fluent code readable across repositories.