laravel-database

Database Engineering Expert Skill

When to Use

Use this skill when designing database schemas, optimizing SQL queries, scaling database infrastructure, implementing search features, or working with database-specific capabilities (PostgreSQL or MySQL). This is the definitive reference for database engineering in Laravel 13 applications.

---

Core Philosophy

Database-First Thinking

Most performance problems originate from the database layer, not application code:

• Poor schema design
• Missing or incorrect indexes
• Inefficient queries
• Over-fetching data
• N+1 queries
• Incorrect scaling strategy

Golden Rule: Never optimize application code before analyzing database performance. Always profile the query first.

---

1. SQL Mastery

1.1 Select Only Needed Data

-- BAD
SELECT * FROM users;

-- GOOD
SELECT id, name, email FROM users;

1.2 Filter Early

-- BAD — loads all rows into application memory
SELECT * FROM orders;

-- GOOD — database filters before returning
SELECT * FROM orders WHERE status = 'paid';

1.3 Always Paginate

-- BAD for large tables
SELECT * FROM orders;

-- GOOD
SELECT * FROM orders ORDER BY id LIMIT 100 OFFSET 0;

1.4 Common Table Expressions (CTEs)

Use WITH queries for complex multi-step logic:

WITH popular_categories AS (
    SELECT category_id, COUNT(*) as total
    FROM products
    WHERE is_published = true
    GROUP BY category_id
    HAVING COUNT(*) > 10
)
SELECT c.name, pc.total
FROM categories c
JOIN popular_categories pc ON pc.category_id = c.id
ORDER BY pc.total DESC;

Laravel query builder:

$results = DB::table('categories')
    ->select('categories.name', 'pc.total')
    ->join(
        DB::raw('(SELECT category_id, COUNT(*) as total FROM products WHERE is_published = true GROUP BY category_id HAVING COUNT(*) > 10) as pc'),
        'pc.category_id', '=', 'categories.id'
    )
    ->orderBy('pc.total', 'desc')
    ->get();

1.5 Window Functions