Laravel Pg Extensions Laravel Package

Getting Started

Install and confirm the driver

composer require umbrellio/laravel-pg-extensions

The service provider is auto-discovered. There is nothing to configure for the basics — the package registers Blueprint/Builder/Schema macros that only make sense when DB_CONNECTION=pgsql, so make sure your connection uses the pgsql driver before you start calling them.

Where to look first

1. The README feature list — it is essentially an index of the macros.
2. src/Extensions/ (or the equivalent macro registration directory) — each capability is a small, readable class; when a method signature is unclear, the source is the fastest answer.
3. The test suite — it doubles as executable documentation of the exact SQL generated.
4. Laravel's own PostgresGrammar — anything the package does is a thin layer on top of it, so knowing what core already supports prevents duplicated work.

First use case: stop writing DB::statement in migrations

The highest-value first step is replacing raw DDL you would otherwise hand-write:

// database/migrations/2024_01_01_000000_init_pg_features.php
public function up(): void
{
    Schema::createExtension('pgcrypto');

    Schema::create('documents', function (Blueprint $table) {
        $table->uuid('id')->primary()->default(DB::raw('gen_random_uuid()'));
        $table->jsonb('meta')->default('{}');
        $table->citext('slug')->unique();
        $table->timestamps();
    });

    Schema::createView(
        'published_documents',
        "select * from documents where (meta->>'published')::boolean"
    );
}

That migration replaces three or four DB::statement() calls with something readable and reversible.

Implementation Patterns

Migrations: use it for the DDL Laravel omits

• PostgreSQL column types (jsonb, citext, uuid, inet, tsvector, geometry types where PostGIS is installed) instead of $table->string() plus manual ALTER.
• Schema::createExtension() / dropExtension() so the extension dependency lives next to the table that needs it.
• Views and materialized views as first-class schema objects, including refresh helpers.
• CREATE TABLE ... (LIKE ...) and cascade drops when cloning or resetting tables.
• Concurrent indexes — critical on large production tables where a normal index build locks writes.

Views and materialized views as read models

Materialized views are the sweet spot: precompute an expensive aggregate, then back a read-only Eloquent model with it.

Schema::createMaterializedView('user_stats', '
    select user_id, count(*) as documents, max(created_at) as last_seen
    from documents group by user_id
');

// artisan command, scheduled nightly
Schema::refreshMaterializedView('user_stats');

Keep the write-side model and the view-side model separate; never save() on a view-backed model.

Query builder macros wrapped in local scopes

The macros are most maintainable when hidden behind named scopes, so the rest of the codebase does not depend on the package API directly:

class Document extends Model
{
    protected $casts = ['meta' => 'array'];

    public function scopeTagged(Builder $query, array $tags): Builder
    {
        return $query->whereInJson('meta->tags', $tags);
    }
}

Common day-to-day macros:

DB::table('documents')->whereInJson('meta->tags', ['php', 'postgres'])->get();
DB::table('documents')->whereLike('title', 'release%');          // case-insensitive ILIKE
DB::table('events')->whereBetweenDates('created_at', $from, $to);
DB::table('documents')->distinctOn('owner_id')
    ->orderBy('owner_id')->orderByDesc('created_at')->get();     // "latest per group"
DB::table('documents')->insertIgnore($rows);                     // ON CONFLICT DO NOTHING

distinctOn + orderBy is the most useful pattern in practice — it replaces a whole class of window-function subqueries for "latest row per group" screens.

Indexes matter more than the macro

JSONB containment and ILIKE are only fast with the right index. Pair every macro with its index in the same migration:

DB::statement('create index documents_meta_tags_idx on documents using gin ((meta->\'tags\') jsonb_path_ops)');
DB::statement('create index documents_title_trgm_idx on documents using gin (title gin_trgm_ops)'); // needs pg_trgm

Testing

Run the suite against real PostgreSQL, not SQLite. Use a service container in CI, or a throwaway schema per run. RefreshDatabase works fine, but wrap materialized-view refreshes in transactions or run them after truncation.

Gotchas and Tips

• Postgres-only, hard. Any macro call fails on MySQL/SQLite. If your test suite still uses SQLite for speed, either move those tests to pgsql or guard package calls behind DB::connection()->getDriverName() === 'pgsql'.
• Extension privileges. CREATE EXTENSION requires superuser (or a trusted extension allow-list). In production, provision extensions via your DBA/terraform and make application migrations assume them, or the deploy will fail on a managed database.
• CONCURRENTLY cannot run inside a transaction. CREATE INDEX CONCURRENTLY and REFRESH MATERIALIZED VIEW CONCURRENTLY will error in a normal migration. Set public $withinTransaction = false; on that migration (or use DB::connection()->unprepared()), and accept that a failed concurrent index leaves an invalid index behind — check pg_index.indisvalid.
• Concurrent refresh needs a unique index on the materialized view, otherwise PostgreSQL refuses it.
• Macro collisions with Laravel core. Laravel has steadily absorbed features (upsert, whereJsonContains, whereLike, lockForUpdate()->skipLocked()). After each framework upgrade, verify which implementation you are actually calling — a macro may silently override or be overridden. Pin the package version and re-read the README on upgrade.
• schema:dump / squashed migrations may not capture extensions and views faithfully. Inspect the generated dump, or exclude the relevant migrations from squashing.
• JSONB semantics. Keys are reordered and duplicates dropped; lookups via -> return text, so compare with strings or cast in SQL. Without a cast on the model, jsonb comes back as a string.
• citext surprises. Comparisons are case-insensitive everywhere, including where('slug', $value) — good for emails, dangerous if you rely on case-sensitive uniqueness elsewhere.
• Debugging. ->toSql() prints placeholders, not values — use ->dd()/->dump() or enable the query log. For slow JSONB queries, run EXPLAIN (ANALYZE, BUFFERS) manually; the macro's generated SQL is easy to copy out and profile.
• Small ecosystem. ~100 stars means fewer Stack Overflow answers. When something behaves unexpectedly, read the macro source and write a regression test in your own suite — you are effectively the maintainer of your integration with it.