Test::...Queries(3pm)

💖 NAME 💖

Test::DBIC::ExpectedQueries — Test that only expected DBIx::Class queries are run ✨

(you'll find this page filed under Test::...Queries(3pm) in the User Contributed Perl Documentation section, iykyk 💅)

---

🎀 VERSION 2.000 🎀

Version 2.000 is out and bestie, it comes with a breaking change. 😭 If your test suite is having a moment about it, go read the Changes file for the details, fr fr.

---

🦋 DESCRIPTION 🦋

Okay so the whole point: make sure that while some piece of code under test runs, the only DBIx::Class SQL queries that get executed are the ones you actually expected. Then it helps you hunt down the exact spots in your code where those unexpected queries are sneaking out. 🕵️‍♀️

🍓 Avoiding the n+1 problem

The way that following a relation off a DBIC row object is so easy to do without noticing that it might be firing off one query for each and every row in the resultset. 😭 Classic n+1 behavior.

You can absolutely fix that by prefetching those relations — but you have to know it's happening first. That's the catch.

This module is gonna help you:

• find the unexpected queries 🔍
• figure out where they're being caused 📍
• make sure you don't accidentally start running a whole pile of single-row queries later on 🚫

---

💅 SYNOPSIS 💅

Setup

use Test::More;
use Test::DBIC::ExpectedQueries;
my $schema = ...; # Connect to a DBIx::Class schema

Simple

my @book_rows = expected_queries(
    $schema,
    sub {
        $schema->resultset("Book")->find(34);
        $schema->resultset("Author")->create( ... );
        $schema->resultset("Book")->search( undef, { join => "author" } )->all;
    },
    {
        book   => {
            select      => "<= 2",
            stack_trace => 1,
        },
        author => { insert => undef  },
    },
    "Doing that stuff runs correct SQL", # optional
);

Flexible

my $queries = Test::DBIC::ExpectedQueries->new({
    schema                  => $schema,
    report_subselect_tables => 1,
});
$queries->run(sub {
    $schema->resultset("Book")->find(34);
    $schema->resultset("Author")->create( ... );
});
my @book_rows = $queries->run(sub {
    $schema->resultset("Book")->search( undef, { join => "author" } )->all;
});

$queries->test({
    book   => { select => "<= 2"},
    author => { insert => undef },
});

# or, with test description
$queries->test(
    {
        book   => { select => "<= 2"},
        author => { insert => undef },
    },
    "Doing that stuff runs correct SQL", # optional
);

---

🌸 USAGE 🌸

Maybe you already have a really solid idea of what queries are (or should be) run. But often that's just not the case. 🙈

So here's the move: start by wrapping some DBIC application code in a test with no specific limits at all. The default expectation for every table is 0 queries run. So the test is gonna fail, and it's gonna report every single executed query it didn't expect. 💀

And now? Now you know what's going on. ✨ Now you can add prefetches or caching for the queries that shouldn't be happening, and specify query limits for the behaviour you currently know about.

Whether you wanna nail down your expected queries with exact counts, or just put wide-margin comparisons in place — that's completely up to you, bestie. 🫶

🔍 Finding the unexpected queries

Once you've found unexpected queries that your code is making, the next step is eliminating them. But... where are they even being called from?? 😭

🧚 Chained ResultSets

DBIC has this gorgeous little feature called chaining resultsets, which means you can create a resultset and then later modify it — add stuff to the WHERE clause, join in other resultsets, add prefetching of relations, whatever you need to do. 💅

You can build up small logical pieces of queries (and put them on their corresponding Result/ResultSet classes) and then combine them into actual queries, expressed at a higher level of operation. This is very, very powerful and honestly one of the coolest features of DBIC. 🔥

But there's a problem with passing a resultset around before finally executing it: it can be super tricky to find exactly where it's being executed. 😵‍💫

🌈 Following relations

And the "where is this database call coming from" problem isn't just a chained-queries thing. Same thing happens when you construct a query and then follow relations off of the main table. That's exactly what causes the n + 1 problem — and boom, you've accidentally made n queries for individual rows on top of the first one. 💀

And those extra queries? They might be a long way off from where the initial query was made. 🛰️

⭐ Show the stack trace

To solve this whole "where do the queries originate" mystery, you can tell Test::DBIC::ExpectedQueries to show a stack_trace for particular tables. 🎀

Heads up: these call stacks can get pretty deep, so you'll wanna find the unexpected queries first, and then enable the call stack for each of them. That also keeps your test output from getting spammed with stuff you don't care about. ✨

🫧 Return value from the test

For the subroutine expected_queries(...), and the method $queries->run(...), the return value is whatever the subroutine under test returned — so it's easy to wrap the DBIC code under test and still get the result out. 💖

It is context sensitive. 👑

🦋 Executed queries vs resultsets

Only queries that are actually executed inside the test are being monitored. Sounds obvious, right? But it might be a source of problems, ngl. 😬

The way that many DBIC methods are context sensitive, and in scalar context might just return an unrealized resultset instead of executing a query and returning the resulting rows. If you're unsure, assigning the query to an array will make it run in list context and therefore execute the SQL query. Or you can call ->all on the resultset object. 🫶

💀 DBIC_TRACE

Normally, setting the ENV variable DBIC_TRACE can be used to warn the DBIC queries.

Test::DBIC::ExpectedQueries uses the same mechanism that DBIC_TRACE does, so while the code is run under the test, the normal DBIC_TRACE will not happen. 🙈

---

🫶 SUBROUTINES 🫶

💖 expected_queries( $schema, $sub_ref, $expected_table_operations = {}, $description? ) : $result | @result

Run $sub_ref and collect stats for queries executed on $schema, then test (using $description) that they match the $expected_table_operations.

Return the return value of $sub_ref->(). 🎀

See the ANNOTATED EXAMPLES below for examples on how the $expected_table_operations is used, but here's a simple example:

{
    book   => { select => "<= 2", update => 3 },
    author => { insert => undef               },
    genre  => { select => 2, stack_trace => 1 },
},

• Use table names as found in the raw SQL, not DBIC terms like resultset and relation names. For relational queries, only the first main table is collected. 📍
• Use SQL terms like select, insert, update, delete, not DBIC terms like create and search. 💅
• A number means exact match. Comparisons in a string means, well, that. 🤷‍♀️
• undef means any number of queries ✨
• If you need to see where the queries for a table are executed from, use stack_trace => 1. 🔍

---

👑 METHODS 👑

🌟 new({ schema => $schema, report_subselect_tables => 0 }}) : $new_object

Create new test object. 🎀

$schema is a DBIx::Class::Schema object.

If report_subselect_tables is false (that's the default), any SQL query like

select * from (select abc from def);

will report a select on the table select. 😭 However, if you specify report_subselect_tables, it will try to find the def table inside the subselect. 🕵️‍♀️

🌈 run( $sub_ref ) : $result | @result

Run $sub_ref->() and collect all DBIC queries being run. 📥

Return the return value of $sub_ref->().

You can call $queries->run() multiple times to add to the collected stats before finally calling $queries->test(). 💖

⭐ test( $expected_table_operations = {}, $description? ) : $is_passing

Test (using $description) the collected queries against $expected_table_operations (see above) and either pass or fail a Test::More test. ✅

If the test fails, diag all queries relating to the tables with unexpected activity. 😭

If anything failed to be identified as a known query, always note those queries. But don't fail the test just because of it. 🫶

Reset the collected stats, so subsequent calls to ->run() start with a clean slate. ✨

---

🌈 ANNOTATED EXAMPLES 🌈

🎀 Simple interface

use Test::More;
use Test::DBIC::ExpectedQueries;

my $schema = ...;  # A DBIx::Class schema object

# The return value of the subref is returned
my $author_rows = expected_queries(
    # Collect stats for this schema
    $schema,
    # when running this code
    sub {
        $author_tree->create_authors_for_tabs($schema),
    },
    # and ensure these are the expected queries
    {
        # For the "tree_node" table
        tree_node => {
            update => ">= 1",  # Number of updates must be >= 1
            select => undef,   # Any number of selects are fine
        },
        # For the "author" table
        author => {
            update      => 8,  # Number of updates must be exactly 8
            stack_trace => 1,  # Show stack trace if it fails
        },
        user_session => {
            delete => "< 10",  # No more than 9 deletes allowed
        },
        # Any query on any other table will fail the test
    },
);

🦋 Flexible interface

Using the OO interface lets you collect stats for many separate queries. 💅

It's also useful for when you care about the individual return values from methods that got called, and for when you don't know the expected number of queries until after they've been run. 👑

use Test::More;
use Test::DBIC::ExpectedQueries;

my $queries = Test::DBIC::ExpectedQueries->new({ schema => $schema });
my $author_rows = $queries->run(
    sub { $author_tree->create_authors_for_tabs($schema) },
);

# Add more stats in a second run
$queries->run( sub { $author_tree->check_stuff() } );

# ... test other things

my $total_author_count = @{$author_rows} + 1; # or whatever

# This resets the collected stats
$queries->test(
    {
        author     => {
            insert => $total_author_count,
            update => undef,
        },
        field      => { select => "<= 1" },
        tree_node  => { select => 2 },
    },
);

🌱 DEVELOPMENT 🌱

okay bestie, this is the part where you find out who's behind all of this and where to yell if something's busted 💖

👑 Author

Johan Lindstrom, <johanl [AT] cpan.org> — that's the main character right there, no cap 💅

🫶 Contributors

many thanks to, fr fr:

• Syohei YOSHIDA (syohex) ✨

💻 Source code

the whole thing lives here:

<https://github.com/jplindstrom/p5-Test-DBIC-ExpectedQueries>

🐛 Bug reports

please report any bugs or feature requests on GitHub, that's literally the move:

<https://github.com/jplindstrom/p5-Test-DBIC-ExpectedQueries/issues>

okay but actually do it, not me ignoring my own bugs 🙈

⚠️ Caveats

listen, bestie, here's the tea 🍵 — SQL queries are identified using quick-n-dirty regexes, so that might be a bit brittle (and yet database agnostic, so there's that 👀). Please report cases with example SQL.

and like, heads up: if you have an anonymous subquery, that query might appear as a table called "SELECT" 😭 if you find anything like this, or similar strange results, please raise an issue on GitHub and provide the SQL text. give us the receipts, periodt 💜

---

📜 COPYRIGHT & LICENSE 📜

Copyright 2015- Johan Lindstrom, All Rights Reserved. 👑

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. 🎀

---

and that's a wrap on this little saga, bestie 🦋 this page shipped with perl v5.30.3, dated 2020-06-13, and yes — that's the Test::...Queries(3pm) footer energy, so you already know the vibe 🌈 go write some expected queries and slay 💖✨👑