The shape of it

One live user per address — soft-deleted rows do not collide:

Schema::create('users', static function (Blueprint $table) {
    $table->primaryUUID();
    $table->string('email');
    $table->softDeletes();

    $table->uniquePartial('email')->whereNull('deleted_at');
});

create table "users" ("id" uuid not null default gen_random_uuid(),
    "email" varchar(255) not null,
    "deleted_at" timestamp(0) without time zone null)

create unique index "users_email_unique" on "users" ("email")
    where ("deleted_at" is null)

alter table "users" add primary key ("id")

Reference

Practice

At a glance

What this package adds, and where each of them is documented:

Requirements

Two features need a newer server than the 13 floor, and only those two:

The package targets PostgreSQL and takes effect on pgsql connections only. Other drivers on the same application are untouched.

Installation

composer require efureev/laravel-support-db

It registers itself through package discovery — no provider to add, no config to publish.

On boot it routes pgsql connections to its own connection class through Illuminate\Database\Connection::resolverFor(). DB::connection() then returns Php\Support\Laravel\Database\Schema\Postgres\Connection, Schema:: reaches the extended builder, and the closure in a Schema::create() receives the extended blueprint.

Type-hint the package's Blueprint in migrations to get the additions in your editor:

use Illuminate\Support\Facades\Schema;
use Php\Support\Laravel\Database\Schema\Postgres\Blueprint;

Schema::create('table', static function (Blueprint $table) { /* … */ });

A bundled .meta.php teaches IDEs about the additions on Schema, Blueprint, ColumnDefinition and the query builder, so the framework's own type hints resolve to them too.

Quick start

A migration using several of the additions at once:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\Schema;
use Php\Support\Laravel\Database\Schema\Postgres\Blueprint;

return new class extends Migration {
    public function up(): void
    {
        Schema::create('documents', static function (Blueprint $table) {
            $table->primaryUUID();                       // uuid pk, gen_random_uuid()
            $table->generateUUID('tenant_id', null);     // uuid, nullable, no default
            $table->string('slug');
            $table->textArray('tags');                   // text[]
            $table->jsonb('payload');
            $table->tsRange('valid_for');                // tsrange
            $table->text('body')->compression('lz4');    // PostgreSQL >= 14
            $table->timestamps();
            $table->softDeletes();

            // unique per tenant, but only among rows that are still live
            $table->uniquePartial(['tenant_id', 'slug'])->whereNull('deleted_at');

            // containment queries on the jsonb column
            $table->ginIndex('payload', 'documents_payload_gin', 'jsonb_path_ops');

            // tag lookups
            $table->ginIndex('tags');
        });

        Schema::createView(
            'live_documents',
            'select id, tenant_id, slug from documents where deleted_at is null'
        );
    }

    public function down(): void
    {
        Schema::dropViewIfExists('live_documents');
        Schema::dropIfExistsCascade('documents');
    }
};

Column types

Each method returns a ColumnDefinition, so the framework's modifiers (nullable(), default(), index(), comment(), …) chain off it as usual.

numeric() differs from the framework's decimal() in that both arguments are optional — omit them for an unconstrained numeric, which stores any precision:

$table->numeric('amount');          // numeric
$table->numeric('amount', 10);      // numeric(10)
$table->numeric('amount', 10, 2);   // numeric(10, 2)

UUID keys

primaryUUID() is a UUID column plus its primary key; generateUUID() is the column alone. Both default to the native gen_random_uuid(), which needs no extension on PostgreSQL 13 and later.

$table->primaryUUID();                // "id" uuid not null default gen_random_uuid() + pk
$table->primaryUUID('uid');           // same, named "uid"
$table->primaryUUID('id', false);     // pk you populate yourself

The second argument decides where the value comes from, and primaryUUID() passes it straight through to generateUUID():

use Illuminate\Database\Query\Expression;

$table->generateUUID();                                  // "id", database-generated
$table->generateUUID('cid');                             // "cid", database-generated
$table->generateUUID('tenant_id', null)->index();        // nullable FK column, indexed
$table->generateUUID('external_id', false);              // not null, supplied by the application
$table->generateUUID('id', new Expression('uuid_generate_v4()'));
$table->generateUUID('id', fn (string $c) => "uuid_generate_v5(uuid_ns_url(), '$c')");

Column compression

PostgreSQL 14 and later can compress TOAST-able columns with either pglz (the historical algorithm) or lz4 (faster, usually a better ratio). Docs.

$table->text('body')->compression('lz4');      // text compression lz4
$table->text('body')->compression();           // defaults to pglz
$table->text('body')->compression('default');  // the server's default_toast_compression

It works on change() too, where PostgreSQL takes it as a separate statement:

$table->text('body')->compression('lz4')->change();
// alter table "docs" alter column "body" set compression lz4

Only the new rows are compressed with the new method; existing ones keep whatever they were written with until they are rewritten.

Partial indexes

A partial index covers only the rows matching a predicate. It is smaller, cheaper to maintain, and — in the unique case — the standard way to express "unique among the rows that count".

$table->partial('code')->whereNull('deleted_at');
// create index "docs_code_partial" on "docs" ("code") where ("deleted_at" is null)

$table->uniquePartial('email')->whereNull('deleted_at');
// create unique index "docs_email_unique" on "docs" ("email") where ("deleted_at" is null)

Both accept the same three arguments and both take one column or several:

partial(array|string $columns, ?string $index = null, ?string $algorithm = null)
uniquePartial(array|string $columns, ?string $index = null, ?string $algorithm = null)

Without an explicit name the index is called {table}_{columns}_partial or {table}_{columns}_unique, following the framework's own convention.

Dropping. Pass the columns to let the name be derived, or the index name itself if you chose one:

$table->dropPartial(['code']);              // drop index "docs_code_partial"
$table->dropUniquePartial(['email']);       // drop index "docs_email_unique"
$table->dropPartial('docs_reachable_ix');   // drop index "docs_reachable_ix"

Index predicates

The predicate is built fluently. Every method also takes a trailing $boolean, and each has an or spelling so a disjunction reads as one.

Each has an orWhere… counterpart: orWhere, orWhereRaw, orWhereBool, orWhereTrue, orWhereFalse, orWhereNull, orWhereNotNull, orWhereColumn, orWhereIn, orWhereNotIn, orWhereBetween, orWhereNotBetween.

$table->partial('code', 'docs_reachable')
    ->whereNull('deleted_at')
    ->orWhereTrue('is_pinned')
    ->whereFalse('is_draft');
// … where ("deleted_at" is null) or ("is_pinned" is true) and ("is_draft" is false)

A leading or is stripped, so the first predicate may use either spelling.

Values. Accepted: string, int, float, bool, null, BackedEnum (its value is used), DateTimeInterface, and Stringable. They are escaped and inlined — an index predicate is part of the DDL, and PostgreSQL takes no parameters there. Anything else is rejected rather than coerced.

$table->partial('state')->where('state', '=', OrderState::Paid);   // backed enum
$table->partial('at')->where('at', '>', new DateTimeImmutable('2026-01-01'));

whereRaw() uses ? placeholders, bound the same way and inlined the same way:

$table->partial('code')->whereRaw('lower(code) = ?', ['abc']);
// … where (lower(code) = 'abc')

Index modifiers

These chain onto partial() and uniquePartial() in any position — before or after the predicates, it makes no difference.

$table->partial('tags', 'docs_tags_gin', 'gin')->whereNotNull('tags');
// identical:
$table->partial('tags', 'docs_tags_gin')->algorithm('gin')->whereNotNull('tags');

$table->partial('code')->whereNull('deleted_at')->online();
// create index concurrently "docs_code_partial" on "docs" ("code") where ("deleted_at" is null)

$table->uniquePartial(['team', 'seat'])->nullsNotDistinct()->whereNull('deleted_at');
// create unique index "docs_team_seat_unique" on "docs" ("team", "seat")
//     nulls not distinct where ("deleted_at" is null)

Covering indexes and column expressions

include() carries extra columns in the index leaf without indexing them, so a query that reads only those never touches the table (PostgreSQL 11 and later):

$table->partial('room_id', 'bookings_cover')
    ->include(['during', 'guest'])
    ->whereNull('cancelled_at');

create index "bookings_cover" on "bookings" ("room_id")
    include ("during", "guest") where ("cancelled_at" is null)

Everything else PostgreSQL allows in an index column list — an expression, a sort direction, where nulls sort, a per-column operator class — is reachable by passing an Expression instead of a name:

use Illuminate\Database\Query\Expression;

$table->partial([new Expression('lower(email)')], 'users_lower_email')->whereNull('deleted_at');
$table->partial([new Expression('created_at desc nulls last'), 'id'], 'docs_recent')->whereTrue('live');
$table->partial([new Expression('payload jsonb_path_ops')], 'docs_payload', 'gin')->whereNotNull('payload');

create index "users_lower_email" on "users" (lower(email)) where ("deleted_at" is null)
create index "docs_recent" on "docs" (created_at desc nulls last, "id") where ("live" is true)
create index "docs_payload" on "docs" using gin (payload jsonb_path_ops) where ("payload" is not null)

GIN indexes and operator classes

ginIndex() is a shorthand for a GIN index — the access method for arrays, jsonb and full-text search.

$table->textArray('tags');
$table->ginIndex('tags');
// create index "docs_tags_index" on "docs" using gin ("tags")

A third argument names an operator class, which is where GIN indexes earn their keep:

ginIndex(array|string $columns, ?string $name = null, ?string $operatorClass = null)

$table->jsonb('payload');
$table->ginIndex('payload', 'docs_payload_gin', 'jsonb_path_ops');
// create index "docs_payload_gin" on "docs" using gin ("payload" jsonb_path_ops)

The framework accepts an operator class on spatial and vector indexes only — its index() takes no such argument, and compileIndex() discards one that arrives another way.

Creating a table from another

Three PostgreSQL forms, three different things copied.

like() — structure, no data. The closest thing to a template.

Schema::create('users_archive', static function (Blueprint $table) {
    $table->like('users')->includingAll();
    $table->ifNotExists();
});
// create table if not exists "users_archive" (like "users" including all)

includingAll() is PostgreSQL's shorthand for INCLUDING DEFAULTS, CONSTRAINTS, INDEXES, STORAGE, COMMENTS. Drop it to copy the column definitions alone.

fromTable() — columns and data, nothing else. No indexes, no constraints, no defaults.

Schema::create('users_snapshot', static function (Blueprint $table) {
    $table->fromTable('users');
});
// create table "users_snapshot" as table "users"

fromSelect() — whatever the query returns.

Schema::create('active_snapshot', static function (Blueprint $table) {
    $table->fromSelect('select id, email from users where is_active');
});
// create table "active_snapshot" as (select id, email from users where is_active)

The query is arbitrary, so this is also how you reshape on the way:

Schema::create('users_reindexed', static function (Blueprint $table) {
    $table->fromSelect('select gen_random_uuid() as id, email, created_at from users');
});

ifNotExists() adds IF NOT EXISTS to any Schema::create(), with or without the above.

Dropping with CASCADE

Drops the table and everything depending on it — views, foreign keys, sequences, and whatever depends on those in turn.

Schema::dropIfExistsCascade('users');

drop table if exists "users" cascade

Without it, PostgreSQL refuses to drop a table a view is built on. Useful in down() and in test teardown; deliberate everywhere else, since the blast radius is by definition not local.

Extensions

Schema::createExtension('uuid-ossp');             // fails if it is already there
Schema::createExtensionIfNotExists('uuid-ossp');  // idempotent

Schema::dropExtensionIfExists('tablefunc');
Schema::dropExtensionIfExists('tablefunc', 'fuzzystrmatch');   // several at once

create extension "uuid-ossp"
create extension if not exists "uuid-ossp"

drop extension if exists "tablefunc"
drop extension if exists "tablefunc", "fuzzystrmatch"

Creating an extension usually needs a superuser or an explicitly trusted extension.

Types of your own

Laravel can read a user-defined type back and drop every one at once, but has no way to create a single one.

Enums. A label set the database itself enforces, ordered the way you declare it:

Schema::createEnumType('order_state', ['new', 'paid', 'shipped']);

create type "order_state" as enum ('new', 'paid', 'shipped')

The type is then an ordinary column type — $table->rawColumn('state', 'order_state') — and PostgreSQL refuses any label it does not know.

Labels are added to an existing type, optionally positioned. The order matters: comparisons and ORDER BY follow it, not the alphabet.

Schema::addEnumValue('order_state', 'refunded');            // at the end
Schema::addEnumValue('order_state', 'pending', 'new');      // before 'new'
Schema::addEnumValue('order_state', 'in_transit', after: 'paid');

Domains. A base type with a rule attached, so the rule lives with the type instead of being repeated on every column that uses it:

use Php\Support\Laravel\Database\Schema\Postgres\Builders\Indexes\PartialBuilder;

Schema::createDomain('positive_int', 'integer', fn (PartialBuilder $c) => $c->where('value', '>', 0));
Schema::createDomain('score', 'integer', fn (PartialBuilder $c) => $c->where('value', '>=', 0)->where('value', '<=', 100));

create domain "positive_int" as integer check (("value" > 0))

The predicate names value — PostgreSQL calls the thing being checked VALUE, and resolves a quoted "value" to it, so the vocabulary from Index predicates reaches here unchanged. The catalogue stores it as CHECK ((VALUE > 0)).

Composites. Several fields under one name:

Schema::createCompositeType('full_name', ['first' => 'text', 'last' => 'varchar(30)']);

create type "full_name" as ("first" text, "last" varchar(30))

Dropping. Types and domains are dropped separately, several at a time, and a name may be schema-qualified:

Schema::dropTypeIfExists('order_state', 'full_name');
Schema::dropDomainIfExists('positive_int');
Schema::dropTypeIfExistsCascade('order_state');   // and every column using it

Exclusion constraints

A unique index says two rows must not be equal. An exclusion constraint says they must not overlap, intersect, or whatever else an operator expresses — which is what a range column exists for, and which Laravel has no form for:

Schema::createExtensionIfNotExists('btree_gist');

Schema::create('bookings', static function (Blueprint $table) {
    $table->increments('id');
    $table->integer('room_id');
    $table->tsRange('during');
    $table->timestamp('cancelled_at')->nullable();

    $table->exclusion('bookings_no_overlap')
        ->using('gist')
        ->with('room_id', '=')
        ->with('during', '&&')
        ->whereNull('cancelled_at');
});

alter table "bookings" add constraint "bookings_no_overlap"
    exclude using gist ("room_id" with =, "during" with &&)
    where ("cancelled_at" is null)

No two live bookings for the same room may overlap; a cancelled one is outside the constraint and may overlap anything. with() accumulates, and the predicate takes the whole vocabulary from Index predicates.

The operator is checked against the character set PostgreSQL builds operator names from, because it is interpolated into DDL and cannot be bound.

Dropping works for any named constraint:

$table->dropConstraint('bookings_no_overlap');
$table->dropCheck('price_positive');          // the same statement, named for what it drops

Check constraints

Laravel's schema builder has no CHECK in any grammar, so a table's columns can be described and most of its invariants cannot. The condition is written with the same vocabulary as a partial index — PostgreSQL treats both as a boolean expression over a row:

Schema::create('products', static function (Blueprint $table) {
    $table->integer('price');
    $table->string('state');

    $table->check('price_positive')->where('price', '>', 0);
    $table->check('state_known')->whereIn('state', ['new', 'paid', 'shipped']);
});

alter table "products" add constraint "price_positive" check (("price" > 0))
alter table "products" add constraint "state_known" check (("state" in ('new','paid','shipped')))

Each is emitted as its own ALTER TABLE, so the same call works on a table being created and on one that already exists. Dropping takes the constraint name:

Schema::table('products', static fn (Blueprint $table) => $table->dropCheck('price_positive'));

alter table "products" drop constraint "price_positive"

Every predicate from Index predicates is available. A check with no conditions is refused rather than compiled — check (()) is a syntax error.

Partitioning

A partitioned table holds no rows; its partitions do, and PostgreSQL routes every insert to the right one. Laravel has no form for any of it.

Schema::create('events', static function (Blueprint $table) {
    $table->bigInteger('id');
    $table->timestamp('at');
    $table->primary(['id', 'at']);

    $table->partitionBy('range', 'at');      // or 'list', or 'hash'
});

create table "events" ("id" bigint not null, "at" timestamp(0) without time zone not null,
    primary key ("id", "at")) partition by range ("at")

A partition takes no column list of its own — it inherits the parent's:

Schema::create('events_2026', fn (Blueprint $t) => $t->partitionOf('events')->fromTo('2026-01-01', '2027-01-01'));
Schema::create('events_rest', fn (Blueprint $t) => $t->partitionOf('events')->asDefault());

Schema::create('logs_eu',   fn (Blueprint $t) => $t->partitionOf('logs')->in(['de', 'fr']));
Schema::create('shards_0',  fn (Blueprint $t) => $t->partitionOf('shards')->hash(modulus: 4, remainder: 0));

create table "events_2026" partition of "events" for values from ('2026-01-01') to ('2027-01-01')
create table "events_rest" partition of "events" default
create table "logs_eu" partition of "logs" for values in ('de', 'fr')
create table "shards_0" partition of "shards" for values with (modulus 4, remainder 0)

An existing table can be adopted, and a partition released back into one of its own — the rows go with it:

Schema::table('events', static function (Blueprint $table) {
    $table->attachPartition('events_2025')->fromTo('2025-01-01', '2026-01-01');
    $table->detachPartition('events_2024');
    $table->detachPartition('events_2023', concurrently: true);
});

Row-level security

Per-row authorisation the database enforces itself, which no amount of application code can be talked out of. It pairs with the schema-qualified views above for multi-tenant data.

Schema::create('documents', static function (Blueprint $table) {
    $table->increments('id');
    $table->string('tenant');
    $table->text('body');

    $table->enableRowLevelSecurity();
    $table->forceRowLevelSecurity();        // the owner obeys the policies too

    $table->policy('tenant_read')
        ->for('select')
        ->to('app_user')
        ->using(fn (PartialBuilder $w) => $w->whereRaw('tenant = current_setting(?, true)', ['app.tenant']));

    $table->policy('tenant_write')
        ->for('insert')
        ->to('app_user')
        ->withCheck(fn (PartialBuilder $w) => $w->whereRaw('tenant = current_setting(?, true)', ['app.tenant']));
});

alter table "documents" enable row level security
alter table "documents" force row level security
create policy "tenant_read" on "documents" for select to "app_user"
    using ((tenant = current_setting('app.tenant', true)))
create policy "tenant_write" on "documents" for insert to "app_user"
    with check ((tenant = current_setting('app.tenant', true)))

using decides which rows a statement may see; withCheck decides which it may leave behind. Both take the predicate vocabulary from Index predicates, and a policy with neither is refused — it would permit nothing, which is a mistake more often than an intention.

$table->dropPolicy('tenant_read');
$table->disableRowLevelSecurity();

How a table is stored

Schema::create('cache', static function (Blueprint $table) {
    $table->string('key');

    $table->unlogged();                                    // no write-ahead log
    $table->storageParameters(['fillfactor' => 70, 'autovacuum_vacuum_scale_factor' => 0.05]);
});

Schema::table('cache', fn (Blueprint $t) => $t->resetStorageParameters('fillfactor'));

create unlogged table "cache" ("key" varchar(255) not null)
alter table "cache" set (fillfactor = 70, autovacuum_vacuum_scale_factor = 0.05)
alter table "cache" reset (fillfactor)

Storage parameters are their own ALTER TABLE, so the same call works on a new table and on one that already exists. Both the name and the value are checked, since neither can be a bound parameter.

Extended statistics

The planner assumes columns are independent. When they are not — a city that implies its country, a status that implies its type — it multiplies the two selectivities and lands orders of magnitude off, and an estimate that wrong usually picks the wrong plan.

Schema::create('events', static function (Blueprint $table) {
    $table->string('kind');
    $table->string('region');

    $table->statistics('events_kind_region')->on('kind', 'region');
});

create statistics "events_kind_region" on "kind", "region" from "events"

That is measurable rather than theoretical. Over a table where kind and region agree exactly, explain estimates about a ninth of the rows before the statistics exist and about a third after — which is the correct answer, and the package's own test asserts the improvement.

All three are collected unless you name fewer:

$table->statistics('s')->on('kind', 'region')->kinds('ndistinct', 'dependencies');
$table->statistics('s')->ifNotExists()->on('kind', 'region');
$table->dropStatistics('s', 'other');

Statistics are gathered by ANALYZE, so a fresh object tells the planner nothing until the next analyze — automatic or otherwise.

RETURNING on UPDATE and DELETE

PostgreSQL can hand back the rows a write touched. One round trip instead of select-then-write, and — unlike reading first — no window in which another transaction changes the set underneath you.

// on the query builder
$updated = DB::table('orders')
    ->where('state', 'new')
    ->updateAndReturn(['state' => 'paid'], 'id', 'total');

$deleted = DB::table('sessions')
    ->where('expires_at', '<', now())
    ->deleteAndReturn('id', 'user_id');

// on Eloquent, via macros
$updated = Order::where('state', 'new')->updateAndReturn(['state' => 'paid'], 'id');
$deleted = Order::where('state', 'cancelled')->deleteAndReturn('id');

Both take the columns as a variadic list; pass * for the whole row.

Rows come back in the connection's configured fetch mode, the same as DB::select() — stdClass objects unless you have changed it, and a StatementPrepared event is dispatched either way.

Inserting and reading back

insertGetId() returns one key of one row. insertAndReturn() returns whatever columns you name, for every row inserted — which is the only way to read back what the database generated for a batch:

$rows = DB::table('orders')->insertAndReturn(
    [['total' => 20], ['total' => 30]],
    'id',
    'created_at'
);

insert into "orders" ("total") values (?), (?) returning "id","created_at"

Pass * for whole rows. A single row may be given unwrapped, exactly as insert() allows, and each row's keys are sorted so a batch lines up.

Upserting against a partial unique index

A partial unique index is the package's answer to "unique among the rows that count", and it makes the framework's upsert() unusable on its own:

$table->uniquePartial('email')->whereNull('deleted_at');

DB::table('users')->upsert([['email' => 'a@x.io', 'name' => 'B']], ['email'], ['name']);

SQLSTATE[42P10]: there is no unique or exclusion constraint
matching the ON CONFLICT specification

PostgreSQL will not infer a partial index from the conflict columns alone — the index predicate has to be repeated, and has to match. onConflictWhere() supplies it, in the same vocabulary the index was declared with:

use Php\Support\Laravel\Database\Schema\Postgres\Builders\Indexes\PartialBuilder;

DB::table('users')
    ->onConflictWhere(static fn (PartialBuilder $where) => $where->whereNull('deleted_at'))
    ->upsert([['email' => 'a@x.io', 'name' => 'B']], ['email'], ['name']);

insert into "users" ("email", "name") values (?, ?)
    on conflict ("email") where ("deleted_at" is null)
    do update set "name" = "excluded"."name"

Every predicate from Index predicates works here, because it is the same builder. Repeated calls accumulate rather than replace.

Unique among live rows only

The classic soft-delete problem: unique on email stops a user from ever re-registering with an address that was once deleted.

Schema::create('users', static function (Blueprint $table) {
    $table->primaryUUID();
    $table->string('email');
    $table->softDeletes();

    $table->uniquePartial('email')->whereNull('deleted_at');
});

Two rows with the same address are now fine as long as at most one is live.

One default per group

"Exactly one primary address per user", "one active price per product" — a unique index over the grouping column, restricted to the rows that claim the role.

Schema::create('addresses', static function (Blueprint $table) {
    $table->increments('id');
    $table->foreignId('user_id');
    $table->boolean('is_primary')->default(false);

    $table->uniquePartial('user_id', 'addresses_one_primary')->whereTrue('is_primary');
});

Any number of non-primary addresses, never two primary ones.

At most one NULL

Nulls are distinct by default, so a plain unique index lets any number of them through. nullsNotDistinct() reverses that — useful when null means something specific, such as "the unassigned seat".

Schema::create('memberships', static function (Blueprint $table) {
    $table->increments('id');
    $table->string('team');
    $table->string('seat')->nullable();
    $table->softDeletes();

    $table->uniquePartial(['team', 'seat'], 'memberships_seat_unique')
        ->nullsNotDistinct()
        ->whereNull('deleted_at');
});

PostgreSQL >= 15.

Tag search over a text array

Schema::create('posts', static function (Blueprint $table) {
    $table->increments('id');
    $table->textArray('tags');

    $table->ginIndex('tags');
});

Post::whereRaw("tags @> ARRAY[?]::text[]", ['postgres'])->get();

Containment queries on jsonb

Schema::create('events', static function (Blueprint $table) {
    $table->primaryUUID();
    $table->jsonb('payload');

    $table->ginIndex('payload', 'events_payload_gin', 'jsonb_path_ops');
});

Event::whereRaw("payload @> ?::jsonb", [json_encode(['type' => 'signup'])])->get();

jsonb_path_ops indexes only containment, which is what makes it smaller and faster than the default. If you also need ? key-existence operators, leave the operator class off.

Fuzzy title search

Schema::createExtensionIfNotExists('pg_trgm');

Schema::create('articles', static function (Blueprint $table) {
    $table->increments('id');
    $table->string('title');

    $table->ginIndex('title', 'articles_title_trgm', 'gin_trgm_ops');
});

Article::where('title', 'ilike', '%postgres%')->get();   // now indexable

Adding an index to a busy table

An ordinary CREATE INDEX holds a write lock for its duration. online() trades a second pass for not blocking writes.

use Illuminate\Database\Migrations\Migration;

return new class extends Migration {
    // a PostgreSQL migration is wrapped in a transaction by default, and
    // CREATE INDEX CONCURRENTLY cannot run inside one
    public $withinTransaction = false;

    public function up(): void
    {
        Schema::table('orders', static function (Blueprint $table) {
            $table->partial('customer_id', 'orders_open_customer')
                ->whereIn('state', ['new', 'paid'])
                ->online();
        });
    }
};

A reporting view refreshed without downtime

Schema::createView(
    'order_totals',
    'select customer_id, count(*) as orders, sum(total) as revenue from orders group by 1',
    true
);

// CONCURRENTLY needs a unique index on the view
DB::statement('create unique index order_totals_customer on order_totals (customer_id)');

Schema::refreshMaterializedView('order_totals', true);

Readers keep seeing the previous contents while the refresh runs.

Snapshot a table three ways

// structure, indexes, defaults and constraints — no rows
Schema::create('orders_template', static function (Blueprint $table) {
    $table->like('orders')->includingAll();
});

// rows and columns — nothing else
Schema::create('orders_backup', static function (Blueprint $table) {
    $table->fromTable('orders');
});

// only what the query selects
Schema::create('orders_2026', static function (Blueprint $table) {
    $table->fromSelect("select * from orders where created_at >= '2026-01-01'");
});

A sweep that reports what it touched

$expired = DB::table('sessions')
    ->where('last_seen_at', '<', now()->subDays(30))
    ->deleteAndReturn('id', 'user_id');

foreach ($expired as $session) {
    SessionExpired::dispatch($session->user_id);
}

One statement, and the rows come back as they were — no select-then-delete race.

The same for a state transition that has to notify:

$paid = Order::where('state', 'authorised')
    ->where('authorised_at', '<', now()->subMinutes(15))
    ->updateAndReturn(['state' => 'captured'], 'id', 'customer_id', 'total');

UUID keys without an extension

Schema::create('documents', static function (Blueprint $table) {
    $table->primaryUUID();                        // default gen_random_uuid()
    $table->generateUUID('parent_id', null);      // nullable reference
});

Schema::table('documents', static function (Blueprint $table) {
    $table->foreign('parent_id')->references('id')->on('documents');
});

gen_random_uuid() is built into PostgreSQL 13 and later — no uuid-ossp, no application-side generation.

If your ids come from the application instead:

$table->primaryUUID('id', false);   // uuid not null, no default

Per-tenant views in their own schema

A view definition takes no bindings — PostgreSQL stores it as text — so the tenant's schema name and id go into the statement literally. Validate the identifier and quote the literal yourself; this is the one place in the package's surface where you have to:

use Illuminate\Support\Facades\DB;

foreach ($tenants as $tenant) {
    // an identifier is interpolated, never bound: allow only what an identifier may contain
    if (! preg_match('/^[a-z_][a-z0-9_]*$/', $tenant->schema)) {
        throw new InvalidArgumentException("Refusing schema name [{$tenant->schema}].");
    }

    // a literal is escaped the way PostgreSQL expects, doubling any quote
    $id = "'" . str_replace("'", "''", $tenant->id) . "'";

    DB::statement("create schema if not exists {$tenant->schema}");

    Schema::createView(
        "{$tenant->schema}.active_users",
        "select id, email from users where tenant_id = {$id} and deleted_at is null"
    );
}

Schema::hasView("{$tenants[0]->schema}.active_users");   // true

Dropping a table other objects depend on

public function down(): void
{
    Schema::dropViewIfExists('order_totals', true);
    Schema::dropIfExistsCascade('orders');
}

dropIfExistsCascade() takes the dependents with it, which is what makes a down() reliable when views were created over the table.

Already in Laravel 13

Some of what this package once existed for now ships with the framework. Reach for these first — they are not duplicated here:

What this package adds on top is everything partial — the index variants Laravel has no form for — plus views, the CREATE TABLE variants, extensions, RETURNING, and the column types above.

The shape of the work

The package exists to say the PostgreSQL things Laravel's builder cannot. Everything planned here has shipped: the missing halves of what had already been sold, the constraint that tsrange and daterange exist for, types of one's own, and the physical layout of a table.

Nothing is outstanding. What follows is the record of how it went — including the two entries this page got wrong, which are corrected in place rather than quietly dropped.

v5.1 — shipped

Every item of this group is done: onConflictWhere() and insertAndReturn() under Query builder, and check() under Schema operations.

v5.2 — shipped

Exclusion constraints are under Schema operations; covering indexes and the Expression route for column expressions, ordering and per-column operator classes are under Indexes.

Three of the four "index expressiveness" items turned out to need no new API at all: an Expression in the column list already reaches PostgreSQL verbatim, so an expression index, a sort direction and a per-column operator class were all expressible before this release and simply undocumented. This list said "nothing, anywhere" for them, and that was wrong. Only INCLUDE was genuinely missing.

v5.3 — shipped

Enum types, domains and composite types are under Schema operations, together with adding a label to an existing enum and dropping either kind.

v5.4 — shipped, and not the major it was planned as

Partitioning, row-level security, unlogged tables and storage parameters are under Schema operations.

This group was written down as v6 because it looked like the one that would break something. It does not: every item turned out to be a new method beside the existing ones, and the only existing code touched was the create compiler, which gained two clauses. So it shipped as a minor, and the version number here is corrected rather than kept for appearance.

CREATE STATISTICS shipped with it after all — see Extended statistics.

Deliberately not on this list

Checked as native in Laravel 13, so building them here would be the duplication this package spent a major version removing:

Common table expressions are not in Laravel, and are still not for this package: WITH is ordinary SQL rather than a PostgreSQL extension, so it belongs to a cross-database package.

How this is sequenced

Nothing on this page broke anything, so every group shipped as a minor.

Testing

The package targets PostgreSQL, so the functional suite needs a running server.

With Docker — nothing local required:

composer test:docker

# both versions are overridable
POSTGRES_VERSION=15 composer test:docker
PHP_VERSION=8.5 composer test:docker

Locally — connection settings come from the environment, defaulting to forge/forge/forge on localhost:5432:

composer test         # PHPCS + the whole suite
composer test-cover   # with coverage
composer phpunit-unit # the unit suite alone — asserts on generated SQL, needs no database

Contributing

Issues and pull requests are welcome. Before opening one, run the gate CI runs:

composer phpcs      # PSR-12 over src and tests
composer phpstan    # level 6 with larastan
composer test       # PHPCS + the whole suite, needs PostgreSQL

New behaviour wants a test that fails without it — the suite is checked by mutation, not by coverage percentage. Break the code, and something should go red.

Design decisions

Four things in this package look like duplicated framework code and are kept deliberately. They were each checked against Laravel 13 and found justified; the reasons are here so the question does not get reopened.

Builder::createExtension() / createExtensionIfNotExists() and the two …AndReturn() methods are each two lines apart from their sibling. Collapsing them would change a public API for a very small gain, so they stay as they are.

The PHP floor is a deliberate choice, not a requirement. Laravel 13 itself needs only php: ^8.3, and nothing in src/ uses an 8.4 or 8.5 construct — ^8.4 || ^8.5 would reach twice as many applications at the same level of modernity. >= 8.5 was chosen anyway, and this is the record of that trade-off.

The rule that matters most: do not copy framework methods — call parent:: and add to it. Five methods had been copied wholesale at one point, and one of them had already drifted out of sync, silently dropping two service bindings. None remain. GrammarTable::compileDropIfExists() is the pattern to follow, and #[\Override] belongs on every override so PHP itself catches a parent method that disappears.