Download the PHP package mattiasgeniar/phpunit-query-count-assertions without Composer

PHP query count assertions for PHPUnit

Count and assert SQL queries in your tests. Catch N+1 problems, full table scans, duplicate queries, and slow queries before they hit production.

Supports Laravel, Doctrine/Symfony, and Phalcon.

Requirements

• PHP 8.2+
• PHPUnit 11 or Pest 3
• Laravel 11/12, Doctrine DBAL 4, or Phalcon 6+

Driver Compatibility

Note: Lazy loading detection requires framework-specific hooks that only Laravel provides. Assertions like assertNoLazyLoading() will emit a warning on Doctrine and Phalcon and pass without checking, since violations cannot be detected.

Note: Doctrine's logging middleware only fires before query execution, so query timing is not available. Timing assertions (assertMaxQueryTime, assertTotalQueryTime) will emit a warning and pass without checking for Doctrine.

Installation

You can install the package via composer:

Quick start

Add the trait, wrap your core logic with efficiency tracking:

This catches N+1 queries, duplicate queries, and missing indexes in a single assertion. Your test setup (factories, seeders) stays outside the tracked block so it doesn't trigger false positives.

Framework Setup

Laravel (auto-detected)

No configuration needed. The package auto-detects Laravel and uses DB::listen() for query tracking.

Symfony

Symfony requires the logging middleware to be registered as a service. Add this to config/packages/test/services.yaml (this directory is only loaded when APP_ENV=test, so the middleware won't affect dev or production):

Then in your tests:

Phalcon

What it catches

• N+1 queries — lazy loading violations
• Duplicate queries — same query executed multiple times
• Missing indexes — full table scans, unused indexes
• Filesort & temp tables — common MySQL performance issues

When something fails, you get actionable output with the exact queries and their locations (file:line).

Query count assertions

For cases where you need precise control over query counts:

Tracking queries across the entire test

If you need to count queries outside closures, initialize tracking in setUp():

Multi-connection support

By default, trackQueries() captures queries from all database connections — not just the default one. This is useful when your application uses read replicas, separate analytics databases, or tenant-specific connections.

Filtering to specific connections

You can optionally filter to only track specific connection(s):

This is useful when:

• Your test setup runs queries on different connections that you don't want to count
• You want to verify that specific queries go to the right connection
• You're debugging connection routing in read/write split setups

Failure messages

Failed assertions show you the actual queries:

Locations (file:line) are shown for each query when available. This applies to duplicate, index, row count, timing, and total time failures too.

Lazy loading / N+1 detection

Uses Laravel's built-in lazy loading prevention:

Output:

Note: Laravel only triggers this when loading multiple models. Single model fetches won't trigger violations.

Index usage / full table scan detection

Runs EXPLAIN on each query to detect performance issues:

Output:

Supported databases

• MySQL (5.6+) - Full support with JSON EXPLAIN
• MariaDB - Full support with tabular EXPLAIN
• SQLite - Index analysis supported, row counting not available

Other databases will emit a warning and pass without checking. See Custom analysers to add support for additional databases.

What gets analyzed

Only queries that support EXPLAIN are analyzed:

• SELECT queries
• UPDATE queries
• DELETE queries
• INSERT...SELECT queries
• REPLACE...SELECT queries

Plain INSERT, CREATE, DROP, and other DDL statements are skipped.

Issue severity levels

Issues are classified by severity and shown with prefixes in the output:

By default, only errors and warnings cause assertion failures. Informational issues are printed as [INFO] notices (non-failing) so they're visible even when tests pass.

MySQL / MariaDB detects

• Full table scans (type=ALL)
• Full index scans (type=index)
• Index available but not used
• Using filesort
• Using temporary tables
• Using join buffer (missing index for joins)
• Full scan on NULL key
• Low filter efficiency (examining many rows, keeping few)
• High query cost (when threshold configured)

SQLite detects

• Full table scans (SCAN table)
• Temporary B-tree usage for ORDER BY, DISTINCT, GROUP BY
• Co-routine subqueries
• FK constraint checks - When a DELETE/UPDATE triggers scans on related tables, the message includes FK details:

Small table optimization

Full table scans, full index scans, and "index available but not used" warnings on tables with fewer than 10 rows are ignored by default, since scanning tiny tables is often faster than using an index. MySQL's docs note this is common for tables with fewer than 10 rows: https://dev.mysql.com/doc/refman/8.4/en/table-scan-avoidance.html. See Configurable thresholds to adjust this.

Duplicate query detection

Same query executed multiple times? You'll know:

Output:

Note: Different bindings = different queries. User::find(1) and User::find(2) are unique.

Row count threshold (MySQL / MariaDB only)

Output:

SQLite doesn't provide row estimates in EXPLAIN QUERY PLAN, so a warning is emitted and the assertion passes without checking.

Query timing assertions

Output:

Combined efficiency assertion

assertQueriesAreEfficient() checks everything at once: N+1, duplicates, and missing indexes. The Quick start shows the recommended inline pattern. Below are alternative approaches.

With a closure

Pest: beforeEach()

PHPUnit: setUp()

Paranoid mode (automatic checks on every test)

Want to automatically check every test for query efficiency issues? You can use afterEach() hooks to run assertions globally. This is aggressive and may surface many issues - use with caution.

Pest (in tests/Pest.php):

PHPUnit (base test class):

This will fail any test that has N+1 queries, duplicate queries, or missing indexes. Consider starting with a subset of tests rather than your entire suite.

Opting out with #[DisableQueryTracking]

In paranoid mode, some tests may need to opt out — for example, tests with heavy seeders, migrations, or tests that intentionally execute many queries. Use the #[DisableQueryTracking] attribute to skip tracking for specific tests or entire classes:

You can also disable tracking for an entire test class:

When #[DisableQueryTracking] is present, trackQueries() returns early without setting up listeners, and all assertions (assertQueriesAreEfficient(), assertQueryCountMatches(), etc.) pass silently.

Configurable thresholds

MySQL analyser options

The MySQL analyser has configurable thresholds that can be set by registering a customized instance:

Custom analysers

Add support for additional databases by implementing the QueryAnalyser interface:

Register your custom analyser in your test's setUp():

Custom analysers are checked before the built-in MySQL and SQLite analysers.

Helper methods

These methods let you inspect query data for custom assertions or debugging:

Testing

License

The MIT License (MIT). Please see License File for more information.