Connection

A thin PDO wrapper with convenience methods, transactions, and savepoint support.

Table of contents

• Creating a Connection
  • From DSN parameters
  • From an existing PDO instance
• Running Raw Queries
  • executeQuery — SELECT queries
  • executeStatement — INSERT/UPDATE/DELETE
  • Fetch helpers
• Transactions
  • Manual transaction management
  • Automatic transactions (recommended)
• Nested Transactions (Savepoints)
  • Checking nesting level
• Query Builder
• Accessing the underlying PDO

---

Creating a Connection

From DSN parameters

use AsceticSoft\Rowcast\Connection;

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

From an existing PDO instance

$pdo = new \PDO('sqlite::memory:');
$connection = new Connection($pdo);

Connection automatically sets PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION to ensure all database errors throw exceptions.

---

Running Raw Queries

executeQuery — SELECT queries

Returns a PDOStatement for reading results:

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

executeStatement — INSERT/UPDATE/DELETE

Returns the number of affected rows:

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

Fetch helpers

// Fetch all rows as associative arrays
$rows = $connection->fetchAllAssociative('SELECT * FROM users');

// Fetch a single row
$row = $connection->fetchAssociative('SELECT * FROM users WHERE id = ?', [1]);

// Fetch a single scalar value
$count = $connection->fetchOne('SELECT COUNT(*) FROM users');

---

Transactions

Manual transaction management

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

Automatic transactions (recommended)

$connection->transactional(function (Connection $conn) {
    $conn->executeStatement('INSERT INTO users (name) VALUES (?)', ['Alice']);
    $conn->executeStatement('INSERT INTO users (name) VALUES (?)', ['Bob']);
});

The transactional() method automatically commits on success and rolls back on exception.

---

Nested Transactions (Savepoints)

By default, calling beginTransaction() inside an active transaction will fail. Enable savepoint-based nesting:

// Via factory
$connection = Connection::create(
    'mysql:host=localhost;dbname=app', 'root', 'secret',
    nestTransactions: true,
);

// Via constructor
$connection = new Connection($pdo, nestTransactions: true);

When enabled, inner beginTransaction() calls create SQL SAVEPOINTs, and commit() / rollBack() release or roll back to the corresponding savepoint:

$connection->transactional(function (Connection $conn) {
    $conn->executeStatement('INSERT INTO users (name) VALUES (?)', ['Alice']);

    try {
        $conn->transactional(function (Connection $inner) {
            $inner->executeStatement('INSERT INTO users (name) VALUES (?)', ['Bob']);
            throw new \RuntimeException('inner failure');
        });
    } catch (\RuntimeException) {
        // Only the inner transaction (Bob) is rolled back.
        // Alice's insert is preserved.
    }
});
// Alice is committed; Bob is not.

Checking nesting level

$level = $connection->getTransactionNestingLevel();
// 0 — no active transaction

---

Query Builder

Create a fluent query builder from the connection:

$qb = $connection->createQueryBuilder();

$rows = $qb->select('id', 'name')
    ->from('users')
    ->where('status = :status')
    ->setParameter('status', 'active')
    ->fetchAllAssociative();

See the Query Builder page for the full reference.

---

Accessing the underlying PDO

$pdo = $connection->getPdo();

Use direct PDO access sparingly. Prefer using Connection methods to maintain consistent error handling and transaction management.