Laravel: List all queries of an SQL transaction with DB::listen()

When an SQL transaction becomes complex, it can be difficult to understand exactly which queries are executed and in what order. Laravel allows you to listen to all SQL queries using the DB::listen() method.

• analyze the queries that are actually executed,
• measure their execution time,
• detect unnecessary or slow queries,
• optimize application performance.

Example of listening to queries inside a transaction

$queries = []; 

DB::listen(function ($query) use (&$queries) { 
    $queries[] = [ 
        'sql' => $query->sql, 
        'bindings' => $query->bindings, 
        'time_ms' => $query->time, 
    ]; 
}); 

try { 
    DB::beginTransaction(); 

    // Execute the queries to analyze here 
    User::get(); 
    Post::where('active', true)->get(); 

    DB::commit(); 
} catch (\Exception $e) { 
    DB::rollBack(); 
    throw $e; 
} 

// Display queries 
foreach ($queries as $i => $q) { 
    echo "#{$i} ({$q['time_ms']} ms) {$q['sql']}"; 
    echo "Bindings: " . json_encode($q['bindings'], JSON_UNESCAPED_UNICODE); 
} 
Code language: PHP (php)

Result obtained:

• the raw SQL query,
• the parameters (bindings),
• the execution time in milliseconds.

Warning: DB::listen() should not be used in production, because it intercepts all SQL queries and can significantly impact performance.
It is recommended to enable it only in the local environment:

if (app()->environment('local')) { 
    DB::listen(function ($query) { 
        logger()->info($query->sql, $query->bindings); 
    }); 
}
Code language: PHP (php)

Tip: rebuild the full SQL query

To display the SQL query with the actual parameter values:

$sql = vsprintf(str_replace('?', '%s', $query->sql), $query->bindings); 
echo $sql;
Code language: PHP (php)

Conclusion

Using DB::listen() is an excellent tool to understand what really happens inside an SQL transaction. It allows you to:

• debug unexpected behavior,
• optimize performance,
• visualize hidden queries generated by the ORM.

It is a simple but very powerful method for analyzing SQL queries in Laravel.