Laravel PG extensions

This project extends Laravel`s database layer to allow use specific Postgres features without raw queries.

Installation

Run this command to install:

php composer.phar require umbrellio/laravel-pg-extensions

Features

• Extended Schema::create()
• Added Support Numeric Type
• Extended Schema with USING
• Extended Schema for views
• Working with unique indexes
• Working with partitions
• Check existing index before manipulation

Extended table creation

Example:

Schema::create('table', function (Blueprint $table) {
    $table->like('other_table')->includingAll(); 
    $table->ifNotExists();
});

Extended Schema USING

Example:

Schema::create('table', function (Blueprint $table) {
    $table->integer('number');
});

//modifications with data...

Schema::table('table', function (Blueprint $table) {
    $table
        ->string('number')
        ->using("('[' || number || ']')::character varyiing")
        ->change();
});

Create views

Example:

// Facade methods:
Schema::createView('active_users', "SELECT * FROM users WHERE active = 1");
Schema::dropView('active_users')

// Schema methods:
Schema::create('users', function (Blueprint $table) {
    $table
        ->createView('active_users', , "SELECT * FROM users WHERE active = 1")
        ->materialize();
});

Extended unique indexes creation

Example:

Schema::create('table', function (Blueprint $table) {
    $table->string('code'); 
    $table->softDeletes();
    $table->uniquePartial('code')->whereNull('deleted_at');
});

Partitions

Support for attaching and detaching partitions.

Example:

Schema::table('table', function (Blueprint $table) {
    $table->attachPartition('partition')->range([
        'from' => now()->startOfDay(), // Carbon will be converted to date time string
        'to' => now()->tomorrow(),
    ]);
});

Check existing index

Schema::table('some_table', function (Blueprint $table) {
   // check unique index exists on column
   if ($table->hasIndex(['column'], true)) {
      $table->dropUnique(['column']);
   }
   $table->uniquePartial('column')->whereNull('deleted_at');
});

Numeric column type

Unlike standard laravel decimal type, this type can be with variable precision

Schema::table('some_table', function (Blueprint $table) {
   $table->numeric('column_with_variable_precision');
   $table->numeric('column_with_defined_precision', 8);
   $table->numeric('column_with_defined_precision_and_scale', 8, 2);
});

Custom Extensions

1). Create a repository for your extension.

2). Add this package as a dependency in composer.

3). Inherit the classes you intend to extend from abstract classes with namespace: namespace Umbrellio\Postgres\Extensions

4). Implement extension methods in closures, example:

use Umbrellio\Postgres\Extensions\Schema\AbstractBlueprint;
class SomeBlueprint extends AbstractBlueprint
{
   public function someMethod()
   {
       return function (string $column): Fluent {
           return $this->addColumn('someColumn', $column);
       };
   }
}

5). Create Extension class and mix these methods using the following syntax, ex:

use Umbrellio\Postgres\PostgresConnection;
use Umbrellio\Postgres\Schema\Blueprint;
use Umbrellio\Postgres\Schema\Grammars\PostgresGrammar;
use Umbrellio\Postgres\Extensions\AbstractExtension;

class SomeExtension extends AbstractExtension
{
    public static function getMixins(): array
    {
        return [
            Blueprint::class => SomeBlueprint::class,
            PostgresConnection::class => SomeConnection::class,
            PostgresGrammar::class => SomeSchemaGrammar::class,
            ...
        ];
    }
    
    public static function getTypes(): string
    {
        // where SomeType extends Doctrine\DBAL\Types\Type
        return [
            'some' => SomeType::class,
        ];
    }

    public static function getName(): string
    {
        return 'some';
    }
}

6). Register your Extension in ServiceProvider and put in config/app.php, ex:

use Illuminate\Support\ServiceProvider;
use Umbrellio\Postgres\PostgresConnection;

class SomeServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        PostgresConnection::registerExtension(SomeExtension::class);
    }
}

TODO features

• Extend CreateCommand with inherits and partition by
• Extend working with partitions
• COPY support
• DISTINCT on specific columns
• INSERT ON CONFLICT support
• ...

License

Released under MIT License.

Authors

Created by Vitaliy Lazeev.

Contributing

• Fork it ( https://github.com/umbrellio/laravel-pg-extensions )
• Create your feature branch (git checkout -b feature/my-new-feature)
• Commit your changes (git commit -am 'Add some feature')
• Push to the branch (git push origin feature/my-new-feature)
• Create new Pull Request