Records & the ORM

A record class maps one database table. Extend Pop\Db\Record and the class name becomes the table name, and from there the class finds rows, hydrates them into objects and writes them back. Four flavors cover four jobs: Record for ordinary tables, Record\Encoded for columns hashed or encrypted on the way in, Record\Auth for a credentials table, and Model\AbstractDataModel for a service layer handing data to a controller.

Defining a Record Class#

An empty subclass is a working table class, once a database adapter is registered with Pop\Db\Record — every table class then inherits it. That registration is bootstrap work, so it belongs on the application class rather than in a config file. The config file holds the credentials as data:

<?php

return [
    'default' => [
        'database' => $_ENV['DB_DATABASE'],
        'adapter'  => $_ENV['DB_ADAPTER'],
        'username' => $_ENV['DB_USERNAME'],
        'password' => $_ENV['DB_PASSWORD'],
        'host'     => $_ENV['DB_HOST'],
        'type'     => $_ENV['DB_TYPE'],
    ],
];

The application's load() consumes it. initDb() walks the configured connections, refuses to start on one it cannot reach, registers each as a service, and binds the default one to Pop\Db\Record:

<?php

namespace App;

use Pop\Db\Db;
use Pop\Db\Record;

class Application extends \Pop\Application
{

    public function load(): Application
    {
        $this->initDb(include __DIR__ . '/../config/database.php');

        return $this;
    }

    protected function initDb(array $database): void
    {
        foreach ($database as $name => $dbConfig) {
            if (empty($dbConfig['adapter']) || empty($dbConfig['database'])) {
                continue;
            }

            $adapter = $dbConfig['adapter'];
            $options = [
                'database' => $dbConfig['database'],
                'username' => $dbConfig['username'] ?? null,
                'password' => $dbConfig['password'] ?? null,
                'host'     => $dbConfig['host'] ?? null,
                'type'     => $dbConfig['type'] ?? null
            ];

            $check = Db::check($adapter, $options);

            if ($check !== true) {
                throw new \Pop\Db\Adapter\Exception('Error: ' . $check);
            }

            $service = ($name == 'default') ? 'database' : 'database_' . $name;

            $this->services()->set($service, [
                'call'   => 'Pop\Db\Db::connect',
                'params' => ['adapter' => $adapter, 'options' => $options]
            ]);

            if ($name == 'default') {
                Record::setDb($this->services['database']);
            }
        }
    }

}

Pop\Application declares no initDb() — this is an app-level convention, and the one kettle pop:init scaffolds. A second connection keyed reports becomes the database_reports service and is left unbound, so Record keeps resolving to default. Applications & Bootstrap covers load(), services and events in full.

<?php

namespace App\Table;

use Pop\Db\Record;

class Users extends Record {}

Users::table() now returns users. The class name is parsed, not guessed at runtime: it's lower-cased and split on camel-case boundaries, so UserLogins maps to user_logins. The primary key defaults to id and there's no table prefix. Three protected properties override each of those:

<?php

namespace App\Table;

use Pop\Db\Record;

class Users extends Record
{

    protected ?string $table       = 'app_users';
    protected ?string $prefix      = 'my_app_';
    protected array   $primaryKeys = ['user_id'];

}

$primaryKeys is an array because a composite key is a list. A table keyed on two columns declares both, and findById() then takes an array of values in the same order.

use Pop\Db\Record;

class Memberships extends Record
{

    protected array $primaryKeys = ['user_id', 'group_id'];

}

$membership = Memberships::findById([1, 2]);

A table class accepts any array of column values in its constructor, which is what makes new Users($request->all()) dangerous — an extra key in the request body sets an extra column. Declare $fillable to allowlist the mass-assignable columns, or $guarded to denylist them.

<?php

namespace App\Table;

use Pop\Db\Record;

class Users extends Record
{

    protected array $fillable = ['username', 'email'];

}

A non-empty $fillable wins outright and $guarded is ignored; declare neither and mass assignment is unrestricted. Filtering happens in fill(), which the constructor routes array input through, so both new Users($input) and $user->fill($input) are covered.

The mass-assignment guard covers fill() and the constructor. Single-property assignment, the bulk save($rows)/delete($columns) forms and hydration from the database are all unfiltered by design.

Connections Per Table Class#

Record::setDb($db) sets the connection every table class falls back to, and a table class can carry one of its own alongside it. That is how a single application reads its users from one database and writes its logs to another:

use Pop\Db\Db;
use Pop\Db\Record;
use App\Table\Logs;
use App\Table\Users;

Record::setDb(Db::mysqlConnect(['database' => 'popdb', 'username' => 'popuser', 'password' => 'db_password']));

Users::setDb(Db::mysqlConnect(['database' => 'accounts', 'username' => 'popuser', 'password' => 'db_password']));
Logs::setDb(Db::sqliteConnect(['database' => __DIR__ . '/../logs.sqlite']));

Users and Logs each resolve to their own adapter, and every other table class resolves to the one Record::setDb() registered. The lookup runs in three steps and takes the first that matches:

The prefix binds a whole namespace through any one class in it, which is the shape to reach for when a group of tables shares a connection:

use App\Report\Sales;

Sales::setDb($reportsDb, 'App\Report');

Every App\Report\* table class now resolves to $reportsDb without binding each one. A class with its own exact binding still wins over a prefix that covers it, and where two prefixes both match, the one registered later wins.

Users::hasDb() reports whether a class resolves to anything, and Db::db('App\Table\Users') runs the same lookup from outside a record class. A class matching nothing with no default behind it raises Pop\Db\Exception, No database adapter was found. — so register the fallback first and the per-class bindings after.

A third argument makes a class's adapter the default at the same time: Users::setDb($db, null, true) binds it to Users and registers it as the fallback for everything else.

This is where a second connection from app/config/database.php lands. The reports entry becomes the database_reports service, and binding it to the classes that should use it is one of these calls in load().

Finding and Saving#

Finding Records#

findById() and findOne() return one record object. findBy() and findAll() return a Pop\Db\Record\Collection of them.

use App\Table\Users;

$user   = Users::findById(1);
$user   = Users::findOne(['username' => 'testuser']);
$user   = Users::findLatest();

$active = Users::findBy(['status' => 'active']);
$all    = Users::findAll();
$count  = Users::getTotal(['status' => 'active']);

A record that was not found is still a record object, not null — the class hands back an empty instance of itself with no columns set. Test for it by checking the primary key rather than the object:

$user = Users::findById(999);

if (!isset($user->id)) {
    // No such row
}

The collection is countable and iterable, and every element is a live record, so a loop can modify and save each one. first(), isEmpty(), column() and toArray() cover the rest of the common cases.

$users = Users::findBy(['status' => 'active']);

if (!$users->isEmpty()) {
    echo $users->first()->username;
    print_r($users->column('username')->toArray());
}

foreach ($users as $user) {
    $user->logins = 0;
    $user->save();
}

Passing true as the last argument to any find method returns plain arrays instead of objects, which is what you want when the rows are headed straight into a view or a JSON response and nothing is going to call save() on them.

$rows = Users::findBy(['status' => 'active'], null, true);

Every read method also takes an $options array that shapes the query around the predicate. Seven keys, combined however you like:

$users = Users::findBy(['status' => 'active'], [
    'select' => ['id', 'username'],
    'order'  => 'id DESC',
    'offset' => 10,
    'limit'  => 25
]);

$options follows the predicate: second on findById(), findOne(), findBy() and getTotal(), first on findAll() and getAll(), and one place later on findLatest() and the value-taking findWhere* methods.

join is the one key that changes the shape of the rest. Two tables are in scope once it is set, so select and the predicate both need qualified column names:

$users = Users::findBy(['users.status' => 'active'], [
    'select' => ['users.username', 'roles.role'],
    'join'   => ['table' => 'roles', 'columns' => ['roles.id' => 'users.role_id']]
]);

The join type defaults to LEFT JOIN. A type key naming a join method on the query builder — rightJoin, innerJoin — overrides it, and a type the builder does not have falls back to the left join rather than complaining. Wrapping the array in another array joins more than one table.

A bare 'age' sorts ascending on every adapter, and '-age' is the descending shorthand — the same -column convention AbstractDataModel::getOrderBy() uses.

'RAND()' reaches MySQL and PostgreSQL as written, so use the engine's own spelling for a random order — see Query Builder.

An unrecognized $options key raises an E_USER_NOTICE naming it, and the query builds with that option left off.

Saving Records#

save() handles both directions. A record built with the constructor is flagged new and inserts, picking up the generated primary key; a record that was fetched updates.

$user = new Users(['username' => 'testuser', 'email' => 'testuser@test.com']);
$user->save();

echo $user->id;    // the id the database assigned

$user->email = 'new@test.com';
$user->save();     // UPDATE, because this record now has an id

$user->delete();
echo $user->id;    // null — delete() clears the in-memory state too

Between the change and the save(), the record knows what moved. isDirty() and getDirty() return the old and new values, which is what an audit trail reads.

$user->username = 'renamed';

if ($user->isDirty()) {
    print_r($user->getDirty());   // ['old' => [...], 'new' => [...]]
}

Four helpers write a single column and save in one call, which avoids the read-modify-write dance:

$user->increment('logins');       // + 1
$user->decrement('credits', 5);   // - 5
$user->reset('logins', 0);        // set to a given value
$copy = $user->copy(['username' => 'testuser-copy']);

reset() assigns a value rather than an offset, and its second argument defaults to null, so $user->reset('mfa_code') nulls a column and persists it in one call. All three go through save().

Two write forms work on whole sets rather than one row. save() given a list of rows does a bulk insert, and delete() given a criteria array does a bulk delete:

$users = new Users();

$users->save([
    ['username' => 'u1', 'email' => 'u1@test.com'],
    ['username' => 'u2', 'email' => 'u2@test.com'],
]);

$users->delete(['status' => 'archived']);

Neither builds a record object at any point. When even those are not enough, query() runs raw SQL and execute() runs a prepared statement, both against the table class's own connection:

$rows = Users::query('SELECT * FROM users', true);

$rows = Users::execute(
    'SELECT * FROM users WHERE username = :username',
    ['username' => 'testuser'],
    true
);

A table class can hook the write path. Override any of beforeSave(), afterSave(), beforeInsert(), afterInsert(), beforeUpdate(), afterUpdate(), beforeDelete() or afterDelete() as a protected method — they are empty by default, so declaring none changes nothing.

<?php

namespace App\Table;

use Pop\Db\Record;

class Users extends Record
{

    protected function beforeSave(): void
    {
        $this->updated_at = date('Y-m-d H:i:s');
    }

}

On save() the order is beforeSave(), then beforeInsert() or beforeUpdate(), then the statement, then afterInsert() or afterUpdate(), then afterSave(). A hook that throws aborts the operation and the exception propagates to the caller.

Hooks fire on the single-record path. The bulk save($rows) and delete($columns) forms, and increment(), decrement(), reset(), replicate() and copy(), go straight to the database.

Shorthand Syntax#

A $columns array of plain column => value pairs is an equality match joined with AND. Anything else — a range, a LIKE, a set — is written as a two-or-more element array whose first element is the operator.

$users = Users::findBy([
    'age'        => ['>=', 18],
    'status'     => ['!=', 'inactive'],
    'username'   => ['LIKE', '%smith%'],
    'created_at' => ['BETWEEN', '2024-01-01', '2024-12-31'],
    'role'       => ['IN', ['admin', 'editor']],
    'deleted_at' => ['IS NULL'],
]);

OR and AND are reserved keys that take a list of nested condition arrays, which is how you get a grouped predicate without the query builder:

$users = Users::findBy([
    'status' => 'active',
    'OR' => [
        ['logins' => ['>=', 9]],
        ['age'    => ['>=', 65]],
    ],
]);
// WHERE status = 'active' AND (logins >= 9 OR age >= 65)

Each operator knows its own arity, and a mismatch throws a Pop\Db\Sql\Parser\Exception at build time rather than rendering something unintended. ['BETWEEN', 18] is rejected for wanting two values, and ['IN', []] is rejected for wanting at least one — which is the case worth knowing about, because an empty IN list is what an unfiltered user-supplied array degrades into.

The findWhere* family is the same thing spelled as a static call, taking the column and the value as arguments. It builds structured shorthand internally, so nothing about it is deprecated.

$adults    = Users::findWhereGreaterThanOrEqual('age', 18);
$editors   = Users::findWhereIn('role', ['admin', 'editor']);
$pending   = Users::findWhereNull('confirmed_at');
$smiths    = Users::findWhereLike('username', '%smith%');

findWhereEquals(), findWhereNotEquals(), findWhereGreaterThan(), findWhereLessThan(), findWhereLessThanOrEqual(), findWhereNotLike(), findWhereNotIn(), findWhereBetween(), findWhereNotBetween() and findWhereNotNull() complete the set. findWhereBetween() takes its two bounds as a single array. A column name also works directly as the suffix — Users::findWhereUsername('bob') is an equality match on username.

Write predicate operators in the value rather than the key — 'age' => '>=45' rather than 'age>=' => 45. The key-suffix form is deprecated.

Data Models#

Pop\Db\Model\AbstractDataModel sits one layer above a table class and gives a controller the CRUD verbs it actually wants — fetch a page of rows, create from a request body, patch by id — without the controller knowing about findBy() or the shape of $options. It's the natural back end for a REST resource.

A model is linked to its table class by naming convention: App\Model\User resolves to App\Table\Users. Nothing else wires them together.

<?php

namespace App\Model;

use Pop\Db\Model\AbstractDataModel;

class User extends AbstractDataModel
{

    protected array $requirements = ['username', 'email'];

}

$requirements lists the columns that must be present. create() and replace() check $data against it before writing, and a missing column comes back as an errors array instead of a record — so the failure is a return value, not an exception the controller has to catch.

use App\Model\User;

$user = User::createNew(['username' => 'testuser', 'email' => 'testuser@test.com']);

if (isset($user['errors'])) {
    // ['errors' => ['email' => "The column 'email' is required."]]
}

The static entry points cover the common reads and the create; the instance methods cover everything that takes an id.

$users = User::fetchAll();
$user  = User::fetch(1);

$model = new User();

$user  = $model->getById(1);
$user  = $model->getOne(['username' => 'testuser']);
$user  = $model->update(1, ['email' => 'new@test.com']);
$user  = $model->replace(1, ['username' => 'testuser', 'email' => 'new@test.com']);
$user  = $model->copy(1, ['username' => 'testuser-copy']);

$model->delete(1);
$model->remove([2, 3, 4]);

echo $model->count();

update() is a PATCH and replace() is a PUT: the first merges the given columns into the existing row, the second resets every column not named in $data back to its default. Both return the record as it now stands. delete() and remove() return the number of rows affected.

Every read takes a $toArray flag as its last argument, which is what a JSON controller passes:

$rows = User::fetchAll(null, null, null, true);
$row  = User::fetch(1, true);

fetchAll() and getAll() also take sorting and pagination directly, and filterBy() puts a WHERE in front of them. A - prefix on the sort column means descending.

$users = User::filterBy('username LIKE myuser%')->getAll('-id', 10, 2);

That reads page 2 of a 10-row page size, ordered by id DESC. filterBy() takes SQL-expression strings rather than the shorthand arrays a record uses, and filter() is the same thing on an instance.

Keep filterBy() expressions to plain equality; anything else raises an E_USER_DEPRECATED notice as it converts internally.

Transactions#

The smallest form wraps a single record. Call startTransaction() before save() and the record commits itself on success and rolls itself back on any exception — including one thrown from a lifecycle hook.

$user = new Users([
    'username' => 'testuser',
    'email'    => 'testuser@test.com'
]);

$user->startTransaction();
$user->save();

Users::start() is the constructor and startTransaction() in one call:

$user = Users::start(['username' => 'testuser', 'email' => 'testuser@test.com']);
$user->save();

That auto-commit is what save()'s second argument controls. Pass false and the transaction stays open, which is what you want when several writes belong to one unit of work.

$user = Users::start(['username' => 'testuser', 'email' => 'testuser@test.com']);

$user->save(null, false);

$user->logins = 1;
$user->save(null, false);

$user->commitTransaction();

Watch for the helpers here: increment(), decrement(), reset() and copy() call save() with the default $commit, so any one of them closes an open per-record transaction as a side effect.

For a unit of work spanning several table classes, drive the transaction from Record itself. Record::start(), Record::commit() and Record::rollback() act on the shared adapter, so every table class resolving to that connection is inside it.

use Pop\Db\Record;
use App\Table\Users;
use App\Table\Roles;

try {
    Record::start();

    (new Users(['username' => 'testuser', 'email' => 'testuser@test.com']))->save(null, false);
    (new Roles(['role' => 'Admin']))->save(null, false);

    Record::commit();
} catch (\Exception $e) {
    Record::rollback();
    echo $e->getMessage();
}

Record::transaction() is the same thing with the try/catch folded in. It commits when the callable returns, rolls back and rethrows when it throws.

use Pop\Db\Record;

try {
    Record::transaction(function () {
        (new Users(['username' => 'testuser', 'email' => 'testuser@test.com']))->save(null, false);
        (new Roles(['role' => 'Admin']))->save(null, false);
    });
} catch (\Exception $e) {
    echo $e->getMessage();
}

The adapter issues a SAVEPOINT for every nested begin, on MySQL, PostgreSQL, SQLite and PDO alike. A rollback at depth 1 is a real ROLLBACK; deeper, it rolls back to that depth's savepoint.

use Pop\Db\Record;
use App\Table\Users;

Record::transaction(function () {
    (new Users(['username' => 'outer', 'email' => 'outer@test.com']))->save(null, false);

    try {
        Record::transaction(function () {
            (new Users(['username' => 'inner', 'email' => 'inner@test.com']))->save(null, false);
            throw new \RuntimeException('inner failed');
        });
    } catch (\RuntimeException $e) {
        // the inner row is gone; the outer transaction is still open
    }

    (new Users(['username' => 'outer2', 'email' => 'outer2@test.com']))->save(null, false);
});

outer and outer2 commit; inner rolls back to the savepoint. Catch the inner failure where it happens — without that inner try/catch it reaches the outer transaction, which rolls everything back and rethrows.

The adapter's own transaction API — $db->beginTransaction(), $db->commit(), $db->rollback(), $db->transaction() and $db->getTransactionDepth() — is available whenever you want the connection rather than the record classes to be the unit of control.

use Pop\Db\Record;
use App\Table\Users;

$db = Record::getDb();

$db->transaction(function () {
    (new Users(['username' => 'testuser', 'email' => 'testuser@test.com']))->save(null, false);
});

See Also#

• Relationships — hasMany(), belongsTo() and eager loading between record classes
• Query Builder — the SQL builder a record uses underneath, driven directly
• Encoded Records — hashing, encrypting and JSON-encoding named columns
• Auth Records — a credentials table with lockout and multi-factor login
• pop-db README — the full record and model API surface