Database Connection

PDO connection management with connection pool, read/write splitting and health checks.

Introduction

Database connections in PHP seem simple: new PDO(...) and you're off. In practice, a production application needs read/write splitting across replicas, health checks with failover, connection pooling and consistent PDO options. Wiring all of that manually means boilerplate code in every project.

jardisadapter/dbconnection provides a connection factory and a connection pool that encapsulates all of this:

Read/Write splitting — queries automatically routed to reader replicas, mutations to the writer. Transparent to the caller
Round-robin and random load balancing — configurable per pool, with automatic failover
Health checks with TTL caching — SELECT 1 with configurable cache duration. Healthy connections are cached for 30s, failed ones re-checked immediately
Reconnect support — automatic reconnect on health check failures
External PDO wrapping — integrate existing PDO instances from legacy code or frameworks
SQLite auto-optimizations — WAL mode, foreign keys, memory temp store applied automatically on every connect
MySQL, PostgreSQL, SQLite — three databases, one API

Installation

bash

composer require jardisadapter/dbconnection

GitHub: jardisAdapter/dbConnection

Basic Usage

Creating Connections

php

use JardisAdapter\DbConnection\Factory\ConnectionFactory;

$factory = new ConnectionFactory();

// MySQL
$mysql = $factory->mysql(
    host: 'localhost',
    user: 'app_user',
    password: 'secret',
    database: 'myapp',
    port: 3306,
    charset: 'utf8mb4',
);

// PostgreSQL
$postgres = $factory->postgres(
    host: 'localhost',
    user: 'app_user',
    password: 'secret',
    database: 'myapp',
    port: 5432,
);

// SQLite
$sqlite = $factory->sqlite('/var/data/app.db');
$inMemory = $factory->sqlite();  // :memory:

// Using PDO
$pdo = $mysql->pdo();
$users = $pdo->query('SELECT * FROM users')->fetchAll();

Wrapping Existing PDO

php

// Wrap legacy PDO (lifecycle remains with the caller)
$connection = $factory->fromPdo($existingPdo);

// Transfer lifecycle to Jardis
$managed = $factory->fromPdo($existingPdo, manageLifecycle: true);
$managed->disconnect();  // PDO is released

Connection Pool

Read/Write Splitting

php

use JardisAdapter\DbConnection\ConnectionPool;

$pool = new ConnectionPool(
    writer: $factory->mysql('primary.db', 'user', 'secret', 'myapp'),
    readers: [
        $factory->mysql('replica1.db', 'user', 'secret', 'myapp'),
        $factory->mysql('replica2.db', 'user', 'secret', 'myapp'),
    ],
);

// Mutations → Writer
$pool->getWriter()->pdo()->exec("INSERT INTO orders (total) VALUES (99.99)");

// Queries → Reader (round-robin)
$orders = $pool->getReader()->pdo()->query("SELECT * FROM orders")->fetchAll();

Pool without Replicas

When no readers are configured, the writer is used for all operations:

php

$pool = new ConnectionPool(writer: $factory->mysql(...));

$writer = $pool->getWriter();
$reader = $pool->getReader();
// $writer === $reader

Pool Configuration

php

use JardisAdapter\DbConnection\Config\ConnectionPoolConfig;

$config = new ConnectionPoolConfig(
    validateConnections: true,             // Enable health checks
    healthCheckCacheTtl: 60,               // Cache healthy connections for 60s
    healthCheckNegativeCacheTtl: 0,         // Re-check failed connections immediately
    loadBalancingStrategy: 'round-robin',  // or 'random'
);

$pool = new ConnectionPool(
    writer: $writerConnection,
    readers: $readerConnections,
    config: $config,
);

Failover

When a reader fails the health check, the next one is tried. Only when all readers are down is a RuntimeException thrown:

php

// Reader 1 is down → automatically Reader 2
$reader = $pool->getReader();

// Statistics
$stats = $pool->getStats();
// ['reads' => 5, 'writes' => 2, 'failovers' => 1, 'readers' => 2]

$pool->resetStats();

Health Check Mechanism

isHealthy($connection):
  1. Check cache → return cached result within TTL
  2. Execute SELECT 1
     → On error: try reconnect(), SELECT 1 again
     → If reconnect fails: false
  3. Cache result (positive with healthCheckCacheTtl, negative with healthCheckNegativeCacheTtl)

With the default configuration (healthCheckNegativeCacheTtl = 0), a failed connection is re-checked on every call: no penalty window.

Transactions

php

$connection = $factory->mysql('localhost', 'user', 'secret', 'myapp');

$connection->beginTransaction();
try {
    $connection->pdo()->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $connection->pdo()->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollback();
    throw $e;
}

$connection->inTransaction();  // false

PDO Options

PDO options are passed as an array and merged with secure defaults:

php

// Default options (always active):
// PDO::ATTR_ERRMODE            → PDO::ERRMODE_EXCEPTION
// PDO::ATTR_DEFAULT_FETCH_MODE → PDO::FETCH_ASSOC
// PDO::ATTR_EMULATE_PREPARES   → false

// Add or override your own options:
$connection = $factory->mysql(
    host: 'localhost',
    user: 'app_user',
    password: 'secret',
    database: 'myapp',
    options: [
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_TIMEOUT    => 5,
    ],
);

SQLite Auto-Optimizations

SQLite connections automatically receive these PRAGMAs on every connect() and reconnect():

sql

PRAGMA foreign_keys = ON
PRAGMA journal_mode = WAL
PRAGMA synchronous  = NORMAL
PRAGMA temp_store   = MEMORY
PRAGMA mmap_size    = 30000000000

Additional methods:

php

$sqlite = $factory->sqlite('/var/data/app.db');
$sqlite->getDatabasePath();  // '/var/data/app.db'
$sqlite->vacuum();           // Execute VACUUM

Reconnect

Every connection type supports reconnect():

Type	Behavior
MySQL/PostgreSQL	PDO is destroyed and rebuilt
SQLite	PDO is rebuilt, PRAGMAs applied again
External	`SELECT 1` health check (cannot recreate PDO)

php

$connection->reconnect();
$connection->isConnected();  // true

Architecture

The package follows a Factory + Config + Pool approach:

ConnectionFactory                      ← Factory
├── MySqlConfig → PdoConnection        ← MySQL
├── PostgresConfig → PdoConnection     ← PostgreSQL
├── SqliteConfig → SqLite              ← SQLite (with PRAGMAs)
└── ExternalConfig → External          ← Existing PDO

ConnectionPool                         ← Orchestrator (Read/Write routing)
├── Writer (DbConnectionInterface)
├── Reader[] (DbConnectionInterface)
└── ConnectionPoolConfig               ← Health check + load balancing

Directory Structure

src/
├── ConnectionPool.php              ← Pool with read/write splitting
├── Config/
│   ├── ConnectionPoolConfig.php    ← Pool configuration
│   ├── MySqlConfig.php             ← MySQL DSN builder
│   ├── PostgresConfig.php          ← PostgreSQL DSN builder
│   ├── SqliteConfig.php            ← SQLite config
│   └── ExternalConfig.php          ← External PDO config
├── Connection/
│   ├── PdoConnection.php           ← Base (MySQL/PostgreSQL)
│   ├── SqLite.php                  ← SQLite with auto-PRAGMAs
│   └── External.php                ← External PDO wrapper
└── Factory/
    └── ConnectionFactory.php       ← Factory

API Reference

ConnectionFactory

Method	Signature	Description
`mysql`	`mysql(string $host, string $user, string $password, string $database, int $port = 3306, string $charset = 'utf8mb4', array $options = []): DbConnectionInterface`	MySQL connection
`postgres`	`postgres(string $host, string $user, string $password, string $database, int $port = 5432, array $options = []): DbConnectionInterface`	PostgreSQL connection
`sqlite`	`sqlite(string $path = ':memory:', array $options = []): DbConnectionInterface`	SQLite connection
`fromPdo`	`fromPdo(PDO $pdo, bool $manageLifecycle = false): DbConnectionInterface`	Wrap existing PDO

DbConnectionInterface

Method	Signature	Description
`connect`	`connect(): void`	Establish connection
`pdo`	`pdo(): PDO`	Retrieve PDO instance
`isConnected`	`isConnected(): bool`	Connection status
`disconnect`	`disconnect(): void`	Close connection
`reconnect`	`reconnect(): void`	Rebuild connection
`beginTransaction`	`beginTransaction(): void`	Start transaction
`commit`	`commit(): void`	Commit transaction
`rollback`	`rollback(): void`	Roll back transaction
`inTransaction`	`inTransaction(): bool`	Inside transaction?
`getDriverName`	`getDriverName(): string`	`mysql`, `pgsql`, `sqlite`
`getDatabaseName`	`getDatabaseName(): string`	Database name
`getServerVersion`	`getServerVersion(): string`	Server version

ConnectionPool

Method	Signature	Description
`getWriter`	`getWriter(): DbConnectionInterface`	Writer connection
`getReader`	`getReader(): DbConnectionInterface`	Reader (round-robin/random)
`getReaders`	`getReaders(): array`	All readers
`getReaderCount`	`getReaderCount(): int`	Number of readers
`getStats`	`getStats(): array`	Statistics
`resetStats`	`resetStats(): void`	Reset statistics

Complete Example

Production setup with read/write splitting, health checks and failover:

php

use JardisAdapter\DbConnection\ConnectionPool;
use JardisAdapter\DbConnection\Config\ConnectionPoolConfig;
use JardisAdapter\DbConnection\Factory\ConnectionFactory;

$factory = new ConnectionFactory();

// Writer + 2 replicas
$pool = new ConnectionPool(
    writer: $factory->mysql('primary.db.internal', 'app', $password, 'orders'),
    readers: [
        $factory->mysql('replica1.db.internal', 'app_ro', $password, 'orders'),
        $factory->mysql('replica2.db.internal', 'app_ro', $password, 'orders'),
    ],
    config: new ConnectionPoolConfig(
        validateConnections: true,
        healthCheckCacheTtl: 30,
        loadBalancingStrategy: 'round-robin',
    ),
);

// Write → Primary
$pool->getWriter()->beginTransaction();
try {
    $pdo = $pool->getWriter()->pdo();
    $stmt = $pdo->prepare("INSERT INTO orders (customer_id, total) VALUES (?, ?)");
    $stmt->execute([42, 299.99]);
    $orderId = $pdo->lastInsertId();
    $pool->getWriter()->commit();
} catch (\Throwable $e) {
    $pool->getWriter()->rollback();
    throw $e;
}

// Read → Replica (automatic failover on outage)
$orders = $pool->getReader()->pdo()
    ->query("SELECT * FROM orders WHERE customer_id = 42")
    ->fetchAll();

// Monitoring
$stats = $pool->getStats();
// ['reads' => 1, 'writes' => 1, 'failovers' => 0, 'readers' => 2]

Core

Adapter

Support

Tools

DevOps

Database Connection

Introduction

Installation

Basic Usage

Creating Connections

Wrapping Existing PDO

Connection Pool

Read/Write Splitting

Pool without Replicas

Pool Configuration

Failover

Health Check Mechanism

Transactions

PDO Options

SQLite Auto-Optimizations

Reconnect

Architecture

Directory Structure

API Reference

ConnectionFactory

DbConnectionInterface

ConnectionPool

Complete Example

Database Connection ​

Introduction ​

Installation ​

Basic Usage ​

Creating Connections ​

Wrapping Existing PDO ​

Connection Pool ​

Read/Write Splitting ​

Pool without Replicas ​

Pool Configuration ​

Failover ​

Health Check Mechanism ​

Transactions ​

PDO Options ​

SQLite Auto-Optimizations ​

Reconnect ​

Architecture ​

Directory Structure ​

API Reference ​

ConnectionFactory ​

DbConnectionInterface ​

ConnectionPool ​

Complete Example ​

Database Connection

Introduction

Installation

Basic Usage

Creating Connections

Wrapping Existing PDO

Connection Pool

Read/Write Splitting

Pool without Replicas

Pool Configuration

Failover

Health Check Mechanism

Transactions

PDO Options

SQLite Auto-Optimizations

Reconnect

Architecture

Directory Structure

API Reference

ConnectionFactory

DbConnectionInterface

ConnectionPool

Complete Example