Pomm Foundation Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require conserto/pomm-foundation
   

   Ensure ext-pgsql is enabled in your PHP configuration.

2. Basic Connection:

   use PommProject\Foundation\SessionBuilder;
   
   $session = (new SessionBuilder())
       ->withDsn('pgsql:host=localhost;dbname=your_db')
       ->build();
   

3. First Query:

   $queryManager = $session->getClientUsingPooler('query_manager');
   $result = $queryManager->getResult('SELECT * FROM users LIMIT 10');
   foreach ($result as $row) {
       print_r($row);
   }
   

Key First Use Case

Type-Safe Data Handling:

// Convert PostgreSQL types to PHP objects/enums
$session->getClientUsingPooler('converter_holder')->registerConverter(
    'your_enum_type',
    new PgBackedEnum('your_enum_class')
);

// Use in a query
$queryManager->getResult('SELECT id, status FROM orders WHERE status = ?', ['active']);

Implementation Patterns

Core Workflows

1. Session Management

• Builder Pattern: Use SessionBuilder for pre-configured clients (e.g., query_manager, prepared_query).

  $session = (new SessionBuilder())
      ->withDsn('pgsql:host=localhost;dbname=app')
      ->withOption('timezone', 'UTC')
      ->build();
  

• Connection Pooling: Reuse sessions for performance. Avoid recreating sessions per request in Laravel.

2. Query Execution

• Simple Queries:

  $queryManager = $session->getClientUsingPooler('query_manager');
  $result = $queryManager->getResult('SELECT * FROM products WHERE price > ?', [100]);
  

• Prepared Queries (for repeated use):

  $preparedQuery = $session->getClientUsingPooler('prepared_query', 'SELECT * FROM users WHERE id = ?');
  $result = $preparedQuery->execute([1]);
  

3. Type Conversion

• Built-in Converters: Handle PostgreSQL types automatically (e.g., jsonb, hstore, citext).

  $converterHolder = $session->getClientUsingPooler('converter_holder');
  $row = $converterHolder->convertRow($result->fetch());
  

• Custom Enums:

  enum UserRole { Case('admin'), User('user') }
  $converterHolder->registerConverter('user_role', new PgBackedEnum(UserRole::class));
  

4. Asynchronous Notifications

• LISTEN/NOTIFY:

  $listener = $session->getClientUsingPooler('listener', 'channel_name');
  $listener->listen(function ($payload) {
      // Handle notification
  });
  

5. Dependency Injection

• Laravel Service Provider:

  use Illuminate\Support\ServiceProvider;
  use PommProject\Foundation\SessionBuilder;
  
  class PommServiceProvider extends ServiceProvider {
      public function register() {
          $this->app->singleton('pomm.session', function () {
              return (new SessionBuilder())
                  ->withDsn(config('database.connections.pgsql.dsn'))
                  ->build();
          });
      }
  }
  

  Bind clients in boot():

  $this->app->bind('pomm.query_manager', function ($app) {
      return $app['pomm.session']->getClientUsingPooler('query_manager');
  });
  

Integration Tips

Laravel-Specific

1. Configuration: Add to config/database.php:

   'pgsql' => [
       'dsn' => env('DB_DSN', 'pgsql:host=localhost;dbname=laravel'),
       'timezone' => env('DB_TIMEZONE', 'UTC'),
   ],
   

2. Query Builder Wrapper: Extend Laravel’s query builder to use Pomm:

   use Illuminate\Database\Query\Builder;
   use PommProject\Foundation\QueryManager;
   
   class PommQueryBuilder extends Builder {
       protected $queryManager;
   
       public function __construct(QueryManager $queryManager) {
           $this->queryManager = $queryManager;
       }
   
       public function get() {
           $result = $this->queryManager->getResult($this->toSql(), $this->getBindings());
           return $result->fetchAll();
       }
   }
   

Performance

• Reuse Sessions: Avoid creating new sessions per request. Use Laravel’s singleton binding.
• Prepared Queries: Cache repeated queries with prepared_query pooler.
• Batch Processing: Use ResultIterator for large datasets:

  $iterator = $queryManager->getIterator('SELECT * FROM large_table');
  foreach ($iterator as $row) {
      // Process row
  }
  

Testing

• Atoum Integration: Extend FoundationSessionAtoum for test sessions:

  use PommProject\Foundation\Tester\FoundationSessionAtoum;
  
  class UserTest extends FoundationSessionAtoum {
      protected function initializeSession($session) {
          $session->getClientUsingPooler('converter_holder')->registerConverter(
              'user_role',
              new PommProject\Foundation\Converter\PgBackedEnum(UserRole::class)
          );
      }
  }
  

Gotchas and Tips

Pitfalls

1. Schema-Aware Composite Types:

   • Issue: ConvertedResultIterator fails to recognize custom composite types in non-public schemas (e.g., schema_name.type).
   • Workaround: Manually register converters for such types or inspect schemas via pg_type queries.

2. Timezone Mismatches:

   • Issue: PostgreSQL and PHP timezone discrepancies can cause timestamp conversion errors.
   • Fix: Explicitly set timezone in DSN or session:

     ->withOption('timezone', 'America/New_York')
     

3. Connection Leaks:

   • Issue: Unclosed sessions or clients may leak connections.
   • Fix: Always call $session->shutdown() in Laravel’s terminating event:

     Event::listen('terminating', function () {
         if ($session = app('pomm.session')) {
             $session->shutdown();
         }
     });
     

4. Enum Placeholders:

   • Issue: Untyped placeholders (?) in WHERE IN clauses may fail with enums.
   • Fix: Use typed placeholders:

     $where = Where::createWhereIn('status', ['active', 'pending'], UserRole::class);
     

5. Hstore Parsing:

   • Issue: Malformed hstore strings may throw exceptions.
   • Fix: Validate input or use PgHstore::fromPg() with error handling:

     try {
         $hstore = PgHstore::fromPg($rawHstoreString);
     } catch (InvalidArgumentException $e) {
         // Handle error
     }
     

Debugging Tips

1. Query Logging: Enable debug logging via PSR-3 logger:

   $session->getClientUsingPooler('logger')->setLogger(new Monolog\Logger('pomm', [...]));
   

2. Result Inspection: Use ResultInspector to debug raw PostgreSQL results:

   $inspector = $session->getClientUsingPooler('inspector');
   $inspector->inspect($result);
   

3. Converter Debugging: Check registered converters:

   $converterHolder = $session->getClientUsingPooler('converter_holder');
   print_r($converterHolder->getConverterNames());
   

Extension Points

1. Custom Converters: Extend PommProject\Foundation\Converter\ConverterInterface:

   class CustomTypeConverter implements ConverterInterface {
       public function convertToPg($value) { /* ... */ }
       public function convertFromPg($value) { /* ... */ }
   }
   

   Register with:

   $converterHolder->registerConverter('custom_type', new CustomTypeConverter());
   

2. Query Managers: Extend PommProject\Foundation\QueryManager\QueryManager to add custom methods:

   class ExtendedQueryManager extends QueryManager {
       public function customQuery($sql, $params) {
           return $this->getResult($sql, $params);
       }
   }
   

   Register with a custom pooler.

3. Session Middleware: Use Session\SessionMiddleware to intercept session operations:

   $session->addMiddleware(new class implements SessionMiddleware {
       public function beforeExecute(Session $session, string $operation) { /* ... */ }
       public function afterExecute