How to Spot N+1 SQL Query Problems Early for Laravel Projects

In this article, I will describe my favorite tools for spotting N+1 problems in database queries, for Laravel projects.

This will come in handy 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:

{
  "data": [
    {
      "temp": 18,
      "hardware_id": "8381fa1a-d2b3-3c67-815a-6884b80099d4",
      "location": "Rauport",
      "datetime": "2022-07-26 16:03:32"
    },
    {
      "temp": -8,
      "hardware_id": "e934c789-2326-37ab-82cc-37aec840fcff",
      "location": "Braxtonhaven",
      "datetime": "2022-07-26 16:05:02"
    },
    {
      "temp": -5,
      "hardware_id": "4694d32b-4ce2-3971-94f7-76664fbf872c",
      "location": "Thaliaberg",
      "datetime": "2022-07-26 16:09:59"
    }
  ]
}

I will have an N+1 query inside the 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

<?php

declare(strict_types=1);

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Device extends Model
{
    use HasFactory;

    protected $fillable = [
        'hardware_id',
        'location',
    ];

    public function samples(): HasMany
    {
        return $this->hasMany(Sample::class);
    }
}

Sample model with Device relation

<?php

declare(strict_types=1);

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Sample extends Model
{
    use HasFactory;

    public $timestamps = false;

    protected $fillable = [
        'device_id',
        'temp',
        'created_at',
    ];

    public function device(): BelongsTo
    {
        return $this->belongsTo(Device::class);
    }
}

Seeder class to have some data

<?php

declare(strict_types=1);

namespace Database\Seeders;

use App\Models\Device;
use App\Models\Sample;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run(): void
    {
        $devices = Device::factory()->count(20)->create();
        $ids = $devices->pluck('id');

        for ($i = 0; $i < 100; $i++) {
            Sample::factory()->create([
                'device_id' => $ids->random(1)->first(),
            ]);
        }
    }
}

JSON Resource

<?php

declare(strict_types=1);

namespace App\Http\Resources;

use App\Models\Sample;
use Illuminate\Contracts\Support\Arrayable;
use Illuminate\Http\Resources\Json\JsonResource;

/**
 * @property-read Sample $resource
 */
class SampleResource extends JsonResource
{
    public function toArray($request): array|\JsonSerializable|Arrayable
    {
        return [
            'temp'        => $this->resource->temp,
            'hardware_id' => $this->resource->device?->hardware_id,
            'location'    => $this->resource->device?->location,
            'datetime'    => $this->resource->created_at,
        ];
    }
}

Controller (broken version)

<?php

declare(strict_types=1);

namespace App\Http\Controllers;

use App\Http\Resources\SampleResource;
use App\Models\Sample;
use Illuminate\Http\Resources\Json\AnonymousResourceCollection;

class SampleController extends Controller
{
    public function last100(): AnonymousResourceCollection
    {
        return SampleResource::collection(
            Sample::latest()->limit(100)->get()
        );
    }
}

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)

<?php

declare(strict_types=1);

namespace App\Http\Controllers;

use App\Http\Resources\SampleResource;
use App\Models\Sample;
use Illuminate\Http\Resources\Json\AnonymousResourceCollection;

class SampleController extends Controller
{
    public function last100(): AnonymousResourceCollection
    {
        return SampleResource::collection(
            Sample::latest()->with(['device'])->limit(100)->get()
        );
    }
}

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

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:

<?php

/** @test */
public function last100_lazy_load_disabled_when_correct_request_then_has_expected_query_count(): void
{
    Model::preventLazyLoading();
    $this->seed(DatabaseSeeder::class);

    // act
    $this->withoutExceptionHandling();
    $response = $this->getJson(route('api.last100'));

    // assert
    $response->assertOk();
}

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

Laravel Debugbar test case:

<?php

/** @test */
public function last100_debugbar_when_correct_request_then_has_expected_query_count(): void
{
    $this->seed(DatabaseSeeder::class);
    $debugbar = new LaravelDebugbar();
    $debugbar->boot();

    // act
    $this->getJson(route('api.last100'));

    // assert
    $queryCount = count($debugbar->collect()['queries']['statements']);
    $this->assertSame(2, $queryCount);
}

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

Telescope test case:

<?php

/** @test */
public function last100_telescope_when_correct_request_then_has_expected_query_count(): void
{
    // phpunit.xml: change => <env name="TELESCOPE_ENABLED" value="true"/>
    $this->seed(DatabaseSeeder::class);
    /** @var EntriesRepository $storage */
    $storage = resolve(EntriesRepository::class);

    // act
    $this->getJson(route('api.last100'));

    // assert
    $entries = $storage->get(
        EntryType::QUERY,
        (new EntryQueryOptions())->limit(100)
    );
    // finds all queries executed in SampleResource file
    $queryCount = $entries->filter(fn($e) => str_contains($e->content['file'], 'SampleResource'))
        ->count();
    $this->assertSame(0, $queryCount);
}

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:

<?php

/** @test */
public function last100_dbquerylog_when_correct_request_then_has_expected_query_count(): void
{
    $this->seed(DatabaseSeeder::class);

    // act
    DB::enableQueryLog();
    $this->getJson(route('api.last100'));
    DB::disableQueryLog();

    // assert
    $queryLog = DB::getQueryLog();
    $queryCount = collect($queryLog)->filter(
        fn($log) => str_contains($log['query'], 'select * from "devices" where "devices"."id"')
    )->count();
    // we expected only 1 query for all devices
    $this->assertSame(1, $queryCount);
}

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.

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 the actual number of queries.

Also published here.