Testing Symfony 5 applications - Working with the database

Article Index

Working with the database

Integration and functional tests usually use a database, and I also do this in the unit tests. In production, this is often a mysql or mariadb system, which can be used for these kind of tests as well. While this works fine, it has some drawbacks:

  • Each test should be independent of the other tests. For this, the database should be identical for each test, which requires a database reset and reload of all fixture data before each test case. This can really slow down the tests.
  • Because all tests use the same database, they must be run one after the other. It is not possible do do multiple tests at the same time.

To work around this, we can use sqlite instead of mysql. While sqlite also is a database with an sql interface, it is stored in a single file. This makes the handling of the tests a lot easier and, if used correctly, also a lot faster.

For tests, I use the Liip test fixtures bundle in combination with an sqlite database. In this way, each single test starts with a clean, fresh database. The fixture bundle ensures, that the sqlite file is cached, so this is done much faster than cleaning and initialising the database for each test case. To give you an example: I run 415 tests cases with 4700+ assertions in about 4 minutes on an i7. Until now, I have not tried to run the tests in parallel on multiple CPU cores to speed things up. In theory though, this should be quite easy in the current setup. We just have to make sure that each tests uses its own sqlite database file.

Problems with case sensitivity

Usually in mysql we use a case insensitive scheme (e.g. utf8_general_ci, _ci as in case insensitive). sqlite on the other hand IS case sensitive. This can lead to problems in testing. To make sure the tests work with either database setting, use the case sensitive tests and fixture data.

Workaround for the foreign key check problem

By default, in sqlite the foreign key checks are disabled, and so are they in doctrine when using sqlite. So when we use sqlite in tests, we could miss some errors which could pop up in production. More importantly, some triggers like cascading deletes or onDelete="SET NULL" do not work when foreign key checks are disabled, since they are part of the foreign key definition. To get this working, the foreign key checks must be enabled before working  with the entity manager. One way to do this is by an event subscriber, as proposed by Tom Newby. This has the advantage, that it also works when using sqlite in the production environment. In my case, I only use sqlite in tests, which makes it a bit easier, by enabling foreign key checks only in our tests. I do this in the setUp method both in the TestCase and WebTestCase, using this method:

/**
 * By default, the foreign key checks are disabled when using sqlite and Doctrine. This prevents cascading
 * deletes, which are managed by the foreign keys. To be able to use the these, the foreign key checks must be
 * enabled manually. This method can enable or disable these checks
 *
 * @param bool $enable
 */
private function useForeignKeys(bool $enable): void
{
    if ($enable) {
        $setting = 'ON';
    } else {
        $setting = 'OFF';
    }
    $this->getEntityManager()
         ->createNativeQuery("PRAGMA foreign_keys = $setting;", new ResultSetMapping())
         ->execute();
}

There is one case to which needs some special attention: loading fixtures fails. There seems to be a not so clean implementation for sqlite in doctrine or in the Liip fixtures bundle, which generates foreign key constraint errors on loading the fixtures. I could not figure out exactly why this fails. To make this work, we have to disable the foreign key support on loading the fixtures, which is done here.