Using Laravel to format SQL statements

I was doing performance testing on a Laravel application and wanted to see the SQL queries that were being executed. As I was running the tests in a terminal, I wanted to see the queries in a nicely formatted way.

I found a package that does this, but it's not Laravel-specific. I wanted to use Laravel's query builder to format the queries, so I had to figure out how to do that.

First, install the required package called doctrine/sql-formatter:

composer require "doctrine/sql-formatter"

Then given an SQL string with placeholders and an array of bindings, we can combine them into a single SQL string using some Laravel internals:

1$builder = DB::query();
2$grammar = $builder->grammar;
3$connection = $builder->connection;
4
5$sql = $grammar->substituteBindingsIntoRawSql(
6    $query->sql,
7    $connection->prepareBindings($query->bindings)
8);

Formatting the SQL string can then be done like this:

1use Doctrine\SqlFormatter\SqlFormatter;
2
3$sql = (new SqlFormatter())->format($sql);

If you put all that into your AppServiceProvider.php boot method, you can do something like this (based on my previous post about this):

 1namespace App\Providers;
 2
 3use Doctrine\SqlFormatter\CliHighlighter;
 4use Doctrine\SqlFormatter\SqlFormatter;
 5use Illuminate\Database\Events\QueryExecuted;
 6use Illuminate\Support\Facades\App;
 7use Illuminate\Support\Facades\DB;
 8use Illuminate\Support\Facades\Log;
 9use Illuminate\Support\ServiceProvider;
10
11class AppServiceProvider extends ServiceProvider
12{
13    public function register(): void
14    {
15    }
16
17    public function boot(): void
18    {
19        DB::listen(function ($query) {
20            $grammar = $query->connection->getQueryGrammar();
21
22            $sql = $grammar->substituteBindingsIntoRawSql(
23                $query->sql,
24                $query->connection->prepareBindings($query->bindings)
25            );
26
27            $sql = (new SqlFormatter(new CliHighlighter()))->format($sql);
28
29            $label = "{$query->time} ms";
30            if ($query->time > 100) {
31                $label = "\033[31m{$label} ms | SLOW\033[0m";
32            }
33
34            Log::debug("{$label}\n\n{$sql}");
35        });
36    }
37}

Then just tail your logs in the terminal and you'll see nicely formatted SQL queries.