How to View Raw SQL Query Generated by Laravel Query Builder

Introduction

Laravel’s Query Builder provides developers with a convenient way of creating database queries using an object-oriented PHP methodology. While this tool makes it easier to write database queries without writing SQL code, there may be times when you need to inspect the raw SQL query that Laravel generates to debug or optimize your database interactions.

This tutorial will guide you through the process of viewing the raw SQL queries crafted by Laravel’s Query Builder, including several ways you can use to get the underlying SQL statements for different types of queries.

Enabling the Query Log

The first step in debugging your queries is to enable the query log. Laravel offers a method called enableQueryLog that you can use on the DB facade. This method turns on the logging of all queries that are executed by the application. To view the log, you will use the getQueryLog method.

// Enable query log
DB::enableQueryLog();

// Execute some queries
User::where('votes', '>', 100)->get();

// Get the query log
$queries = DB::getQueryLog();
print_r($queries);

Once you have executed the queries you’re interested in inspecting, call getQueryLog, and Laravel dumps an array with the queries that have been run. Do note, however, that in a production environment, the query log can grow very quickly and consume a large amount of memory. Therefore, it should not be left on regularly.

Using the toSql Method

Laravel’s Query Builder provides a toSql() method which you can chain onto your queries to get the SQL statement, parameter bindings are not included though. This can be exceptionally useful during development or debugging.

$sql = User::where('votes', '>', 100)->toSql();

// Output the SQL statement
echo $sql;

// The output will be something like:
// select * from `users` where `votes` > ?

The placeholders shown are where bindings are inserted into the query when it is run.

Adding Bindings to Your Output

While toSql() does not include the actual values that are bound to the placeholders in your queries, you can retrieve these bindings separately and combine them with your SQL to see the full query.

$query = User::where('votes', '>', 100);
$sql = $query->toSql();
$bindings = $query->getBindings();

// Combine SQL and bindings
foreach ($bindings as $binding) {
    $value = is_numeric($binding) ? $binding : "'". $binding . "'";
    $sql = preg_replace("/\?/", $value, $sql, 1);
}

// Output the complete SQL statement
echo $sql;

Remember to handle escaping of values properly to avoid issues when combining the values with the SQL query. Laravel’s Query Builder automatically handles this for you during actual query execution, but in this manual combination for viewing, it’s worth being cautious.

Using Laravel Telescope

Laravel Telescope is an elegant debug assistant for Laravel that provides insight into queries, requests, exception, and a lot more. After installing Telescope, every query executed by Laravel will be logged, and you can view detailed information about each, including raw SQL, execution time, and the result.

composer require laravel/telescope
php artisan telescope:install
php artisan migrate

After installing, you can visit /telescope in your application to see detailed logs of your application’s queries. Within the “Queries” section, there’s a feature that lets you view the query’s raw SQL.

Database Events

Laravel fires database events for each query it executes. You can listen for these events and output the SQL from there. This might be appropriate if you wish to log specific queries under certain circumstances only.

DB::listen(function ($query) {
    Log::info("Query: {$query->sql} with ". implode(', ', $query->bindings));
});

Place the listener in any of your service provider’s boot methods.

Third-Party Packages

There are packages developed by the Laravel community that make it easy to see database queries, such as Debugbar.

composer require barryvdh/laravel-debugbar

After installing and enabling Debugbar, it will display a debug panel at the bottom of your app, where you can see information about the queries executed, among many other things.

Final Words

With these tools at your disposal, viewing the raw SQL queries generated by Laravel’s Query Builder is a breeze, giving you deeper insights for optimizing your application’s database interactions.

In summary, use DB::enableQueryLog() for quick checks, toSql() and bindings for seeing query templates quickly during development, employ Telescope or Debugbar for a more comprehensive debugging experience, and use database events for conditional or more granular logging of your application’s queries.