Phpclickhouse Laravel Laravel Package

Getting Started

Install and publish config (5 minutes)

composer require glushkovds/phpclickhouse-laravel
php artisan vendor:publish   # pick the clickhouse provider/tag from the list

The service provider is auto-discovered on Laravel 5.5+. On older skeletons register Glushkovds\PhpClickHouseLaravel\ServiceProvider in config/app.php yourself.

Configuration. Everything lives in config/clickhouse.php — host, port, database, username, password, and (in most versions) an options/settings array passed through to smi2/phpClickHouse. Mirror it in .env so each environment gets its own credentials:

CLICKHOUSE_HOST=clickhouse.internal
CLICKHOUSE_PORT=8123
CLICKHOUSE_DATABASE=analytics
CLICKHOUSE_USERNAME=app_readonly
CLICKHOUSE_PASSWORD=

Note the port: the package talks to ClickHouse's HTTP interface (8123, or 8443 behind TLS), not the native TCP port 9000.

Where to look first. Read the package source before anything else. It is a handful of files in vendor/glushkovds/phpclickhouse-laravel/src/ — a service provider, a facade, a config file, and a thin wrapper. You will need this knowledge anyway (single-maintainer package), and it takes ten minutes. Then read the smi2/phpClickHouse README, because that is the real API surface — select(), selectAssoc(), insert(), write(), execute().

First use case. Do not start with writes. Start with a read-only health check and a dashboard query:

// php artisan tinker
ClickHouse::select('SELECT 1');                       // connectivity
ClickHouse::select('SELECT version()');
ClickHouse::selectAssoc('SELECT count() FROM analytics.events WHERE event_date = today()');

That gives you auth, network path, and result shape in one shot. Confirm which entry point your installed version actually exposes — some versions register a clickhouse connection so DB::connection('clickhouse')->select(...) works, others only ship the ClickHouse facade. Verify in tinker, then standardise on one and hide it behind an interface (below). Also decide early whether your first feature is a reporting dashboard (reads) or event ingestion (batch writes), because the two have very different failure modes.

Implementation Patterns

Wrap it behind your own gateway. Application code should never use the facade directly:

interface AnalyticsGateway {
    public function query(string $sql): array;
    public function insertBatch(string $table, array $columns, array $rows): void;
}

Bind the implementation to the interface in a service provider. When the adapter lags a Laravel upgrade — or when you fork it — you change one class. This is the single highest-value decision in the whole integration.

Reads: aggregate in ClickHouse, not PHP. The client loads the entire response into memory, so the rule is: every production query has a LIMIT, and expensive computation happens server-side. For dashboards, query pre-aggregated tables or materialized views, never raw event tables:

$rows = $gateway->query("
    SELECT toStartOfHour(ts) AS bucket, count() AS events, uniqExact(user_id) AS users
    FROM analytics.events_hourly
    WHERE tenant_id = {$tenantId} AND ts >= '{$from}' AND ts < '{$to}'
    GROUP BY bucket ORDER BY bucket
");

No bindings exist — build a small query helper. phpClickHouse is not PDO; there is no ? parameter binding. Write one helper that interpolates safely: cast ints/floats with (int)/(float), map booleans to 1/0, format DateTimeInterface as 'Y-m-d H:i:s' in UTC, and escape strings with the client's escaping (or addslashes-equivalent with backslash handling for ClickHouse). Identifiers (table and column names from user input) must be validated against a whitelist or quoted with backticks. Skipping this is how you get SQL injection in an analytics feature.

Writes: always batched, always in a job. Never insert per row from a request. Buffer into a queued job, chunk at 10k–100k rows, and pass an explicit column list so Nullable columns and missing keys don't shift positions:

class IngestEvents implements ShouldQueue {
    public $tries = 5; public $backoff = [5, 30, 120];
    public function handle(AnalyticsGateway $ch) {
        $ch->insertBatch('events', ['ts','tenant_id','name','props'], $this->rows);
    }
}

Dispatch with ->afterCommit() if the events are derived from database writes, so you never ingest rows for transactions that rolled back. Use a dedicated queue (onQueue('analytics')) so a slow ClickHouse never starves user-facing jobs.

Make ingestion idempotent. ClickHouse has no transactions and a retried job will duplicate rows. Either write into a ReplacingMergeTree keyed by an event UUID, or set the insert_deduplication_token setting per batch (phpClickHouse accepts per-query settings) to a deterministic hash of the batch.

Pagination: keyset, not offset. LIMIT 50 OFFSET 100000 on a large table scans everything. Cursor on (ts, id) — WHERE (ts, id) > ('...', '...') ORDER BY ts, id LIMIT 50.

Multiple connections / named clients. If the version you install supports named connections in config/clickhouse.php, register read-only and read-write entries and resolve by name. Otherwise instantiate a second ClickHouse\Client and app()->instance() it. Point dashboards at a read-only user (readonly=1) and ingestion at a write user; that separation catches mistakes cheaply.

Schema management is yours. Laravel migrations do not apply. Keep DDL as versioned .sql files (database/clickhouse/), apply them with a small artisan command that records applied filenames in a schema_migrations table in ClickHouse, and review partitioning/TTL/order-by keys like you would an index.

Testing. Unit tests swap the gateway for a fake returning fixture arrays — no network. Integration tests run against a real clickhouse/clickhouse-server container in CI, tagged @group integration, covering insert-then-select round trips and one representative dashboard query. Do not try to fake the Guzzle layer; you'll spend more time than the container costs.

Operational glue. Cache::remember() expensive dashboard queries for 30–60s. Log query text, duration, and row count in a decorator around the gateway. Register a scheduled rollup command. On Octane or long-lived workers, re-resolve the client on RequestTerminated/WorkerStopping if you see stale sockets.

Gotchas and Tips

• The package is a shim; smi2/phpClickHouse is the dependency. Pin both in composer.json and read the upstream changelog, not this repo's. Note the published metadata shows a future-dated release — check the actual tags in the repository before trusting a version constraint, and pin a concrete tag or commit.
• No Eloquent, no migrations, no query builder. DB::table('events') and model relations will not work even if the DB::connection('clickhouse') path is available — the grammar isn't ClickHouse-aware. Raw SQL only; plan a shadow abstraction or accept it.
• DB::transaction() is a no-op here. So is DB::beginTransaction(). Never place analytics writes inside a transaction and expect rollback. Wrap analytics calls in try/catch and degrade (return an empty dataset, show a banner) rather than 500-ing the page.
• Errors are not QueryException. Expect generic client exceptions with the server message embedded. Normalise them in your gateway. Common causes: wrong HTTP port (9000 instead of 8123), TLS on a plain port, a password containing characters not escaped in .env, or a query hitting max_memory_usage before max_execution_time.
• Set limits explicitly per connection. max_execution_time, max_memory_usage, max_result_rows, and a client-side HTTP timeout. Unbounded defaults let one bad dashboard query pin a worker for minutes.
• Watch out for 64-bit integers as strings. ClickHouse's JSON output quotes large integers by default, so count() and IDs arrive as "42". Cast at the boundary or you'll ship a "1" !== 1 bug.
• Timezones bite. Server timezone, column timezone, and PHP timezone are three different things. Store and query in UTC, format dates explicitly, and avoid now() in SQL when you can compute the window in PHP.
• Memory blowups come from result sets, not query cost. A SELECT * FROM events on a wide table will exhaust PHP memory before ClickHouse breaks a sweat. Always LIMIT, select only needed columns, and use FORMAT JSONEachRow with a streaming path if the client version supports it for exports.
• Debug with the server, not the client. Reproduce the failing SQL with clickhouse-client --query "...", then inspect system.query_log (type = 'QueryFinish') and system.errors to see what ClickHouse actually received. For the client side, enable phpClickHouse's verbose/log option to a PSR logger.
• Config caching: keep env() calls only in config/clickhouse.php (never closures), or php artisan config:cache will silently break connection settings.
• Facade alias collisions: the package registers a ClickHouse alias. If you already have one — or an internal ClickHouse class — check config/app.php and import the fully-qualified facade instead.
• Octane and Horizon: HTTP-based ClickHouse calls are mostly stateless, but idle-socket drops and DNS re-resolution do surface in long-running workers. Add a single retry with backoff for connection-level failures, and test a 30-minute worker soak before you trust it.
• Fork budget. The codebase is small enough that vendoring it into app/Support/ClickHouse is a realistic option. Because everything goes through your gateway interface, a swap should be a day's work — keep it that way, and keep the maintenance allowance honest.