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 ( ) reporting temperatures ( ). Every sample is stored in a database. Device model Sample model Our task: create an API to return with the last 100 samples across the whole platform and provide the device’s hardware ID and location for every sample. JSON 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 https://laravel.com/docs/9.x/eloquent-relationships#preventing-lazy-loading?embedable=true 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 https://github.com/barryvdh/laravel-debugbar?embedable=true 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 within your or so tests don’t have additional debug overhead (when not required). DEBUGBAR_ENABLED=false phpunit.xml .env.testing Telescope https://laravel.com/docs/9.x/telescope?embedable=true 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 file to not execute any queries. When running this test with our broken controller we get a fail: SampleResource **_Failed asserting that 100 is identical to 0._** PRO TIP: Use within your or so tests don’t have additional debug overhead (when not required). TELESCOPE_ENABLED=false phpunit.xml .env.testing you need it set to for my example test case to work. BUT true Let me know if you have a solution to this, I want but enabled on an ad-hoc basis during the test case. TELESCOPE_ENABLED=false 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.

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Composer Packages that Every Laravel Developer Must Have

10 Cool CI/CD Tools For Your Project

27 Stories To Learn About Ssl

20 Essential Backend Tools For Developers

3 Most Common Ways to Connect your Node and React Applications

369 Stories To Learn About Database

Composer Packages that Every Laravel Developer Must Have

10 Cool CI/CD Tools For Your Project

27 Stories To Learn About Ssl

20 Essential Backend Tools For Developers

3 Most Common Ways to Connect your Node and React Applications

369 Stories To Learn About Database

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps