New Try managed PHP hosting on Amezmo for FREE with a $50 account credit. Create account →

How to Log SQL Queries in Laravel with a Query Logger Class

Amezmo on
Laravel SQL Query Logging

By default, Laravel does not log all SQL queries to a dedicated query log. However, it’s quite useful to know which queries run during the lifecycle of a request.

Of course, there are solutions to this problem already, but in this tutorial, you will learn how to log SQL queries in Laravel from a custom QueryLogger class.

The source code of this tutorial is available for your use on GitHub. In addition, your SQL query logger will have the following features:

  • A Unique ID identifying the group of queries per request
  • Full SQL Query and bindings
  • Timing information
  • Uses the default Laravel logger
Like this Laravel tutorial?
Sign up to receive latest Laravel tips and tutorials from Amezmo.

Step 1 – Create a Dedicated Query Log Class

In your Laravel application directory, create a directory that will contain the new class.

mkdir -p app/Logging && touch QueryLogger.php

Now that you’ve created a new directory and PHP file, open up your favorite text editor and compose your QueryLogger class. This class is instantiated only once per request.

app/Logging/QueryLogger.php
namespace App\Logging;

use Psr\Log\LoggerInterface;
use Ramsey\Uuid\Uuid;

class QueryLogger
{
    protected $debugId;
    protected $logger;

    /**
     * Create an instance of the QueryLogger class
     *
     * @param \Psr\Log\LoggerInterface $logger
     * @return void
     *
     * @throws \Ramsey\Uuid\Exception\UnsatisfiedDependencyException
     * @throws \InvalidArgumentException
     * @throws \Exception
     */
    public function __construct(LoggerInterface $logger)
    {
        $this->logger = $logger;
        $this->debugId = Uuid::uuid4()->toString();
    }

    public function __call($name, $arguments)
    {
        $this->logger->{$name}('['.$this->debugId.'] ' .$arguments[0], ...array_slice($arguments, 1));
    }
}

Step 2 – Register the Query Log Class With The Laravel Service Container

The next step requires modifying your AppServiceProvider and asking Laravel to tell you whenever a query has been executed.

You will ask Laravel to notify you whenever a QueryExecuted event has been fired and inside your callback execute a call to your QueryLogger class. The QueryExecuted event is a Laravel Internal database event and it contains the following properties:

  • The prepared SQL string of the query
  • An array of parameter bindings
  • The execution time in milliseconds

In your favorite text editor, open Providers/AppServiceProvider.php.

Add the following using statements to your AppServiceProvider.php file.

app/Providers/AppServiceProvider.php
use Illuminate\Contracts\Foundation\Application;
use Psr\Log\LoggerInterface;
use App\Logging\QueryLogger;

After that, register your QueryLogger class to the Laravel service container. Knowing that you only want a single instance of QueryLogger per request, register the QueryLogger class as a singleton to the Laravel Service Container.

Moreover, If you did not register it as a singleton, then you would not be able to associate multiple query log entries to the same request because the debugId would be different.

app/Providers/AppServiceProvider.php
$this->app->singleton(QueryLogger::class, function (Application $app) {
    return new QueryLogger($app->make(LoggerInterface::class));
});

Next, add the following using statements to your AppServiceProvider.php file

app/Providers/AppServiceProvider.php
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Events\QueryExecuted;

Finally, register an event listener with the DB class. Laravel fires this event after it executes a SQL query. In the closure that we provide, an instance of QueryExecuted is passed in with the context into which query ran, and which SQL parameters were provided.

app/Providers/AppServiceProvider.php
DB::listen(function(QueryExecuted $query) {
    /** @var \App\Logging\QueryLogger $logger */
    $logger = app()->make(\App\Logging\QueryLogger::class);
    $logger->debug($query->sql, [
        'execute_time_milliseconds' => $query->time,
        'params' => $query->bindings,
    ]);
});

Now that you created a custom sql query logging class in your Laravel application and setup an event listener for the QueryExecuted event, you’re ready to test it out.

Next, navigate to any route in your Laravel application that calls into your database. Then, confirm that you see an entry for your log by running this command from your Laravel root directory

tail -f storage/logs/laravel.log

You should see your log entries after loading any of your pages that call into your database along with a unique ID per set of queries. This is useful for grouping queries across your entire request lifecycle.

Conclusion

In this Laravel query log tutorial, you created a custom query logging class that writes SQL queries, parameters, and timing information to the default Laravel log channel.

You used an event listener to be notified when Laravel executes queries, and you registered a custom QueryLogger class to the Laravel service container.

Host and Deploy Your PHP Apps for Free
Sign up today and see if Amezmo is right for you.
Read more from Laravel