A Slow API Incident Leading to Database Query Testing and Fully Automated API Test Assertions

Today during client-side testing, feedback indicated a slow API response. With the responsible colleague on leave, I investigated the project and confirmed the issue.

Added SQL logging in the controller:

# SQL logging
DB::listen(fn(QueryExecuted $query) => Log::info($query->sql));
# Log output
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
... (repeated 24 times)

The logs revealed multiple identical queries - a classic N+1 problem caused by missing eager loading
Database query pseudocode:

class Controller
{
    public function index()
    {
        $models = Model::query()->limit(20)->get();
        return ModelsResource::collection($models);
    }
}

class ModelsResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id'         => $this->id,
            'name'       => $this->title,
            'album_id'   => $this->album_id,
            // Problematic line
            'album_name' => $this->album->title, 
        ];
    }
}

The resource accessed the album relationship without eager loading
Solution: Add eager loading

$models = Model::query()->with('album')->limit(20)->get();

Prevention Strategies

Problem detection is costly - better to prevent through automation
Implemented HTTP testing with SQL query monitoring in base TestCase:

public function json($method, $uri, array $data = [], array $headers = [])
{
    $maxCount = 10;
    DB::enableQueryLog();
    
    $response = parent::json($method, $uri, $data, $headers);
    
    $queries = collect(DB::getQueryLog())->pluck('query');
    $this->assertLessThan(
        $maxCount,
        $queries->count(),
        sprintf("Endpoint:%s[%s], SQL:%d\r\n%s", $uri, $method, $maxCount, $queries->implode("\r\n"))
    );
    
    DB::flushQueryLog();
    return $response;
}

Enhanced validation using unique query comparison:

$duplicateQueries = $queries->count() - $queries->unique()->count();
$this->assertLessThan($maxDuplicates, $duplicateQueries, "Excessive duplicate queries");

Route coverage validation via custom test suite:

<testsuite name="End">
    <file>tests/EndApiTest.php</file>
</testsuite>

Route validation test class:

class EndApiTest extends TestCase
{
    public function testRoutes()
    {
        $router = $this->app->make(Router::class);
        $definedRoutes = collect($router->getRoutes()->getRoutes());
        
        $definedRoutes->each(function ($route) {
            $this->assertContains(
                $route->uri(),
                self::$testedRoutes,
                "Untested route: {$route->uri()}"
            );
        });
    }
}

Key Takeaways
Automated testing with query monitoring and route coverage validation helps prevent performance regressions and ensures API reliability. The system now automatically:

Limits total SQL queries per endpoint
Detects duplicate queries
Enforces 100% route test coverage
Provides immediate feedback during development

This approach reduces manual verification efforts while maintaining code quality standards.