Laravel Postgresql Enhanced Laravel Package

Getting Started

Minimal Setup

1. Installation: Add the package via Composer:

   composer require tpetry/laravel-postgresql-enhanced
   

2. Configuration: No additional configuration is required beyond ensuring your config/database.php uses PostgreSQL as the default connection.
3. First Use Case: Enable PostgreSQL-specific features in migrations. For example, create a partial index:

   use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
   use Illuminate\Database\Migrations\Migration;
   use Illuminate\Support\Facades\Schema;
   
   return new class extends Migration {
       public function up(): void
       {
           Schema::table('users', function (Blueprint $table) {
               $table->index('email', 'idx_users_email_active')->where('active', true);
           });
       }
   };
   

Key Starting Points

• Schema Builder: Extends Laravel’s Schema facade with PostgreSQL-specific methods (e.g., createExtension, createFunction).
• Blueprint: Use Tpetry\PostgresqlEnhanced\Schema\Blueprint in migrations for advanced column/index options.
• Query Builder: Add PostgreSQL features like CTEs or RETURNING clauses via DB::statement() or custom query methods.

Implementation Patterns

Migration Workflows

1. Schema Modifications:

   • Use Schema::table() with the enhanced Blueprint for zero-downtime changes:

     Schema::table('orders', function (Blueprint $table) {
         $table->string('status')->change()->nullable()->after('amount');
     });
     

   • Leverage ZeroDowntimeMigration trait to auto-revert if the migration times out:

     use Tpetry\PostgresqlEnhanced\Schema\Concerns\ZeroDowntimeMigration;
     
     class UpdateOrderStatus extends Migration {
         use ZeroDowntimeMigration;
         protected float $timeout = 3.0; // 3-second timeout
     }
     

2. Extensions and Functions:

   • Install extensions (e.g., pg_trgm for fuzzy matching) in a migration:

     Schema::createExtension('pg_trgm');
     

   • Create custom functions for reusable logic:

     Schema::createFunction(
         'calculate_discount',
         ['price' => 'numeric', 'discount' => 'numeric'],
         'numeric',
         'sql:expression',
         'price * (1 - discount)'
     );
     

3. Advanced Indexes:

   • Add functional indexes for computed columns:

     $table->index(['name', 'surname'], 'idx_user_name_surname')->using('gin');
     $table->index('email', 'idx_user_email_trgm')->using('gin')->with('trgm_ops');
     

Query Patterns

1. CTEs (Common Table Expressions):

   DB::statement('
       WITH active_users AS (
           SELECT * FROM users WHERE active = true
       )
       SELECT * FROM active_users WHERE created_at > NOW() - INTERVAL \'1 year\'
   ');
   

2. Full-Text Search:

   $results = DB::table('articles')
       ->where('content', '@@', 'laravel postgresql')
       ->get();
   

3. Lateral Joins:

   $query = DB::table('orders')
       ->select('orders.*')
       ->joinSub(
           DB::table('order_items')
               ->select('order_id', 'product_id')
               ->whereColumn('order_id', 'orders.id')
               ->limit(1),
           'lateral_subquery',
           function ($join) {
               $join->on('lateral_subquery.order_id', '=', 'orders.id');
           }
       );
   

Eloquent Patterns

1. Custom Casts:

   use Tpetry\PostgresqlEnhanced\Casts\JsonCast;
   
   class User extends Model {
       protected $casts = [
           'metadata' => JsonCast::class,
           'tags' => \Tpetry\PostgresqlEnhanced\Casts\ArrayCast::class,
       ];
   }
   

2. Refresh Data on Save:

   class Order extends Model {
       protected $refreshOnSave = ['total', 'tax'];
   }
   

Gotchas and Tips

Pitfalls

1. IDE Autocomplete:

   • Issue: IDEs (e.g., PhpStorm) may not recognize PostgreSQL-specific methods.
   • Fix: Run Laravel IDE Helper’s code generation:

     php artisan ide-helper:generate
     

     Or manually add the PHPStan extension path to phpstan.neon:

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

2. Zero-Downtime Migrations:

   • Gotcha: Complex schema changes (e.g., dropping columns) may still lock tables.
   • Tip: Test migrations in a staging environment first. Use ->ifNotExists() for indexes/triggers to avoid errors.

3. Functional Indexes:

   • Issue: Functional indexes (e.g., LOWER(name)) may not work as expected if the function is not deterministic.
   • Fix: Ensure functions used in indexes are marked as IMMUTABLE or STABLE:

     Schema::createFunction('lower_name', ['name' => 'text'], 'text', 'sql:expression', 'LOWER(name)', [
         'volatility' => 'immutable',
     ]);
     

4. PostgreSQL-Specific Syntax:

   • Gotcha: Raw SQL queries using PostgreSQL syntax (e.g., RETURNING *) must be executed via DB::statement() or DB::select().
   • Example:

     $updatedRows = DB::table('users')
         ->where('id', 1)
         ->update(['name' => 'New Name']);
     $returning = DB::select('UPDATE users SET name = ? RETURNING *', ['New Name']);
     

Debugging Tips

1. Explain Queries: Use the explain() method to analyze query plans:

   $explanation = DB::table('users')->explain()->toSql();
   

2. Transaction Rollbacks: Wrap zero-downtime migrations in transactions to ensure atomicity:

   DB::transaction(function () {
       Schema::table('users', function (Blueprint $table) {
           $table->string('email')->change();
       });
   });
   

3. Extension Conflicts:

   • Error: extension "xyz" already exists.
   • Solution: Use createExtensionIfNotExists() to avoid failures.

Extension Points

1. Custom Query Methods: Extend the query builder by adding methods to app/Providers/AppServiceProvider.php:

   use Illuminate\Support\Facades\DB;
   use Tpetry\PostgresqlEnhanced\Query\Builder;
   
   public function boot(): void {
       Builder::macro('pgFullTextSearch', function ($column, $query) {
           return $this->where($column, '@@', $query);
       });
   }
   

   Usage:

   DB::table('articles')->pgFullTextSearch('content', 'laravel')->get();
   

2. Blueprint Extensions: Add custom methods to the Blueprint class by publishing and modifying the package’s config:

   php artisan vendor:publish --provider="Tpetry\PostgresqlEnhanced\PostgresqlEnhancedServiceProvider"
   

   Then extend app/Providers/PostgresqlEnhancedServiceProvider.php.

3. TimescaleDB Support: For time-series data, enable the TimescaleDB extension:

   Schema::createExtension('timescaledb');
   

   Use timescaledb column types (e.g., timestamp with time zone) and leverage hypertable partitioning:

   $table->timestampTz('created_at')->withTimePrecision(6);
   

Performance Quirks

1. Concurrent Indexes: Add indexes concurrently to avoid table locks:

   $table->index('email')->concurrently();
   

2. Unlogged Tables: Use unlogged tables for temporary data to skip WAL (Write-Ahead Logging):

   $table->unlogged();
   

3. Storage Parameters: Optimize storage for large text columns:

   $table->text('description')->storage('extended');