Laravel 5: How to dump SQL query?

Question

Laravel 5's built-in solution

In Laravel 5+, we can use \DB::getQueryLog() to retrieve all executed queries. Since, query logging is an extensive operation and cause performance issues so it's disabled by default in L5 and only recommend for development environments only. We can enable the query logging by using the method \DB::enableQueryLog(), as mentioned in [Laravel's documentation][1].

Problem in built-in solution

The DB::getQueryLog() function is great but sometimes we wish that it would be great if we get dump in flat SQL format, so we can copy/past it in our favorite MySQL application like phpMyAdmin or Sqlyog to execute it and debug or optimize it.

So, I need a helper function that helps me to produce dump with following additional info:

• On which file and line number the dump has called.
• Remove back-ticks from the query.
• Flat query, so don't need to update binding parameters manually and I can copy/past SQL in phpMyAdmin etc to debug/optimize the query.

Hyehyena

Answer 1

Custom Solution

Step 1: Enable Query Logging

Copy/past following block of code on top of route file:

# File: app/Http/routes.php
if (\App::environment( 'local' )) { 
   \DB::enableQueryLog();
}

Step 2: Add helper function

if (!function_exists( 'dump_query' )) {
function dump_query( $last_query_only=true, $remove_back_ticks=true ) {

    // location and line
    $caller = debug_backtrace( DEBUG_BACKTRACE_IGNORE_ARGS, 1 );
    $info = count( $caller ) ? sprintf( "%s (%d)", $caller[0]['file'], $caller[0]['line'] ) : "*** Unable to parse location info. ***";

    // log of executed queries
    $logs = DB::getQueryLog();
    if ( empty($logs) || !is_array($logs) ) {
        $logs = "No SQL query found. *** Make sure you have enabled DB::enableQueryLog() ***";
    } else {
        $logs = $last_query_only ? array_pop($logs) : $logs;
    }

    // flatten bindings
    if (isset( $logs['query'] ) ) {
        $logs['query'] = $remove_back_ticks ? preg_replace( "/`/", "", $logs['query'] ) : $logs['query'];

        // updating bindings
        $bindings = $logs['bindings'];
        if ( !empty($bindings) ) {
            $logs['query'] = preg_replace_callback('/\?/', function ( $match ) use (&$bindings) {
                return "'". array_shift($bindings) . "'";
            }, $logs['query']);
        }
    }
    else foreach($logs as &$log) {
        $log['query'] = $remove_back_ticks ? preg_replace( "/`/", "", $log['query'] ) : $log['query'];

        // updating bindings
        $bindings = $log['bindings'];
        if (!empty( $bindings )) {
            $log['query'] = preg_replace_callback(
                '/\?/', function ( $match ) use ( &$bindings ) {
                return "'" . array_shift( $bindings ) . "'";
            }, $log['query']
            );
        }
    }

    // output
    $output = ["*FILE*" => $info,
               '*SQL*' => $logs
    ];

    dump( $output );
}

}

How to use?

Take dump of last executed query, use just after the query execution:

dump_query();

Take dump of all executed queries use:

dump_query( false );

Hyehyena

Answer 2

• On which file and line number the dump has called.

I don't understand why you need this because you always know where you called the dump function but never mind you have your solution for that.

• Remove back-ticks from the query.

You don't need to remove back-ticks as the query will work in MySQL along with them also.

• Flat query, so don't need to update binding parameters manually and I can copy/past SQL in phpMyAdmin etc to debug/optimize the query.

You can use vsprintf for binding parameters as:

$queries = DB::getQueryLog();

foreach ($queries as $key => $query) {
    $queries[$key]['query'] = vsprintf(str_replace('?', '\'%s\'', $query['query']), $query['bindings']);
}

return $queries;

And I would suggest you to checkout this github repo squareboat/sql-doctor

Varia

By removing back-ticks, the generated SQL will be easy to read and debug, as the primary job of this function is to debug. Thanks for vsprintf function, yes it will help me to reduce the code. – Hyehyena

Answer 3

I was looking for simple solution and the one below worked for me.

DB::enableQueryLog();

User::find(1); //Any Eloquent query

// and then you can get query log

dd(DB::getQueryLog());

Reference Links:

• How to Get the Query Executed in Laravel 5? DB::getQueryLog() Returning Empty Array
• https://www.codegrepper.com/code-examples/php/dump+sql+query+laravel

Viddah

Answer 4

Add this code in the top of your routes file. Laravel 5.2 routes.php Laravel 5.3+ web.php

<?php
// Display all SQL executed in Eloquent

Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
    var_dump($query->sql);
    var_dump($query->bindings);
    var_dump($query->time);
    echo "<br><br><br>";
});

Buffer

Answer 5

For a Laravel 8 application it could be useful to put the following in the AppServiceProvider.php file:

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    // [...]
    // Dump SQL queries on demand **ONLY IN DEV**
    if (env('APP_ENV') === 'local') {
        DB::enableQueryLog();
        Event::listen(RequestHandled::class, function ($event) {
            if ( $event->request->has('sql-debug') ) {
                $queries = DB::getQueryLog();
                Log::debug($queries);
                dump($queries);
            }
        });
    }

    // [...]
}

Then appending &sql-debug=1 to the url will dump the queries.

Camp