Catching SQL performance issues in PHPUnit and Pest, as part of your test infrastructure

I’ve been on a bit of a SQL performance kick lately. Over at Oh Dear , I wrote a 3-part series about finding, fixing, and automatically detecting SQL performance issues . The last part of that series covers how we catch regressions before they hit production by running checks in our test suite.

That approach worked so well for us that I packaged it up: phpunit-query-count-assertions .

SQL performance test coverage#

The core idea is simple. Wrap any code you want to analyze and let the package figure out if there are performance issues:

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

class YourTest extends TestCase
{
    use AssertsQueryCounts;

    public function test_loading_users_is_efficient(): void
    {
        $this->trackQueries();

        // Your code that runs queries
        $users = User::with('posts')->get();

        $this->assertQueriesAreEfficient();
    }
}

That single assertQueriesAreEfficient() call checks for N+1 queries, duplicate queries running multiple times, and runs EXPLAIN on your queries to catch full table scans and missing indexes.

Real gains#

At Oh Dear, this approach eliminated over 15% of our SQL read queries. These were subtle duplicate queries that slipped through code review. Nobody noticed we were running the same query twice in a request. Now our test suite catches it automatically.

It also catches index regressions. Add a new column and forget to index it? Your tests will tell you before production does.

The package supports PHPUnit 11, Pest 3, Laravel 11 and 12, and PHP 8.2+. It handles multiple database connections too, which is handy if you’re using read replicas.

Laravel package installation#

Install it with:

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

For the full story on why this matters and how we built it, check out the series on Oh Dear:

  1. Finding the right queries to fix
  2. Analysing and fixing the slow queries
  3. Automatic detection and regression testing

The package is on GitHub: mattiasgeniar/phpunit-query-count-assertions . The README contains a ton more examples and configuration options.