Laravel Postgres Helper Laravel Package

Getting Started

haakco/laravel-postgres-helper is a grab-bag of PostgreSQL conveniences for Laravel — the kind of thing you reach for when you find yourself writing the same whereRaw("LOWER(col) LIKE ?") or ON CONFLICT upsert for the fifth time.

Minimal path in:

composer require haakco/laravel-postgres-helper
php artisan vendor:publish --tag=postgres-helper-config   # if the package ships one

Where to look first, in order:

1. The README's method table / API listing. These packages are almost always a set of static helpers, query-builder macros, or traits rather than a cohesive subsystem. You want the inventory of callable names before you read any implementation.
2. src/ for service provider registration. Confirm whether helpers are registered as macros (available on every DB::table() / model query) or must be pulled in via a trait on each model. This determines whether it's a drop-in or an opt-in per model.
3. The tests directory. For a helper package, tests are the most accurate documentation of edge-case behaviour (null handling, empty arrays, quoting) — more reliable than the README.

First real use case: swap a hand-rolled case-insensitive search for the package's ILIKE helper, e.g.

User::query()->whereInsensitive('email', $request->input('q'))->get();

or add an idempotent upsert to a sync job:

DB::table('metrics')->upsertReturning($rows, ['key'], ['value', 'updated_at']);

Start with one helper in one place. If the abstraction holds up under a realistic query, widen adoption; if it leaks (see Gotchas), you've lost an hour instead of a sprint.

On v4.0.12: this is a patch release on the 4.0 line with nothing but a diff link in the notes. It signals no new API surface and no breaking changes. Pin ^4.0.11 or ^4.0.12; if you're mid-sprint, read the compare link before bumping, but there is no migration work implied.

Implementation Patterns

Wrap helpers behind query scopes, not controllers. Helper packages change shape between minor versions more often than Laravel core does. A local scope gives you a single edit point:

public function scopeSearch(Builder $q, string $term): Builder
{
    return $q->whereInsensitive('name', "%{$term}%")
             ->orWhereJsonContains('meta->tags', $term);
}

JSONB: use the helper for the operator, an index for the speed. The helper gets you the @>, ?, ?|, #>> syntax without string-concatenating JSON. It does not make the query fast. Every JSONB path you filter on in production needs a GIN index:

// migration
$table->jsonb('meta')->nullable();
DB::statement('CREATE INDEX users_meta_gin ON users USING GIN (meta jsonb_path_ops)');

jsonb_path_ops is smaller and faster than the default jsonb_ops, but only supports @> — not key-existence (?) queries. Pick per column.

Arrays: pair with ANY/ALL and a GIN index. Array columns are where Postgres pulls ahead of MySQL, and this is the package's highest-leverage feature:

Post::whereArrayContains('tags', ['laravel', 'postgres'])->get();

DB::statement('CREATE INDEX posts_tags_gin ON posts USING GIN (tags)');

Upserts for ingestion jobs. upsert/insertOrIgnore with a RETURNING clause is the cleanest way to write a sync without a read-modify-write race:

$changed = DB::table('products')->upsertReturning(
    $rows, ['sku'], ['price', 'stock', 'synced_at']
);

Chunk at 500–1000 rows. Postgres has a hard 65535 bind-parameter ceiling per statement; with 10 columns that's ~6500 rows, but you'll hit planner pathology long before that.

Full-text search as a generated column. If the package offers tsvector helpers, the durable pattern is a stored generated column plus a GIN index, queried through a scope:

$table->tsvector('search_vector')->generatedAs("to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))");
DB::statement('CREATE INDEX posts_search_gin ON posts USING GIN (search_vector)');

Post::whereFullText('search_vector', $term)->orderByRank('search_vector', $term)->get();

Advisory locks for cron overlap. Far cheaper than a queue WithoutOverlapping middleware when the job touches shared tables:

DB::advisoryLock('nightly-reindex', fn () => Artisan::call('search:reindex'));

Verify the helper wraps this in a transaction that outlives the callback — session-level locks are released only on explicit unlock or connection close.

Gotchas and Tips

SQLite in tests will silently pass and production will fail. Every one of these helpers is Postgres-only. Use a real Postgres in CI (postgres:16 service container, or pestphp/pest-plugin-database pointed at a pgsql connection). If your phpunit.xml has DB_CONNECTION=sqlite, you are not testing the code path you ship. Guard with ->skip(fn () => DB::connection()->getDriverName() !== 'pgsql') if you must keep both.

Case-insensitivity depends on collation, not on ILIKE alone. ILIKE handles ASCII fine; for accented text you want unaccent (an extension, not built in — created via migration, and it needs superuser or a preinstalled extension). If searches for "Zürich" fail, that's the cause, not the helper.

Extensions must exist before the migration that uses them. pg_trgm, unaccent, uuid-ossp — create them in an early migration with CREATE EXTENSION IF NOT EXISTS, and make sure your CI role has permission. Ordering is easy to get wrong on a fresh migrate:fresh and impossible to notice on an existing dev DB.

Raw expressions bypass binding. Any helper that takes a column name as a string (ordering, RETURNING, index definitions) is likely concatenating it into SQL. Never interpolate user input there — that's how a "safe" helper becomes an injection vector. Allowlist sortable columns explicitly.

whereRaw-style helpers don't compose with orWhere as you expect. Precedence bugs are the number one source of wrong-result reports in Laravel query building. Wrap grouped conditions in a closure:

$q->where(function ($q) use ($term) {
    $q->whereInsensitive('name', "%{$term}%")
      ->orWhereInsensitive('email', "%{$term}%");
});

Check the driver before assuming the package is inert. Some helpers fall back to LIKE on non-Postgres connections rather than throwing. That means code "works" on SQLite with different semantics. Prefer loud failure.

Config quirks: publish the config before you need to change it — the merged default is often read at container-boot time, so an env() read in a late-bound service won't pick up changes. And if you use multiple connections (pgsql + pgsql_read), confirm helpers resolve the model's connection rather than the default; several Laravel packages get this wrong, and silently write to the replica.

Debugging: log ->toSql() and ->getBindings() — helpers built on macros frequently produce SQL where the binding count doesn't match the placeholder count, and the resulting InvalidParameterNumberException points at the wrong line. DB::listen() with $query->time will also surface the missing GIN index faster than any profiler.

Upgrade discipline for 4.x. Patch releases on this package have historically been low-risk, but the 4.0 line is a major, so the helpers you rely on are stable only within ^4.0. Pin the minor (~4.0.12), run your pgsql test suite on bump, and keep a short internal list of which helpers you actually call — that list is your upgrade blast radius, and it will be much shorter than the changelog.