How to Retrieve Raw SQL Query Strings from Laravel Query Builder

Overview

In Laravel, the query builder can output its raw SQL query as a string using the toSql() method. Here’s how you can do it:

Example:

use Illuminate\Support\Facades\DB;  

// Example query 
$query = DB::table('users')->where('status', 'active');  

// Get the raw SQL query 
$sql = $query->toSql();  

echo $sql;

Explanation:

The toSql() method does not bind the query parameters to the SQL string. Instead, it provides the raw SQL with placeholders (?) for bindings.
If you want to see the fully interpolated SQL with bindings included, you need to manually replace the placeholders with the actual bindings.

Including Bindings (Optional)

You can combine the SQL and its bindings to get a complete representation:

$query = DB::table('users')->where('status', 'active');  

// Get the raw SQL query and bindings 
$sql = $query->toSql(); 
$bindings = $query->getBindings();  

// Replace placeholders with bindings 
$finalSql = vsprintf(str_replace('?', "'%s'", $sql), array_map('addslashes', $bindings));  

echo $finalSql;

Note This way, you can see the full SQL query with actual values in place. However, note that this method is only for debugging or logging purposes and should not be used in production directly.

There are alternative ways to retrieve or inspect the raw SQL query in Laravel. Here are some other methods:

1. Using `DB::listen`

You can listen to all database queries executed by Laravel and log or inspect them:

use Illuminate\Support\Facades\DB;
DB::listen(function ($query) {
    // The raw SQL query     
    echo $query->sql;

     // Bindings for the query     
     print_r($query->bindings);

     // Query execution time     
     echo $query->time; 
});

Place this in a service provider’s boot method (e.g., AppServiceProvider) or before the query execution for debugging purposes.

2. Using Laravel Debugbar (Package)

If you install the Laravel Debugbar package, it will automatically log all SQL queries and display them in the browser toolbar.

composer require barryvdh/laravel-debugbar --dev

Once installed and configured, you can view all executed queries directly in the debug bar.

3. Using the Query Log

You can enable Laravel’s query log to capture all executed queries:

use Illuminate\Support\Facades\DB;

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

// Execute your query 
DB::table('users')->where('status', 'active')->get();

// Get the logged queries 
$queries = DB::getQueryLog();

print_r($queries);

Each query in the log will include the raw SQL, bindings, and execution time.

4. Custom Helper for Full SQL

Create a helper function to retrieve the full SQL query with bindings interpolated:

use Illuminate\Database\Query\Builder; 

function getFullSql(Builder $query) {
    $sql = $query->toSql();
    $bindings = $query->getBindings();
    return vsprintf(str_replace('?', "'%s'", $sql), array_map('addslashes', $bindings));
}

// Usage 
$query = DB::table('users')->where('status', 'active');

echo getFullSql($query);

This helper can make it easier to inspect queries with bindings.

5. Telescope (Official Package)

Laravel’s Telescope is an official package for debugging and monitoring your application. It tracks all queries executed by your application:

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

After setting it up, you can view all queries (with their bindings) in the Telescope dashboard.

These approaches provide flexibility depending on your use case, whether you want to log queries, inspect them during development, or debug specific queries.

Overview#

Example:#

Explanation:#

Including Bindings (Optional)#

1. Using DB::listen#

2. Using Laravel Debugbar (Package)#

3. Using the Query Log#

4. Custom Helper for Full SQL#

5. Telescope (Official Package)#