Laravel Query Monitor Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require --dev supliu/laravel-query-monitor
   php artisan vendor:publish --provider="Supliu\LaravelQueryMonitor\ServiceProvider"
   

   • Verify the config/laravel-query-monitor.php file is published.

2. First Run:

   php artisan laravel-query-monitor
   

   • Open your Laravel app in a browser or trigger API requests while the monitor runs in the terminal.

3. Key Files:

   • Config: config/laravel-query-monitor.php (host/port customization).
   • Service Provider: Supliu\LaravelQueryMonitor\ServiceProvider (registers the monitor).

First Use Case

Debugging a Slow API Endpoint:

• Start the monitor (php artisan laravel-query-monitor).
• Hit the problematic endpoint (e.g., POST /api/orders).
• Observe real-time queries in the terminal, including:
  • SQL statements.
  • Execution time.
  • Bindings (if enabled in config).

Implementation Patterns

Core Workflow

1. Development Cycle:

   • Pre-Request: Launch the monitor in a terminal tab before testing.
   • Execution: Trigger actions (e.g., CRUD operations, batch jobs).
   • Analysis: Review queries for:
     • N+1 problems (eager load missing?).
     • Inefficient joins/subqueries.
     • Unnecessary queries (e.g., SELECT *).
   • Optimization: Refactor based on findings (e.g., add with() clauses, use whereHas()).

2. Integration with Testing:

   • Unit/Feature Tests:

     // In a test case, use the monitor to verify query count/structure.
     $this->withoutExceptionHandling();
     $this->get('/test-route');
     // Check terminal output for expected queries.
     

   • CI/CD: Exclude the monitor from production builds (installed via --dev).

3. Team Collaboration:

   • Pair Debugging: Share terminal output via screensharing during code reviews.
   • Documentation: Add monitor screenshots to PRs for complex queries (e.g., "This endpoint fires 3 queries; see monitor output").

Advanced Patterns

1. Filtering Queries:

   • Use config to ignore specific queries (e.g., migrations, seeders):

     'ignored_queries' => [
         'prisma:migrate',
         'db:seed',
     ],
     

2. Performance Benchmarking:

   • Compare query counts/timings across branches:

     # Branch A
     php artisan laravel-query-monitor --port=8081
     # Branch B
     php artisan laravel-query-monitor --port=8082
     

3. Event-Driven Monitoring:

   • Hook into Laravel events to log queries for specific actions:

     // In EventServiceProvider
     public function boot()
     {
         Event::listen('eloquent.created: App\Models\User', function ($user) {
             // Monitor will capture this query automatically.
         });
     }
     

4. Custom Output Formatting:

   • Extend the monitor’s output by modifying the QueryMonitor class (see app/Providers/QueryMonitorServiceProvider.php for overrides).

Gotchas and Tips

Pitfalls

1. Port Conflicts:

   • Default port 8081 may clash with other services.
   • Fix: Change port in config and CLI:

     php artisan laravel-query-monitor --port=9090
     

     Update config/laravel-query-monitor.php to match.

2. Missing Bindings:

   • By default, bindings are hidden for security. Enable in config:

     'show_bindings' => env('APP_ENV') !== 'production',
     

3. High Query Volume:

   • Real-time output can flood the terminal for batch jobs.
   • Workaround: Use --limit=10 (if supported) or filter queries in config.

4. Socket Connection Issues:

   • If the monitor fails to connect, check:
     • Firewall rules (allow 0.0.0.0:<port>).
     • Laravel’s APP_URL (must be accessible from the monitor’s host).

5. Eager Loading Pitfalls:

   • Monitor may show redundant queries if with() is misused:

     // Bad: Still fires N+1 for comments.
     $posts = Post::with('comments')->get();
     foreach ($posts as $post) {
         $post->comments->load('user'); // Extra query!
     }
     

Debugging Tips

1. Query Not Showing?:

   • Ensure the monitor is running before the request.
   • Check if the query is ignored (see ignored_queries in config).

2. Slow Queries:

   • Sort terminal output by execution time (if supported) or use:

     php artisan tinker
     >>> \DB::enableQueryLog();
     >>> $user = User::find(1);
     >>> \DB::getQueryLog(); // Inspect manually.
     

3. Binding Values:

   • Enable show_bindings in config to debug parameterized queries:

     -- Before:
     SELECT * FROM users WHERE email = ?
     -- After:
     SELECT * FROM users WHERE email = 'user@example.com'
     

Extension Points

1. Custom Query Formatting:

   • Override the QueryMonitor class to highlight slow queries:

     // app/Providers/QueryMonitorServiceProvider.php
     public function register()
     {
         $this->app->bind('query-monitor', function () {
             return new class extends \Supliu\LaravelQueryMonitor\QueryMonitor {
                 protected function formatQuery($query, $time)
                 {
                     $highlight = $time > 100 ? 'bgRed' : '';
                     return "<span class='$highlight'>$query</span> ($time ms)";
                 }
             };
         });
     }
     

2. Log to File:

   • Extend the monitor to log queries to a file:

     // In QueryMonitor class
     public function logQuery($query, $time, $bindings)
     {
         file_put_contents(
             storage_path('logs/query_monitor.log'),
             "$query ($time ms)\n",
             FILE_APPEND
         );
     }
     

3. Slack/Teams Notifications:

   • Trigger alerts for slow queries via webhooks:

     // In QueryMonitor class
     public function handleQuery($query, $time)
     {
         if ($time > 500) {
             $this->notifySlack($query, $time);
         }
     }