Skip to content

Database

Jump to bottom
Viames Marino edited this page Mar 26, 2026 · 3 revisions

Pair framework: Database

Pair\Orm\Database is Pair’s low-level PDO wrapper.

It is the class used underneath:

  • ActiveRecord
  • Query
  • many module models
  • migrations and schema helpers
  • some API and background-job flows

Use Database when you need precise SQL control, typed fetch modes, transactions, or schema introspection.

Singleton and connection lifecycle

Database::getInstance(): Database

Returns the singleton instance. The connection itself is still opened lazily on the first real query unless you call connect() manually.

connect(): void / connectPersistent(): void

Open a normal or persistent PDO connection immediately.

Example:

use Pair\Orm\Database;

$db = Database::getInstance();

// Opens the connection immediately instead of waiting for the first query.
$db->connect();

if ($db->isConnected()) {
    // Reads the server version for diagnostics.
    $version = $db->getMysqlVersion();
}

isConnected(): bool

Returns whether the PDO handler is already active.

disconnect(): void

Closes the PDO connection explicitly and writes a log entry.

Main query methods

Database::load(string $query, array $params = [], int $option = Database::OBJECT_LIST)

Executes a prepared query and returns the result in the requested format.

Supported result options:

  • Database::OBJECT_LIST Array of stdClass rows.
  • Database::OBJECT First row as stdClass or null.
  • Database::RESULT_LIST Array of scalar values from the first column.
  • Database::RESULT First column of the first row.
  • Database::COUNT Integer count result.
  • Database::DICTIONARY Array of associative arrays.
  • Database::COLLECTION Pair\Orm\Collection of object rows.

Current implementation detail: load() normalizes booleans, null, and DateTime values in bound parameters before execution.

Example:

use Pair\Orm\Database;

// Loads one user row as an object.
$user = Database::load(
    'SELECT * FROM users WHERE id = ?',
    [42],
    Database::OBJECT
);

// Loads many rows as a Collection.
$users = Database::load(
    'SELECT id, email FROM users WHERE active = ?',
    [true],
    Database::COLLECTION
);

Database::value(string $query, array $params = []): string|int|float|null

Convenience helper for one scalar value. It wraps load(..., Database::RESULT) and normalizes PDO false to null.

Example:

use Pair\Orm\Database;

// Reads one scalar value or null if no row matches.
$email = Database::value(
    'SELECT email FROM users WHERE id = ?',
    [42]
);

Database::iterateDictionary(string $query, array $params = []): Generator

Streams rows as associative arrays instead of loading the whole result into memory. This is the right helper for exports, long reports, or background jobs.

Example:

use Pair\Orm\Database;

foreach (Database::iterateDictionary('SELECT id, email FROM users WHERE active = ?', [1]) as $row) {
    // Processes one row at a time.
    $csvLine = $row['id'] . ',' . $row['email'];
}

Database::run(string $query, array $params = []): int

Executes a write query and returns the number of affected rows.

Example:

use Pair\Orm\Database;

// Updates one row and returns the affected-row count.
$affected = Database::run(
    'UPDATE users SET active = ? WHERE id = ?',
    [1, 42]
);

Important: in the current implementation run() is a thin wrapper and does not perform the same parameter casting that load() does. For writes with DateTime objects or more complex value handling, prefer explicit scalars or the object helpers below.

Legacy instance pattern: setQuery() + loadObjectList() + loadCount()

Older Pair code sometimes stores the SQL on the Database instance first and then loads the result later.

Example:

use Pair\Orm\Database;

$db = Database::getInstance();

// Stores the SQL on the singleton instance.
$db->setQuery('SELECT id, email FROM users WHERE active = ?');

// Executes the stored query and returns an object list.
$rows = $db->loadObjectList([1]);

This pattern is still valid, but new code usually reads more clearly with Database::load().

Transactions

Database::start(): void

Database::commit(): void

Database::rollback(): void

Use these helpers when multiple writes must succeed or fail together.

use Pair\Orm\Database;

Database::start();

try {
    // Debits the source account.
    Database::run('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1]);

    // Credits the destination account.
    Database::run('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2]);

    Database::commit();
} catch (\Throwable $e) {
    // Restores the previous state on any error.
    Database::rollback();
    throw $e;
}

Object-level insert and update helpers

insertObject(string $table, stdClass $object, ?array $encryptables = []): bool

Builds an INSERT from the properties of a stdClass.

Current behavior includes:

  • skipping virtual generated columns
  • converting null, booleans, and DateTime objects
  • optional AES_ENCRYPT(...) for selected columns when AES_CRYPT_KEY is configured

Example:

use Pair\Orm\Database;

$row = new stdClass();
$row->email = 'hello@example.com';
$row->active = true;
$row->createdAt = new DateTime('now');

// Inserts the row and returns true when at least one row was created.
Database::getInstance()->insertObject('users', $row);

updateObject(string $table, stdClass &$object, stdClass $key, ?array $encryptables = []): int

Builds an UPDATE from the properties of a stdClass, using another stdClass as the WHERE clause.

Example:

use Pair\Orm\Database;

$row = new stdClass();
$row->email = 'updated@example.com';
$row->active = false;

$key = new stdClass();
$key->id = 42;

// Updates the matching row and returns the affected-row count.
Database::getInstance()->updateObject('users', $row, $key);

These helpers are especially useful when you already have a data object and do not need a full ActiveRecord.

Schema and introspection helpers

These methods are used by ORM internals, generators, migrations, and admin tooling:

  • tableExists(string $tableName): bool
  • describeTable(string $tableName): array
  • describeColumn(string $tableName, string $column): ?stdClass
  • getForeignKeys(string $tableName): array
  • getInverseForeignKeys(string $tableName): array
  • isAutoIncrement(string $tableName): bool
  • isVirtualGenerated(string $tableName, string $columnName): bool

Example:

use Pair\Orm\Database;

$db = Database::getInstance();

// Checks whether the table exists before running a maintenance task.
if ($db->tableExists('users')) {
    // Reads the cached table description.
    $columns = $db->describeTable('users');
}

describeTable(), getForeignKeys(), and getInverseForeignKeys() are cached in memory on the singleton instance.

Charset and utility helpers

setUtf8unicode(): void

Upgrades the current connection to utf8mb4 and utf8mb4_unicode_ci. This is useful when the application must handle extended Unicode such as emoji.

quote(string $text): string

Quotes a string using PDO. Useful when you are building one-off SQL fragments manually.

escape(string $text): string

Wraps a column name in backticks. This is only a formatting helper, not a full SQL parser.

getLastInsertId(): string|bool

Returns the last inserted ID for the current connection.

getMysqlVersion(): string|false

Returns the MySQL server version string.

Example:

use Pair\Orm\Database;

$db = Database::getInstance();

// Quotes one literal for a manually built SQL fragment.
$email = $db->quote('hello@example.com');

// Formats one column name with backticks.
$column = $db->escape('email');

Secondary methods worth knowing

These methods are not the first ones most modules call, but they are useful when maintaining legacy code or framework internals:

  • exec(string $query, array $params = []): int Instance-level write helper used internally by some object methods.
  • setQuery(string $query): void Legacy staging helper for loadObjectList() and loadCount().
  • loadCount(array $params = []): int Executes the SQL previously stored via setQuery().
  • setTableDescription(string $tableName, $tableDesc): void Manually primes cached schema metadata.

Important implementation notes

  • Database errors are converted into PairException or CriticalException depending on the failure.
  • Query execution is logged through LogBar.
  • load() supports richer parameter normalization than run().
  • iterateDictionary() is the safest option for large result sets.
  • insertObject() returns a boolean, while updateObject() returns the affected-row count.

See also: Query, ActiveRecord, Collection, Configuration-file.

Clone this wiki locally