Getting Started

crate/crate-pdo is a pure-PHP PDO driver for CrateDB. It speaks CrateDB's HTTP endpoint (port 4200) rather than a wire protocol, so it is easy to install but has a reduced feature surface compared with MySQL/Postgres drivers. For Laravel, this package is most useful as a secondary read/analytics connection next to your primary transactional database — not as a drop-in replacement.

1. Install and verify the raw driver first

composer require crate/crate-pdo
# requires ext-pdo and ext-curl; check the composer.json constraints

Before touching Laravel, prove connectivity with plain PDO:

$pdo = new PDO('crate:127.0.0.1:4200', 'crate', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->query('SELECT name FROM information_schema.tables LIMIT 5');
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

Check the package README and tests/ directory for the exact DSN grammar (host/port format, multi-host, TLS) — this is the one place where the driver diverges from "standard" PDO and the source is the authoritative reference.

2. Register a connection in Laravel

Laravel has no built-in crate driver, so register one with DB::extend. PostgresConnection is the best base class because its grammar quotes identifiers with double quotes (CrateDB uses " for identifiers and ' for strings) and uses ? placeholders.

// config/database.php
'crate' => [
    'driver'   => 'crate',          // key used by DB::extend()
    'host'     => env('CRATE_HOST', '127.0.0.1'),
    'port'     => env('CRATE_PORT', 4200),
    'username' => env('CRATE_USER', 'crate'),
    'password' => env('CRATE_PASS', ''),
    'database' => env('CRATE_DB', 'doc'),
    'prefix'   => '',
],

// app/Providers/CrateServiceProvider.php
use Crate\PDO\PDO as CratePDO;
use Illuminate\Database\PostgresConnection;
use Illuminate\Support\Facades\DB;

public function boot(): void
{
    DB::extend('crate', function (array $config, string $name) {
        $dsn = sprintf('crate:%s:%s', $config['host'], $config['port']);

        $pdo = new CratePDO($dsn, $config['username'] ?? null, $config['password'] ?? null, [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);

        return new PostgresConnection($pdo, $config['database'] ?? 'doc', $config['prefix'] ?? '', $config);
    });
}

3. First use case to target

The highest-value first integration is a read-only reporting/analytics endpoint: DB::connection('crate')->table('metrics')->whereBetween(...) feeding a dashboard or an export job. Keep writes out of scope until you understand the no-transaction and no-auto-increment behaviour described below.

Implementation Patterns

Separate read connection, not a replacement

Keep your default connection on MySQL/Postgres and add crate as a named connection. This preserves migrations, queues, sessions and transactions on the primary DB while letting heavy time-series/aggregation queries hit CrateDB.

$rows = DB::connection('crate')
    ->table('metrics')
    ->selectRaw("date_bin('1 hour', ts, TIMESTAMP '2024-01-01') AS bucket, avg(value) AS avg_value")
    ->whereBetween('ts', [$from, $to])
    ->groupBy('bucket')
    ->orderBy('bucket')
    ->get();

Custom connection class for driver-specific behaviour

Subclass PostgresConnection so you can neutralise unsupported operations in one place (transactions, lastInsertId, getServerVersion) rather than sprinkling guards through your services:

class CrateConnection extends PostgresConnection
{
    public function transaction(Closure $callback, $attempts = 1)
    {
        // CrateDB has no multi-statement transactions; run inline.
        return $callback($this);
    }

    public function getServerVersion(): string
    {
        return 'crate';
    }
}

Then return new CrateConnection(...) from your DB::extend resolver.

Eloquent for read models only

A Model works fine for find, where, first, get, paginate (with caveats) as long as you disable everything CrateDB cannot do:

class Metric extends Model
{
    protected $connection = 'crate';
    protected $table = 'metrics';
    protected $primaryKey = 'id';
    public $incrementing = false;   // no SERIAL / AUTO_INCREMENT
    protected $keyType = 'string';  // use UUIDs generated in PHP
    public $timestamps = true;      // only if the table actually has those columns
}

Writes: explicit IDs and batched inserts

Generate UUIDs client-side and use insert() with arrays so Laravel emits one multi-row INSERT ... VALUES (...), (...):

DB::connection('crate')->table('metrics')->insert(
    collect($events)->map(fn ($e) => [
        'id'    => (string) Str::uuid(),
        'ts'    => $e['ts'],
        'name'  => $e['name'],
        'value' => $e['value'],
    ])->all()
);

CrateDB supports ON CONFLICT DO UPDATE / DO NOTHING; verify the generated SQL from upsert() before relying on it, and fall back to DB::connection('crate')->statement(...) with explicit CrateDB syntax if the grammar diverges.

DDL and migrations

The Laravel schema builder has no CrateDB grammar, so Schema::create() will not produce valid SQL. Keep DDL in raw statements, either in a dedicated, guarded migration or in a bootstrap command:

DB::connection('crate')->statement(<<<SQL
    CREATE TABLE IF NOT EXISTS doc.metrics (
        id    TEXT PRIMARY KEY,
        ts    TIMESTAMP WITH TIME ZONE NOT NULL,
        name  TEXT,
        value DOUBLE PRECISION
    )
SQL);

Note that php artisan migrate will try to create its migrations repository table via the schema grammar — either run Crate DDL outside the migration system or point the migration repository at the default connection.

Pagination strategy

paginate() works because it issues count(*) plus LIMIT/OFFSET, but count(*) on CrateDB is a full scan and offsets get expensive as they grow. For large time-series tables prefer keyset pagination on ts + id, or use the total only when the filter is narrow.

Full-text and geo search

CrateDB-specific syntax is where the package shines — reach for it via whereRaw/selectRaw:

->whereRaw("MATCH(message, ?) USING best_fields", [$term])
->whereRaw("MATCH(geo_location, ?) USING within", ['POINT(10.0 53.0)'])

Testing

Use a real CrateDB instance (the crate Docker image) in your test suite. RefreshDatabase and SQLite fakes will not work; isolate CrateDB tests in their own suite that recreates tables with DROP TABLE IF EXISTS + CREATE TABLE.

Gotchas and Tips

No transactions. CrateDB is a distributed, eventually-consistent store without multi-statement transactions. beginTransaction()/commit()/rollBack() cannot do anything meaningful, and DB::transaction() around CrateDB writes gives a false sense of atomicity. Route any workflow that needs rollback to your primary database. If you must keep call sites uniform, override transaction() on a custom connection class as shown above and document it loudly.
No auto-increment. insertGetId() and $model->save() on a fresh model will not return a generated key. Use UUIDs (or ULIDs) generated in PHP, or a deterministic natural key.
rowCount() is unreliable. HTTP-based drivers often cannot report affected-row counts. Do not gate logic on $stmt->rowCount(); confirm with a follow-up SELECT count(*) if you truly need it.
Identifier quoting and case. Unquoted identifiers are folded to lowercase in CrateDB. A table created as Metrics needs "Metrics" everywhere. Staying lowercase end-to-end avoids an entire class of "relation does not exist" errors.
Return types are lossy. Objects/arrays come back as JSON strings, timestamps may arrive as strings or epoch millis depending on driver version and column type, and booleans can surface as 't'/'f' or 1/0. Normalise in a small accessor layer rather than trusting raw PDO values. Cast explicitly in Eloquent ($casts) or in your repository.
No foreign keys, no FOR UPDATE, no advisory locks. Any invariant you normally rely on the database to enforce must be enforced in application code or kept in Postgres/MySQL.
Grammar mismatches. whereJsonContains, whereDate sugar, lockForUpdate, whereFullText, and some upsert shapes generate SQL CrateDB will reject. When in doubt, log the query with DB::connection('crate')->listen(fn ($q) => logger($q->sql)) or ->toSql() and run it directly against the CrateDB admin UI.
HTTP per statement. Each prepared statement is an HTTP round trip; N+1 loops that are tolerable on a local MySQL become visibly slow. Batch inserts, batch whereIn lookups, and cache reference data.
Connection reuse matters. Keep the PDO handle alive (Laravel does this per request) rather than constructing new CratePDO(...) inside services — you lose nothing by reusing and pay a handshake each time otherwise.
php artisan db:show / db:table may fail because they introspect driver metadata that this package does not expose. Use the CrateDB Admin UI (http://host:4200/_console) or information_schema queries for exploration.
Maintenance and version compatibility. The package is low-velocity; pin a version in composer.json and confirm it against your PHP and Laravel versions before upgrading either. If the PDO layer becomes a bottleneck or you need bulk/COPY-style ingestion, the extension point is the DB::extend resolver — swap the resolver for a thin wrapper around CrateDB's HTTP _sql endpoint (or crate/crate-dbal) without changing application code, because everything above it is just the Connection interface.
Debugging tip: set PDO::ATTR_ERRMODE => ERRMODE_EXCEPTION and log $e->getCode() alongside the message. CrateDB error responses are descriptive (they name the offending column and position) but the generic PDO message prefix hides them if you only log getMessage() from a QueryException.

Crate Pdo Laravel Package

Getting Started

Implementation Patterns

Gotchas and Tips