Get raw SQL query from Laravel Query Builder

To get raw SQL query from Laravel query builder, you can use toSql() method on the query builder instance. Alternatively, you can use the enableQueryLog method to enable query logging in Laravel and call the getQueryLog method on the DB facade to get the logged queries.

Using toSql() method

To get the raw SQL output of a query, you can use the toSql() method.

Let us take an example, you have a query builder instance as given below.

$query = DB::table('products')
            ->where('price', '>', 100)
            ->toSql();

The above query builder fetches all the products whose price is greater than 100 from the products table. This will give us a string of raw SQL built with Laravel query builder. In the above example, the value for $query will be:

select * from `products` where `price` > ?

The ? symbol is a placeholder for the value of 100, which will be added to the query when it is executed.

You can use this raw SQL output for debugging purposes and can also show the raw SQL output in the browser or log it to a file. Keep in mind that the toSql() method simply returns the SQL string, the query is not get executed until calling the get() or first() methods.

Enabling the log

Another way to get raw SQL query from Laravel Query Builder is by enabling the query log using enableQueryLog method. If query logging is enabled, then every SQL query that your application executes will be stored in the memory by Laravel. To return all the logged queries, use getQueryLog function on the DB facade.

Let us explain in detail.

To enable query logging, call the enableQueryLog method on the DB facade as given below.

DB::enableQueryLog();

This will enable query logging for all queries executed by the DB facade. The getQueryLog function on the DB facade can be used to get all the logged queries once query logging has been enabled.

$queries = DB::getQueryLog();

This will give us a list of all the logged queries in the order they were executed in an array. The array contains the query, bindings, and the number of milliseconds it took to execute the query. Then, you can use this array for debugging purposes.

If your application is large and you are running a lot of queries then query logging can take a lot of memory. Therefore, you should only enable query logging only when you need it to troubleshoot a problem. After troubleshooting you should disable query logging. For disabling the query log, you can use the DB facade's disableQueryLog function as given below.

DB::disableQueryLog();

Listen to the database events

Another option is to listen to the database events and write them into a log file.

Here is a sample code.

File path: app\Providers\AppServiceProvider.php

<?php

namespace App\Providers;

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

class AppServiceProvider extends ServiceProvider
{
    public function register()
    {
        //
    }

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

In the above example, we have listened to database events in the boot() method of AppServiceProvider and logged into a log file located inside the storage folder.

Log file path: storage\logs\laravel.log

Conclusion

To get raw SQL query from the query builder, use toSql() method on the query builder instance. Another option is to use the enableQueryLog method on the DB facade to enable query logging and call the getQueryLog method to get the logged queries. Alternatively, you can listen to the database events in boot() method of AppServiceProvider and write them into a log file.