Testing Doctrine Repositories with In-Memory SQLite: Fast Integration Tests in Symfony

1. Why in-memory SQLite for repository tests

A Doctrine repository test with a mocked EntityManager usually only checks whether a method was called, not whether the underlying DQL query or query builder call actually delivers the correct result. This is exactly the gap that an in-memory SQLite database closes: the query is executed for real against an actual, if minimal, relational schema, and a broken join or a wrong where condition shows up immediately instead of silently reaching production.

The second big advantage is speed. A SQLite database with the DSN sqlite:///:memory: only exists in the test process's memory, there is no TCP handshake, no connection setup to an external server, and no disk I/O. Where a repository test against MySQL needs several hundred milliseconds for connection setup and transaction overhead, the same test against in-memory SQLite often runs in under ten milliseconds.

This article shows how to configure a dedicated Doctrine connection for tests, how the schema gets built per test run, how fixtures generate test data, and where the limits of this strategy lie when production grade SQL features are needed.

2. Test environment: configuring a dedicated Doctrine connection

The first step is a dedicated doctrine.yaml configuration for the test environment that uses the pdo_sqlite driver and the special DSN sqlite:///:memory:. This configuration overrides the production grade MySQL or PostgreSQL connection exclusively in config/packages/test/doctrine.yaml, so the development and production environments remain untouched.

It matters that Symfony actually opens a new connection for every new test process. Since :memory: databases are tied to the lifetime of the connection, the schema disappears once the connection closes. For parallel test execution with paratest, this means every worker process automatically gets its own isolated in-memory SQLite instance, without tests overwriting each other's data.

# config/packages/test/doctrine.yaml
doctrine:
    dbal:
        driver: 'pdo_sqlite'
        url: 'sqlite:///:memory:'
        charset: UTF8
    orm:
        auto_generate_proxy_classes: true
        enable_lazy_ghost_objects: true

3. Building the schema per test run with SchemaTool

An in-memory SQLite database always starts empty, there are no migrations to run, because migrations are typically tailored to specific SQL dialects such as MySQL. Instead, one uses Doctrine's SchemaTool, which generates an SQL schema directly from the entity mappings and creates it inside the SQLite database. This bypasses migration files entirely and works even when migrations contain SQL dialect specific statements that SQLite does not understand.

The SchemaTool setup belongs in a reusable test base class that all repository tests inherit from. In the setUp() method the schema gets created for all registered entities, in tearDown() it gets dropped again. This way every test starts with a guaranteed empty but structurally correct schema, with no leftovers from previous test runs.

<?php

declare(strict_types=1);

namespace App\Tests\Integration;

use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Tools\SchemaTool;
use Symfony\Bundle\FrameworkBundle\Test\KernelTestCase;

abstract class SqliteRepositoryTestCase extends KernelTestCase
{
    protected EntityManagerInterface $entityManager;

    protected function setUp(): void
    {
        self::bootKernel();

        /** @var EntityManagerInterface $entityManager */
        $entityManager = static::getContainer()->get(EntityManagerInterface::class);
        $this->entityManager = $entityManager;

        $metadata = $this->entityManager->getMetadataFactory()->getAllMetadata();
        $schemaTool = new SchemaTool($this->entityManager);
        $schemaTool->dropSchema($metadata);
        $schemaTool->createSchema($metadata);
    }

    protected function tearDown(): void
    {
        $this->entityManager->close();
        parent::tearDown();
    }
}

4. Generating test data with fixtures and Foundry

After the schema is built, a Doctrine repository test needs concrete test data. The zenstruck/foundry library has become the go to choice here, since it provides entity factories with sensible default values and only explicitly overrides the fields relevant to the given test. Instead of manually setting every required field of an entity, a call like ProductFactory::createOne(['stock' => 0]) creates a complete, valid product with exactly one deviating field.

It matters that fixtures inside the in-memory SQLite database get persisted through the same test class's entity manager, not through a separate connection. Since :memory: databases exist per connection, a second connection would see a completely empty, independent schema, even if data had previously been written over the first connection. This pitfall especially affects console commands that try to load fixtures separately.

<?php

declare(strict_types=1);

namespace App\Tests\Integration\Repository;

use App\Tests\Factory\ProductFactory;

final class ProductRepositoryTest extends SqliteRepositoryTestCase
{
    public function testFindOutOfStockProducts(): void
    {
        ProductFactory::createOne(['name' => 'In stock item', 'stock' => 10]);
        ProductFactory::createOne(['name' => 'Out of stock item', 'stock' => 0]);
        ProductFactory::createOne(['name' => 'Also out of stock', 'stock' => 0]);

        $repository = $this->entityManager->getRepository(\App\Entity\Product::class);
        $result = $repository->findOutOfStockProducts();

        self::assertCount(2, $result);
    }
}

5. Testing repository methods against real queries

The real value of a Doctrine repository test with SQLite shows up with complex query builder constructs involving several joins, subqueries, or aggregating functions. A test that only checks that $repository->findAll() was called says nothing about the correctness of a hand written method like findTopSellingProductsInCategory(). Run against a real, if small, database, the same test immediately reveals when a join points the wrong direction or an aggregation groups the wrong rows.

A proven pattern: every non trivial repository method has at least one Doctrine repository test that covers both the positive case, where data is found, and the negative case, where the query deliberately stays empty. Both cases together catch most of the query builder mistakes that pure unit tests with mocks systematically miss.

6. Isolation between tests: transactions and rollback

Even within a single in-memory SQLite database, tests need to stay isolated from each other so one test does not see another's data. The strategy shown in the previous section, with dropSchema and createSchema in every setUp(), is the most robust variant, but has a speed disadvantage once many entities are registered, since the entire schema is rebuilt per test.

A faster alternative is to build the schema once per test class and wrap every individual test in a database transaction that always rolls back at the end, regardless of whether the test succeeded. This pattern, often known through DAMADoctrineTestBundle, keeps the database unchanged between tests, while individual test cases stay fully isolated since no commit ever happens.

<?php

declare(strict_types=1);

// config/packages/test/dama_doctrine_test_bundle.yaml equivalent behaviour,
// manually implemented for clarity:

protected function setUp(): void
{
    self::bootKernel();
    $this->entityManager = static::getContainer()->get(EntityManagerInterface::class);
    $this->entityManager->getConnection()->beginTransaction();
}

protected function tearDown(): void
{
    $this->entityManager->getConnection()->rollBack();
    $this->entityManager->close();
    parent::tearDown();
}

7. Limits of SQLite compared to MySQL

SQLite is not production identical to MySQL or PostgreSQL, so a Doctrine repository test against SQLite does not check every peculiarity relevant in production. Full text search functions, JSON column operators, certain date and time functions, and the case sensitivity behavior of string comparisons all differ noticeably between the database systems in places. A test that passes in SQLite can still fail in MySQL if the query relies on a MySQL specific feature that doctrine/dbal does not fully abstract.

The pragmatic recommendation: use in-memory SQLite for the bulk of repository tests concerned with general query logic, joins, and simple aggregations. For repository methods that explicitly use database specific functions such as MATCH AGAINST in MySQL or JSON_EXTRACT, keep a separate, smaller set of integration tests running against a real MySQL instance in Docker, instead of blindly transferring the findings from SQLite.

8. Common mistakes in SQLite test configurations

The most common mistake is accidentally using the production doctrine.yaml in the test environment too, causing tests to run against a real MySQL database without that being intended. A second common mistake concerns foreign key constraints: SQLite does not enforce foreign key relationships by default unless PRAGMA foreign_keys = ON is explicitly set, which lets a test stay incorrectly green even though it would fail on a constraint violation in MySQL.

<?php

// WRONG: no explicit test doctrine.yaml — falls back to production connection
// config/packages/doctrine.yaml only, nothing under config/packages/test/

// RIGHT: explicit override, foreign keys enforced for realistic constraint checks
// config/packages/test/doctrine.yaml
// doctrine:
//   dbal:
//     driver: 'pdo_sqlite'
//     url: 'sqlite:///:memory:'
//     options:
//       1002: 'PRAGMA foreign_keys = ON;' // PDO::MYSQL_ATTR_INIT_COMMAND equivalent for SQLite

A third mistake concerns parallel test execution: if a SQLite file is accidentally used instead of :memory:, for example sqlite:///%kernel.project_dir%/var/test.db, every parallel test process shares the same file and blocks each other through write locks. In-memory SQLite avoids this problem entirely, since every connection gets its own invisible instance.

9. In-memory SQLite compared to alternatives

Besides in-memory SQLite, there are other common strategies for repository tests. The following table compares them by speed, closeness to production, and setup effort.

The pragmatic combination for most Symfony projects: in-memory SQLite for the bulk of fast repository tests in local development and CI, complemented by a smaller suite of real MySQL integration tests for database specific functions, run before every release.

10. Summary

Doctrine repository tests with in-memory SQLite solve a central problem: query builder methods and DQL calls get checked against a real relational schema, without the overhead of an external database server. The configuration in config/packages/test/doctrine.yaml with the DSN sqlite:///:memory: takes only a few lines, the schema comes from Doctrine's SchemaTool instead of migrations, and Foundry factories generate realistic test data with minimal boilerplate.

The clear boundary remains important: in-memory SQLite is excellent for general query logic, joins, and aggregations, but no complete substitute for MySQL specific features. Anyone who knows this boundary and adds a smaller, targeted MySQL suite for database specific functions gets a test pyramid that is both fast and reliable.

11. FAQ: Doctrine repository tests with in-memory SQLite