PHP code example of senza1dio / database-pool

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

senza1dio / database-pool example snippets

Senza1dio\DatabasePool\Config\PoolConfig;
use Senza1dio\DatabasePool\DatabasePool;

// Configure pool once at application boot
$config = (new PoolConfig())
    ->setHost('localhost')
    ->setPort(5432)
    ->setDatabase('myapp')
    ->setCredentials('postgres', 'secret')
    ->setPoolSize(5, 50);

$pool = new DatabasePool($config);

// Handle requests (pool persists across requests)
while ($request = $server->acceptRequest()) {
    $pdo = $pool->getConnection();
    $stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
    $stmt->execute([123]);
    $user = $stmt->fetch();

    // Connection automatically released and returned to pool
}

Senza1dio\DatabasePool\Config\PoolConfig;
use Senza1dio\DatabasePool\DatabasePool;

$config = (new PoolConfig())
    ->setHost('localhost')
    ->setPort(5432)
    ->setDatabase('myapp')
    ->setCredentials('postgres', 'secret')
    ->setPoolSize(5, 50);

$pool = new DatabasePool($config);

// Process queue messages (long-running daemon)
while (true) {
    $message = $queue->pop();

    $pdo = $pool->getConnection();
    $pdo->exec("INSERT INTO processed VALUES (...)");

    // Connection auto-released, reused on next iteration
}

// config/database-pool.php
return [
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', 5432),
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'postgres'),
    'password' => env('DB_PASSWORD', ''),
    'minConnections' => 10,
    'maxConnections' => 100,
];

// app/Providers/DatabasePoolServiceProvider.php
use Senza1dio\DatabasePool\Config\PoolConfig;
use Senza1dio\DatabasePool\DatabasePool;
use Senza1dio\DatabasePool\Adapters\Locks\RedisLock;
use Senza1dio\DatabasePool\Adapters\Loggers\Psr3LoggerAdapter;

$this->app->singleton(DatabasePool::class, function ($app) {
    $config = PoolConfig::fromArray(config('database-pool'))
        ->setLock(new RedisLock(Redis::connection()->client()))
        ->setLogger(new Psr3LoggerAdapter(Log::channel('database')));

    return new DatabasePool($config);
});

// Usage in controllers
public function __construct(DatabasePool $pool) {
    $this->pool = $pool;
}

public function show($id) {
    $pdo = $this->pool->getConnection();
    // ... use PDO
}

// config/services.yaml
services:
    Senza1dio\DatabasePool\DatabasePool:
        arguments:
            $config: '@database_pool.config'

    database_pool.config:
        class: Senza1dio\DatabasePool\Config\PoolConfig
        factory: ['Senza1dio\DatabasePool\Config\PoolConfig', 'fromArray']
        arguments:
            - host: '%env(DATABASE_HOST)%'
              port: '%env(int:DATABASE_PORT)%'
              database: '%env(DATABASE_NAME)%'
              username: '%env(DATABASE_USER)%'
              password: '%env(DATABASE_PASSWORD)%'
              minConnections: 10
              maxConnections: 100

// Usage in controllers
public function index(DatabasePool $pool) {
    $pdo = $pool->getConnection();
    // ... use PDO
}

use Senza1dio\DatabasePool\Config\PoolConfig;
use Senza1dio\DatabasePool\DatabasePoolSingleton;

$config = (new PoolConfig())
    ->setHost('localhost')
    ->setPort(5432)
    ->setDatabase('myapp')
    ->setCredentials('postgres', 'secret')
    ->setPoolSize(5, 50)
    ->enablePersistentConnections(true);

// Get singleton instance (persists across requests in same worker)
$pool = DatabasePoolSingleton::getInstance($config);
$pdo = $pool->getConnection();

// ❌ WRONG - Creates new config object each time (will throw LogicException)
$pool = DatabasePoolSingleton::getInstance(PoolConfig::fromArray($config));

// ✅ CORRECT - Reuse same config instance
static $poolConfig = null;
if ($poolConfig === null) {
    $poolConfig = PoolConfig::fromArray($config);
}
$pool = DatabasePoolSingleton::getInstance($poolConfig);

$config = (new PoolConfig())
    // Database connection
    ->setHost('localhost')
    ->setPort(5432)
    ->setDatabase('myapp')
    ->setCredentials('user', 'pass')
    ->setCharset('utf8')

    // Pool sizing
    ->setPoolSize(10, 100) // Min 10, Max 100

    // SSL/TLS (recommended for production)
    ->enableSsl(
        verify: true,
           // Performance tuning
    ->setSlowQueryThreshold(2.0) // Log queries >2s
    ->setIdleTimeout(1500)       // Close idle connections after 25min
    ->enableAutoScaling(true)    // Auto-scale based on load

    // Dependencies (optional)
    ->setLogger($monolog)
    ->setLock($redisLock)

    // Metadata (for monitoring)
    ->setApplicationName('myapp')
    ->setTimezone('Europe/Rome');

$config = PoolConfig::fromArray([
    'driver' => 'pgsql',
    'host' => 'localhost',
    'port' => 5432,
    'database' => 'myapp',
    'username' => 'postgres',
    'password' => 'secret',
    'min_connections' => 10,
    'max_connections' => 100,
    'ssl' => true,
    'ssl_verify' => true,
    'ssl_ca' => '/path/to/ca.pem',
]);

$config = PoolConfig::fromDsn(
    'pgsql:host=localhost;port=5432;dbname=myapp',
    'postgres',
    'secret',
    ['maxConnections' => 100]
);

$pool = new DatabasePool($config);
$pdo = $pool->getConnection();

// Type-aware binding (no manual PDO::PARAM_* needed)
$stmt = $pool->executeQuery($pdo, '
    INSERT INTO users (is_active, age, name, bio)
    VALUES (:is_active, :age, :name, :bio)
', [
    'is_active' => true,    // Auto-detected as PARAM_BOOL
    'age' => 25,            // Auto-detected as PARAM_INT
    'name' => 'John',       // Auto-detected as PARAM_STR
    'bio' => null,          // Auto-detected as PARAM_NULL
]);

// Setup Redis cache
$redis = new \Redis();
$redis->connect('127.0.0.1', 6379);
$pool->setQueryCache($redis);

// Execute cached query
$stmt = $pool->executeQuery($pdo, 'SELECT * FROM products WHERE category_id = ?', [123], [
    'cache' => true,        // Enable caching
    'cache_ttl' => 300,     // Cache for 5 minutes
]);

// Second request = instant (Redis cache hit)

$pool = new DatabasePool($config);

// Warmup 10 connections (recommended after deployment)
$created = $pool->warmup(10);

// First user request = instant (connections already created)

// New Relic integration
$pool->addMonitoringHook(function($metrics) {
    if (extension_loaded('newrelic')) {
        newrelic_custom_metric('Database/QueryTime', $metrics['duration_ms']);
        newrelic_custom_metric('Database/SlowQueries', $metrics['slow_queries']);
    }
});

// Datadog integration
$pool->addMonitoringHook(function($metrics) use ($statsd) {
    $statsd->timing('database.query.duration', $metrics['duration_ms']);
    $statsd->increment('database.queries.total');
    $statsd->gauge('database.pool.active', $metrics['active_connections']);
});

// Custom metrics
$pool->addMonitoringHook(function($metrics) {
    file_put_contents('/var/log/db-metrics.log', json_encode($metrics) . PHP_EOL, FILE_APPEND);
});

// Configured automatically (3 retries with exponential backoff)
// Retry 1: 100ms delay
// Retry 2: 200ms delay
// Retry 3: 400ms delay

$pool = new DatabasePool($config);
$pdo = $pool->getConnection(); // Auto-retries on failure

// Check metrics
$metrics = $pool->getMetrics();
echo "Connection retries: {$metrics['connection_retries']}";

$pdo = $pool->getConnection();

$pdo->beginTransaction();
$pdo->exec('UPDATE accounts SET balance = balance - 100 WHERE id = 1');

// Developer forgets to commit/rollback
unset($pdo); // Connection released

// AUTOMATIC ROLLBACK HAPPENS HERE
// No data corruption! Transaction rolled back safely.

$metrics = $pool->getMetrics();
echo "Auto-rollbacks: {$metrics['transaction_rollbacks']}";

$config = (new PoolConfig())
    ->enableSsl(
        verify: true,      // Verify server certificate
        /client.key'
    );

$config = (new PoolConfig())
    ->setQueryLimits(
        maxSize: 1048576,   // 1MB max query (prevents memory exhaustion)
        maxParams: 1000     // Max 1000 params (prevents parameter bombing)
    );

try {
    $pdo = $pool->getConnection();
} catch (CircuitBreakerOpenException $e) {
    // Circuit breaker is open
    // Retry after $e->getTimeout() seconds
    sleep(20);
    $pdo = $pool->getConnection();
}

use Senza1dio\DatabasePool\Adapters\Locks\RedisLock;

$redis = new Redis();
$redis->connect('localhost', 6379);

$config = (new PoolConfig())
    // ... other config
    ->setLock(new RedisLock($redis, 'myapp:pool:'));

$pool = new DatabasePool($config);

use Senza1dio\DatabasePool\Adapters\Loggers\Psr3LoggerAdapter;
use Monolog\Logger;
use Monolog\Handler\StreamHandler;

$logger = new Logger('database');
$logger->pushHandler(new StreamHandler('php://stdout'));

$config = (new PoolConfig())
    // ... other config
    ->setLogger(new Psr3LoggerAdapter($logger));

$pool = new DatabasePool($config);
// Slow queries, errors, circuit breaker events logged automatically

$stats = $pool->getStats();

/*
[
    'total_connections' => 50,
    'active_connections' => 20,
    'idle_connections' => 30,
    'total_queries' => 10000,
    'slow_queries' => 5,
    'circuit_breaker_state' => 'closed',
    'pool_hits' => 9950,      // Reused connections
    'pool_misses' => 50,      // New connections created
]
*/