⚡️ 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('mysql:host=localhost;dbname=mydb;user=root;password=secret');

You may also pass credentials explicitly as constructor arguments:

$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.

🔎 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

⚠️ Note: Nested calls to beginTransaction() are ignored. Each transaction must be matched with a commit() or rollBack().

🧩 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: 'main'));
$set->addConnection(new Connection('mysql:host=replica;dbname=app', name: 'replica'));

$main = $set->getConnection();
$replica = $set->getConnection('replica');

🪵 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.

⚠️ Logging should be disabled in production environments to avoid performance penalties and potential data exposure.

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.

DriverInfo API

DriverCapabilities API