README

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

Laravel only.

Requirements

PHP 8.2+
Laravel 11 or 12
PHPUnit 11

Installation

You can install the package via composer:

composer require --dev mattiasgeniar/phpunit-query-count-assertions

Usage

Add the trait, wrap your code in a closure:

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

class YourTest extends TestCase
{
    use AssertsQueryCounts;

    public function test_eager_loading_is_efficient(): void
    {
        $this->assertQueryCountMatches(2, function() {
            $user = User::find(1);
            $posts = $user->posts()->get();
        });
    }
}

Available assertions

All assertions accept an optional closure:

// No queries at all
$this->assertNoQueriesExecuted(fn() => $this->getCachedData());

// Exact count
$this->assertQueryCountMatches(2, fn() => $this->loadUserWithPosts());

// Upper bounds
$this->assertQueryCountLessThan(6, fn() => $this->fetchDashboard());
$this->assertQueryCountLessThanOrEqual(5, fn() => $this->fetchDashboard());

// Lower bounds
$this->assertQueryCountGreaterThan(0, fn() => $this->warmCache());
$this->assertQueryCountGreaterThanOrEqual(1, fn() => $this->warmCache());

// Range
$this->assertQueryCountBetween(3, 7, fn() => $this->complexOperation());

Tracking queries across the entire test

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

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

class YourTest extends TestCase
{
    use AssertsQueryCounts;

    protected function setUp(): void
    {
        parent::setUp();

        self::trackQueries();
    }

    public function test_queries_across_method_calls(): void
    {
        $this->step1();
        $this->step2();

        $this->assertQueryCountMatches(5);
    }
}

Failure messages

Failed assertions show you the actual queries:

Expected 1 queries, got 3.
Queries executed:
  1. [0.45ms] SELECT * FROM users WHERE id = ?
      Bindings: [1]
  2. [0.32ms] SELECT * FROM posts WHERE user_id = ?
      Bindings: [1]
  3. [0.28ms] SELECT * FROM comments WHERE post_id IN (?, ?, ?)
      Bindings: [1, 2, 3]

Lazy loading / N+1 detection

Uses Laravel's built-in lazy loading prevention:

// Fails if any lazy loading occurs
$this->assertNoLazyLoading(function () {
    $users = User::all();

    foreach ($users as $user) {
        $user->posts->count(); // N+1 query
    }
});

// Passes with eager loading
$this->assertNoLazyLoading(function () {
    $users = User::with('posts')->get();

    foreach ($users as $user) {
        $user->posts->count();
    }
});

// Assert specific number of violations
$this->assertLazyLoadingCount(2, function () {
    // ...
});

Output:

Lazy loading violations detected:
Violations:
  1. App\Models\User::$posts
  2. App\Models\User::$posts

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:

$this->assertAllQueriesUseIndexes(function () {
    User::find(1); // Uses primary key, passes
});

$this->assertAllQueriesUseIndexes(function () {
    User::where('name', 'John')->get(); // Full table scan, fails
});

Output:

Queries with index issues detected:

  1. SELECT * FROM users WHERE name = ?
     Bindings: ["John"]
     Issues:
       - [ERROR] Full table scan on 'users'

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 skip the assertion. 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:

Severity	Prefix	Meaning
Error	`[ERROR]`	Critical issues that almost always need fixing (full table scans, unused available indexes)
Warning	`[WARNING]`	Potential issues that may be acceptable in some cases (filesort, temporary tables, full index scans)
Info	`[INFO]`	Informational notes (low filter efficiency, co-routine usage)

By default, only errors and warnings cause assertion failures.

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:
```
[WARNING] Full table scan on 'posts' (FK constraint check: posts.user_id → users.id (ON DELETE CASCADE))
```

Small table optimization

Full table scans on tables with fewer than 100 rows are ignored by default, since scanning small tables is often faster than using an index. See Configurable thresholds to adjust this.

Duplicate query detection

Same query executed multiple times? You'll know:

$this->assertNoDuplicateQueries(function () {
    User::find(1);
    User::find(1); // Duplicate
});

Output:

Duplicate queries detected:

  1. Executed 2 times: SELECT * FROM users WHERE id = ?
     Bindings: [1]

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

Row count threshold (MySQL / MariaDB only)

$this->assertMaxRowsExamined(1000, function () {
    User::where('status', 'active')->get();
});

Output:

Queries examining more than 1000 rows:

  1. SELECT * FROM users WHERE status = ?
     Bindings: ["active"]
     Rows examined: 15000

SQLite doesn't provide row estimates in EXPLAIN QUERY PLAN, so this assertion is skipped.

Query timing assertions

// No single query over 100ms
$this->assertMaxQueryTime(100, function () {
    User::with('posts', 'comments')->get();
});

// Total time under 500ms
$this->assertTotalQueryTime(500, function () {
    $users = User::all();
    $posts = Post::where('published', true)->get();
    $stats = DB::select('SELECT COUNT(*) FROM analytics');
});

Output:

Queries exceeding 100ms:

  1. [245.32ms] SELECT * FROM users
  2. [102.15ms] SELECT * FROM posts WHERE published = ?
     Bindings: [true]

Combined efficiency assertion

assertQueriesAreEfficient() checks everything at once: N+1, duplicates, and missing indexes.

With a closure

$this->assertQueriesAreEfficient(function () {
    $users = User::with('posts')->get();

    foreach ($users as $user) {
        $user->posts->count();
    }
});

Pest: beforeEach()

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

uses(AssertsQueryCounts::class);

beforeEach(function () {
    $this->trackQueriesForEfficiency();
});

it('loads the dashboard efficiently', function () {
    $this->get('/dashboard');

    $this->assertQueriesAreEfficient();
});

it('processes orders without N+1', function () {
    $order = Order::factory()->create();

    $this->post("/orders/{$order->id}/process");

    $this->assertQueriesAreEfficient();
});

PHPUnit: setUp()

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;
use Tests\TestCase;

class DashboardTest extends TestCase
{
    use AssertsQueryCounts;

    protected function setUp(): void
    {
        parent::setUp();

        $this->trackQueriesForEfficiency();
    }

    public function test_dashboard_loads_efficiently(): void
    {
        $this->get('/dashboard');

        $this->assertQueriesAreEfficient();
    }

    public function test_order_processing_has_no_n_plus_one(): void
    {
        $order = Order::factory()->create();

        $this->post("/orders/{$order->id}/process");

        $this->assertQueriesAreEfficient();
    }
}

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):

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

pest()->extend(Tests\TestCase::class)
    ->use(AssertsQueryCounts::class)
    ->beforeEach(fn () => $this->trackQueriesForEfficiency())
    ->afterEach(fn () => $this->assertQueriesAreEfficient())
    ->in('Feature');

PHPUnit (base test class):

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

abstract class TestCase extends BaseTestCase
{
    use AssertsQueryCounts;

    protected function setUp(): void
    {
        parent::setUp();
        $this->trackQueriesForEfficiency();
    }

    protected function tearDown(): void
    {
        $this->assertQueriesAreEfficient();
        parent::tearDown();
    }
}

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.

Configurable thresholds

MySQL analyser options

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

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;
use Mattiasgeniar\PhpunitQueryCountAssertions\QueryAnalysers\MySQLAnalyser;

class YourTest extends TestCase
{
    use AssertsQueryCounts;

    protected function setUp(): void
    {
        parent::setUp();

        // Flag full table scans only on tables with 500+ rows (default: 100)
        self::registerQueryAnalyser(
            (new MySQLAnalyser)->withMinRowsForScanWarning(500)
        );

        // Also flag queries with cost above threshold
        self::registerQueryAnalyser(
            (new MySQLAnalyser)
                ->withMinRowsForScanWarning(500)
                ->withMaxCost(1000.0)
        );
    }
}

Method	Default	Description
`withMinRowsForScanWarning(int)`	100	Minimum rows to flag a full table scan as an error
`withMaxCost(float)`	null (disabled)	Maximum query cost before flagging as a warning

Custom analysers

Add support for additional databases by implementing the QueryAnalyser interface:

use Illuminate\Database\Connection;
use Mattiasgeniar\PhpunitQueryCountAssertions\QueryAnalysers\QueryAnalyser;
use Mattiasgeniar\PhpunitQueryCountAssertions\QueryAnalysers\QueryIssue;
use Mattiasgeniar\PhpunitQueryCountAssertions\QueryAnalysers\Concerns\ExplainsQueries;

class PostgreSQLAnalyser implements QueryAnalyser
{
    use ExplainsQueries; // Provides canExplain() for SELECT, UPDATE, DELETE, INSERT...SELECT

    public function supports(string $driver): bool
    {
        return $driver === 'pgsql';
    }

    public function explain(Connection $connection, string $sql, array $bindings): array
    {
        return $connection->select('EXPLAIN (FORMAT JSON) ' . $sql, $bindings);
    }

    public function analyzeIndexUsage(array $explainResults, ?string $sql = null, ?Connection $connection = null): array
    {
        $issues = [];

        // Parse PostgreSQL EXPLAIN JSON output
        // Look for "Seq Scan" nodes (full table scans)
        // Return QueryIssue instances for problems found
        // Use $sql to detect FK constraint checks (see SQLiteAnalyser for example)

        return $issues;
    }

    public function supportsRowCounting(): bool
    {
        return true; // PostgreSQL provides row estimates
    }

    public function getRowsExamined(array $explainResults): int
    {
        // Sum up "Plan Rows" from EXPLAIN output
        return 0;
    }
}

protected function setUp(): void
{
    parent::setUp();

    self::registerQueryAnalyser(new PostgreSQLAnalyser);
}

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:

// Get all executed queries with their SQL, bindings, and timing
$queries = self::getQueriesExecuted();
// Returns: [['query' => 'SELECT...', 'bindings' => [...], 'time' => 0.45], ...]

// Get total number of queries executed
$count = self::getQueryCount();

// Get lazy loading violations from the last assertion
$violations = self::getLazyLoadingViolations();
// Returns: [['model' => 'App\Models\User', 'relation' => 'posts'], ...]

// Get detailed EXPLAIN results from the last index analysis
$results = self::getIndexAnalysisResults();
// Returns: [['query' => '...', 'bindings' => [...], 'issues' => [...], 'explain' => [...]], ...]

// Get duplicate queries from the last check
$duplicates = self::getDuplicateQueries();
// Returns: ['key' => ['count' => 2, 'query' => '...', 'bindings' => [...]], ...]

// Get total query execution time in milliseconds
$totalTime = self::getTotalQueryTime();

Testing

composer test

License

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

mattiasgeniar / phpunit-query-count-assertions

Maintainers

Details

README

Requirements

Installation

Usage

Available assertions

Tracking queries across the entire test

Failure messages

Lazy loading / N+1 detection

Index usage / full table scan detection

Supported databases

What gets analyzed

Issue severity levels

MySQL / MariaDB detects

SQLite detects

Small table optimization

Duplicate query detection

Row count threshold (MySQL / MariaDB only)

Query timing assertions

Combined efficiency assertion

With a closure

Pest: beforeEach()

PHPUnit: setUp()

Paranoid mode (automatic checks on every test)

Configurable thresholds

MySQL analyser options

Custom analysers

Helper methods

Testing

License