Home » Laravel » How to Get Raw SQL Query in Laravel

How to Get Raw SQL Query in Laravel

Laravel uses the Eloquent ORM to build and execute database queries. It allows you to build complex queries with multiple conditions, grouping, and joining multiple tables. Most of the queries you may need can be built using Eloquent methods, so you almost never need to write SQL code.

However, sometimes you may need to know which SQL query is sent to the database to debug query errors. In this article, I will explain how to get raw SQL query in Laravel.

Table of Contents

How to Get Raw SQL Query in Laravel

1. toSql()

The easiest way to debug queries in Eloquent is to use the toSql() method after building the query instead of get(), paginate(), first(), or another method to retrieve data. For example:

app/Console/Commands/PrintRawSqlCommand.php$query = \App\Models\User::query()->where("email", "admin@haait.net"); dump($query->toSql());

2. toRawSql()

The disadvantage of the method shown above is that it only prints the query template without data inserted. If you want to see the entire query, with the data already filled in, you should use the toRawSql() method:

app/Console/Commands/PrintRawSqlCommand.php$query = \App\Models\User::query()->where("email", "admin@haait.net"); dump($query->toRawSql());

This method was added in Laravel 10.13. You can also use the dumpRawSql() or ddRawSql() method instead of calling dd() to print query SQL in the console:

app/Console/Commands/PrintRawSqlCommand.php$query = \App\Models\User::query()->where("email", "admin@haait.net"); $query->dumpRawSql(); //$query->ddRawSql();

3. DB::getQueryLog()

If you need to get SQL for several queries at once or if your query contains preloaded relations using the with() method, it is better to use the database query log. First, you need to enable it by calling DB::enableQueryLog(true). Then execute all queries and get the result using DB::getQueryLog(). For example:

app/Console/Commands/PrintRawSqlCommand.php\DB::enableQueryLog(true). \App\Models\User::query()->where("email", "admin@haait.net")->get(); \App\Models\User::query()->where("email", "support@haait.net")->get(); dump(\DB::getQueryLog());

The result will look like this:

If you want to get SQL queries with the data already filled in, you should use the DB::getRawQueryLog() method:

app/Console/Commands/PrintRawSqlCommand.php\DB::enableQueryLog(true). \App\Models\User::query()->where("email", "admin@haait.net")->get(); \App\Models\User::query()->where("email", "support@haait.net")->get(); dump(\DB::getRawQueryLog());

4. Telescope

The laravel/telescope package allows you to view not only the history of HTTP requests to Laravel but also the database queries that have been executed. If you don’t have it installed yet, you can do so with the following command:

composer require laravel/telescope --dev

After that, you execute the telescope:install command and then run migrations:

php artisan telescope:install php artisan migrate

For this example, let’s create an endpoint that finds users by email and displays information about them. Then, make a request to this endpoint:

routes/api.phpRoute::get("/admin", function(){    return \App\Models\User::query()->where("email", "admin@haait.net")->get(); });

Next, open the Telescope web interface at the following URL: http://application_domain/telescope. Select the request you want to view information about and go to the Queries tab at the bottom of the page:

You can click and see the details and the data for each database query:

Wrapping Up

In this article, we learned how to get raw SQL query in Laravel using dd(), the query log, or Telescope.

Leave a Comment

Exit mobile version