🐥 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:

<?php
$builder = DB::query();
$grammar = $builder->grammar;
$connection = $builder->connection;

$sql = $grammar->substituteBindingsIntoRawSql(
    $query->sql,
    $connection->prepareBindings($query->bindings)
);

Formatting the SQL string can then be done like this:

<?php
use Doctrine\SqlFormatter\SqlFormatter;

$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):

<?php
namespace App\Providers;

use Doctrine\SqlFormatter\CliHighlighter;
use Doctrine\SqlFormatter\SqlFormatter;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\App;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function register(): void
    {
    }

    public function boot(): void
    {
        DB::listen(function ($query) {
            $grammar = $query->connection->getQueryGrammar();

            $sql = $grammar->substituteBindingsIntoRawSql(
                $query->sql,
                $query->connection->prepareBindings($query->bindings)
            );

            $sql = (new SqlFormatter(new CliHighlighter()))->format($sql);

            $label = "{$query->time} ms";
            if ($query->time > 100) {
                $label = "\033[31m{$label} ms | SLOW\033[0m";
            }

            Log::debug("{$label}\n\n{$sql}");
        });
    }
}

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