home / skills / hoangnguyen0403 / agent-skills-standard / database-expert

database-expert skill

safe

This skill helps Laravel developers optimize database interactions with advanced query patterns, Redis caching, and scalable architecture guidance.

npx playbooks add skill hoangnguyen0403/agent-skills-standard --skill database-expert

Review the files below or copy the command above to add this skill to your agents.

Files (2)

SKILL.md

1.6 KB

---
name: Laravel Database Expert
description: Expert patterns for advanced queries, Redis caching, and database scalability.
metadata:
  labels: [laravel, database, redis, sql, performance]
  triggers:
    files: ['config/database.php', 'database/migrations/*.php']
    keywords: [join, aggregate, subquery, selectRaw, Cache]
---

# Laravel Database Expert

## **Priority: P1 (HIGH)**

## Structure

```text
config/
└── database.php        # Connection & Cluster config
app/
└── Http/
    └── Controllers/    # Query logic entry points
```

## Implementation Guidelines

- **Advanced Query Builder**: Prefer `selectSub`, `joinSub`, and `whereExists` over raw SQL.
- **Aggregates**: Use `count()`, `sum()`, and `avg()` directly via Eloquent/Builder.
- **Cache-Aside Pattern**: Utilize `Cache::remember()` for frequently accessed data.
- **Redis Tagging**: Group related cache keys using `Cache::tags()` for atomic flushing.
- **Read/Write Splitting**: Configure master/slave connections in `config/database.php`.
- **Vertical Partitioning**: Decouple high-traffic tables to dedicated database instances.
- **Indices**: Ensure correct indexing for all aggregate and join columns.

## Anti-Patterns

- **Raw Concatenation**: **No String SQL**: Always use bindings or Query Builder.
- **Loop Queries**: **No queries in Loops**: Use subqueries or aggregates.
- **Global Cache Flush**: **No Cache::flush()**: Use tags to target specific groups.
- **Untracked Redis**: **No ungoverned Redis usage**: Use standard Laravel wrappers.

## References

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

Overview

This skill captures expert patterns for building scalable, maintainable database access in Laravel applications. It focuses on advanced query techniques, Redis-backed caching patterns, and practical strategies for read/write scaling and partitioning. The guidance emphasizes safety, performance, and predictable cache management for high-traffic systems.

How this skill works

The skill inspects query design and caching choices and recommends using Eloquent/Query Builder primitives like selectSub, joinSub, and whereExists instead of raw SQL string concatenation. It enforces cache-aside patterns with Cache::remember and encourages Redis tagging for grouped cache invalidation. It also surfaces configuration and architectural recommendations such as read/write splitting and vertical partitioning of hot tables.

When to use it

When replacing raw SQL with safer, composable Query Builder constructs
When implementing or auditing Redis caching for read-heavy endpoints
When planning database scaling: read replicas or vertical partitioning
When optimizing aggregate-heavy queries to use indexes and builder aggregates
When preventing anti-patterns like looped queries, global cache flushes, or untracked Redis usage

Best practices

Prefer selectSub, joinSub, whereExists, and builder aggregates (count, sum, avg) over raw SQL
Apply Cache::remember for cache-aside reads and Cache::tags for group invalidation
Configure master/slave connections in config/database.php for read/write split
Index join and aggregate columns proactively; monitor slow queries and EXPLAIN plans
Avoid queries inside loops by using subqueries or batch operations
Never use Cache::flush() globally; target tagged keys for safe invalidation

Example use cases

Refactor a report endpoint to use joinSub and builder aggregates to reduce N+1 queries
Introduce Cache::remember with Cache::tags for product listing pages to enable atomic cache clears
Configure read replicas for reporting traffic while directing writes to the primary
Partition a high-write analytics table to a dedicated DB instance to isolate load
Replace raw concatenated SQL in legacy code with parameterized Query Builder statements

FAQ

How do I safely invalidate caches for related resources?

Use Cache::tags to group related keys and call Cache::tags([...])->flush() to atomically clear only those groups.

When should I introduce read replicas instead of optimizing queries?

Optimize queries and add indexes first; introduce read replicas when read traffic outgrows a single instance or you need horizontal read scaling.