laravel-postgresql-enhanced

Open source

Support for many missing PostgreSQL specific features

1.0K stars0 likes3 views

About

Support for many missing PostgreSQL specific features

![License][icon-license] ![PHP][icon-php] [![Latest Version on Packagist][icon-version]][href-version] [![Downloads on Packagist][icon-downloads]][href-downloads] [![GitHub PHPUnit Action Status][icon-tests]][href-tests] [![GitHub PHPStan Action Status][icon-phpstantest]][href-phpstantest] [![GitHub PhpCsFixer Action Status][icon-style]][href-style]

Laravel supports many different databases and therefore has to limit itself to the lowest common denominator of all databases. PostgreSQL, however, offers a ton more functionality which is being added to Laravel by this extension.

Installation

You can install the package via composer:

bash

composer require tpetry/laravel-postgresql-enhanced

Versioning Policy

This package follows the semantic versioning policy. So you can always safely upgrade to a new minor and patch version without any issues.

As Laravel is a healthy framework that is still adding new database functionality, some things may break: For instance, in the past, Laravel introduced new builder methods that had already been part of this package for a long time - but with different semantics. In such cases, a new major version is promptly released with a breaking change to align with Laravel's implementation.

When upgrading Laravel, you may also have to upgrade this package to a new major version when something breaks! The minimal breaking changes of the past years are listed in the breaking changes section.

IDE Autocomplete

Laravel provides many extension capabilities, making it hard for IDEs to do proper autocomplete. Currently, there is no generic way to tell IDEs about the added functionality of this enhanced driver. However, this package is integrated with specific IDE combinations:

PhpStorm with Laravel Idea: The IDE Helper for Laravel can detect all PostgreSQL features by running the Generate Helper Code (Eloquent, Maco, Facades, etc.) action from the Laravel -> Code Generation toolbar menu.

PHPStan

This extension is adding a lot of missing PostgreSQL functionality to Laravel. A custom set of PHPStan extensions have been developed to get full static analysis support!

All features provided this extension are automatically recognized by the phpstan/extension-installer. Otherwise, you have to manually add the following path to your includes list in phpstan.neon, your config should now look like this:

includes:
    - ./vendor/nunomaduro/larastan/extension.neon
    - ./vendor/tpetry/laravel-postgresql-enhanced/phpstan-extension.neon

Migration

Zero-Downtime Migration

For applications with 24/7 requirements, migrations must never impact availability. PostgreSQL provides many functionalities to execute changes on the schema without downtime. However, sometimes a change to the schema is not tested sufficiently and locks the tables for a longer period of time in order to make the desired change. To avoid this problem, a migration can be marked as zero-downtime migration. If the migration exceeds a specified time limit, it is cancelled and the schema is reset to its original state.

…

The timeout for a maximum time limit of 1.0 second can be set separately for each migration. You can set private float $timeout = 5.0 on the migration for a up/down timeout. Or you can set the specific timeouts $timeoutUp and $timeoutDown to differentiate between the methods.

Extensions

Create Extensions

The Schema facade supports the creation of extensions with the createExtension and createExtensionIfNotExists methods:

php

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createExtension('tablefunc');
Schema::createExtensionIfNotExists('tablefunc');

Dropping Extensions

To remove extensions, you may use the dropExtension and dropExtensionIfExists methods provided by the Schema facade:

php

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropExtension('tablefunc');
Schema::dropExtensionIfExists('tablefunc');

You may drop many extensions at once by passing multiple extension names:

php

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropExtension('tablefunc', 'fuzzystrmatch');
Schema::dropExtensionIfExists('tablefunc', 'fuzzystrmatch');

Functions

Create Functions

The Schema facade supports the creation of functions with the createFunction and createFunctionOrReplace methods. For the definition of your function you have to provide the name of the function, the parameters, the return type, the function's language and body:

php

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createFunction(
  name: 'sales_tax',
  parameters: ['subtotal' => 'numeric'],
  return: 'numeric',
  language: 'plpgsql',
  body: '
    BEGIN
      RETURN subtotal * 0.06;
    END;
  '
);

A sixth parameter lets you define further options for the function. Please read the manual for the exact meaning, some of them set enable or disable ways for PostgreSQL to optimize the execution.

Option	Values	Description
`calledOnNull`	bool	Defines whether the function should be called for NULL values.
`cost`	integer	Defines the cost for executing the function.
`leakproof`	bool	Informs whether the function has side effects.
`parallel`	`restricted`, `safe`, `unsafe`	Defines whether the function can be executed in parallel.
`security`	`definer`, `invoker`	Defines that the function will be executed with the privileges of the current user or creator of the function.
`volatility`	`immutable`, `stable`, `volatile`	Informs whether the function changes database values.
`searchPath`	array\	Defines the search path of the function.

The former example can be optimized by using the special sql:expression language identifier created by this driver. The function body can only be one SQL expression, but it will be inlined in the query instead of executed with recent PostgreSQL versions for much better performance:

php

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createFunction('sales_tax', ['subtotal' => 'numeric'], 'numeric', 'sql:expression', 'subtotal * 0.06', [
  'parallel' => 'safe',
  'volatility' => 'immutable',
]);

If you want your function to return a table, you have to provide the columns as return type:

php

Schema::createFunction('search_user', ['pattern' => 'text'], ['id' => 'int', 'email' => 'text'], 'plpgsql', "
  BEGIN
    RETURN QUERY select user_id, contactemail from users where name ilike '%' || pattern || '%';
  END;
");

Drop Functions

To remove functions, you may use the dropFunction and dropFunctionIfExists methods provided by the Schema facade:

php

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropFunction('sales_tax');
Schema::dropFunctionIfExists('sales_tax');

Triggers

Create Triggers

On your Blueprint you can add triggers to a table. You need to pass in a unique name, call of a function you've created before and the action that will fire the trigger:

php

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('projects', function (Blueprint $table): void {
    $table->trigger('rollup_quota', 'update_quota_by_projects()', 'AFTER INSERT OR DELETE');
});

The following table contains all of the available trigger modifiers:

Modifier	Description
`->forEachRow()`	The trigger will be called for every row.
`->forEachStatement()`	The trigger will be called once for each statement (default).
`->transitionTables(`
`old: 'oldrows',`
`new: 'newrows',`
`)`	The forEachStatement-trigger will provide the before/after state of the affected rows in special tables. You can omit eit