Adding bound toSql() to laravel

Often when debugging I find myself using ->toSql() to get the SQL query string used by the query builder, to see if it looks correct. Sadly this does not work when the query includes parameters as these are only used for binding in PDO.

It would be nice to be able to get the query with the parameters as well, so let's add that to laravel!

But how?!

Luckily laravel supports macros, which lets us easily extend various built in classes in laravel, without having to edit the vendor source files (Which can be overwritten every time we run composer update).

You can add a macro inside a service providers boot method. It works by calling the static macro() method on the class we wish to extend. The first parameter is the new method name, and the second parameter is a callback method that we call as if it was a native method on the underlying class. Since it is being run from the class, we have access to $this inside the callback!

Add a service provider

First add a service provider that can hold the new macros.

php artisan make:provider MacroServiceProvider

This will give you a clean service provider like this

 1<?php
 2 
 3namespace App\Providers;
 4 
 5use Illuminate\Support\ServiceProvider;
 6 
 7class MacroServiceProvider extends ServiceProvider
 8{
 9    /**
10     * Register services.
11     *
12     * @return void
13     */
14    public function register()
15    {
16        //
17    }
18 
19    /**
20     * Bootstrap services.
21     *
22     * @return void
23     */
24    public function boot()
25    {
26        //
27    }
28}

Add the macro

We delete the register method since we don't need it, and add the following code to the boot method

 1Builder::macro('toBoundSql', function () {
 2    /* @var Builder $this */
 3    $bindings = array_map(
 4        fn ($parameter) => is_string($parameter) ? "'$parameter'" : $parameter,
 5        $this->getBindings()
 6    );
 7 
 8    return Str::replaceArray(
 9        '?',
10        $bindings,
11        $this->toSql()
12    );
13});
14 
15EloquentBuilder::macro('toBoundSql', function () {
16    return $this->toBase()->toBoundSql();
17});

The end result should look like this.

 1<?php
 2 
 3namespace App\Providers;
 4 
 5use Illuminate\Support\Str;
 6use Illuminate\Database\Query\Builder;
 7use Illuminate\Support\ServiceProvider;
 8use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
 9 
10class MacroServiceProvider extends ServiceProvider
11{
12    /**
13     * Bootstrap services.
14     *
15     * @return void
16     */
17    public function boot()
18    {
19        Builder::macro('toBoundSql', function () {
20            /* @var Builder $this */
21            $bindings = array_map(
22                fn ($parameter) => is_string($parameter) ? "'$parameter'" : $parameter,
23                $this->getBindings()
24            );
25 
26            return Str::replaceArray(
27                '?',
28                $bindings,
29                $this->toSql()
30            );
31        });
32 
33        EloquentBuilder::macro('toBoundSql', function () {
34            return $this->toBase()->toBoundSql();
35        });
36 
37    }
38}

There are two macros with the same name. The first is for binding the method to the DB query builder, that binding is what makes all of this work. However, we are not quite there yet, this won't work with eloquent as it will just return the string to eloquent. That means that we will output the eloquent builder class object, instead of a string. To fix this, we add the second macro, which will bind directly to eloquent. All it does is get the string by calling the underlying query builder, and return the result as a string.

Let's try it out!

To test it out, you can add the following

This is what it looks like without the new macros.

1dd(\App\Models\User::where('id', '>'. 100)->latest()->toSql());

It gives us this SQL.

1select * from `users` where `id` = ? and `users`.`deleted_at` is null order by `created_at` desc

After we added the new macros.

1dd(\App\Models\User::where('id', '>', 100)->latest()->toBoundSql());

This should give us

1select * from `users` where `id` > 100 and `users`.`deleted_at` is null order by `created_at` desc

Or we can try the DB version

1dd(\DB::table('users')->where('id', '>', 100)->latest()->toBoundSql());

Which will give us

1select * from `users` where `id` > 100 order by `created_at` desc

Conclusion

As you can see adding new methods to laravel is quite easy, and getting a better SQL output only takes a few lines of code. Even though packages like Debugbar or Clockwork handle this already, I personally like being able to get the raw query inside of tinker or a command.