Let’s assume you are using fixtures to populate your test database. Before each test execution, you would have to load fixtures all over again in case previous tests made some changes to the database. This flow is very slow, especially on larger databases with big number of functional tests.

SQLite solution

Many developers decide to switch tests to SQLite which is file-based database. When an application uses SQLite, the integration works with functional and direct calls made to a file holding the data instead of communicating through an interface of sorts (i.e. ports, sockets). This makes SQLite extremely fast and efficient, and also powerful thanks to the library’s underlying technology.

The idea is to have a SQLite file filled with test data and copy it before each test, ensuring same database state at all times.

This approach speeds up refreshing database a lot and it is a great solution if SQLite meets all the needs of your application. But if you are using MySQL or PostgreSQL for production, you can’t be sure your code will react the same as it does on SQLite. Let’s try to speed it up using MySQL.

MySQL solution

We will use MySQL and Alice Data Fixtures for the sake of example, but this approach can be applied to PostgreSQL too.

The idea is to make a custom command for running tests. Command will first import fixtures and then export it to an SQL file. After that it will run PHPUnit tests as usual. Each test will load database from exported SQL file using shell command in setUp() method.

First step is to make Symfony console command:

<?php
namespace AppBundle\Command;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\ArrayInput;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
class RunTestsCommand extends Command
{
    protected function configure(): void
    {
        $this
            ->setName('locastic:phpunit')
            ->setDescription('Resets database and runs unit tests')
            ->addArgument('test', InputArgument::OPTIONAL)
            ->addOption(
                'coverage',
                null,
                InputOption::VALUE_REQUIRED,
                'Should coverage be run?',
                false
            );
        ;
    }
    protected function execute(InputInterface $input, OutputInterface $output): void
    {
//        get database parameters
        $container = $this->getApplication()->getKernel()->getContainer();
        $db_name = $container->getParameter('database_name');
        $db_user = $container->getParameter('database_user');
        $db_pass = $container->getParameter('database_password');
        $output->writeln(
            [
                '',
                'Reseting database and importing fixtures',
                '========================================',
                '',
            ]
        );
//       restart database schema
        $command = $this->getApplication()->find('doctrine:schema:drop');
        $arguments = [
            'command' => 'doctrine:schema:drop',
            '--force' => true,
            '-e' => 'test',
        ];
        $command->run(new ArrayInput($arguments), $output);
        $command = $this->getApplication()->find('doctrine:schema:update');
        $arguments = [
            'command' => 'doctrine:schema:update',
            '--force' => true,
        ];
        $command->run(new ArrayInput($arguments), $output);
//      create folder for test database
        shell_exec('mkdir -m 777 -p var/db');
//      load fixtures
        $command = $this->getApplication()->find('hautelook:fixtures:load');
        $arguments = [
            'command' => 'hautelook:fixtures:load',
            '--no-interaction' => true,
        ];
        $command->run(new ArrayInput($arguments), $output);
//      export database to test_db.sql file
        shell_exec(
            'mysqldump -u '.$db_user.' -p'.$db_pass.' '.$db_name.' > var/db/test_db.sql --add-drop-table'
        );
        $output->writeln(
            [
                '',
                'Running phpunit tests',
                '=====================',
                '',
            ]
        );
//      create phpunit command using arguments from input
        $phpunitCommand = 'phpunit';
        if ($input->getArgument('test')) {
            $phpunitCommand .= ' ' . $input->getArgument('test');
        }
        if ($input->getOption('coverage')) {
            $phpunitCommand .= ' --coverage-html ./coverage';
        }
//      run phpunit command
        $this->runShellCommand($phpunitCommand);
    }
    /**
     * @param $cmd
     */
    private function runShellCommand($cmd): void
    {
        while (@ ob_end_flush()) {
            ;
        } // end all output buffers if any
        $proc = popen($cmd, 'r');
        while (!feof($proc)) {
            echo fread($proc, 4096);
            @ flush();
        }
    }
}

Second and last step is to import test database before each test:

<?php
namespace AppBundle\Tests;
use PHPUnit\Framework\TestCase;
class BaseTestCase extends TestCase
{
    private $dbUser = 'ENTER_USER';
    private $dbPass = 'ENTER_PASS';
    private $dbName = 'ENTER_DB_NAME';
    private $dbHost = 'ENTER_DB_HOST';
    public function setUp()
    {
        
//      ...
        
        $command = 'mysql -u '.$this->dbUser.' -p'.$this->dbPass.' '.$this->dbName.'<var/db/test_db.sql --host '.$this->dbHost;
        shell_exec($command);
    }
}

Run command:

php bin/console locastic:phpunit — env=test

You only need to use this command for running tests when you made changes to database schema or fixtures, otherwise just use the standard PHPUnit command.

This is not as fast as using SQLite, but it does speed up database refreshing a lot and also allows you to have same test and production environment.

** UPDATE **

When we published the blog, we received a lot of feedback, some of that feedback was really useful, so we have even faster solution now:

Rollback transaction solution

dmaicher/doctrine-test-bundle provides PHPUnit listener which wraps transactions executed in test and rolls it back afterwards. As a result, we don’t need to reload full database each time, it’s enough to load it just once.

Using this approach instead of the one mentioned before, we managed to speed up our tests by approximately 40%:

Tests execution time with reloading database

Tests execution time with transaction rollback

When you take everything into consideration, it’s really easy to notice the improvements regarding test speed. I hope that it will be really useful for everyone out there. Huge thanks to everyone on Reddit for their suggestions, keep them coming!

Results for tag #sqlite

Speed-up database refreshing in PHPUnit tests