Tricks to speed up tests with database

Posted on

Not everything is worth to unit testing.
Sometimes Integration testing is better:

An integration test has the target to test the behavior of a component or the integration between a set of components. The term functional test is sometimes used as synonym for integration test. Integration tests check that the whole system works as intended, therefore they are reducing the need for intensive manual tests.

See: vogella

Components of your application like repositories which are dealing with database should be tested with real database connection. Another case is testing controllers, endpoints or CLI commands. Integration testing with real database connection is really valuable.

In many cases which I have seen, tests that work with database have very poor performance and it disturbs during development. My typical cases were with Mysql database platform and Doctrine ORM. That’s why I created examples using Mysql and Doctrine ORM. I have some tricks to speed up the tests with database and I want to present it below.

I also prepared the benchmarks to compare inefficient solution with more efficient one. Benchmarks were made on CPU i7, 16GB RAM, HDD SSD. Every example is available here: https://github.com/lzakrzewski/tests-with-database-examples. You can run it on your own machine :)

At the beginning: Why Your tests should be fast?

The tests with fast feedback are important during development. Fast feedback of the tests allows to run test suites frequently and avoids creating bugs or needles code. If we can run the tests frequently than we can stay "green" its especially useful during refactoring. Another obvious advantage of the tests which gives you the fast feedback is saving time, we all know how the time is important in the work of the developer.

Trick no 1: Don't load redundant fixtures!

A test fixture is a fixed state in code which is tested used as input for a test.

See: vogella

Very popular case which I met is loading whole fixtures before every single test:

<?php
//..

class FixturesLoadingTest extends TestCase
{
    /** @test */
    public function loading_whole_fixtures()
    {
        $this->loadWholeFixtures();

        ApplyDiscountUseCase::create($this->getEntityManager())
            ->apply(0.5);

        $this->assertEquals(50, $this->findItemByName('teapot_1')->price());
        $this->assertEquals(200, $this->findItemByName('phone_1')->price());
    }

    private function loadWholeFixtures()
    {
        $loader = new Loader();
        $loader->addFixture(new LoadItems());

        $purger   = new ORMPurger($this->getEntityManager());
        $executor = new ORMExecutor($this->getEntityManager(), $purger);
        $executor->execute($loader->getFixtures());
    }

    //..
}

Its extremely inefficient. Libraries like data-fixtures are great for filling up the database with exemplary data (for clicking), but using this component to load fixtures for testing is redundant. On the other hand this test is unreadable. For me the reason why the product name is "item_1" and why price is "50" is still unclear.

I have also created more efficient version of this test:

<?php
//..

class FixturesLoadingTest extends TestCase
{
    /** @test */
    public function loading_minimal_fixtures()
    {
        $this->givenDatabaseIsClear();

        $this->add(new Teapot('brand-new-teapot', 100.0));
        $this->add(new Phone('amazing-phone', 400.0));

        ApplyDiscountUseCase::create($this->getEntityManager())
            ->apply(0.5);

        $this->assertEquals(50, $this->findItemByName('brand-new-teapot')->price());
        $this->assertEquals(200, $this->findItemByName('amazing-phone')->price());
    }

    //..
}

Its much better. Every single test should have it's own minimal database setup to reduce time of test executing. I can see clearly that previous price was 100, after executing usecase ApplyDiscountUseCase price was discounted.

I've prepared the benchmark of 500 tests with 500 fixtures to compare 500 tests with minimal setup:

Test method repeated times total execution time
FixturesLoadingTest::loading_whole_fixtures 500 12.62 minutes
FixturesLoadingTest::loading_minimal_fixtures 500 1.22 minutes

Trick no 2: Purge database instead drop and create

Dropping and creating schema before every test method is another popular case which has poor performance:

<?php
//..

class EmptyDatabaseTest extends TestCase
{
    /** @test */
    public function drop_and_create_database()
    {
        $this->dropAndCreateSchema();

        $this->add(new Teapot('brand-new-teapot', 100.0));
        $this->add(new Phone('amazing-phone', 400.0));

        ApplyDiscountUseCase::create($this->getEntityManager())
            ->apply(0.5);

        $this->assertEquals(50, $this->findItemByName('brand-new-teapot')->price());
        $this->assertEquals(200, $this->findItemByName('amazing-phone')->price());
    }

    private function dropAndCreateSchema()
    {
        $schemaTool = new SchemaTool($this->getEntityManager());
        $metadata   = $this->getEntityManager()->getMetadataFactory()->getAllMetadata();

        $schemaTool->dropSchema($metadata);
        $schemaTool->createSchema($metadata);
    }

    //..
}

Much faster is to just purge database before every test method:

<?php
//..

class EmptyDatabaseTest extends TestCase
{
    /** @test */
    public function purge_database()
    {
        $this->purgeDatabase();

        $this->add(new Teapot('brand-new-teapot', 100.0));
        $this->add(new Phone('amazing-phone', 400.0));

        ApplyDiscountUseCase::create($this->getEntityManager())
            ->apply(0.5);

        $this->assertEquals(50, $this->findItemByName('brand-new-teapot')->price());
        $this->assertEquals(200, $this->findItemByName('amazing-phone')->price());
    }

    private function purgeDatabase()
    {
        $purger = new ORMPurger($this->getEntityManager());
        $purger->purge();
    }

    //..
}

I used ORMPurger for purging. It creates sql to purify every table and executes it in right order.

And some benchmark:

Test method repeated times total execution time
EmptyDatabaseTest::drop_and_create_database 500 2.93 minutes
EmptyDatabaseTest::purge_database 500 1.17 minutes

Trick no 3: Test repositories or projections in single transaction

If you are testing simple component which does not commit transaction internally, then you can run every test method in single transaction:

<?php
//..

class RepositoryTest extends TestCase
{
    /** @test */
    public function reverting_transaction()
    {
        $this->givenDatabaseIsClear();
        $this->getEntityManager()->beginTransaction();

        $this->add(new Teapot('brand-new-teapot', 10.0));
        $this->add(new Phone('amazing-phone', 400.0));

        $items = ItemRepository::create($this->getEntityManager())
            ->paginate(1, 2);

        $this->assertCount(2, $items);

        $this->getEntityManager()->rollback();
    }

    //..
}

Benchmark:

Test method repeated times total execution time
RepositoryTest::purge_database 500 1.13 minutes
RepositoryTest::reverting_transaction 500 25.23 seconds

Trick no 4: Restore cached copy of database

If you are refactoring legacy app and probably you aren't able to remove whole fixtures now, there is also solution for this case. In most cases when you use Doctrine ORM with DQL queries and Mysql platform you can try to replace your database with Sqlite platform during testing:

<?php
//..

class FixturesLoadingTest extends TestCase
{
    /** @test */
    public function loading_whole_fixtures_from_cached_copy()
    {
        $this->givenSqliteDatabaseWasConnected();
        $this->loadWholeFixturesFromCachedCopy();

        ApplyDiscountUseCase::create($this->getEntityManager())
            ->apply(0.5);

        $this->assertEquals(50, $this->findItemByName('teapot_1')->price());
        $this->assertEquals(200, $this->findItemByName('phone_1')->price());
    }

    private function loadWholeFixturesFromCachedCopy()
    {
        $path       = SqliteConfig::getParams()['path'];
        $backupPath = $path.'.bck';

        if (!file_exists($backupPath)) {
            $this->loadWholeFixtures();

            file_put_contents($backupPath, file_get_contents($path));
        }

        file_put_contents($path, file_get_contents($backupPath));
    }

    //..
}

Configuration of doctrine with Sqlite was described here: pdo-sqlite. Sqlite is the database which is storied in a file. This file could be created before executing the first test method, and then this file could be restore before every other test method.

I prefer to run the tests with the same database platform as on production, but Sqlite could be perfect in fighting with legacy code.
Another advantage of using Sqlite platform is that it has better performance while running weekly designed schema (updates without indexes etc.) see speed.

Benchmark:

Test method repeated times total execution time
FixturesLoadingTest::loading_whole_fixtures 500 12.62 minutes
FixturesLoadingTest::loading_whole_fixtures_from_cached_copy 500 28.1 seconds

Trick no 5: Cache purge query

Creating the query which purges the database needs to read the whole metadata of entities which takes time. It could be done only once before the first test and then it could be restored from cache:

<?php
//..

class EmptyDatabaseTest extends TestCase
{
    /** @var string */
    private static $purgeQueryInMemory;

    /** @test */
    public function purge_database_with_cached_purge_query()
    {
        $this->executedCachedQueryToPurgeDatabase();

        $this->add(new Teapot('brand-new-teapot', 100.0));
        $this->add(new Phone('amazing-phone', 400.0));

        ApplyDiscountUseCase::create($this->getEntityManager())
            ->apply(0.5);

        $this->assertEquals(50, $this->findItemByName('brand-new-teapot')->price());
        $this->assertEquals(200, $this->findItemByName('amazing-phone')->price());
    }

    private function executedCachedQueryToPurgeDatabase()
    {
        $conn = $this->getEntityManager()->getConnection();

        if (null === self::$purgeQueryInMemory) {
            self::$purgeQueryInMemory = $this->createQueryToPurgeDatabase();
        }

        $conn->exec(self::$purgeQueryInMemory);
    }

    //..
}

Benchmark:

Test method repeated times total execution time
EmptyDatabaseTest::purge_database 500 1.17 minutes
EmptyDatabaseTest::purge_database_with_cached_purge_query 500 32.76 seconds

I highly recommend to take a moment to profile performance of your tests. It certainly will benefit in the future.

I also created the library which supports caching purge query and caching Sqlite out of the box. I urge to try this: https://github.com/lzakrzewski/DoctrineDatabaseBackup any feedback will be very helpful!

Deploy sculpin

Posted on

I created a very simple script to quickly deploy my blog using Sculpin. It was already described on scuplin readme https://sculpin.io/getstarted/#deploy-sculpin, but I have added some improvements for it :)

Firstly create blank script and open it with your favorite editor:

nano deployment

Copy and paste script below and fill it up with your host data:

#!/usr/bin/env bash
DEPLOY_ENV="prod"

#Configuration
DEPLOY_ADDRESS=username@host.com
DEPLOY_DIR="~/path/to/dir/with/blog"

echo "Deploying to: $DEPLOY_ADDRESS"

#Prepare local
echo "Prepare local"
rm -rf "output_$DEPLOY_ENV/"
sculpin generate --env=$DEPLOY_ENV
cp source/.htaccess "output_$DEPLOY_ENV/"

#Copy files
echo "Coping files"
rsync -avze "ssh" "output_$DEPLOY_ENV/" "$DEPLOY_ADDRESS:$DEPLOY_DIR"

I decided to generate a blog before deployment automatically to ensure that freshly generated version with every change will be deployed. Sculpin currently doesn't copy from source/ to output_{env}/ files prefixed with "." so I need to copy .htaccess before deployment (see #121).

Add file execution permissions and move it to bin/:

 chmod +x deployment & mkdir -p bin & mv deployment bin/

Now you can just type:

bin/deployment

and than your blog will be deployed.

Integration testing with slim

Posted on

Slim is very simple framework which is suitable for small projects http://www.slimframework.com/. I'm interested in this framework because it provides Request and Response which implements PSR-7 standards.

My case was: Create small API with slim.

The very important thing for me was to test the overall behavior of application, because of of possibility of regression.. There was not much examples about integration testing with slim but I created my own solution which I present below.

Example of application:

<?php

require __DIR__ . '/../vendor/autoload.php';

use Psr\Http\Message\ResponseInterface;
use Psr\Http\Message\ServerRequestInterface;

$app = new \Slim\App;

//List of posts
$app->get('/posts', function (ServerRequestInterface $request, ResponseInterface $response) {
    $posts = [
        [
            'title' => 'Example post 1',
            'content' => 'Aliquam erat volutpat.',
        ],
        [
            'title' => 'Example post 2',
            'content' => 'Vestibulum suscipit nulla quis orci.',
        ],
        [
            'title' => 'Example post 3',
            'content' => 'Phasellus magna.',
        ],
        [
            'title' => 'Example post 4',
            'content' => 'Sed augue ipsum, egestas nec.',
        ]
    ];

    $query = $request->getQueryParams();

    if (isset($query['page']) && $query['page'] >= 1) {
        $page = $query['page'];

        $postsPerPage = 2;

        $offset = ($page-1) * $postsPerPage;
        $length = $postsPerPage;

        $posts = array_slice($posts, $offset, $length);
    }

    $responseBody = $response->getBody();
    $responseBody->write(json_encode($posts));

    return $response
        ->withHeader('Content-Type', 'application/json')
        ->withStatus(200)
        ->withBody($responseBody);
});

//Add new post
$app->put('/posts/{postId}', function (ServerRequestInterface $request, ResponseInterface $response, $args = []) {
    $postId = $args['postId'];

    $responseBody = $response->getBody();
    $responseBody->write(json_encode(['id' => $postId]));

    return $response
        ->withHeader('Content-Type', 'application/json')
        ->withStatus(201)
        ->withBody($responseBody);
});

return $app;

It's a quite simple blog. Actions of this app are mocked - it's only the example.
There are routes:

  • PUT (add new post),
  • GET (list of post with ability to paginate it).

Now, I want to ensure that logic of adding and listing posts is integrated with slim correctly:

<?php

namespace Lzakrzewski\tests;

class BlogTest extends ApiTestCase
{
    /** @test */
    public function it_adds_new_post()
    {
        $this->request('PUT', '/posts/1', ['title' => 'A new blog post', 'content' => 'Hello world']);

        $this->assertThatResponseHasStatus(201);
        $this->assertThatResponseHasContentType('application/json');
        $this->assertArrayHasKey('id', $this->responseData());
    }

    /** @test */
    public function it_has_list_of_posts()
    {
        $this->request('GET', '/posts');

        $this->assertThatResponseHasStatus(200);
        $this->assertThatResponseHasContentType('application/json');
        $this->assertCount(4, $this->responseData());
    }

    /** @test */
    public function it_paginates_list_of_posts()
    {
        $this->request('GET', '/posts?page=2');

        $this->assertThatResponseHasStatus(200);
        $this->assertThatResponseHasContentType('application/json');
        $this->assertCount(2, $this->responseData());
    }
}

I expect status 201 on creation and status 200 on listing. I have also asserted count of list. In any case I don't need to response with status 500.

I created base class ApiTestCase for BlogTest (I was inspired by WebTestCase from symfony framework):

<?php

namespace Lzakrzewski\tests;

use Slim\App;
use Slim\Http\Environment;
use Slim\Http\Headers;
use Slim\Http\Request;
use Slim\Http\RequestBody;
use Slim\Http\Response;
use Slim\Http\Uri;

abstract class ApiTestCase extends \PHPUnit_Framework_TestCase
{
    /** @var Response */
    private $response;
    /** @var App */
    private $app;

    protected function request($method, $url, array $requestParameters = [])
    {
        $request = $this->prepareRequest($method, $url, $requestParameters);
        $response = new Response();

        $app = $this->app;
        $this->response = $app($request, $response);
    }

    protected function assertThatResponseHasStatus($expectedStatus)
    {
        $this->assertEquals($expectedStatus, $this->response->getStatusCode());
    }

    protected function assertThatResponseHasContentType($expectedContentType)
    {
        $this->assertContains($expectedContentType, $this->response->getHeader('Content-Type'));
    }

    protected function responseData()
    {
        return json_decode((string) $this->response->getBody(), true);
    }

    /** {@inheritdoc} */
    protected function setUp()
    {
        $this->app =  require __DIR__.'/../src/app.php';
    }

    /** {@inheritdoc} */
    protected function tearDown()
    {
        $this->app = null;
        $this->response = null;
    }

    private function prepareRequest($method, $url, array $requestParameters)
    {
        $env = Environment::mock([
            'SCRIPT_NAME' => '/index.php',
            'REQUEST_URI' => $url,
            'REQUEST_METHOD' => $method,
        ]);

        $parts = explode('?', $url);

        if (isset($parts[1])) {
            $env['QUERY_STRING'] = $parts[1];
        }

        $uri = Uri::createFromEnvironment($env);
        $headers = Headers::createFromEnvironment($env);
        $cookies = [];

        $serverParams = $env->all();

        $body = new RequestBody();
        $body->write(json_encode($requestParameters));

        $request = new Request($method, $uri, $headers, $cookies, $serverParams, $body);

        return $request->withHeader('Content-Type', 'application/json');
    }
}

There are methods to create request and assertions. The only setup is require file app.php from src/. Every request invokes application. Notice that during CLI testing Environment should be mocked.

Full working example is available in the link below:
https://github.com/lzakrzewski/slim-integration-testing-example