Getting Started

Minimal Setup

Install via Composer:

composer require pomm-project/foundation

Configure PostgreSQL Connection: Create a config/pomm.php file (or extend Laravel’s config/database.php):

'pomm' => [
    'connections' => [
        'default' => [
            'dsn' => 'pgsql:host=localhost;dbname=your_db',
            'user' => 'your_user',
            'password' => 'your_password',
            'timezone' => 'UTC', // Critical for time consistency
        ],
    ],
],

First Query:

use PommProject\Foundation\ConnectionManager;
use PommProject\Foundation\QueryManager;

$connManager = new ConnectionManager('default');
$session = $connManager->getSession();
$queryManager = new QueryManager($session);

// Execute a simple query
$result = $queryManager->query('SELECT * FROM users WHERE id = $1', [1]);
foreach ($result as $row) {
    print_r($row);
}

First Use Case: Raw SQL with Parameter Binding

Replace Laravel’s DB::select() with Foundation’s QueryManager for PostgreSQL-specific queries:

$queryManager->query(
    'SELECT id, name, data->>\'key\' as custom_data
     FROM users
     WHERE created_at > $1
     ORDER BY id LIMIT $2',
    [new \DateTime('2023-01-01'), 10]
);

Implementation Patterns

Core Workflows

1. Connection Management

Pooling: Foundation handles connection pooling natively. Configure in ConnectionManager:

$connManager = new ConnectionManager('default', [
    'pool' => [
        'min_connections' => 5,
        'max_connections' => 20,
        'lifetime' => 3600, // 1 hour
    ],
]);

Laravel Integration: Bind to Laravel’s service container in a provider:

$this->app->singleton('pomm.conn', function ($app) {
    return new ConnectionManager($app['config']['pomm.connections.default']);
});

2. Query Execution

Prepared Statements: Foundation auto-prepares queries for security and performance:

$queryManager->query('INSERT INTO logs (message, level) VALUES ($1, $2)', [
    'User logged in',
    'info'
]);

Result Handling: Use iterators for large datasets:

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

3. Asynchronous Notifications

LISTEN/NOTIFY: Subscribe to PostgreSQL channels:

$session->listen('user_events');
$session->onNotify(function ($payload, $channel) {
    Log::info("Received $channel: $payload");
});

4. Transaction Management

Explicit Transactions: Foundation supports BEGIN, COMMIT, and ROLLBACK:

$session->beginTransaction();
try {
    $queryManager->query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
    $queryManager->query('UPDATE accounts SET balance = balance + $1 WHERE id = $3', [100, 2]);
    $session->commit();
} catch (\Exception $e) {
    $session->rollback();
    throw $e;
}

Integration Tips

Laravel Service Provider

Create app/Providers/PommServiceProvider.php:

public function register()
{
    $this->app->singleton('pomm', function ($app) {
        $config = $app['config']['pomm.connections.default'];
        return new ConnectionManager($config['dsn'], $config['user'], $config['password']);
    });

    $this->app->bind('pomm.query', function ($app) {
        return new QueryManager($app['pomm']->getSession());
    });
}

Facade for Convenience

Create app/Facades/Pomm.php:

namespace App\Facades;

use Illuminate\Support\Facades\Facade;

class Pomm extends Facade
{
    protected static function getFacadeAccessor()
    {
        return 'pomm.query';
    }
}

Usage:

Pomm::query('SELECT * FROM users')->fetchAll();

Query Builder Adapter (Advanced)

Extend Laravel’s Builder to use Foundation:

namespace App\Database;

use PommProject\Foundation\QueryManager;
use Illuminate\Database\Query\Builder as LaravelBuilder;

class PommBuilder extends LaravelBuilder
{
    protected $queryManager;

    public function __construct(QueryManager $queryManager)
    {
        $this->queryManager = $queryManager;
    }

    public function run()
    {
        $sql = $this->toSql();
        $bindings = $this->getBindings();
        return $this->queryManager->query($sql, $bindings);
    }
}

Gotchas and Tips

Pitfalls

Timezone Mismatches:
- Issue: PostgreSQL and PHP timezones must match to avoid timestamp conversion errors.
- Fix: Explicitly set both in config:
```
'timezone' => 'America/New_York', // PHP
```
  And in PostgreSQL:
```
SET TIME ZONE 'America/New_York';
```

Composite Types in Non-Public Schemas:

Issue: Foundation cannot auto-detect composite types in schemas other than public.

Fix: Manually register converters:

$session->getConverterManager()->registerConverter(
    'my_schema.my_composite_type',
    new \PommProject\Foundation\Converter\CompositeTypeConverter(
        $session,
        'my_schema.my_composite_type'
    )
);

Transaction Isolation:
- Issue: Foundation uses PostgreSQL’s default transaction isolation (read committed). For serializable transactions, explicitly set:
```
$session->beginTransaction('SERIALIZABLE');
```

Memory Leaks with Iterators:

Issue: Large result sets can consume memory if not handled properly.

Fix: Always use iterators (foreach) instead of fetchAll():

// Bad: Loads all rows into memory
$rows = $queryManager->query('SELECT * FROM huge_table')->fetchAll();

// Good: Processes row-by-row
foreach ($queryManager->query('SELECT * FROM huge_table') as $row) {
    // Process
}

LISTEN/NOTIFY Persistence:
- Issue: Notifications are lost if the connection drops.
- Fix: Implement a retry mechanism or use a persistent connection pool.

Debugging Tips

Enable Query Logging:
```
$session->getLogger()->setLevel(\Psr\Log\LogLevel::DEBUG);
```
Logs will show executed queries and parameters.

Check Connection Health:

if (!$session->isActive()) {
    $session->reconnect();
}

Handle PostgreSQL Errors: Foundation throws \PommProject\Foundation\Exception\RuntimeException. Catch and translate to Laravel’s QueryException:

try {
    $queryManager->query('INVALID SQL');
} catch (\PommProject\Foundation\Exception\RuntimeException $e) {
    throw new \Illuminate\Database\QueryException(
        $e->getMessage(),
        $e->getCode(),
        $e
    );
}

Extension Points

Custom Converters: Extend \PommProject\Foundation\Converter\AbstractConverter for unsupported types (e.g., custom JSON structures):

class CustomJsonConverter extends AbstractConverter
{
    public function convertToPHP($value, $type)
    {
        return json_decode($value, true);
    }

    public function convertToPostgreSQL($value, $type)
    {
        return json_encode($value);
    }
}

$session->getConverterManager()->registerConverter('jsonb', new CustomJsonConverter());

Query Hooks: Subclass QueryManager to intercept queries:

class LoggingQueryManager extends QueryManager
{
    public function query($sql, $params = [])
    {
        Log::debug("Executing: $sql", ['params' => $params]);
        return parent::query($sql, $params);
    }
}

Session Events: Listen for session events (e.g., connection drops):

$session->onConnect(function () {
    Log::info('Connected to PostgreSQL');
});
$session->onDisconnect(function () {
    Log::warning('Disconnected from

Foundation Laravel Package