The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBI-Log-0.12
River stage zero No dependents
/ DBI::Log

NAME

DBI::Log - Log all DBI queries

SYNOPSIS

    use DBI::Log;

or

    perl -MDBI::Log path/to/script.pl

DESCRIPTION

You can use this module to log all queries that are made with DBI. You can include it in your script with `use DBI::Log` or use the -M option for perl to avoid changing your code at all.

By default, it will send output to STDERR, which is useful for command line scripts and for CGI scripts since STDERR will appear in the error log.

You can control where output goes, and various other behaviour, by setting the options documented below.

OPTIONS

Options can be set on the use DBI::Log` line when loading the module:

    use DBI::Log timing => 1, file => "/tmp/querylog.sql";

or passed to -M e.g.:

    perl -M'DBI::Log timing => 1' path/to/script.pl

The following options are available:

file

Set the file option to send query logs to the named file instead of STDERR.

    use DBI::Log file => "~/querylog.sql";

Each query in the log is prepended with the date and the place in the code where it was run from. You can add a full stack trace by setting the trace option.

trace

Include a stack trace with each query, so you can see where the code which performed the query was called from:

    use DBI::Log trace => 1;
timing

If you want timing information about how long the queries took to run add the timing option.

    use DBI::Log timing => 1;
exclude

If you want to exclude function calls from within certain package(s) appearing in the stack trace from trace, you can use the exclude option like this:

    use DBI::Log exclude => ["DBIx::Class"];

It will exclude any package starting with that name, for example DBIx::Class::ResultSet and DBI::Log are excluded by default.

format

By default the log is formatted as SQL, so if you look at it in an editor, it might be syntax highlighted. Additional information about the query is added as SQL comments.

This is what the output may look like:

    -- Fri Sep 11 17:31:18 2015
    -- execute t/test.t 18
    CREATE TABLE foo (a INT, b INT)

    -- Fri Sep 11 17:31:18 2015
    -- do t/test.t 21
    INSERT INTO foo VALUES ('1', '2')

    -- Fri Sep 11 17:31:18 2015
    -- selectcol_arrayref t/test.t 24
    SELECT * FROM foo

    -- Fri Sep 11 17:31:18 2015
    -- do t/test.t 27
    -- (eval) t/test.t 27
    INSERT INTO bar VALUES ('1', '2')

JSON output is also available, enable it by setting the format option to json e.g.:

    use DBI::Log format => "json";

Query logs will then be emitted in "line-delimited JSON" format, where each record is a JSON object, separated by newlines - this format is useful if you want to post-process the information - for example, using jq to get only queries which took longer than a second:

    jq 'select(.time_taken >= 1)' < querylog.json
replace_placeholders

By default, this module replaces placeholders in the query with the values - either provided in a call to execute() or bound beforehand - but this behaviour can be disabled by setting replace_placeholders to false:

    use DBI::Log replace_placeholders => 0;

This may be useful if you're doing later processing on the log, e.g. parsing it and grouping by queries, and want all executions of the same query to look alike without the values.

SEE ALSO

There is a built-in way to log with DBI, which can be enabled with DBI-trace(1)>, but the output is not particulary easy to read through nor does it give you much idea where the queries are run from.

DBIx::Class provides facilities via the DBIC_TRACE env var or setting $class-storage->debug(1);>, and even more powerful facilities by setting debugobj(), but if you have a codebase which mixes DBIx::Class queries with direct DBI queries, you won't be capturing all queries.

DBIx::Class::UnicornLogger, DBIx::Class::Storage::Debug::PrettyTrace and other similar options may be useful if you use DBIx::Class exclusively.

METACPAN

https://metacpan.org/pod/DBI::Log

REPOSITORY

https://github.com/zorgnax/dbilog

AUTHOR

Jacob Gelbman, <gelbman@gmail.com>

CONTRIBUTORS

  • Árpád Szász, <arpad.szasz@plenum.ro>

  • Pavel Serikov

  • David Precious (BIGPRESH) - <davidp@preshweb.co.uk>

COPYRIGHT AND LICENSE

Copyright (C) 2015 by Jacob Gelbman

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.18.2 or, at your option, any later version of Perl 5 you may have available.