DEV Community

Xun Zhou
Xun Zhou

Posted on • Updated on

Tips for testing the database in Symfony application

Use SQLite Memory DB

SQLite In-memory database is a great alternative to test the interaction with database in your local environment. As they exist only in the memory of the application, they are truly disposable. And it is also very easy to set up with Symfony applications that use Doctrine.

I use SQLite in-memory database for the integration and functional tests, if I wanna run the entire tests in my local development. It reduces the duration of the execution time from 9 minutes to 50 seconds. It is great, right? Sure, I switch the test database in CI/CD back to MySQl to ensure that everything will be executed against the real test database.

👉 Install php extension to support SQLite: more about install sqlite for php

## I installed the extension in alpine docker
RUN apk add --update \
    php7-mysqli \
Enter fullscreen mode Exit fullscreen mode

👉 Check if sqlite installed and enabled

 php -i | grep sqlite
Enter fullscreen mode Exit fullscreen mode

👉 Config Memory DB in Symfony

## app/config/packages/test/doctrine.yaml
                driver: 'pdo_sqlite'
                url: '%env(resolve:DATABASE_URL)%'
Enter fullscreen mode Exit fullscreen mode

👉 Config app env file

## app/.env.test.local

## :memory: will create the database in memory

## %kernel.project_dir%/db/sqlite3.db3 will breate the database on filesystem
# DATABASE_URL="sqlite:///%kernel.project_dir%/db/sqlite3.db3"
Enter fullscreen mode Exit fullscreen mode

👉 Create a DatabaseTestCase


declare(strict_types = 1);

namespace App\Tests\Utils;

use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Tools\SchemaTool;
use LogicException;
use Symfony\Bundle\FrameworkBundle\Test\KernelTestCase;
use Symfony\Component\HttpKernel\KernelInterface;

class DatabaseTestCase extends KernelTestCase
    protected EntityManagerInterface $entityManager;

    protected function setUp(): void
        $kernel = self::bootKernel();

        if ('test' !== $kernel->getEnvironment()) {
            throw new LogicException('Execution only in Test environment possible!');


        $this->entityManager = $kernel->getContainer()

    private function initDatabase(KernelInterface $kernel): void
        $entityManager = $kernel->getContainer()->get('doctrine.orm.entity_manager');
        $metaData = $entityManager->getMetadataFactory()->getAllMetadata();
        $schemaTool = new SchemaTool($entityManager);
Enter fullscreen mode Exit fullscreen mode

👉 Example to test the Repository class in symfony

final class ScheduleRepositoryTest extends DatabaseTestCase
    private ?ScheduleRepository $repository;

    protected function setUp(): void

        $this->repository = ScheduleRepositoryTest::$container->get(ScheduleRepository::class);

    public function testFindDefault(): void

        $this->assertInstanceOf(Schedule::class, $this->repository->findDefault());

    private function insertDefaultSchedule(): void
        $default = Schedule::defaultSchedule();

Enter fullscreen mode Exit fullscreen mode

The execution of the tests is pretty fast, see the screenshot
Alt Text

Use symfony Test-bundle to benefit from transaction and caching

By using this test bundle, it will begin a transaction before every testcase and roll it back again after the test finished for all configured DBAL connections. This results in a performance boost as there is no need to rebuild the schema, import a backup SQL dump or re-insert fixtures before every testcase. It also includes a StaticArrayCache that will be automatically configured as meta data & query cache for all EntityManagers. This improved the speed and memory usage for all testsuites dramatically.

👉 install the package

composer require --dev dama/doctrine-test-bundle
Enter fullscreen mode Exit fullscreen mode

👉 Config the bundle in dama_doctrine_test_bundle.yaml

## app/config/packages/test/dama_doctrine_test_bundle.yaml
    enable_static_connection: true
    enable_static_meta_data_cache: true
    enable_static_query_cache: true
Enter fullscreen mode Exit fullscreen mode

👉 Enable the bundle in bundles.php

if ($env === 'test') {
    $bundles[] = new DAMA\DoctrineTestBundle\DAMADoctrineTestBundle();
Enter fullscreen mode Exit fullscreen mode

👉 Enable the PHPunit listner in phpunit.xml.dist: example for php7.4, more config info

        <listener class="\DAMA\DoctrineTestBundle\PHPUnit\PHPUnitListener" />
Enter fullscreen mode Exit fullscreen mode

Top comments (2)

jmsche profile image

Hi, I would not recommend to test against a different database system than the one you really use in your app (eg. your app runs against Postgres and your tests against SQLite in this example) as the behaviours can defer.

vikbert profile image
Xun Zhou

yes, your are absolute right. SQLite will not be able to replace the MysQL or PostgreSQL DB, because of its limited features. It is actually your decision at the end, if this approach is ok for your current project. If you have very complex Datbase queries, which are not possible in SQLite. It is not recommended to use in-momory DB for DB test. But the project has simple DB schema and simple DB queries, then SQLite in-memory-DB will be a lightweight alternative for your DB tests.