Log All Executed SQL Queries to File in Laravel

One of the ways to debug Laravel application is logging all executed SQL queries. After that, we can analyze queries, find bugs and optimize the performance of a Laravel application.

This tutorial shows how to log all executed SQL queries to a file in Laravel application.

One of the easiest ways to log queries is to register the query listener by using DB::listen method. This can be done in the boot method of a AppServiceProvider class located at app/Providers directory. Listener is provided as callback which is invoked for each time when SQL query was executed.

app/Providers/AppServiceProvider.php

<?php

namespace App\Providers;

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    // ...

    public function boot(): void
    {
        DB::listen(function(QueryExecuted $event) {
            Log::info(
                'SQL Query',
                [
                    $event->sql,
                    $event->bindings,
                    $event->time,
                ]
            );
        });
    }
}

Log entries are written to a laravel.log file located at the storage/logs directory.

storage/logs/laravel.log

[2020-12-30 03:17:47] local.INFO: SQL Query ["select * from `projects` where `id` = ? limit 1",["4"],13.08]

The log entry contains the SQL query, an array of query bindings and query execution time in milliseconds.

Another way to log queries is to create the query listener as a separate class and register it in a $listen array of a EventServiceProvider class. This way is recommended because code for query logging is stored in a separate file.

Execute the following command to generate listener class:

php artisan make:listener QueryExecutedListener

This command creates the QueryExecutedListener class located at app\Listeners directory. The handle method contains code to log queries.

app\Listeners\QueryExecutedListener.php

<?php

namespace App\Listeners;

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\Log;

class QueryExecutedListener
{
    public function handle(QueryExecuted $event): void
    {
        Log::info(
            'SQL Query',
            [
                $event->sql,
                $event->bindings,
                $event->time,
            ]
        );
    }
}

Query listener must be registered in the EventServiceProvider class. The handle method will be executed when the QueryExecuted event is triggered.

app/Providers/EventServiceProvider.php

<?php

namespace App\Providers;

use App\Listeners\QueryExecutedListener;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Foundation\Support\Providers\EventServiceProvider as ServiceProvider;

class EventServiceProvider extends ServiceProvider
{
    // ...

    protected $listen = [
        QueryExecuted::class => [
            QueryExecutedListener::class,
        ],
    ];
}

SQL queries can be logged in a separate file. Since Laravel 5.6 we create custom log channel which can be specified in config\logging.php file.

config/logging.php

<?php
// ...

return [
    // ...

    'channels' => [
        // ...

        'sql_query' => [
            'driver' => 'single',
            'path' => storage_path('logs/query.log'),
            'level' => env('LOG_LEVEL', 'debug'),
        ],
    ],
];

We created sql_query log channel. Entries will be written to a query.log file located at the storage/logs directory.

We can use the Log::channel method to specify which channel should be used for logging.

app\Listeners\QueryExecutedListener.php

<?php

namespace App\Listeners;

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\Log;

class QueryExecutedListener
{
    public function handle(QueryExecuted $event): void
    {
        Log::channel('sql_query')->info(
            'SQL Query',
            [
                $event->sql,
                $event->bindings,
                $event->time,
            ]
        );
    }
}