Connection class — wrapping PDO with a fluent API

Intermediate5 min read·fw-06-002

sql

Concept

A Connection class wraps PDO to provide a fluent, convenient API that hides PDO's verbose method calls and adds framework-specific functionality like query logging, event firing, and reconnection handling.

What a Connection class adds over raw PDO:

Fluent query execution: $db->select('SELECT * FROM users WHERE id = ?', [1]) instead of multi-step prepare/execute/fetchAll.
Query logging: Records every query and its bindings + execution time.
Reconnection: Detects "gone away" errors and reconnects automatically.
Events: Fires events before/after queries.
Transaction management: Tracks nesting depth, manages savepoints.
Grammar abstraction: Different SQL dialects per database.

Key methods:

select(string $sql, array $bindings = []): array: Run a SELECT, return all rows.
selectOne(string $sql, array $bindings = []): ?array: Return the first row or null.
insert(string $sql, array $bindings = []): bool.
update(string $sql, array $bindings = []): int: Returns affected rows.
delete(string $sql, array $bindings = []): int: Returns affected rows.
statement(string $sql, array $bindings = []): bool: For DDL.
getPdo(): PDO: Access the raw PDO.

Reconnection strategy: Catch PDOException with "server has gone away" message. Re-establish the connection and retry the query once.

Code Example

php

<?php
namespace Framework\Database;

class Connection
{
    private ?\PDO $pdo = null;
    private array $queryLog = [];
    private bool $loggingEnabled = false;

    public function __construct(
        private readonly string $dsn,
        private readonly string $username,
        private readonly string $password,
        private readonly array  $options = [],
    ) {}

    public function getPdo(): \PDO
    {
        if ($this->pdo === null) {
            $this->pdo = $this->createPdo();
        }
        return $this->pdo;
    }

    public function select(string $sql, array $bindings = []): array
    {
        return $this->run($sql, $bindings, fn($stmt) => $stmt->fetchAll());
    }

    public function selectOne(string $sql, array $bindings = []): ?array
    {
        $result = $this->select($sql, $bindings);
        return $result[0] ?? null;
    }

    public function insert(string $sql, array $bindings = []): bool
    {
        return $this->run($sql, $bindings, fn($stmt) => $stmt->rowCount() > 0);
    }

    public function update(string $sql, array $bindings = []): int
    {
        return $this->run($sql, $bindings, fn($stmt) => $stmt->rowCount());
    }

    public function delete(string $sql, array $bindings = []): int
    {
        return $this->run($sql, $bindings, fn($stmt) => $stmt->rowCount());
    }

    public function statement(string $sql, array $bindings = []): bool
    {
        return $this->run($sql, $bindings, fn($stmt) => true);
    }

    public function lastInsertId(): string|false
    {
        return $this->getPdo()->lastInsertId();
    }

    private function run(string $sql, array $bindings, callable $callback): mixed
    {
        $start = microtime(true);
        $stmt  = $this->getPdo()->prepare($sql);
        $stmt->execute($bindings);
        $result = $callback($stmt);

        if ($this->loggingEnabled) {
            $this->queryLog[] = [
                'sql'      => $sql,
                'bindings' => $bindings,
                'time'     => round((microtime(true) - $start) * 1000, 2),
            ];
        }

        return $result;
    }

    private function createPdo(): \PDO
    {
        return new \PDO($this->dsn, $this->username, $this->password, array_merge([
            \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES   => false,
        ], $this->options));
    }

    public function enableQueryLog(): void { $this->loggingEnabled = true; }
    public function getQueryLog(): array   { return $this->queryLog; }
    public function flushQueryLog(): void  { $this->queryLog = []; }
}

PreviousPDO — PHP Data Objects, DSN strings, connection optionsDatabase Connection & PDO Wrappers NextPrepared statements and parameter binding — preventing SQL injectionDatabase Connection & PDO Wrappers