/laravel-database-expert

name: laravel-database-expert
description: 'Optimize Laravel queries with subqueries, joinSub, Redis cache-aside patterns, and read/write connection splitting. Use when writing complex joins, implementing Cache::remember with tags, or configuring database read replicas.'
metadata:
  triggers:
    files:
    - 'config/database.php'
    - 'database/migrations/*.php'
    keywords:
    - join
    - aggregate
    - subquery
    - selectRaw
    - Cache

Laravel Database Expert

**Priority: P1 (HIGH)**

Workflow: Optimize Slow Query

1. **Profile query** — Use `DB::enableQueryLog()` or Laravel Debugbar. 2. **Add missing indexes** — Create migration for join/where columns. 3. **Replace N+1** — Use `withCount()`, `withSum()`, or `addSelect` subqueries. 4. **Cache results** — Apply `Cache::remember()` with tags for frequently accessed data. 5. **Split reads/writes** — Configure `read`/`write` keys in `config/database.php`.

Cache-Aside with Tags Example

See [implementation examples](references/implementation.md#cache-aside-with-tags) for cache-aside pattern with tag-based invalidation.

Implementation Guidelines

Advanced Query Builder

• **Complex Joins**: Prefer **`joinSub($subquery, 'alias', ...)`** and **`whereExists(fn($q) => $q->select(DB::raw(1))...)`** over raw SQL or `whereIn` for correlated subqueries.
• **Subqueries**: Use **`addSelect`** with **`DB::raw`** subquery to avoid N+1 issues.
• **Aggregates**: Use **`withCount()`**, **`withSum()`**, and **`withAvg()`** directly via Eloquent for optimized column-based aggregation.
• **Raw Expressions**: Always use **`selectRaw`** or **`whereRaw`** with bindings; **never use string concatenation** in raw queries.

Caching Strategy (Redis/Memcached)

• **Cache-Aside**: Utilize **`Cache::remember('key', $ttl, $closure)`** for frequently accessed data (e.g., `posts.all`).
• **Redis Tagging**: Group related keys using **`Cache::tags(['posts', 'user:1'])`** for **grouped invalidation**.
• **Invalidation**: Call **`Cache::tags(['posts'])->flush()`** to clear specific subsets; **never use `Cache::flush()` globally** in production.

Scalability & Infrastructure

• **Read/Write Splitting**: Configure **'read'** and **'write'** keys in **`config/database.php`** mysql/pgsql connections. Laravel automatically routes **SELECT** to read and **INSERT/UPDATE/DELETE** to write; **no code changes needed**.
• **Indices**: Ensure correct **database indexes** present on all join and aggregate columns.

Anti-Patterns

• **No string SQL concatenation**: Use bindings or Query Builder.
• **No queries in loops**: Use subqueries, joins, or aggregates.
• **No `Cache::flush()`**: Use tags to target specific cache groups.
• **No direct Redis calls**: Use Laravel Cache wrappers consistently.

References

• [Advanced SQL & Cache Patterns](references/implementation.md)

Canonical response anchors

When this skill applies, preserve the following domain terminology or equivalent concrete examples in the answer when relevant:

• INSERT/UPDATE/DELETE
• correlated subqueries
• grouped invalidation
• no code changes needed
• posts.all

• Additional task-grounded exact anchors: Cache::remember; withAvg