Debugging Query in Laravel

It is very useful for a developer to trace the each query that runs in the server including the performance by the time the query executed.

There is a lot of package/library that can suit to your requirement which can just plug and play. But some of developer just to make their own solution.

Therefore, Laravel have an event called QueryExecuted. This event return such as time execution, sql command and sql bindings. Click here for more information about QueryExecuted.

Usage

Create a listener class

To create listener class, run command php artisan make:listener MyQueryListener --event=QueryExecuted. Find and open MyQueryListener , it will show something like below

public function handle(QueryExecuted $event) 
{
     
}

2. Register Event to the Listener

Once listener created, you need to register the event in EventServiceProvider class.

protected $listen = [
    QueryExecuted::class => [
         MyQueryListener::class
    ]
];

3. Handling event response

Handling the event response, just call the related information that you want. For example like below:

public function handle(QueryExecuted $event) 
{
     $connection = $event->connectionName;
     $sql = $event->sql;
     $time = $event->time; in milliseconds
     ....
     ....
}

Now, It’s depend to developer itself either to log or to store it.

For information, $event->sql will return string without bindings. For example

$user = User::query()->where(‘full_name’, $name)->get();

The output is like below

select * from `users` where `full_name` = ?

You might be wondering, what is the value of ? if there a lot of queries log/stored. You can replace all the ? sign with the value, so that its easier for developer to read.

So create a function call replaceBindings .

public function replaceBindings($event)
{
    $sql = $event->sql;

    foreach ($event->connection->prepareBindings($event->bindings) as $key => $binding) {
        $regex = is_numeric($key)
            ? "/\?(?=(?:[^'\\\']*'[^'\\\']*')*[^'\\\']*$)/"
            : "/:{$key}(?=(?:[^'\\\']*'[^'\\\']*')*[^'\\\']*$)/";

        if ($binding === null) {
            $binding = 'null';
        } elseif (!is_int($binding) && !is_float($binding)) {
            $binding = $event->connection->getPdo()->quote($binding);
        }

        $sql = preg_replace($regex, $binding, $sql, 1);
    }

    return $sql;
}

What this function do is to transform from this

select * from `users` where `full_name` = ?

select * from `users` where `full_name` = "john doe"

Much readable right ? 😎

But how about if you want to know which function/class runs the query? Which lines? which files?

It can be solve by extracting debug_backtrace function.

$trace = collect(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS))->forget(0)->first();
$trace['line'] -> Will give the code line
$trace['file'] -> Will give the file name

Tada! Awesome right? By right, it should see any log entries after loading any query that call into database.

Conclusion