Laravel Crate.io Laravel Package

Getting Started

1. Vet compatibility before installing. This package is a thin driver shim (36 stars, last tagged release Oct 2023), so its composer.json constraints are the real gate. Before adopting:

composer require ratkor/laravel-crate.io
composer why-not laravel/framework   # surfaces version conflicts

If you're on Laravel 11/12 or PHP 8.3+, expect to either pin an older framework or fork the package and widen the constraints. Treat "it installed" as the first milestone, not the last.

2. Stand up CrateDB locally. The driver talks to CrateDB's HTTP(S) endpoint (default port 4200, not the Postgres-wire 5432), so verify the service independently of Laravel:

docker run --name crate -p 4200:4200 -d crate
curl -s localhost:4200/_sql -H 'Content-Type: application/json' \
  -d '{"stmt":"SELECT 1"}'

3. Register it as a second connection. CrateDB is a distributed analytics/search store — no foreign keys, no multi-statement transactions, eventually consistent. Almost nobody uses it as DB_CONNECTION; use it alongside your existing MySQL/Postgres connection:

// config/database.php
'crate' => [
    'driver'   => 'crate',
    'host'     => env('CRATE_HOST', '127.0.0.1'),
    'port'     => env('CRATE_PORT', 4200),
    'database' => env('CRATE_DATABASE', 'doc'), // 'doc' is the default schema, not a database
    'username' => env('CRATE_USER', 'crate'),
    'password' => env('CRATE_PASSWORD', ''),
],

Package auto-discovery registers the crate driver via DB::extend(); if you see Unsupported driver [crate], check php artisan package:discover ran and that config:cache isn't stale.

4. First use case — a read-only reporting query. The fastest win is a dashboard query over a table you've already ingested into:

DB::connection('crate')->table('events')
    ->selectRaw('country, count(*) as total')
    ->groupBy('country')
    ->orderByDesc('total')
    ->limit(20)
    ->get();

Sanity-check it in php artisan tinker before wiring it into a controller, then SELECT * FROM sys.jobs_log in CrateDB to confirm what actually executed.

Implementation Patterns

Analytics sidecar, not system of record. Keep OLTP on MySQL/Postgres and treat CrateDB as a denormalized read model. That means no cross-connection joins — resolve IDs on the primary, chunk them (->chunkById(1000)), and query Crate with whereIn().

UUID keys, not auto-increment. CrateDB has no sequences and lastInsertId() is meaningless. Every Crate-backed model needs:

class Event extends Model
{
    protected $connection = 'crate';
    protected $primaryKey = 'id';
    public $incrementing = false;   // required
    protected $keyType = 'string';
    public $timestamps = false;     // unless you add the columns yourself
}

Use HasUuids (Laravel 9+) or generate Str::uuid() in PHP — never let the database assign the ID, because create() may return a model without one.

Bulk ingestion with upsert(). This is where CrateDB shines and where most of your code will live. Batch 500–5,000 rows per statement so the driver maps to INSERT ... ON CONFLICT DO UPDATE, which makes replays idempotent:

DB::connection('crate')->table('events')->upsert(
    $rows, ['id'], ['country', 'amount', 'occurred_at']
);

Drive it from a queued job per chunk, with a watermark table on the source side so a failed batch can be retried safely.

Migrations: prefer raw DDL. The schema grammar is the weakest part of any third-party driver. For anything non-trivial — OBJECT columns, array types, GEO_POINT, partition keys, CLUSTERED INTO n SHARDS — write the DDL directly:

public function up(): void
{
    DB::connection('crate')->statement(
        'CREATE TABLE events (
            id TEXT PRIMARY KEY,
            country TEXT,
            amount DOUBLE PRECISION,
            occurred_at TIMESTAMP WITH TIME ZONE
        )'
    );
}

Guard these migrations with a config check so contributors without CrateDB can still run the suite. Also expect Schema::table() alterations to be limited to adding columns — modifying or dropping a column usually means create-new-table-and-copy.

Search and full-text via raw predicates. The query builder can't express MATCH(...) USING BEST_FIELDS or _score ordering:

->whereRaw("MATCH(title, body) USING BEST_FIELDS WITH (fuzziness := 1) ?", [$term])
->orderByRaw('_score DESC')

Keyset pagination, not OFFSET. Deep offsets are expensive in a distributed engine. Use ->where('occurred_at', '<', $last)->orderByDesc('occurred_at')->limit(50).

Isolate the connection in long-running processes. Under Octane or a queue:work daemon, call DB::purge('crate') after config/env reloads so the resolved connection picks up new credentials.

Gotchas and Tips

• Eventually consistent — read-after-write will bite you. Inserts become visible after the table's refresh_interval (1s by default, longer during bulk loads). Call REFRESH TABLE events before assertions, and set refresh_interval: 0 on test tables.
• No multi-statement transactions. DB::transaction() and DB::beginTransaction() are at best inert, at worst an exception. Consequently the DatabaseTransactions test trait is unusable — clean up with TRUNCATE TABLE (bare DELETE FROM t may be rejected; DELETE generally wants a WHERE).
• No auto-increment, no insertGetId(). If Model::create() returns a model with a null key, this is why.
• ORDER BY without LIMIT can fail on non-indexed columns with a memory-related error. Always bound the query.
• COUNT(*) and deep OFFSET are scans. Don't put them on a hot path; cache aggregates or maintain rollup tables.
• No foreign keys, limited constraint enforcement. Primary-key uniqueness is your only real handle — dedupe and referential integrity belong in application code or the ingestion job.
• Type mapping is lossy. timestamps() without timezone, json() columns, enums, and charset/collation connection keys are all meaningless or mis-mapped. Model casts ('payload' => 'array') usually work for OBJECT columns; verify round-trips.
• Errors surface as generic QueryExceptions because the driver wraps an HTTP response. Always inspect $e->getPrevious() and check sys.jobs_log — the real CrateDB error is usually in the JSON body.
• DB::listen() may not capture everything depending on whether the driver routes through the standard connection. Use ->toRawSql() / ->dd() on the builder, and fall back to sys.jobs for ground truth.
• Bulk inserts can exceed the HTTP timeout before they exceed any row limit. Set an explicit options['timeout'] and keep batch sizes conservative; a partially applied bulk is normal here.
• config:cache and doc-as-database confusion are the two most common setup failures — database maps to a CrateDB schema, and cached config silently ignores your .env changes.
• Vendor risk is the headline gotcha. 36 stars and a 2023 release means no guaranteed fixes. Wrap all Crate access behind your own repository layer (one CrateRepository class or a few Eloquent models), so that if you need to swap the driver, you're replacing a dozen call sites instead of hundreds. The escape hatch is small: DB::extend('crate', fn () => new CrateConnection(...)) in AppServiceProvider, backed directly by crate/crate-pdo or crate/crate-dbal.