laravel-database

Trigger Keywords

This skill automatically activates when Claude detects these keywords:

Overview

The laravel-database skill provides expertise for database design, query optimization, and Eloquent best practices. It helps identify and fix N+1 problems, design efficient schemas, and write performant queries.

What This Skill Provides

• Schema Design - Proper column types, indexes, foreign keys
• N+1 Detection - Identify and fix eager loading issues
• Big O Fixes - Detect O(n²) patterns and fix with O(1) lookups
• Query Optimization - Efficient queries with proper indexing
• Migrations - Safe, reversible database changes
• Eloquent Patterns - Scopes, accessors, relationships
• Raw Queries - When and how to use raw SQL safely

Example Conversations

# Fixing N+1 issues
"My page is making 100 queries to load 10 posts with their authors"

# Schema design
"Design a database schema for a multi-tenant e-commerce platform"

# Query optimization
"This query is slow - how can I optimize it?"

# Migration safety
"How do I add a column to a table with millions of rows without downtime?"

N+1 Problem & Solution

// BAD: N+1 queries (1 + N queries)
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name; // Query for each post!
}

// GOOD: Eager loading (2 queries total)
$posts = Post::with('author')->get();
foreach ($posts as $post) {
    echo $post->author->name; // No additional query
}

// BETTER: Eager load nested relationships
$posts = Post::with(['author', 'comments.user'])->get();

// BEST: Constrain eager loads
$posts = Post::with(['comments' => function ($query) {
    $query->where('approved', true)->latest()->limit(5);
}])->get();

Big O Complexity Issues

Big O complexity issues cause exponential slowdowns as data grows. Detect and fix O(n²) patterns.

// BAD: O(n²) - Nested loops comparing all items
$users = User::all();
$orders = Order::all();
foreach ($users as $user) {
    foreach ($orders as $order) {
        if ($order->user_id === $user->id) {
            // Process - runs n×m times!
        }
    }
}

// GOOD: O(n) - Eager load relationships
$users = User::with('orders')->get();
foreach ($users as $user) {
    foreach ($user->orders as $order) {
        // Process - each order accessed once
    }
}

// GOOD: O(n) - Use groupBy for O(1) lookups
$ordersByUser = Order::all()->groupBy('user_id');
foreach ($users as $user) {
    $userOrders = $ordersByUser->get($user->id, collect());
}

// BAD: O(n²) - contains() is O(n), called n times
$existingEmails = User::pluck('email');
foreach ($newUsers as $userData) {
    if (!$existingEmails->contains($userData['email'])) {
        User::create($userData);
    }
}

// GOOD: O(n) - flip() for O(1) has() lookups
$existingEmails = User::pluck('email')->flip();
foreach ($newUsers as $userData) {
    if (!$existingEmails->has($userData['email'])) {
        User::create($userData);
    }
}

Index Strategies

Migration Best Practices

// Safe column addition (nullable first, then backfill)
public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->string('timezone')->nullable()->after('email');
    });

    // Backfill in chunks to avoid memory issues
    User::query()->chunk(1000, function ($users) {
        foreach ($users as $user) {
            $user->update(['timezone' => 'UTC']);
        }
    });
}

// Safe column removal (make nullable first in separate deploy)
public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn('legacy_field');
    });
}

Common Pitfalls

// 1. N+1 Queries - Always eager load relationships
// BAD
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name; // N+1!
}

// GOOD
$posts = Post::with('author')->get();

// 2. Missing Indexes - Add indexes for WHERE and ORDER BY
Schema::table('orders', function (Blueprint $table) {
    $table->index(['user_id', 'created_at']);
    $table->index('status');
});

// 3. SELECT * - Only select needed columns
// BAD
$users = User::all();

// GOOD
$users = User::select(['id', 'name', 'email'])->get();

// 4. No Chunking - Use chunk() for large datasets
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        // Process
    }
});

// 5. No Foreign Keys - Always use constraints
$table->foreignId('user_id')->constrained()->cascadeOnDelete();

// 6. No Transactions - Wrap related operations
DB::transaction(function () {
    $order = Order::create([...]);
    $order->items()->createMany([...]);
});

Package Integration

• beyondcode/laravel-query-detector - N+1 detection
• barryvdh/laravel-debugbar - Query profiling
• spatie/laravel-query-builder - API query building

Best Practices

• Design indexes based on actual queries
• Use foreign key constraints
• Monitor slow queries in production
• Use database transactions for related operations
• Prevent lazy loading in development: Model::preventLazyLoading(!app()->isProduction())

Query Debugging

// Enable query log
DB::enableQueryLog();

// Run your code
$posts = Post::with('author')->get();

// Dump queries
dd(DB::getQueryLog());

// Or use Laravel Debugbar / Telescope in development

Related Commands

• /laravel-agent:db:optimize - Analyze and optimize queries
• /laravel-agent:db:diagram - Generate schema diagrams

Related Agent

• laravel-database - Database optimization specialist