Laravel Postgresql Enhanced Laravel Package

## Getting Started

### First Steps
1. **Installation**: Require the package via Composer:
   ```bash
   composer require tpetry/laravel-postgresql-enhanced

Publish the config (if needed) with:

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

2. Verify Setup: Ensure your config/database.php uses pgsql as the connection driver.

3. First Use Case: Use PostgreSQL-specific features in migrations. For example, create a materialized view:

   use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
   use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;
   
   Schema::createMaterializedView('user_stats', function (Blueprint $table) {
       $table->from('users');
       $table->select(['id', 'name', 'created_at']);
   });
   

Where to Look First

• README.md: Focus on the Features section for quick reference.
• Migrations: The Schema facade methods (e.g., createExtension, createFunction) are the most commonly used entry points.
• Eloquent: Check the Casts and Date Formats for immediate productivity gains.

Implementation Patterns

Migration Workflows

1. Zero-Downtime Migrations:

   • Use the ZeroDowntimeMigration trait for critical schema changes:

     use Tpetry\PostgresqlEnhanced\Schema\Concerns\ZeroDowntimeMigration;
     
     class UpdateUserTable extends Migration {
         use ZeroDowntimeMigration;
     
         public function up() {
             Schema::table('users', function (Blueprint $table) {
                 $table->string('email', 255)->change();
             });
         }
     }
     

   • Set $timeout = 5.0 to abort if the migration exceeds 5 seconds.

2. Extension Management:

   • Enable extensions early in migrations:

     Schema::createExtension('uuid-ossp');
     Schema::createExtensionIfNotExists('pg_trgm');
     

   • Drop unused extensions in down() methods.

3. Functional Indexes:

   • Create indexes on expressions (e.g., for full-text search):

     $table->index('(lower(name))', 'idx_name_lower');
     

Query Patterns

1. Full-Text Search:

   • Use the ->whereFullText() builder method:

     $results = User::whereFullText('name', 'John Doe')->get();
     

   • Combine with PostgreSQL-specific operators:

     $results = User::where('name', 'plainto_tsquery', 'John & Doe')->get();
     

2. CTEs (Common Table Expressions):

   • Define recursive or non-recursive CTEs:

     $query = DB::table(DB::raw('
         WITH RECURSIVE category_tree AS (
             SELECT id, name, parent_id, 1 AS level FROM categories WHERE parent_id IS NULL
             UNION ALL
             SELECT c.id, c.name, c.parent_id, ct.level + 1
             FROM categories c
             JOIN category_tree ct ON c.parent_id = ct.id
         )
         SELECT * FROM category_tree
     '))->get();
     

3. Lateral Joins:

   • Use joinLateral() for correlated subqueries:

     $query = User::select('users.*')
         ->joinLateral(function ($join) {
             $join->select('order_id')
                 ->from('orders')
                 ->whereColumn('users.id', 'orders.user_id')
                 ->orderBy('orders.created_at', 'desc')
                 ->limit(1);
         }, 'latest_order');
     

Eloquent Patterns

1. Custom Casts:

   • Cast PostgreSQL-specific types (e.g., jsonb, uuid):

     protected $casts = [
         'metadata' => 'jsonb',
         'id' => 'uuid',
     ];
     

   • Use the ->asPostgresType() helper for custom types:

     protected $casts = [
         'ip_address' => 'ip_address:asPostgresType',
     ];
     

2. Refresh Data on Save:

   • Enable automatic refresh of timestamps or computed columns:

     use Tpetry\PostgresqlEnhanced\Eloquent\Concerns\RefreshesDataOnSave;
     
     class User extends Model {
         use RefreshesDataOnSave;
     
         protected $refreshOnSave = ['updated_at', 'last_login'];
     }
     

Gotchas and Tips

Pitfalls

1. IDE Autocomplete:

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

     php artisan ide-helper:generate
     

   • Alternative: Manually add to phpstan.neon:

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

2. Function Creation:

   • Issue: Forgetting to specify sql:expression for simple functions can lead to poor performance.
   • Fix: Use sql:expression for immutable, inlineable logic:

     Schema::createFunction('calculate_tax', ['amount' => 'numeric'], 'numeric', 'sql:expression', 'amount * 0.08');
     

3. Trigger Conflicts:

   • Issue: Naming triggers ambiguously can cause conflicts.
   • Fix: Use descriptive names (e.g., trigger_update_user_status) and scope to tables:

     $table->trigger('trigger_update_user_status_after_insert', 'update_user_status()', 'AFTER INSERT');
     

4. Zero-Downtime Timeouts:

   • Issue: Setting too short a timeout may abort valid migrations.
   • Fix: Start with 5.0 seconds and adjust based on testing.

5. Partial Indexes:

   • Issue: Partial indexes may not behave as expected if the WHERE clause is too broad.
   • Fix: Test partial indexes with EXPLAIN ANALYZE:

     DB::select('EXPLAIN ANALYZE SELECT * FROM users WHERE active = true');
     

Debugging Tips

1. Query Explanation:

   • Use ->toSqlWithBindings() to inspect raw SQL:

     $sql = User::whereFullText('name', 'John')->toSqlWithBindings();
     

   • For PostgreSQL-specific plans, use EXPLAIN:

     DB::select('EXPLAIN ANALYZE ' . $sql);
     

2. Migration Rollback:

   • If a zero-downtime migration fails, check PostgreSQL logs for locks:

     psql -c "SELECT * FROM pg_locks WHERE relation = 'users'::regclass;"
     

3. Extension Conflicts:

   • Verify extensions are installed:

     psql -c "\dx"
     

   • Reinstall if missing:

     Schema::dropExtension('pg_trgm');
     Schema::createExtension('pg_trgm');
     

Extension Points

1. Custom Types:

   • Extend the package by adding new casts or query builders. Example:

     // app/Providers/PostgresqlEnhancedServiceProvider.php
     public function boot() {
         \Tpetry\PostgresqlEnhanced\Eloquent\Casts\PostgresType::macro('customType', function ($value) {
             return new CustomType($value);
         });
     }
     

2. Query Builder Macros:

   • Add PostgreSQL-specific macros to the query builder:

     DB::macro('whereJsonContains', function ($key, $value) {
         return $this->whereRaw("jsonb_typeof({$key}) = 'object' AND {$key} ? '{$value}'");
     });
     

3. Schema Blueprints:

   • Extend the Blueprint class for custom schema operations:

     class CustomBlueprint extends \Tpetry\PostgresqlEnhanced\Schema\Blueprint {
         public function customIndex($columns, $name = null) {
             $this->index($columns, $name)->using('gin');
         }
     }
     

   • Bind it in AppServiceProvider:

     Schema::extend(function ($schema, $connection) {
         return new CustomBlueprint($schema, $connection);
     });
     

Configuration Quirks

1. TimescaleDB:

   • Ensure the timescaledb extension is enabled in config/postgresql-enhanced.php:

     'extensions' => [
         'timescaledb' => true,
     ],
     

   • Requires the timescale/timescaledb PHP extension.

2. Case-Insensitive Text:

   • Use ->asPostgresType('citext') for case-insensitive strings:

     $table->string('slug')->asPostgresType('cite