Skip to content

Recipes

Jump to bottom
Muhammet Şafak edited this page May 24, 2026 · 1 revision

Recipes

Pattern-style snippets for things you'll actually do. Each example is self-contained and uses real builder / CRUD calls.

Pagination (offset-based)

function listUsers(int $page, int $perPage = 20): array
{
    $offset = max(0, ($page - 1) * $perPage);

    return DB::select('id', 'name', 'email')
        ->where('active', '=', 1)
        ->orderBy('id', 'DESC')
        ->limit($perPage)
        ->offset($offset)
        ->read('users')
        ->asAssoc()
        ->rows();
}

function countUsers(): int
{
    $row = DB::selectCount('id', 'total')
        ->where('active', '=', 1)
        ->read('users')
        ->asAssoc()
        ->row();

    return (int) $row['total'];
}

Wrap into a paginator if you have one:

$total = countUsers();
$rows  = listUsers($page);

return [
    'items'    => $rows,
    'page'     => $page,
    'per_page' => 20,
    'total'    => $total,
    'pages'    => (int) ceil($total / 20),
];

Pagination (cursor / keyset — recommended for large tables)

Offset-based pagination gets slower as the offset grows. Use a cursor on a monotonically-increasing column:

function listUsersAfter(?int $cursorId, int $perPage = 20): array
{
    $db = DB::select('id', 'name', 'email')
        ->orderBy('id', 'ASC')
        ->limit($perPage);

    if ($cursorId !== null) {
        $db->where('id', '>', $cursorId);
    }

    return $db->read('users')->asAssoc()->rows();
}

The client passes back the last seen id as the next cursor.

Chunking large inserts

createBatch() is fast — but a single 100k-row INSERT can exceed max_allowed_packet on MySQL or hit memory limits. Chunk it:

foreach (array_chunk($hugeArray, 500) as $chunk) {
    DB::createBatch('events', $chunk);
}

Wrap in a transaction for atomicity:

DB::transaction(function ($db) use ($hugeArray) {
    foreach (array_chunk($hugeArray, 500) as $chunk) {
        $db->createBatch('events', $chunk);
    }
});

Streaming a large result

Don't load 1M rows into memory. Iterate one at a time:

$result = DB::read('events')->asAssoc();

while (($row = $result->row()) !== null) {
    processEvent($row);
}

For MySQL, also disable result buffering at the prepare step:

$result = DB::query(
    'SELECT * FROM events ORDER BY id',
    [],
    [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false]
)->asAssoc();

Soft-delete pattern

DB::update('users', [
    'deleted_at' => DB::raw('NOW()'),
    'active'     => 0,
], ['id' => 5]);

// Soft-aware read
DB::where('deleted_at', 'IS', null)->read('users');

// Restoring
DB::update('users', ['deleted_at' => null, 'active' => 1], ['id' => 5]);

For a more structured soft-delete (with model-level guards), see initorm/orm.

Upsert (insert-or-update)

MySQL

DB::query('
    INSERT INTO users (email, name)
    VALUES (:email, :name)
    ON DUPLICATE KEY UPDATE name = VALUES(name)
', ['email' => 'a@x.com', 'name' => 'A']);

PostgreSQL

DB::query('
    INSERT INTO users (email, name)
    VALUES (:email, :name)
    ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
', ['email' => 'a@x.com', 'name' => 'A']);

SQLite (3.24+)

DB::query('
    INSERT INTO users (email, name)
    VALUES (:email, :name)
    ON CONFLICT(email) DO UPDATE SET name = excluded.name
', ['email' => 'a@x.com', 'name' => 'A']);

Atomic counter

DB::transaction(function ($db) {
    $db->update('counters', [
        'value' => DB::raw('value + 1'),
    ], ['id' => 1]);
});

$row = DB::read('counters', ['value'], ['id' => 1])->asAssoc()->row();
echo $row['value'];

Race-safe "get or create"

$user = DB::read('users', ['*'], ['email' => $email])->asAssoc()->row();

if ($user === null) {
    DB::transaction(function ($db) use ($email, &$user) {
        $db->create('users', ['email' => $email, 'name' => $email]);
        $user = $db->read('users', ['*'], ['email' => $email])->asAssoc()->row();
    });
}

return $user;

For true race-safety under concurrent inserts, use an upsert instead.

EXISTS check

$row = DB::query(
    'SELECT EXISTS(SELECT 1 FROM users WHERE email = :email) AS found',
    [':email' => 'alice@example.com']
)->asAssoc()->row();

if ($row['found']) {
    // …
}

Bulk delete with WHERE IN

DB::whereIn('id', $idsToDelete)->delete('users');
// DELETE FROM users WHERE id IN (1, 2, 3, 4, 5)

Note: numeric IN values are inlined verbatim; strings are parameterized. This is intentional — IN-lists with hundreds of numeric IDs would otherwise blow past PDO's max-parameter limit on some drivers.

N+1 to one query

Before:

$users = DB::read('users')->asAssoc()->rows();
foreach ($users as $user) {
    $posts = DB::read('posts', ['*'], ['user_id' => $user['id']])->asAssoc()->rows();
    $user['posts'] = $posts;
}

After (one query + grouping in PHP):

$rows = DB::select('users.id AS user_id', 'users.name', 'posts.id AS post_id', 'posts.title')
    ->from('users')
    ->leftJoin('posts', 'posts.user_id = users.id')
    ->read()
    ->asAssoc()
    ->rows();

$users = [];
foreach ($rows as $row) {
    $userId = $row['user_id'];
    if (!isset($users[$userId])) {
        $users[$userId] = ['id' => $userId, 'name' => $row['name'], 'posts' => []];
    }
    if ($row['post_id'] !== null) {
        $users[$userId]['posts'][] = ['id' => $row['post_id'], 'title' => $row['title']];
    }
}

Or fetch in two queries (better for sparse joins):

$users   = DB::read('users')->asAssoc()->rows();
$userIds = array_column($users, 'id');

$posts   = DB::whereIn('user_id', $userIds)->read('posts')->asAssoc()->rows();
$byUser  = [];
foreach ($posts as $p) {
    $byUser[$p['user_id']][] = $p;
}

foreach ($users as &$u) {
    $u['posts'] = $byUser[$u['id']] ?? [];
}

Audit log inside a transaction

DB::transaction(function ($db) use ($userId, $changes) {
    $db->update('users', $changes, ['id' => $userId]);
    $db->create('audit_log', [
        'user_id'   => $userId,
        'event'     => 'user.updated',
        'payload'   => json_encode($changes),
        'created_at' => DB::raw('NOW()'),
    ]);
});

Either both happen, or neither — never an unaudited write.

Test fixture with rollback

Use testMode: true to run an entire test inside a transaction that always rolls back:

public function test_creates_a_user(): void
{
    $this->db->transaction(function ($db) {
        $db->create('users', ['name' => 'X', 'email' => 'x@y.com']);

        $row = $db->read('users', ['name'], ['email' => 'x@y.com'])->asAssoc()->row();
        self::assertSame('X', $row['name']);
    }, testMode: true);
}

After the test, the users table is untouched.

Query-log-driven N+1 assertion

public function test_loadAll_runs_at_most_two_queries(): void
{
    $this->db->enableQueryLog();
    $this->repository->loadAll();

    self::assertLessThanOrEqual(
        2,
        count($this->db->getQueryLogs()),
        'Should fetch users and posts in two queries, not N+1.'
    );
}

Connecting to a Unix socket (MySQL)

new Database([
    'dsn'      => 'mysql:unix_socket=/var/run/mysqld/mysqld.sock;dbname=app;charset=utf8mb4',
    'username' => 'app',
    'password' => '…',
]);

SSL connection to RDS / managed MySQL

new Database([
    'dsn'      => 'mysql:host=db.cluster-x.eu-west-1.rds.amazonaws.com;port=3306;dbname=app',
    'username' => 'app',
    'password' => $_ENV['DB_PASS'],
    'options'  => [
        PDO::MYSQL_ATTR_SSL_CA  => '/etc/ssl/rds-ca-eu-west-1-bundle.pem',
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    ],
]);

See also

InitORM Database · MIT · maintained by Muhammet ŞAFAK · part of the InitORM stack

InitORM Database

Getting Started

Core Operations

Cross-Cutting

Reference

Upgrading

Project

Clone this wiki locally