Laravel: Spot N+1 SQL Query Problems Early
In this article, I will describe my top favorite tools for spotting N+1 problems in database queries for Laravel projects.
This will be helpful when developing new projects or when debugging slow responses from any legacy code you’d have to work with.
Example objective
We have devices in the wild (Device model) reporting temperatures (Sample model). Every sample is stored in a database.
Our task: create an API to return JSON with the last 100 samples across the whole platform and provide the device’s hardware ID and location for every sample.
Response structure:
I will have an N+1 query inside resource mapper object on purpose, then we’ll look into ways to debug this type of problem and catch them early.
Global outlook
Device model
Sample model with Device relation
Seeder class to have some data
JSON Resource
Controller (broken version)
You can see here we introduced an N+1 query problem. The fix, in this case, is very easy and will just be adding ->with(['device']).
Controller (fixed version)
Our options
For handling this kind of problem we have those options:
- Laravel 8, Laravel 9+ - disabled lazy load
- Laravel Debugbar
- Telescope
- DB query log
Laravel 8, Laravel 9 — disabled lazy loading
https://laravel.com/docs/9.x/eloquent-relationships#preventing-lazy-loading
A good option when you’re starting from scratch.
Enable this globally for non-production environments.
That’s all you will ever need.
Not an option if you inherit an already large project, so you can start disabling lazy loading in your newer test cases (and some older ones) and see if it breaks.
Example test case:
While our route is not fixed we’ll get a failing test.
Illuminate\Database\LazyLoadingViolationException : Attempted to lazy load [device] on model [App\Models\Sample] but lazy loading is disabled.
All further mentioned packages are useful in day-to-day development and their APIs can be injected into tests to find the bottlenecks and explore how your app behaves.
Laravel Debugbar
https://github.com/barryvdh/laravel-debugbar
Laravel Debugbar test case:
We expect our route to only use 2 queries: 1st would be to select 100 samples, second to select all devices with whereIn(…devices_id…).
While our route is not fixed we’ll get a failing test.
Failed asserting that 101 is identical to 2.
PRO TIP:
Use DEBUGBAR_ENABLED=false within your phpunit.xml or .env.testing so tests don’t have additional debug overhead (when not required).
Telescope
https://laravel.com/docs/9.x/telescope
Telescope test case:
We expected our SampleResource file to not execute any queries. When running this test with our broken controller we get a fail:
Failed asserting that 100 is identical to 0.
PRO TIP:
Use TELESCOPE_ENABLED=false within your phpunit.xml or .env.testing so tests don’t have additional debug overhead (when not required).
BUT you need it set to true for my example test case to work.
Let me know if you have a solution to this, I want TELESCOPE_ENABLED=false but enabled on an ad-hoc basis during the test case.
DB facade — query log
Using a DB facade we can enable query log on an ad-hoc basis and get a hint on what’s going on during our request.
DB facade test case:
We expect only 1 query that retrieves all devices, but we get 100:
Failed asserting that 100 is identical to 1.
The sample repository for this article can be found here https://github.com/dkhorev/spot-n-plus-1-sql-query-problems-early
Bonus: have test coverage for query count
Just an idea — if you want to be sure your API will not introduce N+1 problems during refactoring and/or new release — have them test covered with similar cases that compare the expected number of queries to actual.
