Everyone who once started to lead a more or less normal Enterprise project in Laravel, faced with the fact that the standard solutions that Laravel offers out of the box are no longer enough.
And if you, like me, use Postgres in your projects, then sooner or later you will need the perks of this wonderful DBMS, such as: various kinds of indexes and constraints, extensions, new types, etc.
Today, as you have already noticed, we will talk about Postgres, about Laravel migrations, how to make it all together, in general, about everything that we lack in Lara's standard migrations.
Well, for those who do not want to dive into the intricacies of the internal structure of Laravel, they can simply download a package that extends the capabilities of Laravel and Postgres migrations from this link and use it in their projects.
But I still recommend not to scroll, but to read everything to the end.
Migrations
This is what the standard migration looks like in Laravel:
Typical migration example
<?php
declare(strict_types=1);
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateDocuments extends Migration
{
private const TABLE = 'documents';
public function up()
{
Schema::create(static::TABLE, function (Blueprint $table) {
$table->bigIncrements('id');
$table->timestamps();
$table->softDeletes();
$table->string('number');
$table->date('issued_date')->nullable();
$table->date('expiry_date')->nullable();
$table->string('file');
$table->bigInteger('author_id');
$table->bigInteger('type_id');
$table->foreign('author_id')->references('id')->on('users');
$table->foreign('type_id')->references('id')->on('document_types');
});
}
public function down()
{
Schema::dropIfExists(static::TABLE);
}
}
But, here you read an article on Habré about new types in Postgres, for example, tsrange and wanted to add something like this to the migration ...
$table->addColumn('tsrange', 'period')->nullable();
, , , Laravel, , :
$table->tsRange('period')->nullable();
, , , :
Laravel
Blueprint
<?php
Blueprint::macro('tsRange', function (string $columnName) {
return $this->addColumn('tsrange', $columnName);
});
PostgresGrammar
<?php
PostgresGrammar::macro('typeTsrange', function () {
rerurn 'tsrange';
});
- , ExtendDatabaseProvider:
<?php
use Illuminate\Support\ServiceProvider;
class DatabaseServiceProvider extends ServiceProvider
{
public function register()
{
Blueprint::macro('tsRange', function (string $columnName) {
return $this->addColumn('tsrange', $columnName);
});
PostgresGrammar::macro('typeTsrange', function () {
return 'tsrange';
});
}
}
, , ..
, Laravel MacroableTrait, .
( 1)
, , +100500 , . , CI, ...
, " " - :
Doctrine\DBAL\Driver\PDOException: SQLSTATE[08006] [7]
FATAL: sorry, too many clients already
, , .env CI GitLab, , , , , . CI - , , , - , vendor. .
, , , .
- ( ), , , , - , , .
sorry, too many clients already .
, , PostgresGrammar , , .... -, . :
Doctrine\DBAL\DBALException: Unknown database type tsrange requested,
Doctrine\DBAL\Platforms\PostgreSQL100Platform may not support it.
, , ... , . ?
Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType().
You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap().
If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type.
Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes().
If the type name is empty you might have a problem with the cache or forgot some mapping information.
, Doctrine\Dbal , Database Connection , ( , Postgres, getTypesMap Doctrine.
( 2)
, - vendor doctrine\dbal...
- , , Doctrine, , , .
, !
, , ..
..
, .
, , , , - Doctrine.
, , , , , , , , Laravel Doctrine, .
, , , . .
DatabaseProvider, Laravel, Extension- , , , Doctrine.
, , , :
Blueprint - , ,
Builder - Schema
PostgresGrammar - Blueprint- SQL-
Types -
, , Laravel , , , IDE , , .
,
<?php
namespace Umbrellio\Postgres\Extensions;
use Illuminate\Support\Traits\Macroable;
use Umbrellio\Postgres\Extensions\Exceptions\MacroableMissedException;
use Umbrellio\Postgres\Extensions\Exceptions\MixinInvalidException;
abstract class AbstractExtension extends AbstractComponent
{
abstract public static function getMixins(): array;
abstract public static function getName(): string;
public static function getTypes(): array
{
return [];
}
final public static function register(): void
{
collect(static::getMixins())->each(static function ($extension, $mixin) {
if (!is_subclass_of($mixin, AbstractComponent::class)) {
throw new MixinInvalidException(sprintf(
'Mixed class %s is not descendant of %s.',
$mixin,
AbstractComponent::class
));
}
if (!method_exists($extension, 'mixin')) {
throw new MacroableMissedException(sprintf('Class %s doesn’t use Macroable Trait.', $extension));
}
/** @var Macroable $extension */
$extension::mixin(new $mixin());
});
}
}
, , , Laravel , Doctrine.
PostgresConnection
<?php
namespace Umbrellio\Postgres;
use DateTimeInterface;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Events;
use Illuminate\Database\PostgresConnection as BasePostgresConnection;
use Illuminate\Support\Traits\Macroable;
use PDO;
use Umbrellio\Postgres\Extensions\AbstractExtension;
use Umbrellio\Postgres\Extensions\Exceptions\ExtensionInvalidException;
use Umbrellio\Postgres\Schema\Builder;
use Umbrellio\Postgres\Schema\Grammars\PostgresGrammar;
use Umbrellio\Postgres\Schema\Subscribers\SchemaAlterTableChangeColumnSubscriber;
class PostgresConnection extends BasePostgresConnection
{
use Macroable;
private static $extensions = [];
final public static function registerExtension(string $extension): void
{
if (!is_subclass_of($extension, AbstractExtension::class)) {
throw new ExtensionInvalidException(sprintf(
'Class %s must be implemented from %s',
$extension,
AbstractExtension::class
));
}
self::$extensions[$extension::getName()] = $extension;
}
public function getSchemaBuilder()
{
if ($this->schemaGrammar === null) {
$this->useDefaultSchemaGrammar();
}
return new Builder($this);
}
public function useDefaultPostProcessor(): void
{
parent::useDefaultPostProcessor();
$this->registerExtensions();
}
protected function getDefaultSchemaGrammar()
{
return $this->withTablePrefix(new PostgresGrammar());
}
private function registerExtensions(): void
{
collect(self::$extensions)->each(function ($extension) {
/** @var AbstractExtension $extension */
$extension::register();
foreach ($extension::getTypes() as $type => $typeClass) {
$this
->getSchemaBuilder()
->registerCustomDoctrineType($typeClass, $type, $type);
}
});
}
}
:
DatabaseProvider
<?php
namespace Umbrellio\Postgres;
use Illuminate\Database\DatabaseManager;
use Illuminate\Database\DatabaseServiceProvider;
use Umbrellio\Postgres\Connectors\ConnectionFactory;
class UmbrellioPostgresProvider extends DatabaseServiceProvider
{
protected function registerConnectionServices(): void
{
$this->app->singleton('db.factory', function ($app) {
return new ConnectionFactory($app);
});
$this->app->singleton('db', function ($app) {
return new DatabaseManager($app, $app['db.factory']);
});
$this->app->bind('db.connection', function ($app) {
return $app['db']->connection();
});
}
}
ConnectionFactory
<?php
namespace Umbrellio\Postgres\Connectors;
use Illuminate\Database\Connection;
use Illuminate\Database\Connectors\ConnectionFactory as ConnectionFactoryBase;
use Umbrellio\Postgres\PostgresConnection;
class ConnectionFactory extends ConnectionFactoryBase
{
protected function createConnection($driver, $connection, $database, $prefix = '', array $config = [])
{
if ($resolver = Connection::getResolver($driver)) {
return $resolver($connection, $database, $prefix, $config);
}
if ($driver === 'pgsql') {
return new PostgresConnection($connection, $database, $prefix, $config);
}
return parent::createConnection($driver, $connection, $database, $prefix, $config);
}
}
tsrange . .
TsRangeExtension.php
<?php
namespace App\Extensions\TsRange;
use App\Extensions\TsRange\Schema\Grammars\TsRangeSchemaGrammar;
use App\Extensions\TsRange\Schema\TsRangeBlueprint;
use App\Extensions\TsRange\Types\TsRangeType;
use Umbrellio\Postgres\Extensions\AbstractExtension;
use Umbrellio\Postgres\Schema\Blueprint;
use Umbrellio\Postgres\Schema\Grammars\PostgresGrammar;
class TsRangeExtension extends AbstractExtension
{
public const NAME = TsRangeType::TYPE_NAME;
public static function getMixins(): array
{
return [
TsRangeBlueprint::class => Blueprint::class,
TsRangeSchemaGrammar::class => PostgresGrammar::class,
// ... Laravel
];
}
public static function getName(): string
{
return static::NAME;
}
public static function getTypes(): array
{
return [
static::NAME => TsRangeType::class,
];
}
}
TsRangeBlueprint.php
<?php
namespace App\Extensions\TsRange\Schema;
use Illuminate\Support\Fluent;
use App\Extensions\TsRange\Types\TsRangeType;
use Umbrellio\Postgres\Extensions\Schema\AbstractBlueprint;
class TsRangeBlueprint extends AbstractBlueprint
{
public function tsrange()
{
return function (string $column): Fluent {
return $this->addColumn(TsRangeType::TYPE_NAME, $column);
};
}
}
TsRangeSchemaGrammar.php
<?php
namespace App\Extensions\TsRange\Schema\Grammars;
use App\Extensions\TsRange\Types\TsRangeType;
use Umbrellio\Postgres\Extensions\Schema\Grammar\AbstractGrammar;
class TsRangeSchemaGrammar extends AbstractGrammar
{
protected function typeTsrange()
{
return function (): string {
return TsRangeType::TYPE_NAME;
};
}
}
TsRangeType.php
<?php
namespace App\Extensions\TsRange\Types;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;
class TsRangeType extends Type
{
public const TYPE_NAME = 'tsrange';
public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform): string
{
return static::TYPE_NAME;
}
public function convertToPHPValue($value, AbstractPlatform $platform): ?array
{
//...
return $value;
}
public function convertToDatabaseValue($value, AbstractPlatform $platform): ?string
{
//...
return $value;
}
public function getName(): string
{
return self::TYPE_NAME;
}
}
TsRangeExtension :
<?php
namespace App\TsRange\Providers;
use Illuminate\Support\ServiceProvider;
use App\Extensions\TsRange\TsRangeExtension;
use Umbrellio\Postgres\PostgresConnection;
class TsRangeExtensionProvider extends ServiceProvider
{
public function register(): void
{
PostgresConnection::registerExtension(TsRangeExtension::class);
}
}
Postgres AbstractExtension, , , Laravel Doctrine.
, - PHP , Laravel / Postgres, , .
, , Issues / Pull-, , .
GitHub: laravel-pg-extensions.
.