Testing query counts in functional web tests with Symfony2 and PHPUnit post

Posted on 2012-07-10 by jwage


At OpenSky we were faced with a challenge of being able to evolve functionality fast without having the overhead of developers constantly watching for changes in performance, or the number of queries required for a request. To help solve part of this problem we integrated the Symfony2 profiler with our functional web tests to assert that a request required a certain number of database queries.

First in order to accomplish this we need to create a special test environment named test_logging that will be the same as the normal test environment except profiling and logging is enabled. We don’t want this enabled for all of our tests as it does add some overhead to the request and will slow things down a little bit.

imports:
    - { resource: config_test.yml }

doctrine:
    dbal:
        connections:
            default:
                logging: true

doctrine_mongodb:
    document_managers:
        default:
            logging: true

Now in your PHPUnit functional tests you can issue requests with the test_logging environment client and run assertions afterwards to make sure the request executed the queries you expected.

namespace OpenSky\Bundle\MainBundle\Tests\Functional;

use OpenSky\Bundle\MainBundle\Tests\WebTestCase;

class TestSomeQueryCounts extends WebTestCase
{
    // ...

    public function testQueryCounts()
    {
        $client = static::createClient(array(
            'environment' => 'test_logging'
        ), array(
            'PHP_AUTH_USER' => 'foobar',
            'PHP_AUTH_PW'   => 'foobar',
        ));

        $client->request('GET', '/some_page');
        $response = $client->getResponse();
        $profile = $this->getContainer()->get('profiler')->loadProfileFromResponse($response);

        $numMysqlQueries = $profile->getCollector('db')->getQueryCount();
        $numMongoQueries = $profile->getCollector('mongodb')->getQueryCount();

        $this->assertEquals($numMysqlQueries, 1);
        $this->assertEquals($numMongoQueries, 1);
    }
}

You can abstract this a little bit and add some convenience methods in your base WebTestCase class that would clean this up and make it more reusable. Here is an example:

// ...
class WebTestCase
{
    // ...
    protected function assertResponseQueryCounts(Response $response, $expectedMysql, $expectedMongo)  
    {
        $profile = $this->getContainer()->get('profiler')->loadProfileFromResponse($response);

        $numMysqlQueries = $profile->getCollector('db')->getQueryCount();
        $numMongoQueries = $profile->getCollector('mongodb')->getQueryCount();

        if ($expectedMysql !== $numMysqlQueries) {
            print_r($profile->getCollector('db')->getQueries());
        }
        $this->assertEquals($expectedMysql, $numMysqlQueries);
        if ($expectedMongo !== $numMongoQueries) {
            print_r($profile->getCollector('mongodb')->getQueries());
        }
        $this->assertEquals($expectedMongo, $numMongoQueries);
    }

    protected function assertRequestQueryCounts($client, $url, $method, $expectedMysql, $expectedMongo)
    {
        if ($client->getKernel()->getEnvironment() !== 'test_logging') {
            throw new \InvalidArgumentException(
                'You must pass a client created with createClient(array("environment" => "test_logging"))'
            );
        }
        $client->request($method, $url);
        $this->assertResponseQueryCounts($client->getResponse(), $expectedMysql, $expectedMongo);
    }
}

Now the example functional test we showed in the beginning can be cleaned up quite a bit to use the convenience methods we created above:

// ...
class TestSomeQueryCounts extends WebTestCase
{
    // ...
    public function testQueryCounts()
    {
        $client = static::createClient(array(
            'environment' => 'test_logging'
        ), array(
            'PHP_AUTH_USER' => 'foobar',
            'PHP_AUTH_PW'   => 'foobar',
        ));

        $this->assertRequestQueryCounts($client, '/some_page', 'GET', 1, 1);
    }
}

I hope this is a helpful tip for someone else.