PHP Database

PHP Database Patterns

When to Use

Writing raw PDO code, Eloquent/Doctrine queries, migrations, or diagnosing N+1 queries and transaction bugs in a PHP application.

Core Patterns

PDO Fundamentals

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false, // use real prepared statements
]);

$stmt = $pdo->prepare('SELECT id, email FROM users WHERE status = :status');
$stmt->execute(['status' => 'active']);
$users = $stmt->fetchAll();

ERRMODE_EXCEPTION is essential — without it, PDO fails silently and bugs surface as missing data instead of thrown errors.

Query Builder Over Raw SQL for Dynamic Queries

$query = Order::query();

if ($status !== null) {
    $query->where('status', $status);
}

if ($fromDate !== null) {
    $query->where('created_at', '>=', $fromDate);
}

$orders = $query->orderByDesc('created_at')->paginate(25);

Building dynamic WHERE clauses by string concatenation is both an injection risk and unreadable — the query builder composes safely.

Migrations: Reversible and Data-Safe

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->string('tracking_number')->nullable()->after('status');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropColumn('tracking_number');
        });
    }
};

For large tables, avoid locking ALTER TABLE operations during peak traffic — add nullable columns (fast, no backfill needed), backfill in a background job, then add constraints in a follow-up migration.

Detecting and Fixing N+1 Queries

// BAD: 1 query for orders + N queries for each order's customer
foreach (Order::all() as $order) {
    echo $order->customer->name;
}

// GOOD: eager load
foreach (Order::with('customer')->get() as $order) {
    echo $order->customer->name;
}

Enable Model::preventLazyLoading() in local/testing environments so N+1 access throws instead of silently issuing extra queries.

Transactions with Rollback

DB::transaction(function () use ($orderData, $items) {
    $order = Order::create($orderData);

    foreach ($items as $item) {
        $order->items()->create($item);
        Product::where('id', $item['product_id'])->decrement('stock', $item['qty']);
    }

    // Any exception thrown here rolls back the entire transaction automatically
});

// Manual PDO transaction
$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([$amount, $from]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([$amount, $to]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Connection Pooling Notes

Standard PHP-FPM/Apache mod_php processes open a new DB connection per request (no persistent pooling by default). For high-throughput services:

• Use PDO::ATTR_PERSISTENT => true cautiously — persistent connections can leak transaction state across requests if not reset properly
• Prefer an external pooler (PgBouncer for Postgres, ProxySQL for MySQL) over PHP-level persistence for production scale
• Long-running workers (Swoole, RoadRunner) can safely reuse one pooled connection since they don't tear down between requests

Checklist

• PDO::ATTR_ERRMODE set to ERRMODE_EXCEPTION
• All queries use bound parameters, never string concatenation
• Migrations have a working, tested down()
• Model::preventLazyLoading() enabled outside production
• Multi-step writes wrapped in a transaction with rollback on failure
• Large-table migrations split into safe, non-locking steps

Anti-Patterns

// BAD: fetching all rows into memory for a large table
$allUsers = DB::table('users')->get();
foreach ($allUsers as $user) { /* ... */ }

// GOOD: chunk or lazy-iterate
DB::table('users')->orderBy('id')->chunk(500, function ($users) {
    foreach ($users as $user) { /* ... */ }
});

Quick Reference

See Also

• skills/php-ecosystem/laravel-patterns.md
• skills/php-ecosystem/php-performance.md
• skills/databases/query-optimization.md