Download the PHP package alex-frolov/query-guard without Composer

query-guard

Русская версия

A PHPUnit extension that watches the queries your tests already make and reports the performance problems hiding behind them — N+1 above all.

Install it, add six lines to phpunit.xml, and run your suite as usual. No assertions in your tests, no separate command, no code changes.

Status: 1.0.0. Everything documented below works and is covered by tests, run on PHPUnit 10.5–13, Doctrine ORM 2–3, DBAL 3–4, MySQL and PostgreSQL. The public API is listed in UPGRADING.md and does not break before 2.0; the same file names the two things a minor release may still change.

What it looks like

Those two are real: the numbers above come from running query-guard over the controller suite of an open-source Symfony application. 207 findings in 21 distinct places, including three rate lookups per timesheet on every flush.

The line under a finding is where the query left from; the from lines above it are who asked for it. The two are rarely the same place — a lazy association is touched inside a getter that every caller shares, and on Eloquent inside the one magic accessor every association goes through — and the fix belongs to the caller. call-chain-depth sets how many of them are shown, 0 turns them off.

Why not a static analyser

N+1 is not a property of a query. It is a property of a sequence of queries.

This is not a philosophical point — it is measurable. On MySQL, where plan analysis works completely, the plan of every single query in a textbook N+1 is flawless: access_type: const, key: PRIMARY, one row examined, zero issues. The problem is that there are fifty of them. No amount of EXPLAIN will tell you that.

Nor will an AST-based analyser: lazy loading happens when you touch a property, dynamic query builders resolve at runtime, and neither is visible in source code.

About the closest neighbour

Tested on real projects rather than read from its README:

• its duplicate detection compares SQL and bound values, so a real N+1 — where the values differ by definition — produces zero duplicates and a green test;
• lazy-loading detection requires Laravel; on Doctrine assertNoLazyLoading() returns green without checking anything;
• query timing is unavailable on Doctrine (it reuses Doctrine's logging middleware, which records queries before execution), so assertMaxQueryTime(0.001) passes on six real queries;
• on PostgreSQL, plan analysis silently switches itself off and reports green with zero queries analysed.

query-guard writes its own DBAL middleware (so timings exist), fingerprints SQL with the values stripped (so N+1 is visible), and says out loud when a rule cannot judge instead of showing green.

Install

Then wire the adapter for your ORM — one line for Doctrine, nothing at all for Eloquent.

Under Pest it is the same two steps. Pest runs on PHPUnit, the <extensions> node in phpunit.xml is read as usual, and the summary is printed after Collision's. There is one thing worth knowing: pest --parallel is ParaTest, so give report-json a %token% — see "When query-guard stays silent".

Doctrine

The middleware has to be in the connection configuration before the connection is created, which is why the extension cannot install it for you:

Works with Doctrine ORM 2 and 3, DBAL 3 and 4.

Eloquent

Nothing to do: Laravel discovers QueryGuardServiceProvider automatically. It subscribes to the event dispatcher, so every connection is covered — including ones created later, and including queries made in setUp().

Anything else

The collector is the seam. Feed it from a PDO decorator, another ORM, wherever:

Outside a PHPUnit run the collector is a null object: this code does nothing and costs nothing.

If you already know where the query came from, pass callsite: instead of stack: and skip the stack walk entirely — that is what both built-in adapters do.

QueryGuard::collector(), QueryCollector::record(), QueryEvent and Callsite are the public API for this, and the release notes treat them as such. Everything else under QueryGuard\ is marked @internal — the adapter interfaces included — and can change in any release; UPGRADING.md lists what is public and why the rest is not.

Rules

Tier 1 — works on day one, no reference database needed

Three rules stay silent until configured, on purpose. select-star would fire on every Eloquent query (it is Eloquent's default); "a large table" cannot be determined from a three-row fixture database; and a query budget is a project policy, not a constant.

n-plus-one only looks at reads, requires the repeated values to differ, and ignores batch fetches (IN (?, ?, ?)) — that pattern is the cure for N+1, not the disease.

Tier 2 — plan rules, needs a database with real data

Enable with tier2="true". Runs EXPLAIN once per distinct query shape, on the same connection and inside the same transaction.

Several connections are handled separately. Each is explained by itself, with its own platform driver — a query that ran on the analytics database is never explained against the primary one. A connection on a platform tier 2 does not support is named in the summary and skipped; the others keep working.

Where a rule cannot work, the summary says so. A green report and "we did not look" must never be the same output.

Tier 2 needs volume. Plan rules stay quiet until a table has at least min-rows (default 1000) rows, because on a small table the optimiser's own estimate lies — we have watched a competitor report error: Full table scan on a five-row table. The one exception is no-possible-index: a missing index is a fact about the schema, true even when the table is empty.

When query-guard stays silent

A tool that shows green when it did not look is worse than no tool. Everything on this list is printed as a notice in the summary — except the last three, which are silent by design:

A few more worth knowing:

• Tier 2 on Eloquent explains eagerly, right where a query fires — not lazily, when a rule asks. RefreshDatabase/DatabaseTransactions roll a test's transaction back in its own tearDown(), before any rule gets to run, so waiting would mean explaining against data that is already gone. This also means an EXPLAIN briefly shows up as another query on the same connection — other listeners on the same dispatcher (Telescope, Debugbar, an application's own query logger) will see it too.
• Queries made in setUp() are not analysed. They are counted and shown separately (in setUp: N). That is the decision the whole false-positive story rests on — a factory creating 50 rows in a loop is 50 identical INSERTs from one callsite. The max-queries budget is charged against the test body for the same reason.
• The trace describes the program your tests run, which is not always the program you deploy. An application that swaps an implementation under test — a cache replaced by a no-op, a queue driver set to sync, a service faked in a provider guarded by runningUnitTests() — is traced honestly, and the finding is a true statement about a program nobody ships. This is a step beyond "coverage equals your tests' coverage": here the coverage is there and the trace is real, it is the subject that differs. Seen on a music streamer whose scanner memoises artist lookups in production and deliberately does not under test, which turned an N+1 that cannot happen in production into the largest cluster in the report. Worth one question when a finding looks too easy: is this class the one that runs in production?
• The trace describes the program your tests run, which is not always the program you deploy. An application that swaps an implementation under test — a cache replaced by a no-op, a queue driver set to sync, a service faked in a provider guarded by runningUnitTests() — is traced honestly, and the finding is a true statement about a program nobody ships. This is a step beyond "the tool sees as much as the tests cover": here the coverage is there and the trace is real, it is the subject that differs. Seen on a music streamer whose scanner memoises artist lookups in production and deliberately does not under test, which turned an N+1 that cannot happen in production into the largest cluster in the report. Worth one question when a finding looks too easy: is this class the one that runs in production?
• Under a parallel runner every worker is its own run. ParaTest starts a process per worker, and query-guard has no way to see across them: each prints its own summary, keeps its own counters, and in strict mode fails its own process. Findings are still correct — a trace never spans workers — but "findings: 3" printed four times is four partial reports, not twelve findings. Put %token% in the report-json path so every worker writes its own file, or run query-guard in a sequential job of its own. Regenerating a baseline under a parallel runner is refused outright and said so in the summary: a baseline is one committed file, twelve workers writing it would leave whichever share finished last, and the run after that would fail on findings nobody added.

What it costs

Worth knowing before putting it in CI, and measured rather than estimated:

The stack is captured only while a test is being traced, and only the resolved callsite is kept — 200 raw frames per query cost about 106 MB per 1000 queries and used to be the one way this tool could hurt a suite.

Tier 2 is the part with a real price: it talks to the database. Its own queries are excluded from the trace, so the tool never counts its own traffic.

On an ordinary suite ~0.5 MB per 1000 queries is nothing, but a test-data import running 100k queries in one test is ~50 MB held until that one test ends. max-trace-queries is the valve for that case — see the configuration table above.

Baseline

Point query-guard at an existing project and you will get hundreds of findings. That is what a baseline is for:

Everything known stays quiet; anything new shows up. Commit the file.

A finding is keyed by rule|file|fingerprint — deliberately without the line number and without the test name, since both move for harmless reasons and would reset your baseline for nothing. Paths in the file are relative, so it survives the trip to CI.

The summary always reports how many findings the baseline silenced.

What is in the file

platform is the database the file was generated on. A signature holds the fingerprint of the statement, and the same DQL becomes different SQL per dialect — Doctrine emits CONCAT(...) and CAST(... AS CHAR) on MySQL against ... || ... and CAST(... AS VARCHAR) on PostgreSQL. Measured on one schema across all three: 2 of 114 findings differed that way while the other 112 matched byte for byte. So a run against a database the baseline did not come from says so, rather than leaving you to work out why known findings arrived as new ones.

format-version is the shape of the file. It goes up only when a field is removed or starts to mean something else — never for a new one — and a release that meets a number it does not know silences nothing and says so in the summary, rather than guessing what the entries mean. A file without the field is format 1.

The key is what silences a finding; everything under it is there so the file can be read in a pull request. place carries the line number the key deliberately leaves out, and sample is the message as it was when the entry was written — neither is matched against, and neither is refreshed. Delete a key by hand to start failing on it again; editing place or sample changes nothing.

Entries that outlive their findings

Fix a finding and its baseline entry stays behind, silencing something that no longer exists — including, in time, a fresh regression that lands on the same rule in the same file. So a run reports how many entries matched nothing:

The distinction is the whole point of the wording. --filter, --exclude-group, a sharded CI job — all of them leave entries unmatched without anything being stale. Regenerate from a full run or not at all.

Machine-readable report

The summary above is written to be read by a person, which means it will be reworded. Anything automated should read this instead:

Paths are relative to the working directory, so the file survives the trip to CI. format-version goes up only when a field is removed or starts to mean something else, never for a new one: a script that checks it keeps working across releases until the report actually changes under it. failing answers the only question a CI script usually has — whether this run is about to exit non-zero — without it having to reimplement the fail-on comparison. worker is null in an ordinary run and carries the ParaTest token when the file holds one worker's share of a parallel one, so that merged reports stay attributable.

The directory is created if it is not there yet — var/ is in .gitignore on most projects and missing from a fresh checkout, and a report that was not written because of that used to be a notice easily lost in a screenful of findings.

The console summary is not replaced by the file: a run that writes a report and prints nothing is a run whose findings nobody sees. The one exception is the run that asked for silence — --no-output — where the file is still written and the summary is not printed.

Per-test overrides

Both work on the class as well as the method. #[IgnoreRule] is repeatable, and a method adds to whatever its class already ignores; #[AllowQueries] on a method replaces the class-level budget rather than adding to it.

Where a callsite lands inside a framework your project happens to vendor, and no #[IgnoreRule] will help because the rule is right and the file is not yours, add the package to skip-paths:

Those are path fragments, not regular expressions, and they are added to the built-in list (PHPUnit, Pest, Doctrine, Symfony, Laravel, Illuminate, Composer) rather than replacing it. The first frame outside every listed path is what a finding points at.

Some plumbing cannot be recognised by its path, because the path is genuinely yours. A frame's file is the caller's, so return $next($request); in your own Laravel middleware shows up as an application frame — on every request, telling you nothing about which query fired. On one Laravel project those frames held 28% of all chain slots and were the whole chain for a quarter of the findings. skip-functions matches what a frame called instead:

Same rules as skip-paths — fragments rather than patterns, added to the built-in list (currently Illuminate\Pipeline\Pipeline::) rather than replacing it. Reach for it only for transit code: it hides every frame that called the named method, wherever from.

Both of those are about naming a finding. fixture-paths is about not making one:

Queries made in setUp() are counted and shown separately (in setUp: N) and never analysed — that decision is what keeps a factory creating fifty rows in a loop from being reported as an N+1. Laravel suites call their factories on the first line of the test body instead, where the setUp() boundary does not reach: on one 1 617-test suite that put 64% of the findings on factory and test files. fixture-paths moves a query into the same bucket because of where it came from, wherever in the test it happened.

Reaching for skip-paths here does not work, and it is worth knowing why before trying: that list steps over a frame so the callsite lands on the next one, so pointing it at a factory directory brings the same findings back against the test line that called the factory, and pointing it at the tests as well leaves 41% of them with no callsite at all. fixture-paths does not change how anything is named.

The same list is the answer to migrations showing up in the report. LazilyRefreshDatabase builds the schema on first use — inside the body of whichever test ran first — so the schema build is traced as that test's work, and under a parallel runner that happens once per worker.

Everything moved this way is still counted in in setUp: N, so the summary keeps saying how much of the run nobody looked at. Nothing is skipped silently: when a third or more of the findings land in the conventional fixture directories of either supported ORM, the summary says so and names them, ready to paste in.

It matches the callsite, and only the callsite. A migration that issues its query through a package gets that package's file as its callsite, not the migration's, and stays in the report. Measured on the suite above: 24 findings out of 1 869 (1.3%) came back that way. Matching anywhere in the call chain would catch them, and is deliberately not done — the chain is only call-chain-depth frames deep, so what counted as a fixture would start depending on a parameter that has nothing to do with it.

Configuration

fail-on reads the severity scale. [error] means the adapter recognised lazy loading and named the association; [warning] that only the shape heuristic fired; [info] is a style note. Everything found is always printed — fail-on only decides what strict is willing to fail the run over. The default of warning deliberately leaves info out: the only info rule is select-star, and select * is Eloquent's default mode, so failing on it would turn a whole Laravel suite red the moment the rule is enabled. Set fail-on="error" to fail only on what was proved rather than guessed.

On Eloquent, fail-on="error" currently fails on nothing. [error] comes from the adapter recognising lazy initialisation in the stack — an uninitialised Doctrine proxy or PersistentCollection is visible there as an object. The Eloquent adapter does not enrich its events yet, so every n-plus-one it reports is a warning from the query's shape. Until that changes, fail-on="error" on a Laravel project is a run that cannot fail: use the baseline to draw the line instead. When it does change, those findings become error in a minor release — UPGRADING.md says what that means for a run with fail-on="error".

A value that cannot be read — mode="strickt", max-queries="lots" — produces a warning in the summary naming the parameter, the value and what was used instead. It never falls back in silence. A misspelled parameter name is the one thing that cannot be caught: PHPUnit's ParameterCollection offers no way to enumerate what was actually written.

strict mode fails the whole run (exit code 1), not an individual test: PHPUnit's event system gives an extension no way to mark a test as failed. When PHPUnit is already failing the run for its own reasons, its exit code is left alone — it is the more specific one.

Recipes

Catching an N+1 in five minutes

No baseline, no strict mode, no tier 2 — just point it at one test and read the output.

1. Install the extension and wire the adapter (Install section above). Defaults are fine: n-plus-one, duplicate-query and query-in-loop are on out of the box, the noisy rules are off.

2. Run the one test you suspect:

3. Read the summary. A finding names the rule, the test, the diagnosis and the line:

[error] means the adapter recognised lazy loading and there is nothing left to guess. [warning] means only the shape heuristic fired — same query, same place, different values — and it is worth a look but not a certainty.

Nothing found? Check the top of the summary before concluding there is no problem: tests traced: 1, queries: 0 means interception never took, and the notice below it says what to fix. See When query-guard stays silent.

When you are ready to hold the whole suite to this, read on.

Reading the three grouping rules

n-plus-one, duplicate-query and query-in-loop all say "the same place ran a lot of queries", and they are three different diagnoses with three different fixes. The distinction is what the finding turns on:

Two rules never fire on the same queries: n-plus-one requires the bound values to differ, duplicate-query requires them to match, and query-in-loop stands down when a group has only one shape. If you are looking at all three on one line of code, they are describing three different sets of queries that happen to leave from it.

Severity is the second half of the message and means certainty, not cost. [error] on n-plus-one is the adapter having recognised Doctrine lazy initialisation and named the association — there is nothing left to guess. [warning] is the shape heuristic alone.

Fixing an N+1 once you have found one

The finding gives you a file, a line and a shape. What it cannot tell you is which of three fixes applies, because that depends on what the code is doing with the rows.

Doctrine — a lazy association walked in a loop. The usual case, and the one that gets reported as [error] with the association named:

addSelect is the whole trick, and leaving it out is the most common way this "fix" changes nothing: a leftJoin alone still leaves the collection lazy. Do not reach for fetch: 'EAGER' on the mapping instead — it makes every read of that entity anywhere in the application pay for the association.

Doctrine — many entities by id, not through an association:

query-guard skips IN (?, ?, ?) on purpose — a batch fetch is the cure, so the rule must not report the fix as the disease.

Eloquent:

Then check it. Re-run the one test and read the summary, rather than assuming:

The finding disappears and the query count drops. If the count did not drop, the fix did not fire — an unhydrated join, or a lazy load that moved rather than went away. If a duplicate-query finding appeared where the n-plus-one was, the loop is now asking one query repeatedly instead of one per row: nearly there, and the remaining fix is to keep the result rather than to change the query.

Troubleshooting false positives

A finding you do not believe is not a reason to raise the global budget or drop a rule project-wide. Work through these, in order, before deciding it is wrong:

1. Is it happening in setUp()? It should not be flagged at all — queries made there are counted separately (in setUp: N) and never analysed (see "When query-guard stays silent" above). If a factory loop in a test body is producing the finding, moving that fixture creation into setUp() is usually the actual fix, not an exception.

2. Is it a batch fetch that got misread? n-plus-one already skips IN (?, ?, ?) — that pattern is the cure, not the disease. It does not skip NOT IN (?, ?, ?): an exclude-list is a different pattern from "fetch a page of rows by key", and a per-row lookup that happens to also exclude a couple of ids is still N+1.

3. Is the test doing genuinely heavy, one-off work? Bulk import, a report export, a migration test — give it its own budget rather than raising the number every other test is measured against:

4. Is the rule right about the pattern but wrong for this test? Narrow the exception to exactly the rule that misjudges it, on exactly the test, with a comment saying why — see "Per-test overrides" above. A class-wide #[IgnoreRule] is the last resort, not the first.

5. Does the callsite land inside vendored framework code? No #[IgnoreRule] fixes a finding whose file is not yours — add the package to skip-paths instead, so the finding points at the first frame in your own code. When this is not the odd finding but a third of the run — a project whose repositories all extend one from a wrapper package, say — the summary says so and names the packages, ready to paste in.

6. Are you running under a parallel runner? Each ParaTest worker prints its own summary; "findings: 3" from four workers is four partial reports, not twelve findings — see "When query-guard stays silent" above. Give the report-json path a %token% so the workers stop overwriting each other's file; the summary says so too when they do.

7. Is the class in the trace the one that runs in production? Applications routinely swap implementations under test, and a service provider branching on runningUnitTests() is the usual way. The finding is then a true statement about code that is never deployed — a memoising cache replaced by a pass-through, a driver faked, a client stubbed. Follow the chain to the class that issued the queries and check how it is bound, before treating the finding as real.

If none of the above explains it, it is a bug report rather than a configuration change: the rule genuinely cannot tell this pattern apart from the one it exists to catch.

Adopting on a legacy project

You cannot fix hundreds of old tests at once, and you do not need to. The goal is to draw a line: everything existing goes into the baseline, everything new is checked. The steps below go from install to strict.

1. Install and enable every check. Extension and adapter as in the Install section above. Keep mode="report" while you are still measuring the damage, and set the baseline path right away — step 5 writes to it:

max-queries here is a probe, not the budget you intend to keep — see steps 3 and 4.

2. Run the suite and save the log. Findings and query counts go to stdout:

3. Pick the real max-queries from the log. Only breaches are printed, as N queries against a budget of M — so one run shows you the tests above the probe and nothing about the rest. To see the shape of the distribution, run it twice: once with a low probe (say 20) and once with a high one (say 100). Between the two you have every test's exact count.

Recommendations:

• set the global budget near the 90th–95th percentile of ordinary business tests — a number chosen "with headroom" protects nothing;
• genuinely heavy tests (bulk import, export, reporting) get their own #[AllowQueries(N)] instead of an inflated global budget;
• legacy code you cannot touch: leave its breaches to be absorbed by the baseline (step 4) — query-count findings are keyed per test, so new tests are still checked against the budget.

4. Write the budget you picked back into phpunit.xml — the probe from step 1 is still in there, and step 5 is about to freeze whatever it produces:

Do this before generating the baseline, not after. A baseline generated against the probe records query-count findings at the wrong threshold, and lowering the budget later then looks like a wave of regressions.

5. Generate the baseline and commit it:

Everything currently found lands in tests/query-guard-baseline.json; the file goes into the repository.

Set that variable for the one command, never in CI. With it on, the run always passes and always rewrites the baseline — the quietest possible way to switch the tool off for good. If the variable is set but no baseline parameter is configured, the summary says so and nothing is written.

6. Decide on tier 2 — only if the tests run on a database with real volume. Plan rules need rows and statistics; on a three-row fixture database the optimiser's estimate lies and the rules do more harm than good (see the Tier 2 section above). If your test database is a production copy or is seeded at scale, add:

and regenerate the baseline (step 5), so existing plan findings are recorded as well.

7. Re-run and verify:

The summary must show silenced by baseline: N and stay otherwise empty — anything that still appears is either a flaky callsite or a regression between the two runs, and the baseline has nothing to do with it.

8. Switch the mode to strict:

From now on every finding outside the baseline fails the run: a new N+1 in a new test, a moved callsite of an old one, a budget breach in a test whose name did not exist when the baseline was written.

One habit worth adopting together with the tool: prepare test data in setUp(), not in the test body. The trace opens after setUp() finishes, so factory INSERTs there are kept out of the analysis; the same creation loop inside a test body is a textbook n-plus-one/duplicate-query false positive — on your own fixtures.

Starting a new project

No legacy, no baseline: every finding is either a bug you fix or an exception you justify in writing. Enable everything that works on day one and keep report while triaging:

Two more parameters are worth adding, but only once each condition holds — neither has a sensible value in the abstract:

1. Run and save the log:

2. Triage every finding. Three possible verdicts:

• True positive — fix the code, not the config: eager fetch (JOIN/IN) instead of lazy loading, one batched query instead of one per row, the missing index behind a table-scan or no-possible-index.
• Deliberately heavy test — bulk import, report export: an exception on the test (step 3), not a raised global budget.
• False positive — a rule judged a pattern it cannot see through: an exception on the test with a comment saying why.

3. Exceptions — how to set them correctly:

A method-level #[AllowQueries] overrides a class-level one; #[IgnoreRule] is repeatable and adds to whatever the class ignores. Keep exceptions narrow: one rule on one method beats a class-wide ignore of three. An exception without a reason comment is where the rot starts.

4. When the log is clean, switch to strict:

On a new project the baseline is optional — every finding is fresh enough to fix. If the suite ever outgrows that, the baseline parameter is right there.

Migrating from phpunit-query-count-assertions

The differences are covered in "About the closest neighbour" above; this is the mechanical part of moving over.

1. Keep both installed while you migrate. Nothing about query-guard conflicts with the trait-based assertions — they watch the same queries independently, so there is no need to rip the old one out before the new one is in place.

2. Map the assertions you have to query-guard's equivalents:

3. Remove the trait and its assertions from each test as you confirm query-guard catches the same thing. Run with mode="report" first (see "Starting a new project" above) so a query-guard finding shows up next to the assertion it is replacing, before you delete the assertion.

4. Expect query-guard to find more, not less. It is not a stricter version of the same check: assertNoDuplicateQueries() compares SQL and bound values, so a real N+1 — where the values differ by definition — produces zero duplicates there, and query-guard's n-plus-one is what catches it instead. A suite that was green under the old assertions is not evidence the new ones will start green too; see "Adopting on a legacy project" above for baselining whatever surfaces.

5. Once every test has been converted, remove the dependency:

Running it in CI

The extension needs no CI job of its own — it rides along with the suite you already run. What CI adds is somewhere to put the report and a rule about the baseline.

1. Write the machine-readable report and keep it:

with <parameter name="report-json" value="var/query-guard.json"/> in phpunit.xml. The if: always() is the point of the step: without it the artifact is uploaded only on the runs where nobody needs it.

2. Never set QUERY_GUARD_GENERATE_BASELINE in CI. With it on, the run always passes and always rewrites the baseline. It is the quietest possible way to switch the tool off for good — quieter than removing it, because the job stays green and the file keeps changing. Regeneration is a local command, and its output is a reviewed commit.

3. Review the baseline diff like code. A pull request that adds entries is a pull request that ships known problems, and the diff says exactly which:

Sometimes that is the right call — a deadline, a refactor already scheduled. The value is that it was a call, made by a named reviewer, rather than a rule quietly not firing.

4. Read the counts, not just the exit code. tests traced: 0 or queries: 0 is a green build that checked nothing, and it looks identical to a clean one unless somebody reads the two numbers at the top of the summary. The JSON carries them under summary, which is the easier thing to assert on.

A project with several databases

Tier 2 handles connections separately by design: each is explained by itself, through the connection that ran the query, with its own platform driver. What that needs from you is mostly a way to check that it happened.

Which connections were seen. The summary names every one it had to skip, and why:

0 failed next to a plausible number of plans is what a working tier 2 looks like. A large failed count usually means EXPLAIN is being refused rather than misparsed — permissions, or a statement the platform will not explain.

Connections are named after their database. Two entries pointing at the same dbname — a primary and a read replica, most often — are told apart by host, port and user, and the second is reported under a suffixed name:

Nothing is lost when that happens, but the name in the summary is one you never configured. If that matters, give the replica its own database name, or read the notice as the mapping it is.

See "With Eloquent, and RefreshDatabase/DatabaseTransactions" below for the Eloquent side of the same per-connection design.

Keeping a baseline healthy through a refactor

A baseline entry is keyed by rule|file|fingerprint. The line number is deliberately not in there, so ordinary edits cost nothing — but the file is, and the fingerprint is the normalised SQL. Two refactors therefore move entries:

• moving or renaming a file — every entry pointing into it stops matching, and its findings come back as new;
• changing the query itself — a new column in the SELECT is a new fingerprint, even though the N+1 is the same one.

Both look identical from the outside: findings reappear in code you did not think you had touched, and the same number of entries starts reporting that it silenced nothing.

An upgrade can do the same without any refactor: a minor release may improve how SQL is normalised, which changes fingerprints — the SQL dialect is the first candidate. Its UPGRADING.md entry says when, and the fix below is the same.

The fix is the same in both cases and it is deliberately blunt — regenerate from a full run and read the diff:

What the diff should show is entries moving: the same count out and back, under new keys. A regeneration that adds more than it removes has recorded something new as pre-existing, which is the one thing a baseline must not do — check what appeared before committing.

Do not hand-edit keys to follow a moved file. A key is derived, and a hand-written one that is subtly wrong silences nothing while looking like it does; deleting entries by hand is fine, because deleting can only make the tool stricter.

With dama/doctrine-test-bundle and transactional tests

Nothing to configure — and the combination is worth naming, because tier 2 was built around it. That bundle wraps each test in a transaction and rolls it back, so the rows a test created exist on that connection and nowhere else. This is why EXPLAIN is run through the connection that issued the query rather than a fresh one: a separate connection would explain an empty database and report plans for data that is not there.

Two things follow that are worth knowing:

• The connection is reused across tests, the trace is not. The bundle keeps one connection open for the suite; query-guard opens and closes a trace per test, so findings stay per-test regardless.
• Plans are cached per shape per connection, not per test. A query explained in the first test is not explained again in the four-hundredth — which is what keeps tier 2 affordable, and also means the plan reflects the data as it was when first seen. On a fixture-sized database that would matter; tier 2 is not for fixture-sized databases anyway, and min-rows is what keeps it quiet there.

With Eloquent, and RefreshDatabase/DatabaseTransactions

Nothing to configure — and the combination is worth naming, because it decided how tier 2 had to work here. Both traits roll a test's transaction back in that test's own tearDown(), and Laravel rebuilds the whole application between tests: unlike dama/doctrine-test-bundle's one connection for the whole suite, there is no point after the query runs where the connection is still guaranteed to hold that test's data. So the EXPLAIN runs immediately, inside the QueryExecuted listener, before the test can tear anything down — not lazily when a rule asks, the way Doctrine's tier 2 does.

Two things follow that are worth knowing:

• The EXPLAIN is itself a query on the same connection, so it briefly shows up to every other listener on the same dispatcher — Telescope, Debugbar, an application's own query logger. query-guard's own trace is unaffected; theirs may not be.
• Plans are cached by query shape and connection name, not by test. A shape explained in the first test is not explained again in the four-hundredth — the connection itself is re-resolved on every attempt (Laravel hands out a fresh one every test), but the Plan is not, so it reflects the data as it was the first time this shape was seen. Same trade-off as dama/doctrine-test-bundle above, and the same answer: not a fixture-sized database's friend, and min-rows is what keeps tier 2 quiet there.

Keeping smoke/load tests out of query-guard

Performance smoke tests should run without the extension: the DBAL middleware and the per-shape EXPLAIN of tier 2 distort the very timings those tests exist to measure, and strict fails the run on a query budget that is the subject of the test, not a bug.

The setup is a group and a flag. A second phpunit.xml is not needed — PHPUnit's --no-extensions switches every registered extension off for one run, so there is no copy of the config to keep in sync.

1. Put the tests in their own group:

2. Exclude the group from normal runs — either in the config, as a top-level <groups> block (an <exclude> inside <testsuite> is a path, not a group):

or on the command line: vendor/bin/phpunit --exclude-group=smoke. Both leave --group=smoke working: an explicit group on the command line overrides the config.

3. Wire both commands into composer:

or into a Makefile:

--no-extensions turns off every extension registered in phpunit.xml, not only this one. If the smoke suite depends on another extension, that is the case — and the only case — for a second configuration file; then porting every change of phpunit.xml into it becomes a standing obligation, and a config that has silently drifted is worse than no config at all.

One habit keeps the rest honest: run the smoke suite sequentially. Parallel workers race each other over CPU and the database, and the measurements are worthless.

Requirements

PHP 8.2+, PHPUnit 10.5 / 11 / 12 / 13, Pest 4. Doctrine ORM 2 / 3 with DBAL 3 / 4, or Laravel 11+. Tier 2: MySQL 8 / MariaDB or PostgreSQL.

Development

No local PHP needed — everything runs in containers. composer update and not install: this is a library, so composer.lock is deliberately not in the repository and CI resolves dependencies afresh on every run.

Tier 2 is verified against a synthetic stand with 100 000 rows:

The official php images ship neither pdo_mysql nor pdo_pgsql, so run-tier2-tests.sh builds a throwaway image per platform on first use. Set QG_MYSQL_IMAGE / QG_PG_IMAGE to reuse images your project already has.

The expected plan flags in those tests were written by hand from reading real EXPLAIN output, never generated from our own parser — otherwise a misunderstanding of a plan would land in the code and in its test at the same time.

Pest has a stand of its own, for the same reason: it installs the package the way a user does, and it caught a way of breaking the extension that no test inside this repository could see (see tools/pest/README.md).

License

MIT. See LICENSE.