Awesome
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:
composer 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 EXCLUDE constraints
- Working with CHECK constraints
- Working with partitions
- Check existing index before manipulation
- Getting foreign keys for table
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 varying")
->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();
});
Get foreign keys
Example:
// Facade methods:
/** @var ForeignKeyDefinition[] $fks */
$fks = Schema::getForeignKeys('some_table');
foreach ($fks as $fk) {
// $fk->source_column_name
// $fk->target_table_name
// $fk->target_column_name
}
Extended unique indexes creation
Example:
Schema::create('table', function (Blueprint $table) {
$table->string('code');
$table->softDeletes();
$table->uniquePartial('code')->whereNull('deleted_at');
});
If you want to delete partial unique index, use this method:
Schema::create('table', function (Blueprint $table) {
$table->dropUniquePartial(['code']);
});
$table->dropUnique()
doesn't work for Partial Unique Indexes, because PostgreSQL doesn't
define a partial (ie conditional) UNIQUE constraint. If you try to delete such a Partial Unique
Index you will get an error.
CREATE UNIQUE INDEX CONCURRENTLY examples_new_col_idx ON examples (new_col);
ALTER TABLE examples
ADD CONSTRAINT examples_unique_constraint USING INDEX examples_new_col_idx;
When you create a unique index without conditions, PostgresSQL will create Unique Constraint automatically for you, and when you try to delete such an index, Constraint will be deleted first, then Unique Index.
Exclude constraints creation
Using the example below:
Schema::create('table', function (Blueprint $table) {
$table->integer('type_id');
$table->date('date_start');
$table->date('date_end');
$table->softDeletes();
$table
->exclude(['date_start', 'date_end'])
->using('type_id', '=')
->using('daterange(date_start, date_end)', '&&')
->method('gist')
->with('some_arg', 1)
->with('any_arg', 'some_value')
->whereNull('deleted_at');
});
An Exclude Constraint will be generated for your table:
ALTER TABLE test_table
ADD CONSTRAINT test_table_date_start_date_end_excl
EXCLUDE USING gist (type_id WITH =, daterange(date_start, date_end) WITH &&)
WITH (some_arg = 1, any_arg = 'some_value')
WHERE ("deleted_at" is null)
Check constraints creation
Using the example below:
Schema::create('table', function (Blueprint $table) {
$table->integer('type_id');
$table->date('date_start');
$table->date('date_end');
$table
->check(['date_start', 'date_end'])
->whereColumn('date_end', '>', 'date_start')
->whereIn('type_id', [1, 2, 3]);
});
An Check Constraint will be generated for your table:
ALTER TABLE test_table
ADD CONSTRAINT test_table_date_start_date_end_chk
CHECK ("date_end" > "date_start" AND "type_id" IN [1, 2, 3])
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 [
SomeBlueprint::class => Blueprint::class,
SomeConnection::class => PostgresConnection::class,
SomeSchemaGrammar::class => PostgresGrammar::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
withinherits
andpartition 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 & Korben Dallas.
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