Better SQL tracing with DBIx::Class

I've been trying to track down some SQL issues with Tau Station and to be honest, I've never been terribly happy with the output from the DBIx::Class DBIC_TRACE or the DBI DBI_TRACE. So I have something better.

You need to install my Data::Record module (think "split" on steroids) and the Regexp::Common module.

use Data::Record;
use Regexp::Common;

my $schema = ...;

open my $fh, '>', $file
  or die "Cannot open $file for writing: $!";
my $split_sql = Data::Record->new(
    {   split  => ":",
        unless => $RE{quoted},
        limit  => 2,
my $split_params = Data::Record->new(
    {   split  => ",",
        unless => $RE{quoted},
my $split_placeholders = Data::Record->new(
    {   split  => "?",
        unless => $RE{quoted},
        chomp  => 0,

my ( %seen_sql, %seen_with_params );

# turn on debugging

# hey, it allows a callback!
    sub ( $op, $info ) {
        my ( $sql, $params ) = $split_sql->records($info);
        my $seen = ++$seen_sql{$sql};
        chomp( my @params = $split_params->records($params) );

        # it's a hack, but it seems to work well
        my @fragments = $split_placeholders->records($sql);
        my $error;
        FRAGMENT: foreach (@fragments) {
            next unless /\?$/;
            unless (@params) {
                $error = '-- Found more placeholders than params';
                last FRAGMENT;
            my $param = shift @params;
        $sql = join '' => @fragments;
        if (@params) {
            $error = '-- Found fewer placeholders than params';
        my $seen_with_params = ++$seen_with_params{$sql};
        my $times            = 1 == $seen ? 'time' : 'times';
        my $times_params     = 1 == $seen_with_params ? 'time' : 'times';
        say $fh $error if $error;
        say $fh
          "-- Seen $seen $times ($seen_with_params $times_params with params)";
        say $fh "$sql;";

This code hooks into th DBIx::Class::Storage debug system and for each SELECT, INSERT, UPDATE, or DELETE, it substitutes the query params into the SQL so that every line in the debug file contains an SQL statement you can cut 'n paste directly into the database client. Before each SQL statement is an SQL comment indicating how many times that SQL was seen, along with how many times it was seen with those exact parameters.

So instead of this:

SELECT COUNT( * ) FROM character_skills me WHERE ( me.character_id = ? ): '17065'

You get this:

-- Seen 131 times (2 times with params)
SELECT COUNT( * ) FROM character_skills me WHERE ( me.character_id =  '17065' )

Here's an example from our full test suite run (wrapped and truncated to fit):

-- Seen 2871 times (1835 times with params)
SELECT ... FROM station_area me 
WHERE ( ( me.area_id =  '3' AND me.station_id =  '351' ) );

That's a beautiful target for optimizing because that data never changes.

Hire Me

Drop me a line at ovid at if you'd like top-notch training or software development. Depending on your needs, we provide individual developers in a variety of languages (both computer and spoken), or entire teams with project managers


Besides the nifty counter, isn't this the same as $ENV{DBIC_TRACE_PROFILE}=console ?

ok, DBIC_TRACE_PROFILE also won't work with plain DBI (I guess)..

I did something similar with: - timing information (average and max query time, sum of times) - an EXPLAIN for each query (so you could easily detect potential troublesome queries) - beautify the query (since lots of times I would have to beautify it manually) with SQL::Beautify

Leave a comment

About Ovid

user-pic Freelance Perl/Testing/Agile consultant and trainer. See for our services. If you have a problem with Perl, we will solve it for you. And don't forget to buy my book!