Debugging Laravel: Printing SQL Queries with Ease

As developers working on Laravel projects, there are moments when we need to delve into the inner workings of SQL queries. Printing the SQL query along with its bindings becomes crucial for debugging or running queries in SQL editors like Sequel Ace to verify expected results.

Laravel, although not having this feature out of the box (as of Laravel 9), provides methods like toSql() for printing queries without bindings and getBindings() for printing bindings separately. Let's explore how to print them together using a simple example:

Example Query:

$query = UserModel::where('email', 'name@example.com');

Using toSql() and getBindings():

$rawQuery = str_replace('?', "'?'", $query->toSql());
logger()->info(vsprintf(str_replace('?', '%s', $rawQuery), $query->getBindings()));

This will output:

 select * from `users` where `email` = 'name@example.com' and `users`.`deleted_at` is null

Macroable Trait for Convenience:

For a more convenient and reusable approach, you can create a Macroable Trait. In your AppServiceProvider:

Builder::macro('toSqlWithBindings', function () {
    $rawQuery = str_replace('?', "'?'", $this->toSql());

    return vsprintf(str_replace('?', '%s', $rawQuery), $this->getBindings());
});

Now, you can use it seamlessly in your code:

UserModel::where('email', 'name@example.com')->toSqlWithBindings(); 

This will output the same SQL query as before.

Conclusion:

By incorporating these techniques, you can easily print SQL queries with bindings, empowering you to debug and optimize your Laravel applications effectively.

Happy coding!