How to Get Raw SQL Query From Laravel Query Builder or Model

When working with Laravel’s Query Builder, you may sometimes need to retrieve the raw SQL query generated by your code. This can be useful for debugging or logging purposes, or when you want to analyze the exact SQL being executed against your database.

In Laravel, we can use ->toSql() to retrieve a query with? placeholders and ->getBindings() to obtain the corresponding values. To print the query as raw SQL, we can combine both of them using PHP’s vsprintf() function. This approach is suitable for both Eloquent and Query Builder instances.

The following code achieves exactly that, it gets toSql with bindings so you can print it as full raw SQL:

// Gets raw SQL from $query using `toSql` and `getBindings` and combines their results with `vsprintf`
$rawSql = vsprintf(str_replace(['?'], ['\'%s\''], $query->toSql()), $query->getBindings()));

The reason we need to combine toSql() and getBindings() is that Laravel uses prepared statements to query the database. For this reason, toSql() will contain the SQL query but will contain question marks instead of parameter values. While getBindings() will give an array of parameter values but without the SQL query. To combine them we can use the powerful vsprintf function. Look at the screenshot below which illustrates the differences accordingly:

Read on to learn how to use printing raw SQL with either Query Builder or an Eloquent Model. We’ll also learn how to enable the Laravel Query log to capture multiple queries. Finally, we’ll also learn a very handy method to show all queries in your browser using the Laravel Debugbar package.

Example 1: Printing Raw SQL Made by the Laravel Query Builder

The code below shows how to print raw SQL using Query Builder:

Route::get('/query1', function () {
    $query = DB::table('users')
        ->select('name', 'email')
        ->where('active', true)
        ->where('email', 'LIKE', 'info@%');

    // print SQL query (with ? placeholders)
    dump($query->toSql());
    
    // print SQL query parameter value array
    dump($query->getBindings());
    
    // print raw SQL query
    dump(vsprintf(str_replace(['?'], ['\'%s\''], $query->toSql()), $query->getBindings()));
});

In this example, we created a query using Query Builder which selects the name and email columns from the users table. We filtered by the active column being true and the email being all email addresses that start with “info@”.

Example 2: Printing Raw SQL Made by a Laravel Eloquent Model

Similar to Query Builder an Eloquent model instance supports calling the toSql() and getBindings() methods directly. We can use them to get our (raw) SQL. Here’s an example:

Route::get('/query2', function () {
    $user = User::where('active', true)
        ->where('email','LIKE',"info@%");

    // print SQL query (with ? placeholders)
    dump($user->toSql());

    // print SQL query parameter value array
    dump($user->getBindings());

    // print raw SQL query
    dump(vsprintf(str_replace(['?'], ['\'%s\''], $user->toSql()), $user->getBindings()));
});

In this example, we created a query using a Laravel Eloquent Model. This selects name and email columns from the users table where active equals true and email starts with “info@”.

Example 3: Printing Raw SQL Using DB::getQueryLog()

Laravel provides a handy method called getQueryLog() which allows you to retrieve an array of all executed queries. It captures queries generated by Query Builder as well as Eloquent. Here’s an example:

Route::get('/query3', function () {
    DB::enableQueryLog();

    // Execute your queries here
    DB::enableQueryLog();

    // Execute your queries here
    $queryBuilder = DB::table('users')
        ->select('name', 'email')
        ->where('active', true)
        ->where('email','LIKE',"info@%")
        ->get();
    $users = DB::table('users')
        ->select('name', 'email')
        ->where('active', true)
        ->where('email', 'LIKE', 'info@%')
        ->get();

    $queries = DB::getQueryLog();
    DB::disableQueryLog();

    echo 'Logged queries: <br>';
    dump($queries);
});

By calling enableQueryLog(), Laravel starts recording all executed queries. After executing your queries, you can retrieve the query log using getQueryLog() which returns an array containing the executed queries along with their parameters. Should you no longer require query logging for the current request you can call disableQueryLog() to switch it off.

Solution 4: Using the Laravel Debugbar Package

Another convenient way to obtain the raw SQL query is by using the Laravel Debugbar package made by Barry vd. Heuvel. This package provides a comprehensive debug toolbar for Laravel applications, including detailed information about executed queries. To install and use it, follow these steps:

1. Install the Laravel Debugbar package via Composer:

composer require barryvdh/laravel-debugbar --dev

2. Open your application in the browser, and you will see an icon in the bottom left corner. You may click this to open the DebugBar:

3. Click on the “Queries” tab to view the executed queries along with their raw SQL queries. Using one of the pages from one of our examples we can see the exact queries it ran:

Conclusion

Retrieving the raw SQL query from Laravel’s Query Builder or Eloquent models is helpful for debugging and understanding the exact SQL being executed against the database.

In this article, we explored several methods to obtain the SQL query, including using the toSql() method on both Query Builder and Eloquent instances, leveraging DB::getQueryLog(), and utilizing the Laravel Debugbar Package.

By using these techniques, you can efficiently troubleshoot and optimize your queries, enabling smoother development. Happy coding!