Getting Started

Minimal Setup

Installation:

composer require benjdiasaad/laravel-db-monitor
php artisan vendor:publish --tag=db-monitor-config
php artisan vendor:publish --tag=db-monitor-migrations
php artisan migrate

Verify the config/db-monitor.php is published and configured (e.g., slow_query_threshold, n_plus_one_threshold).

Enable Middleware: Add Benjdiasaad\LaravelDbMonitor\Middleware\MonitorQueries::class to your app/Http/Kernel.php under $middleware (for global monitoring) or $middlewareGroups (e.g., web).
First Use Case: Trigger a slow query or N+1 issue in your app (e.g., manually loop through a collection with ->each() or force a slow query with DB::select('SELECT SLEEP(2)')). Check the dashboard at /db-monitor or run:
```
php artisan db:report
```
- The CLI report will show slow queries, N+1 issues, and missing index suggestions.

Implementation Patterns

1. Real-Time Monitoring Workflow

Middleware Integration:
- Use MonitorQueries middleware globally or selectively (e.g., only for API routes).
- Example: Add to app/Http/Kernel.php:
```
protected $middleware = [
    // ...
    \Benjdiasaad\LaravelDbMonitor\Middleware\MonitorQueries::class,
];
```
- Tip: Exclude admin routes or health checks by adding them to $except in the middleware.
Query Tagging: Tag queries for better organization (e.g., by feature or module). Use the tag() method in queries:
```
DB::select('SELECT * FROM users', ['tag' => 'auth']);
```
- View tagged queries in the dashboard or CLI report.

2. N+1 Detection and Fixes

Identify N+1 Issues: Run php artisan db:report or check the dashboard. N+1 issues appear under the "N+1 Queries" section with details like:
```
Query: SELECT * FROM posts WHERE user_id = ?
Parent Query: SELECT * FROM users
Occurrences: 5
```

Fix Patterns:

Eager Loading: Replace ->each() with ->with() or ->load().

// Before (N+1)
$users = User::all();
foreach ($users as $user) {
    $user->posts; // Triggers N+1 queries
}
// After (Fixed)
$users = User::with('posts')->get();

Use cursor() for Large Datasets:

User::cursor()->each(function ($user) {
    // Avoids loading all users into memory
});

3. Slow Query Optimization

Threshold Configuration: Set slow_query_threshold in config/db-monitor.php (default: 100ms). Adjust based on your app’s needs.
```
'slow_query_threshold' => 200, // Log queries slower than 200ms
```
Query Analysis: The dashboard shows:
- Execution time.
- SQL query.
- Bindings.
- Suggested optimizations (e.g., "Add index on email column").
Generate Index Migrations: Use the db:generate-index Artisan command:
```
php artisan db:generate-index --query="SELECT * FROM users WHERE email = ?"
```
- This creates a migration file with the suggested index.

4. Alerting and Notifications

Configure Alerts: Set up email or Slack alerts in config/db-monitor.php:

'alerts' => [
    'email' => true,
    'slack' => [
        'enabled' => true,
        'webhook_url' => env('SLACK_WEBHOOK_URL'),
    ],
],

Trigger Alerts Manually: Use the alert helper to notify admins:

\Benjdiasaad\LaravelDbMonitor\Facades\DbMonitor::alert(
    'High query load detected!',
    'Slow queries exceeded threshold.'
);

5. CLI Reporting

Generate Reports:

php artisan db:report --days=7       # Report for last 7 days
php artisan db:report --format=json  # Export as JSON

Automate Reports: Add a cron job to run daily reports:

0 3 * * * cd /path-to-project && php artisan db:report --days=1 >> /var/log/db_monitor.log

6. Integration with Laravel Debugbar

Enable Debugbar Integration: If using barryvdh/laravel-debugbar, add this to config/db-monitor.php:
```
'debugbar' => true,
```
- Slow queries will appear in the Debugbar under the "Queries" tab.

Gotchas and Tips

1. Performance Overhead

Middleware Impact: The MonitorQueries middleware adds minimal overhead (~1-2ms per request), but avoid using it in high-frequency loops (e.g., cron jobs or queue workers).
- Workaround: Exclude middleware for specific routes or use conditional logic:
```
if (!app()->runningUnitTests()) {
    $middleware = [MonitorQueries::class];
}
```
Disable in Production (Temporarily): Set enabled to false in config/db-monitor.php during high-traffic periods:
```
'enabled' => env('DB_MONITOR_ENABLED', true),
```

2. Database-Specific Quirks

MySQL vs. PostgreSQL:
- MySQL: Use EXPLAIN in the dashboard for query optimization tips.
- PostgreSQL: Ensure pg_stat_statements is enabled for accurate metrics.
```
-- Enable in PostgreSQL
CREATE EXTENSION pg_stat_statements;
```
SQLite Limitations: SQLite doesn’t support all monitoring features (e.g., EXPLAIN ANALYZE). Use the package for basic query logging.

3. Query Tagging Pitfalls

Tagging Dynamic Queries: Avoid tagging queries dynamically if the tag changes per request (e.g., tag => request()->ip). Use static tags for consistency:
```
DB::select('SELECT * FROM users', ['tag' => 'user-list']);
```
Missing Tags: Untagged queries appear as <untagged>. Ensure critical queries are tagged for better organization.

4. Index Generation Issues

Migration Conflicts: If you manually create an index after generating a migration, delete the old migration file to avoid conflicts:
```
rm database/migrations/xxxx_create_index_on_users_email.php
```
Composite Indexes: The db:generate-index command may not always suggest composite indexes. Manually review EXPLAIN output for complex queries.

5. N+1 Detection Edge Cases

False Positives: The package may flag intentional N+1 patterns (e.g., pagination with ->with()). Exclude such queries by tagging them:
```
DB::select('SELECT * FROM posts', ['tag' => 'pagination-safe']);
```
- Add pagination-safe to ignored_tags in config/db-monitor.php:
```
'ignored_tags' => ['pagination-safe'],
```
Lazy Collections: N+1 issues may not appear if using cursor() or lazy(). Ensure you’re testing with eager-loaded collections.

6. Logging and Storage

Log Pruning: The package uses Laravel’s Prunable trait to auto-delete old logs. Adjust prune_after_days in config/db-monitor.php:
```
'prune_after_days' => 30, // Delete logs older than 30 days
```
Storage Engine: Use mysql or pgsql for the db_monitor table (avoid SQLite for production).

7. Debugging Tips

Disable All Monitoring: Set enabled: false in config/db-monitor.php to debug issues without logging queries.
Check Raw Logs: Inspect the db_monitor_queries table directly for missing or corrupted entries:
```
SELECT * FROM db_monitor_queries ORDER BY created_at DESC LIMIT 10;
```
Clear Cache: If changes to config aren’t reflected, run:
```
php artisan config:clear
php artisan cache:clear
```

8. Extending the Package

Custom Alert Logic: Extend the `Benjdiasaad\

Laravel Db Monitor Laravel Package