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.

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 );