# PHP Database

> When to activate: PDO, prepared statements, fetch modes, Eloquent query builder, Doctrine, database migrations, N+1 queries, transactions, rollback, connection pooling, PHP database access

- Skill: `mattakushi432/php-database` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mattakushi432/php-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mattakushi432/php-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: Mattakushi432 (https://skillmd.com/u/mattakushi432)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/mattakushi432/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

```php
$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

```php
$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

```php
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

```php
// 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

```php
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
});
```

```php
// 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

```php
// 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

| Need | Tool |
|---|---|
| Safe dynamic queries | Query builder / Eloquent |
| Detect N+1 in dev | `Model::preventLazyLoading()` |
| Atomic multi-step writes | `DB::transaction()` |
| Large-table iteration | `->chunk()` / `->lazy()` |
| Reversible schema changes | Migrations with `up()`/`down()` |

## See Also

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