Tips for testing the database in Symfony application
Tags: sqlite, symfony, doctrine, testing
Published: 2021-07-16 08:46:23

---
title: Tips for testing the database in Symfony application
published: true
description: show some tips to test the database in symfony application faster and easier by using memeory database
tags: sqlite, symfony, doctrine, testing
//cover_image: https://direct_url_to_image.jpg
---

## 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](https://www.getastra.com/kb/knowledgebase/how-to-install-sqlite-for-php-on-my-apache-nginx-server/)
```bash

## I installed the extension in alpine docker
RUN apk add --update \
    ...
    php7-mysqli \
    ...
```

👉 Check if `sqlite` installed and enabled
```sh
 php -i | grep sqlite
```

👉 Config Memory DB in Symfony
```yaml
## app/config/packages/test/doctrine.yaml
doctrine:
    dbal:
        connections:
            default:
                driver: 'pdo_sqlite'
                url: '%env(resolve:DATABASE_URL)%'
```

👉 Config app `env` file
```bash
## app/.env.test.local

## :memory: will create the database in memory
DATABASE_URL="sqlite:///:memory:"

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

👉 Create a `DatabaseTestCase`
```php
<?php

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->initDatabase($kernel);

        $this->entityManager = $kernel->getContainer()
            ->get('doctrine')
            ->getManager();
    }

    private function initDatabase(KernelInterface $kernel): void
    {
        $entityManager = $kernel->getContainer()->get('doctrine.orm.entity_manager');
        $metaData = $entityManager->getMetadataFactory()->getAllMetadata();
        $schemaTool = new SchemaTool($entityManager);
        $schemaTool->updateSchema($metaData);
    }
}
```

👉 Example to test the `Repository` class in symfony
```php
final class ScheduleRepositoryTest extends DatabaseTestCase
{
    private ?ScheduleRepository $repository;

    protected function setUp(): void
    {
        parent::setUp();

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

    public function testFindDefault(): void
    {
        $this->assertEmpty($this->repository->findDefault());

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

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

        $this->entityManager->persist($default);
        $this->entityManager->flush();
    }
}
```
The execution of the tests is pretty fast, see the screenshot
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b8ov8kz8nuv50mzfo85t.png)


## 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
```sh
composer require --dev dama/doctrine-test-bundle
```
👉 Config the bundle in `dama_doctrine_test_bundle.yaml`

```yaml
## app/config/packages/test/dama_doctrine_test_bundle.yaml
dama_doctrine_test:
    enable_static_connection: true
    enable_static_meta_data_cache: true
    enable_static_query_cache: true
```
👉 Enable the bundle in `bundles.php`

```php
if ($env === 'test') {
    $bundles[] = new DAMA\DoctrineTestBundle\DAMADoctrineTestBundle();
}
```

👉 Enable the PHPunit listner in `phpunit.xml.dist`: example for `php7.4`, more [config info](https://github.com/dmaicher/doctrine-test-bundle)

```xml
<phpunit>
    ...
    <listeners>
        <listener class="\DAMA\DoctrineTestBundle\PHPUnit\PHPUnitListener" />
    </listeners>
</phpunit>
```