⚡️ Connection

The hectororm/connection package provides a lightweight and highly flexible abstraction layer for managing PDO-based database connections in PHP. It enables clean handling of basic SQL execution, read/write DSN separation, transactions, and driver introspection, making it suitable for both simple scripts and advanced web applications.

This guide covers how to create and configure connections, run queries, manage transactions, use multiple connections, introspect driver capabilities, and enable query logging for development and debugging purposes. 🧰

Creating a connection

The Connection class allows you to establish a connection using a DSN (Data Source Name). You can optionally provide credentials, define a connection name, set up a read-only replica, or inject a logger instance.

Constructor parameters

Simple connection

use Hector\Connection\Connection;

$connection = new Connection(
    dsn: 'mysql:host=localhost;dbname=mydb',
    username: 'root',
    password: 'secret'
);

Using secrets in container environments

$dsn = 'mysql:host=db;dbname=app';
$username = trim(file_get_contents('/run/secrets/db_user'));
$password = trim(file_get_contents('/run/secrets/db_pass'));

$connection = new Connection(
    dsn: $dsn,
    username: $username,
    password: $password
);

Read/write separation

$connection = new Connection(
    dsn: 'mysql:host=master.db;dbname=mydb;user=write_user;password=secret',
    readDsn: 'mysql:host=replica.db;dbname=mydb;user=read_user;password=secret'
);

Read operations will use the read DSN until a write or transaction begins, after which the write DSN is used exclusively.

PDO options

options is passed as the $options argument of the PDO constructor. The same options apply to the read/write and read-only connections, and are preserved when the connection is serialized.

$connection = new Connection(
    dsn: 'mysql:host=localhost;dbname=mydb',
    username: 'root',
    password: 'secret',
    options: [
        PDO::ATTR_TIMEOUT => 5,
        // Driver-specific attributes are accepted as well (e.g. SSL options of pdo_mysql)
    ],
);

Executing queries

The Connection class provides simple and expressive methods for executing SQL queries and retrieving data.

Query methods

Usage example

$affected = $connection->execute(
    'UPDATE users SET name = ? WHERE id = ?',
    ['Alice', 1]
);

$user = $connection->fetchOne('SELECT * FROM users WHERE id = ?', [1]);

foreach ($connection->fetchAll('SELECT * FROM users') as $row) {
    // Process each row
}

foreach ($connection->fetchColumn('SELECT email FROM users') as $email) {
    // Process each email address
}

Last insert ID

$connection->execute('INSERT INTO posts (title) VALUES (?)', ['Hello']);
$id = $connection->getLastInsertId();

Transactions

Ensure atomic operations using transactions.

Usage example

$connection->beginTransaction();
try {
    $connection->execute('INSERT INTO users (name) VALUES (?)', ['Bob']);
    $connection->execute('INSERT INTO profiles (user_id) VALUES (?)', [$connection->getLastInsertId()]);
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollBack();
    throw $e;
}

Transaction API

Managing multiple connections

Use the ConnectionSet class to register and retrieve multiple named Connection instances.

ConnectionSet API

Usage example

use Hector\Connection\Connection;
use Hector\Connection\ConnectionSet;

$set = new ConnectionSet();
$set->addConnection(new Connection('mysql:host=localhost;dbname=app', name: 'default'));
$set->addConnection(new Connection('mysql:host=replica;dbname=app', name: 'replica'));

// Get default connection (name = 'default')
$main = $set->getConnection();

// Get named connection
$replica = $set->getConnection('replica');

// Check existence
$set->hasConnection('replica'); // true
$set->hasConnection('unknown'); // false

Query logging

To help debug and optimize queries, you can enable logging using the built-in Logger class. This logger collects detailed information for each executed SQL statement, including execution time and stack trace.

Logger API

Usage example

use Hector\Connection\Log\Logger;

$logger = new Logger();
$connection = new Connection('mysql:host=localhost;dbname=app', logger: $logger);
$connection->fetchOne('SELECT * FROM users WHERE id = ?', [1]);

foreach ($logger->getLogs() as $log) {
    echo $log->getStatement();
    print_r($log->getParameters());
    echo $log->getDuration() . "ms\n";
}

Driver information

The Connection class can return introspective metadata about the current PDO driver in use via getDriverInfo().

$driverInfo = $connection->getDriverInfo();

echo $driverInfo->getDriver();   // e.g. 'mysql'
echo $driverInfo->getVersion();  // e.g. '8.0.32'

$capabilities = $driverInfo->getCapabilities();
$capabilities->hasJson();        // true

DriverInfo API

DriverCapabilities API

Interface change: third-party DriverCapabilities implementations must implement both CHECK methods. Return false when support cannot be established.

MySQL 8.0.16+ returns true for both. MariaDB 10.2.1+, SQLite and PostgreSQL return true for CHECK support and false for DROP CHECK: MariaDB/PostgreSQL use DROP CONSTRAINT, while SQLite requires a rebuild. Older MySQL/MariaDB versions, Vitess and unknown engines return false for both. These capabilities describe engine support, not session overrides. PostgreSQL has native support but no Hector schema-plan compiler. See CHECK compilation for online/offline usage.

Since 1.5, the concrete Hector\Connection\Driver\SQLiteCapabilities class also exposes hasGeneratedColumns(): bool, which returns true for SQLite 3.31.0 or newer. This method is specific to SQLite capabilities and is not part of the common DriverCapabilities interface.

Installation

composer require hectororm/connection