When working with Laravel, you will probably use the query builder in order to create and run database queries.

Sometimes you want to see the raw SQL output of a query, for example when you are debugging.

In Laravel, this is possible in several ways.

The query builder

First, let us write a query to demonstrate a use case. For this post, I made a demo project that has a User, Order, and Payment model. The DemoController contains this query:

use App\User;

User::whereHas(
    'orders.payments',fn ($q) => $q->where('amount', '>', 400)
)->get();

It returns all the users that have made payments where the amount is higher than 400.

Get raw SQL output of a query

The toSql() method

The easiest way to get the raw SQL output of this query, is by using the toSql() method:

use App\User;

User::whereHas(
    'orders.payments',fn ($q) => $q->where('amount', '>', 400)
)->toSql();

This will result in:

select * from `users`
where exists (
    select * from `orders`
    where
        `users`.`id` = `orders`.`user_id`
        and exists (
            select * from `payments`
            where `orders`.`id` = `payments`.`order_id`
            and `amount` > ?
        )
)

It is fast and simple, but as you can see, the bind parameter for amount > 400 is shown as amount > ?.

This can be resolved with the getBindings() method.

You can replace the bindings manually, but @lorismatic created a very nice toSqlWithBindings() macro for the query builder to replace the occurrences of ?.

When you implement this, you can run:

use App\User;

User::whereHas(
    'orders.payments',fn ($q) => $q->where('amount', '>', 400)
)->toSqlWithBindings();

This will replace the binding amount > ? with amount > 400.

For obvious reasons, be very careful with getting raw SQL output in other environments than your local environment.

Enabling the query log

Another way to get the raw query output, is to enable the query log.

DB::enableQueryLog();

User::whereHas(
    'orders.payments',fn ($q) => $q->where('amount', '>', 400)
)->get();

return DB::getQueryLog();

This will show all the queries that are executed after enabling the log, including an array with the bindings.

It might be a fast and easy method to get what you need, but it could also be overkill and take too much of your resources.

Listening for database events

You can also capture queries by listening for database events in the boot() method of your AppServiceProvider:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

DB::listen(function ($query) {
    Log::info($query->sql);
    Log::info($query->bindings);
});

This will return the query, and the bindings. Of course, you could replace the occurrences of ? with the values from the $query->bindings array, in the same way as in the toSqlWithBindings macro.

Debugging tools that can show raw SQL

If you don't want to get the raw SQL output of a query programmatically, there are quite some tools out there that can do this for you.

I will mention some of them.

Debugbar

Laravel Debugbar is a very popular package that integrates PHP Debug Bar with Laravel.

After installing, it will be loaded automatically and look like this:

When you load the page that somehow executes a query with the query builder, the raw SQL will be shown in the Debugbar, with the bindings included.

Telescope

Another tool you can use is Laravel Telescope, that is part of the Laravel Ecosystem.

After installation, you can access Telescope by the /telescope route.

One of the aspects of your application it watches and logs are the raw queries that are executed. They can be shown like this:

Ray

Another debugging tool you could use is Ray, that is developed by Spatie. It looks like a beautiful tool, and one of its features is showing queries.

I hope this post helped you in finding a method to debug queries in your Laravel application. Please let me know if you know of any other useful methods.