Laravel 4 profiler – Log and display SQL queries

Laravel 3 had a cool profiler bundle called Anbu, written by Daylee Rees. It was written as a bundle but Taylor, creator of Laravel, liked it so it was eventually included in the Laravel core. Laravel 4, at the time of this writing, however has no such profiler included in the core. So how do we display database SQL queries for debugging?

Here are a couple of solutions:

1. Use Loic Sharma’s Profiler. It is a PHP 5.3 profiler based off of Laravel 3’s Anbu. I have not tried this yet so just follow the instructions there.

2. Try Profiler by Juy, a PHP 5.3 profiler for Laravel 4. Backend based on sorora/omni, fronted based on loic-sharma/profiler, some features inspirated from papajoker/profiler, some features written by Juy.

3. Create an event that will listen to illuminate.query. Open your routes.php file and add this at the bottom:

 Event::listen('illuminate.query', function($sql)
 {
     var_dump($sql);
 }); 

This will print every query on the screen on the place where it is called so it might not look pretty,especially in loops. Also you might not see the queries on the screen in some cases since they will be “hidden” in the HTML source code (so just view the page source to see them, they will usually be at the top of the page)

4. You can also display the last executed query by placing a code in front of the code that executes a query. For example let say we have this code in our controller:

// Grab all the users
$customers = Customer::orderBy('name','asc')->get();

Place the Event::listen code in front of that statement and you will see that query displayed on your screen.

// dump the next executed query and die (dd)
Event::listen('illuminate.query', function($sql)
{
    dd($sql);
}); 

// Grab all the users
$customers = Customer::orderBy('name','asc')->get();

Keep in mind that this will only display 1 query. If your statement calls more queries they will not get displayed.

5. Put the following code in the App::before filter to get a dump of your database queries:

DB::listen(function($sql, $bindings, $time)
{
    var_dump($sql);
    var_dump($bindings);
});

6. There is another “trick” that I use sometimes (because having SQL queries displayed all the time is not practical and it messes up the code and design): I intentionally make an error in my SQL statement. For example here I mistyped type as tyspe:

$contracts = Contract::where('tyspe','=',0)->orderBy('note','asc')->get();

Since tyspe doesn’t exist Laravel returns an error,showing me the complete SQL statement:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘tyspe’ in ‘where clause’ (SQL: select * from `contracts` where `tyspe` = ? order by `note` asc) (Bindings: array ( 0 => 0, ))

Let me know if you know of any other solutions in the comments.

Share this page:Share on RedditDigg thisShare on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on StumbleUponShare on TumblrBuffer this pageEmail this to someone

8 comments

  1. Jules César - July 5, 2013 1:56 am

    Thanks dude!

    Reply
  2. Brian - July 28, 2013 5:07 pm

    I read that Dayle was working on a profiler for Laravel 4, although, I have not unconfirmed this. I use Sublime Text 3, so I just created a snippet based on your tip 4 with a tab trigger.

    So, after I type “sql” and hit tab, I get: Event::listen(‘illuminate.query’, function($sql) {dd($sql);});

    Thanks for the tips!

    Reply
    • El Coderino - August 3, 2013 7:56 pm

      Great, happy to hear this!

      Reply
  3. Biswarup Adhikari - September 23, 2013 5:56 pm

    Thanks

    Reply
  4. Adam - September 30, 2013 2:13 am

    Here is a quick Javascript snippet you can throw onto your master page template.
    As long as it’s included, all queries will be output to your browser’s Javascript Console.
    It prints them in an easily readable list, making it simple to browse around your site and see what queries are executing on each page.

    When you’re done debugging, just remove it from your template.

    var queries = {{ json_encode(DB::getQueryLog()) }};
    console.log(‘/****************************** Database Queries ******************************/’);
    console.log(‘ ‘);
    $.each(queries, function(id, query) {
    console.log(‘ ‘ + query.time + ‘ | ‘ + query.query + ‘ | ‘ + query.bindings[0]);
    });
    console.log(‘ ‘);
    console.log(‘/****************************** End Queries ***********************************/’);

    Reply
    • Jules César - October 15, 2013 3:40 am

      Thanks! I got to remove Profiler by Juy, it hangs when using Model::paginate()

      Reply
  5. Vegibit - January 22, 2014 6:37 pm

    Really helpful, thanks! Now I know how the Event Listener works :-)

    Reply
  6. Vaibhav Yadav - September 11, 2014 7:00 pm

    helpful..:)

    Reply

Have your say