Database

Prepared statements only, typed rows, transactions, and why the connection is a singleton.

Connection wraps PDO and only speaks in prepared statements. There is no method that takes a fully-built query with values in it.

Choosing an engine

SQLite, MySQL/MariaDB, PostgreSQL and Microsoft SQL Server 2012 or newer are supported. Pick one in .env; nothing in your application changes.

.env


# sqlite | mysql | pgsql | sqlsrv

# ("postgres", "postgresql", "mariadb", "sqlserver", "sql_server" and "mssql" also work)

DB_DRIVER=sqlite


# sqlite: a file path, resolved against the project root. ":memory:" works too.

# mysql/pgsql/sqlsrv: the database name, and required.

DB_DATABASE=storage/app.sqlite


# Ignored by sqlite. Port defaults to 3306 (mysql), 5432 (pgsql) or 1433 (sqlsrv).

#DB_HOST=127.0.0.1

#DB_PORT=3306

#DB_USERNAME=phponwheels

#DB_PASSWORD=

# DB_CHARSET applies to mysql and pgsql. PDO_SQLSRV uses UTF-8 by default.

#DB_CHARSET=utf8mb4

#DB_SOCKET=/var/run/mysqld/mysqld.sock

PHP


<?php
use PhpOnWheels\Database\Connection;
use PhpOnWheels\Database\DatabaseSettings;

// Read and validated once, at boot.
$database = Connection::connect(DatabaseSettings::fromEnvironment($env, $root));

// Or explicitly, without an .env
$database = Connection::connect(DatabaseSettings::postgres('phponwheels', 'db.internal'));
$database = Connection::connect(DatabaseSettings::mysql('phponwheels', 'db.internal'));
$database = Connection::connect(DatabaseSettings::sqlServer('phponwheels', 'db.internal'));
$database = Connection::sqlite($root . '/storage/app.sqlite');

Settings are validated at boot, so a missing database name or an out-of-range port stops the application starting rather than surfacing on whichever request queries first:

SQL Server requires the pdo_sqlsrv PHP extension. Its connection enables encryption and verifies the server certificate; install a trusted certificate on the database server rather than disabling verification in production.

Shell


$ ./pow routes
Configuration error: Setting "DB_DRIVER" is not a valid database driver.
Accepted values: sqlite, mysql, pgsql, sqlsrv.

What the framework smooths over

Difference between engines	How it is handled
Identifiers and paging have engine-specific syntax	Identifiers use the engine's delimiter; SQL Server paging uses `OFFSET/FETCH`.
Auto-increment keys are spelled differently	`$database->driver()->autoIncrementPrimaryKey()`, for migrations.
PostgreSQL's `lastInsertId()` is session-wide	`insert()` uses `RETURNING` there.
SQL Server returns inserted identity values with `OUTPUT`	`insert()` reads the generated key from `OUTPUT INSERTED`.
MySQL truncates over-long values by default	A strict `sql_mode` is set per connection.
SQLite ignores foreign keys by default	`PRAGMA foreign_keys` is set per connection.

Quoting is per connection, not a server setting

Switching MySQL's sql_mode to ANSI_QUOTES would be the other way to make double quotes work, and a worse one: it changes how every other statement on that connection is parsed, including SQL you wrote yourself.

Registered as a singleton at boot, then injected wherever it is needed:

PHP


<?php
$app->container->singleton(Connection::class, static fn (): Connection => $database);

Reading

PHP


<?php
// list<array<string, scalar|null>>
$rows = $database->select(
    'SELECT id, title FROM articles WHERE author_id = :author ORDER BY created_at DESC',
    ['author' => $authorId],
);

// array<string, scalar|null>|null
$article = $database->selectOne(
    'SELECT * FROM articles WHERE id = :id',
    ['id' => $id],
);

// A single scalar
$count = (int) $database->selectValue('SELECT COUNT(*) FROM articles');

Writing

PHP


<?php
$changed = $database->execute(
    'UPDATE articles SET title = :title WHERE id = :id',
    ['title' => $title, 'id' => $id],
);

$id = $database->query('articles')->insert(['title' => $title]);

Schema statements

PHP


<?php
$database->executeSchema(sprintf(
    'CREATE TABLE articles (id %s, title %s NOT NULL)',
    $database->driver()->autoIncrementPrimaryKey(),
    $database->driver()->textType(),
));

Separate from execute() because it binds nothing. Reaching for a method with no parameter binding should be a visible decision, not a convenient shortcut for interpolating a value.

Why values are always bound

PHP


<?php
// There is no API that accepts this. Deliberately.
$database->select("SELECT * FROM users WHERE email = '{$email}'");   // no such thing

// This is the only way, and a value can never be parsed as SQL.
$database->select('SELECT * FROM users WHERE email = :email', ['email' => $email]);

Emulated prepares are switched off. With emulation on, PDO interpolates values itself before sending the query, which reintroduces exactly the class of bug prepared statements exist to remove.

Identifiers are the exception

No driver can bind a table or column name — placeholders only work for values. Anything dynamic there must come from a list your application controls, never from a request. The query builder whitelists identifiers for you.

Transactions

PHP


<?php
$articleId = $database->transaction(function (Connection $database) use ($data): int {
    $id = (int) $database->query('articles')->insert($data);

    $database->query('audit')->insert([
        'action' => 'article.created',
        'article_id' => $id,
    ]);

    return $id;   // committed, and returned
});

The closure's return value is passed through. If it throws, the transaction rolls back and the exception propagates.

PHP


<?php
$database->inTransaction();     // bool
$database->rollBackIfOpen();    // for cleanup, not control flow

The connection is shared, so transactions are guarded

Under a worker the connection outlives the request. A handler that opens a transaction and throws would hand the next request a connection inside someone else's transaction — their writes would commit or roll back with work they never made. TransactionGuard rolls back anything left open and logs it, so the bug is reported rather than silently corrupting an unrelated request.

Per-request SAPIs hide this entirely: the process dies and the driver cleans up. It only appears under a worker, which is why the cleanup is explicit rather than left to the runtime.

Typed rows

PDO returns untyped arrays. Connection narrows them once, at the driver boundary, so nothing downstream deals in mixed:

PHP


<?php
$row = $database->selectOne('SELECT id, title, published FROM articles WHERE id = :id', ['id' => $id]);

// array<string, scalar|null> — narrow to what you need
$id = (int) ($row['id'] ?? 0);
$title = (string) ($row['title'] ?? '');
$published = (bool) ($row['published'] ?? false);

Errors

PHP


<?php
use PhpOnWheels\Database\QueryFailed;

try {
    $database->execute('INSERT INTO articles (title) VALUES (:title)', ['title' => $title]);
} catch (QueryFailed $e) {
    // "Query failed: UNIQUE constraint failed: articles.title
    //  -- SQL: INSERT INTO articles (title) VALUES (:title)"
}

The SQL is in the message; the parameters are not

The SQL is written by you and is what makes a failure diagnosable. The bound parameters are user data, and routinely contain passwords, tokens and personal information that would then travel into logs and bug reports.

Bringing your own PDO

Any PDO instance works, provided it is configured the same way and told which engine it is talking to:

PHP


<?php
use PhpOnWheels\Database\Driver;

$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$database = new Connection($pdo, Driver::PostgreSql);

The driver is what the builder consults for quoting and paging, so passing the wrong one produces SQL the server will reject.

Portable migrations

Most DDL is accepted by all four. Primary keys and unbounded text types are selected through the driver:

PHP


<?php
public function up(Connection $database): void
{
    $database->executeSchema(sprintf(
        'CREATE TABLE notes (
            id %s,
            title %s NOT NULL,
            created_at VARCHAR(40) NOT NULL
        )',
        $database->driver()->autoIncrementPrimaryKey(),
        $database->driver()->textType(),
    ));
}

Two things to watch when targeting MySQL

Index a VARCHAR, not a TEXT. MySQL cannot build a unique index on a TEXT column without a prefix length, so columns you intend to index should be declared VARCHAR(n).

DDL is not transactional on MySQL. SQLite, PostgreSQL and SQL Server roll a failed migration back cleanly; MySQL commits implicitly on most schema changes, so a failure there can leave a half-applied migration behind. Keep MySQL migrations small for that reason — $database->driver()->supportsTransactionalDdl() reports which you are on.

SQL Server uses NVARCHAR(MAX) rather than the deprecated TEXT type for comparable, unbounded text columns. Use textType() when a migration needs one.