The database layer

One database class per entity

Application code never calls the Database module directly. Instead, each entity has a single database class, for example MemberDatabase for the member table. It exposes named functions for the reads and writes that entity needs, and keeps a private fromRow that maps a raw row to an immutable data object. Because the mapping lives in one place, callers only ever see MemberData, never a raw row or a column name.

<?php
// One database class per entity. It wraps the Database module and maps rows to
// immutable data objects through a single private fromRow.
final class MemberDatabase
{
    static function Get(int $memberId): MemberData
    {
        return Database::SelectRow('member', 'MemberID', $memberId, MemberDatabase::fromRow(...));
    }

    static function OptionalByEmail(string $email): ?MemberData
    {
        $where = (object)['MemberEmail' => $email];
        return Database::SelectRowIfExistsWhere('member', $where, MemberDatabase::fromRow(...));
    }

    static function Create(string $name, string $email): int
    {
        $row = (object)['MemberName' => $name, 'MemberEmail' => $email];
        return Database::InsertRow('member', $row);
    }

    static function Rename(int $memberId, string $name): void
    {
        Database::UpdateField('member', 'MemberID', $memberId, 'MemberName', $name);
    }

    private static function fromRow(object $row): MemberData
    {
        $id = To::ID($row->MemberID);
        $name = To::Str($row->MemberName);
        $email = To::Str($row->MemberEmail);
        return new MemberData($id, $name, $email);
    }
}

The mapper is passed to the read helpers as their last $fromRow argument, using PHP's first-class callable syntax (MemberDatabase::fromRow(...)). It builds the data object with To:: helpers and positional constructor arguments, the same way data objects are built everywhere.

Reading rows

The read helpers cover the common shapes so you rarely write the SQL yourself. Read by id with SelectRow, or by a $where object with SelectRowWhere. A $where is a plain object whose fields are matched with a null-safe comparison and combined with AND.

Whether a missing row is an error is in the name. SelectRow and SelectRowWhere throw DatabaseErrorNoRow when there is no match; the Optional and IfExists variants return null instead. Name the variable to match: a value that can be null is named optional..., so a plain name is known to be present.

<?php
// Throws if there is no such member
$member = MemberDatabase::Get($memberId);

// Returns null if there is no member with that email
$optionalMember = MemberDatabase::OptionalByEmail($email);

Other helpers read a page of rows (SelectRowsWhereLimit), a single column (SelectField), or the distinct values of a column. See the reference for the full set.

Writing rows

InsertRow inserts one row and returns its new id. If the insert breaks a unique or foreign key constraint it throws DatabaseErrorAlreadyExists, so a duplicate is easy to tell apart from a real failure; pass the ignore flag to turn a duplicate into a no-op instead.

Update one column with UpdateField, or several from a row object with UpdateFields and UpdateRow. Delete a single row with DeleteRow or DeleteRowWhere, and every match with the plural forms. The Create and Rename methods above are the usual shape: a small named function per write, each a single call into the Database module.

Custom queries

When a read is beyond the helpers, write the SQL and bind the values. Database::Query returns a query you bind parameters onto and then fetch from; FetchAll takes the same fromRow mapper, so a list comes back as data objects. Every value is bound, never put into the SQL string.

<?php
// A method on MemberDatabase: active members, newest name first
$query = Database::Query("
    SELECT * FROM member
    WHERE MemberStatus = :status
    ORDER BY MemberName ASC
");
$query->BindString(':status', 'active');
$members = $query->FetchAll(MemberDatabase::fromRow(...));

For an IN clause, bind an array. BindIntArray and BindStringArray expand one placeholder into a list; string values are bound, never inlined. Both reject an empty array, so short-circuit when you have nothing to match.

<?php
$memberIds = [4, 8, 15, 16, 23, 42];
$query = Database::Query("SELECT * FROM member WHERE MemberID IN (:ids)");
$query->BindIntArray(':ids', $memberIds);
$members = $query->FetchAll(MemberDatabase::fromRow(...));

Transactions and locking

Wrap related writes in Transaction: it commits when the callback returns and rolls back if it throws, re-throwing the exception. Transactions nest through savepoints, so a callback can safely call other code that starts its own transaction.

To read a row and then update it without another request slipping in between, lock it with LockRow. It selects the row FOR UPDATE and holds it until the transaction ends, so it must be called inside a Transaction and throws if it is not.

<?php
Database::Transaction(function() use ($accountId, $amount) {
    $account = Database::LockRow('account', 'AccountID', $accountId);
    $newBalance = $account->AccountBalance - $amount;
    Database::UpdateField('account', 'AccountID', $accountId, 'AccountBalance', $newBalance);
});

Installing the tables

A module owns its tables and installs them itself. Keep numbered install scripts in an install/ folder and call Install from the module's index.php, naming the module and the latest version. The scripts run in order, once, and are skipped entirely when the module is already current. Migrations and seed data are covered in their own guide, Migrations and seed data.

<?php
// In the module index.php, after the connection is ready:
Database::Install('Member', __DIR__ . '/install', 3);

Three read shapes

Most reads are one of three shapes, and the name and return type say which. A single-record getter promises exactly one, so it returns the data object and throws DatabaseErrorNoRow when the row is absent; the caller does not null-check.

<?php
// (a) One record, or it throws. Return type is the object, not nullable.
static function Get(int $productId): ShopProductData
{
    return Database::SelectRow('shop_product', 'ShopProductID', $productId, ShopProductDatabase::fromRow(...));
}

An optional getter is for a lookup that may legitimately find nothing. Its name starts with optional and it returns DataType|null, so the caller knows up front that null is a real answer to handle.

<?php
// (b) One record or null. Named optional, returns ShopProductData|null.
static function optionalBySku(string $sku): ShopProductData|null
{
    $where = (object)['ShopProductSku' => $sku];
    return Database::SelectRowIfExistsWhere('shop_product', $where, ShopProductDatabase::fromRow(...));
}

A list getter returns an array of data objects. An empty array is a normal result, so it never throws for zero rows.

<?php
// (c) Many records. An empty array is fine, not an error.
static function ForIds(array $productIds): array
{
    if (CollectionArray::Empty($productIds)) {
        return CollectionArray::Zero();
    }
    $query = Database::Query("SELECT * FROM shop_product WHERE ShopProductID IN (:ids)");
    $query->BindIntArray(':ids', $productIds);
    return $query->FetchAll(ShopProductDatabase::fromRow(...));
}

Joining across modules without a join

The one query you must not write is a JOIN across two modules' tables. Orders live in the Shop order module and products in the Shop product module; a JOIN would wire the two together so neither could ever move to its own database. Instead reconcile them in PHP, in three steps: gather the ids, load them in a single bulk query, then zip the two lists together.

Gather the ids with CollectionArray::UniqueFieldValues. It reads one field from every object and returns the unique, non-null values, so a list of orders becomes the distinct set of product ids they reference, with no duplicates and no separate query per order.

<?php
// $orders is OrderData[]. Each has a ->productId field.
$productIds = CollectionArray::UniqueFieldValues($orders, 'productId');

Load every product in one bulk call. ForIds from above runs a single IN query and returns the product data objects, whatever module or even database they belong to. That is the replacement for the join: one extra query, not one per row.

<?php
$products = ShopProductDatabase::ForIds($productIds);

Now cross-reference in PHP. Index the products by id, then CollectionArray::Map over the orders to pair each order with its product, producing a combined data object per row. The two modules meet here, in plain code above the database layer, never in SQL.

<?php
// Index products by id so the lookup is a plain array read.
$productById = [];
CollectionArray::Each($products, function(ShopProductData $product) use (&$productById) {
    $productById[$product->id] = $product;
});

// Zip each order with its product into a combined row.
$rows = CollectionArray::Map($orders, function(OrderData $order) use ($productById) {
    $product = $productById[$order->productId];
    return new OrderWithProductData($order, $product);
});

The rule this follows is stated on Database layer conventions, and the reasoning is on Loose coupling.

Conventions to keep

• One database class per entity. Reads and writes for that entity go through it, and nothing else calls the Database module for that table.
• Every query returns a data object through a single private fromRow, built with To:: helpers and positional constructor arguments.
• Reads and writes are separate functions; identifiers appear only where they are needed.
• Anything that can be null is named optional..., matching the helper that returns it.
• Never join tables from different modules in one query. When two modules meet, do it in plain code above this layer.

These are stated on their own in Database layer conventions, and the full function list is in the Database module reference.

Next: Migrations and seed data.