Laravel Query Detector Laravel Package

Getting Started

1. Installation:

   composer require beyondcode/laravel-query-detector --dev
   

   The package auto-registers in Laravel (Lumen requires manual registration in bootstrap/app.php).

2. First Use Case:

   • Run your app in debug mode (default behavior).
   • Trigger a route that loads models with relationships (e.g., User::with('posts')->get()).
   • Observe real-time alerts (browser notifications, console logs, or Debugbar) when N+1 queries are detected.

3. Where to Look First:

   • Config: Publish the config (php artisan vendor:publish --provider="BeyondCode\QueryDetector\QueryDetectorServiceProvider").
   • Outputs: Check config/querydetector.php for available outputs (Alert, Console, Debugbar, etc.).
   • Threshold: Adjust threshold (default: 1) to control sensitivity (e.g., threshold: 3 ignores queries executed ≤3 times).

Implementation Patterns

1. Daily Workflow Integration

• Local Development: Use Alert or Console outputs to catch N+1 queries during feature development. Example: Add User::with('posts')->get() to a controller and see alerts for missing eager loading.

• API Development: Enable Json output to log N+1 warnings in API responses (useful for GraphQL/REST endpoints).

  // config/querydetector.php
  'output' => [
      \BeyondCode\QueryDetector\Outputs\Json::class,
  ],
  

• Testing: Disable in production by setting enabled: false in .env or config.

  QUERY_DETECTOR_ENABLED=false
  

2. Team-Wide Adoption

• Whitelisting: Exclude known-optimized relationships (e.g., polymorphic morphTo or cached queries).

  // config/querydetector.php
  'except' => [
      User::class => [
          Post::class,
          'posts',
      ],
  ],
  

• Custom Events: Listen to \BeyondCode\QueryDetector\Events\QueryDetected for advanced integrations (e.g., Slack alerts, Sentry logging).

  // app/Listeners/LogNPlusOneToSentry.php
  public function handle(QueryDetected $event) {
      Sentry::captureMessage("N+1 Query Detected: {$event->query}");
  }
  

3. CI/CD Enforcement

• GitHub Actions: Fail builds if N+1 queries exceed a threshold (requires custom script or Json output parsing).

  # .github/workflows/performance.yml
  - name: Check N+1 Queries
    run: |
      php artisan query:detect | grep -q "N+1" && exit 1 || exit 0
  

• Debugbar for QA: Use Debugbar output in staging to audit query patterns before production.

  'output' => [
      \BeyondCode\QueryDetector\Outputs\Debugbar::class,
  ],
  

4. Advanced Debugging

• Clockwork Integration: Log N+1 queries to Clockwork for detailed request analysis.

  'output' => [
      \BeyondCode\QueryDetector\Outputs\Clockwork::class,
  ],
  

  Requires itsgoingd/clockwork.

• Dynamic Thresholds: Adjust thresholds per environment (e.g., stricter in CI).

  QUERY_DETECTOR_THRESHOLD=0  # Fail on any N+1 in CI
  

Gotchas and Tips

Pitfalls

1. False Positives:

   • Issue: Legitimate queries (e.g., polymorphic morphTo) may trigger alerts.
   • Fix: Whitelist them in config/querydetector.php under except.
   • Example:

     'except' => [
         Activity::class => [
             Post::class,
             'post',
             Comment::class,
             'comment',
         ],
     ],
     

2. Debugbar Conflicts:

   • Issue: Debugbar output may not appear if multiple Debugbar packages (e.g., barryvdh/laravel-debugbar vs. fruitcake/laravel-debugbar) are installed.
   • Fix: Use the runtime-resolved facade in v2.3.0+ or explicitly require one version.

3. Lumen Quirks:

   • Issue: Lumen requires manual provider registration.
   • Fix: Add to bootstrap/app.php:

     $app->register(\BeyondCode\QueryDetector\LumenQueryDetectorServiceProvider::class);
     

4. JSON Output Edge Cases:

   • Issue: Non-HTML responses (e.g., API calls) may not show alerts by default.
   • Fix: Ensure Json output is enabled and responses are parsed correctly.

5. Backtrace Noise:

   • Issue: Stack traces may include irrelevant frames (e.g., Laravel service container).
   • Fix: Updated in v1.8.0 to ignore invalid backtrace sources.

Debugging Tips

1. Isolate Queries:

   • Temporarily set threshold: 0 to catch all N+1 queries during debugging.

   QUERY_DETECTOR_THRESHOLD=0
   

2. Log-Only Mode:

   • Use Log output to persist findings for later review:

   'output' => [
       \BeyondCode\QueryDetector\Outputs\Log::class,
   ],
   

3. Clockwork Deep Dives:

   • Combine with Clockwork to correlate N+1 queries with specific request timings.

4. Custom Outputs:

   • Extend the package by creating a custom output class (e.g., for Datadog or New Relic).

Extension Points

1. Event Listeners:

   • Trigger custom logic (e.g., Slack notifications) via QueryDetected events.
   • Example listener:

     public function handle(QueryDetected $event) {
         if ($event->count > 5) {
             Slack::alert("Critical N+1: {$event->query}");
         }
     }
     

2. Dynamic Whitelisting:

   • Use middleware to conditionally enable/disable detection:

     public function handle(Request $request, Closure $next) {
         config(['querydetector.enabled' => $request->user()->isAdmin()]);
         return $next($request);
     }
     

3. Performance Budgets:

   • Integrate with tools like spatie/laravel-performance-monitor to correlate N+1 queries with overall request latency.

4. Testing:

   • Mock the detector in unit tests to verify query optimization:

     $this->expectsEvents(QueryDetected::class);