Choosing DB, QueryBuilder, and Repository

Choosing DB, QueryBuilder, and Repository#

Introduction#

DBLayer intentionally exposes three layers:

DB: process-level orchestration and infrastructure controls.
QueryBuilder: per-query SQL composition.
Repository: reusable table-level rules and behavior.

You usually use all three in one application, but for different reasons.

Entry Path (How Most Apps Work)#

Most codebases enter through DB first, then branch:

stay on DB for infra concerns (transaction boundaries, retries, connection capabilities, telemetry/profiler/pooling), or
move to DB::table() for ad-hoc SQL composition, or
move to DB::repository() for reusable table rules.

This is the normal and intended flow in DBLayer.

Quick Decision Matrix#

You Need	Use	Why
Start/commit transactions, manage connections, inspect capabilities	`DB`	Infrastructure concerns live at the facade layer.
Compose a one-off complex query (joins, CTEs, custom select/having)	`QueryBuilder`	Maximum query-shaping flexibility.
Reuse tenant/soft-delete/hooks/default-order rules across services	`Repository`	Centralized table policy avoids duplicated filters.
Raw SQL execution with bindings	`DB::select()`, `DB::statement()` and related helpers	Fluent builder is optional; raw SQL stays first-class.
Long-running table scan with stable pagination	`QueryBuilder::chunkById()` or `Repository::chunkById()`	Keyset chunking is safer than offset paging under writes.

Mental Model#

DB answers: “How should this run?”
QueryBuilder answers: “What SQL should be emitted?”
Repository answers: “What table rules must always apply?”

If the question is about operational behavior (timeouts, retries, transactions, telemetry), start at DB. If it is about SQL shape, start at builder. If it is about consistent table policies, start at repository.

Practical Scenarios#

1. Health/Readiness check#

Use the facade:

$ok = DB::ping();
$version = DB::version();

2. API list endpoint with dynamic filters#

Use builder for ad-hoc query shape:

$rows = DB::table('users')
    ->select('id', 'email', 'name')
    ->when($onlyActive, fn ($q) => $q->where('active', '=', 1))
    ->when($role !== null, fn ($q) => $q->where('role', '=', $role))
    ->orderBy('id', 'desc')
    ->forPage($page, 20)
    ->get();

3. Multi-tenant table access used in many services#

Use repository so tenant rules are centralized:

$users = DB::repository('users')->forTenant($tenantId);
$active = $users->get(fn ($q) => $q->where('active', '=', 1));

4. Soft-delete + restore workflow#

Use repository feature toggles:

$users = DB::repository('users')->enableSoftDeletes();
$users->deleteById($id);
$users->restoreById($id);

5. Cross-table reporting query#

Use builder for joins and aggregate shaping:

$rows = DB::table('orders as o')
    ->join('users as u', 'o.user_id', '=', 'u.id')
    ->select('u.email')
    ->selectRaw('count(*) as orders_count')
    ->groupBy('u.email')
    ->having('orders_count', '>=', 5)
    ->get();

6. Transaction boundary with retry attempts#

Use facade transaction orchestration; repository/builder calls can live inside:

DB::transaction(function (): void {
    DB::table('accounts')->where('id', '=', 1)->update(['balance' => 900]);
    DB::table('accounts')->where('id', '=', 2)->update(['balance' => 1100]);
}, attempts: 3);

7. Capability-aware write path#

Use facade capabilities to branch behavior:

if (DB::supportsReturning()) {
    $row = DB::table('users')->insertReturning(['email' => $email]);
} else {
    DB::table('users')->insert(['email' => $email]);
}

8. Large table backfill job#

Use keyset streaming from builder or repository:

DB::table('events')
    ->orderBy('id')
    ->chunkById(1000, function (array $rows): bool {
        // process rows
        return true;
    }, 'id');

Repository-Style App Service Pattern#

If you want repository-oriented naming in your app, wrap DBLayer repository with composition:

use Infocyph\DBLayer\DB;
use Infocyph\DBLayer\Query\Repository;

final class UserRepository
{
    public function __construct(private readonly Repository $repo) {}

    public static function make(int $tenantId): self
    {
        $repo = DB::repository('users')
            ->forTenant($tenantId)
            ->enableSoftDeletes()
            ->setDefaultOrder('id', 'desc');

        return new self($repo);
    }

    public function findActiveByEmail(string $email): ?array
    {
        return $this->repo->first(
            fn ($q) => $q->where('active', '=', 1)->where('email', '=', $email)
        );
    }
}

This keeps domain naming explicit without turning DBLayer into an ORM system.

Laravel-Like Static Repository Facade (Still Repo Style)#

If you want a class that feels like a static repository API (User::find(), User::create(), User::query()), you can build one on top of repository. This is still not ORM behavior; it is repository delegation.

DBLayer includes this base class directly:

Infocyph\DBLayer\Repository\TableRepository

use Infocyph\DBLayer\Repository\TableRepository;
use Infocyph\DBLayer\Query\Repository;

abstract class AppTableRepository extends TableRepository
{
    protected static function configureRepository(Repository $repository): Repository
    {
        return $repository->enableSoftDeletes();
    }
}

final class User extends AppTableRepository
{
    protected static string $table = 'users';
    protected static ?string $connection = 'main';
}

$one = User::find(1);
$recent = User::query()->orderBy('id', 'desc')->limit(20)->get();
$tenantActive = User::forTenant(10)->get(fn ($q) => $q->where('active', '=', 1));
$reportRows = User::query('reporting')->limit(20)->get();

What this gives:

repository-oriented class ergonomics
table and connection mapping in one class
full repository features (tenant scope, soft deletes, optimistic locking, hooks, casts)

What this intentionally does not give:

ORM relations/identity map/dirty tracking/unit-of-work

TableRepository Scenarios#

Read-Repository Connection Split#

final class UserReadRepository extends TableRepository
{
    protected static string $table = 'users';
    protected static ?string $connection = 'reporting';
}

Policy Method Pattern#

final class User extends TableRepository
{
    protected static string $table = 'users';

    public static function activeForTenant(int $tenantId)
    {
        return static::forTenant($tenantId)
            ->get(fn ($q) => $q->where('active', '=', 1));
    }
}

One-Off Query Shape#

$recentEmails = User::query()
    ->select('email')
    ->where('created_at', '>=', $since)
    ->orderBy('id', 'desc')
    ->limit(100)
    ->pluck('email');

Common Pitfalls#

Treating DB and Repository as interchangeable abstractions. They have different responsibilities.
Repeating tenant/soft-delete filters manually in every builder query instead of centralizing them in a repository.
Using offset paging for long-running jobs where chunkById() would be safer.
Mixing optimistic-locking writes with blind updates on the same rows.