PHP code example of mattiasgeniar / phpunit-query-count-assertions

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

mattiasgeniar / phpunit-query-count-assertions example snippets

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

class CertificateHealthCheckTest extends TestCase
{
    use AssertsQueryCounts;

    public function test_health_checker_is_efficient(): void
    {
        // Setup - create test data (these queries aren't tracked)
        $certificate = Certificate::factory()->expired()->create();
        $run = new InMemoryRun();

        // Track only the code under test
        $this->trackQueries();
        app(CertificateHealthChecker::class)->perform($run);
        $this->assertQueriesAreEfficient();
    }
}

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;
use Mattiasgeniar\PhpunitQueryCountAssertions\Drivers\DoctrineDriver;
use Symfony\Bundle\FrameworkBundle\Test\KernelTestCase;

// For KernelTestCase (unit/integration tests)
class YourTest extends KernelTestCase
{
    use AssertsQueryCounts;

    protected function setUp(): void
    {
        parent::setUp();
        self::bootKernel();
        $this->setUpQueryAssertions();
    }

    private function setUpQueryAssertions(): void
    {
        $driver = self::getContainer()->get('test.query_assertions.driver');
        $connection = self::getContainer()->get('doctrine.dbal.default_connection');
        $driver->registerConnection('default', $connection);
        self::useDriver($driver);
    }

    public function test_queries(): void
    {
        $this->trackQueries();
        // ... your test code
        $this->assertQueryCountMatches(2);
    }
}

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;
use Symfony\Bundle\FrameworkBundle\Test\WebTestCase;

// For WebTestCase (functional/controller tests)
class YourControllerTest extends WebTestCase
{
    use AssertsQueryCounts;

    public function test_queries(): void
    {
        $client = static::createClient(); // Boots kernel automatically

        // Set up query assertions AFTER createClient()
        $driver = self::getContainer()->get('test.query_assertions.driver');
        $connection = self::getContainer()->get('doctrine.dbal.default_connection');
        $driver->registerConnection('default', $connection);
        self::useDriver($driver);

        $this->trackQueries();
        $client->request('GET', '/api/users');
        $this->assertQueryCountMatches(2);
    }
}

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;
use Mattiasgeniar\PhpunitQueryCountAssertions\Drivers\PhalconDriver;

class YourTest extends TestCase
{
    use AssertsQueryCounts;

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

        // Get DB adapter from DI and register with driver
        $driver = new PhalconDriver();
        $driver->registerConnection('default', $this->getDI()->get('db'));

        self::useDriver($driver);
    }

    public function test_queries(): void
    {
        $this->trackQueries();
        // ... your test code
        $this->assertQueryCountMatches(2);
    }
}

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

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

// No queries (cached?)
$this->assertNoQueriesExecuted(fn() => $this->getCachedData());

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

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

class YourTest extends TestCase
{
    use AssertsQueryCounts;

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

        $this->trackQueries();
    }

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

        $this->assertQueryCountMatches(5);
    }
}

// Track all connections (default)
$this->trackQueries();

DB::select('SELECT 1');                         // Tracked
DB::connection('replica')->select('SELECT 2');  // Also tracked

$queries = self::getQueriesExecuted();
// $queries[0]['connection'] === 'mysql'
// $queries[1]['connection'] === 'replica'

// Track only the replica connection
$this->trackQueries('replica');

// Track multiple specific connections
$this->trackQueries(['mysql', 'replica']);

// 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 () {
    // ...
});

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

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

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

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

// 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');
});

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

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

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

uses(AssertsQueryCounts::class);

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

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();
});

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

class DashboardTest extends TestCase
{
    use AssertsQueryCounts;

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

        $this->trackQueries();
    }

    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();
    }
}

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

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

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

abstract class TestCase extends BaseTestCase
{
    use AssertsQueryCounts;

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

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

use Mattiasgeniar\PhpunitQueryCountAssertions\Attributes\DisableQueryTracking;

class DashboardTest extends TestCase
{
    use AssertsQueryCounts;

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

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

    // This test is checked normally
    public function test_dashboard_loads_efficiently(): void
    {
        $this->get('/dashboard');
    }

    // This test opts out of query tracking
    #[DisableQueryTracking]
    public function test_heavy_seeder_setup(): void
    {
        $this->seed(LargeDatasetSeeder::class);
        // ...
    }
}

use Mattiasgeniar\PhpunitQueryCountAssertions\Attributes\DisableQueryTracking;

#[DisableQueryTracking]
class MigrationTest extends TestCase
{
    use AssertsQueryCounts;

    // All tests in this class skip query tracking
}

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: 10)
        self::registerQueryAnalyser(
            (new MySQLAnalyser)->withMinRowsForScanWarning(500)
        );

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

use Mattiasgeniar\PhpunitQueryCountAssertions\Contracts\ConnectionInterface;
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(ConnectionInterface $connection, string $sql, array $bindings): array
    {
        return $connection->select('EXPLAIN (FORMAT JSON) ' . $sql, $bindings);
    }

    public function analyzeIndexUsage(array $explainResults, ?string $sql = null, ?ConnectionInterface $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);
}

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

// 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' => [...], 'locations' => [['file' => '...', 'line' => 123]]], ...]

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

Expected 1 queries, got 3.
Queries executed:
  1. [0.45ms] SELECT * FROM users WHERE id = ?
      Bindings: [1]
      Locations:
        #1: tests/Feature/UserTest.php:42
  2. [0.32ms] SELECT * FROM posts WHERE user_id = ?
      Bindings: [1]
      Locations:
        #1: tests/Feature/UserTest.php:46
  3. [0.28ms] SELECT * FROM comments WHERE post_id IN (?, ?, ?)
      Bindings: [1, 2, 3]
      Locations:
        #1: tests/Feature/UserTest.php:50

Queries exceeding 100ms:

  1. [245.32ms] SELECT * FROM users
     Locations:
       #1: tests/Feature/UserTest.php:42
  2. [102.15ms] SELECT * FROM posts WHERE published = ?
     Bindings: [true]
     Locations:
       #1: tests/Feature/UserTest.php:43