PHPUnit Database Tests: When Real DB Access Makes Sense

1. When Real DB Access Makes Sense

The decision "real DB access or mock" is not a question of principle, but of purpose. If the test checks whether business logic in a service class works correctly, a mock repository is the right choice, because the test should isolate the logic, not the database layer. But if the test checks whether a repository persists correctly, loads correctly, produces correct SQL queries, and returns correct results, then real DB access must happen.

There are four classes of bugs that can only be found with a real database. First, SQL errors: wrong column names, missing joins, syntax errors in raw queries. Second, mapping errors: ORM configuration mistakes that cause objects to be saved or loaded incorrectly. Third, performance problems: N+1 queries, missing indexes, unsuitable lazy-loading strategies. Fourth, constraint violations: unique constraints and foreign-key constraints that produce errors for certain data combinations. For all four classes of bugs, real DB access is indispensable.

The rule of thumb: repository classes and data-access-layer code need real database tests. Service classes that use repositories as dependencies can be tested with mock repositories. Domain logic that knows nothing about the database needs neither a real DB nor a mock repository, only input values and assertions on output values.

2. Database Isolation: The Central Problem

The fundamental problem with database tests is isolation: every test should start with a defined database state and leave no state behind after the test that affects other tests. Without isolation, tests become dependent on each other. The bug in one test shows up in the next one, or tests only pass in a particular execution order. This is the most common reason teams abandon database tests: not because real DB access is wrong, but because isolation was never implemented cleanly.

There are three main strategies for database isolation: transaction rollback (the fastest option, works for most tests), database truncate after each test (slower, but necessary when the tests themselves use transactions), and a separate test database with a reset before the entire test suite (for integration tests with external dependencies). The choice depends on whether the code under test manages transactions itself.

3. Transaction Rollback: Isolation Without Data Loss

The transaction rollback approach is the most efficient isolation strategy: before the test, a transaction is opened, the test inserts data and runs operations, and after the test the transaction is rolled back. The database is then in exactly the state it was in before the test, without DELETE operations, without TRUNCATE, without a schema reset. This is usually orders of magnitude faster than other isolation strategies.

The important caveat: this approach does not work if the code under test itself commits transactions. A command that calls $entityManager->flush() and then commits itself would also commit the outer test transaction (with MySQL/InnoDB) or produce an error. In these cases you have to switch to truncate-after-test or savepoints. With Doctrine ORM and SQLite there is another special case: SQLite does not natively support nested transactions, but emulates them via SAVEPOINT.

<?php

declare(strict_types=1);

namespace Tests\Integration;

use Doctrine\DBAL\Connection;
use PHPUnit\Framework\TestCase;

/**
 * Base class for database integration tests using transaction rollback isolation.
 * Each test runs inside a transaction that is rolled back after the test.
 */
abstract class DatabaseTestCase extends TestCase
{
    protected Connection $connection;
    private bool $transactionStarted = false;

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

        // Get the shared DB connection from the container / bootstrap
        $this->connection = $this->getConnection();

        // Begin transaction to isolate this test
        $this->connection->beginTransaction();
        $this->transactionStarted = true;
    }

    protected function tearDown(): void
    {
        // Roll back all changes, DB is back to pre-test state
        if ($this->transactionStarted && $this->connection->isTransactionActive()) {
            $this->connection->rollBack();
        }

        parent::tearDown();
    }

    /**
     * Returns the database connection from the test container.
     */
    abstract protected function getConnection(): Connection;
}

// Concrete test using transaction rollback isolation
final class UserRepositoryTest extends DatabaseTestCase
{
    private UserRepository $repository;

    protected function setUp(): void
    {
        parent::setUp(); // starts transaction
        $this->repository = new UserRepository($this->connection);
    }

    /** @test */
    public function save_persists_user_and_assigns_id(): void
    {
        $user = User::create('anna@example.com', 'Anna');

        $this->repository->save($user);

        // User is in DB within the transaction
        $this->assertNotNull($user->getId());
        $this->assertGreaterThan(0, $user->getId());
    }

    /** @test */
    public function find_by_email_returns_matching_user(): void
    {
        // Arrange: insert fixture data in the open transaction
        $this->connection->executeStatement(
            'INSERT INTO users (email, name) VALUES (?, ?)',
            ['test@example.com', 'Test User']
        );

        $found = $this->repository->findByEmail('test@example.com');

        $this->assertNotNull($found);
        $this->assertSame('Test User', $found->getName());
        // tearDown rolls back, no cleanup needed
    }

    /** @test */
    public function find_by_email_returns_null_for_missing_user(): void
    {
        $result = $this->repository->findByEmail('nobody@example.com');

        $this->assertNull($result);
    }

    protected function getConnection(): Connection
    {
        return TestKernel::getContainer()->get(Connection::class);
    }
}

4. Fixtures and Data Preparation

Fixtures are predefined data inserted into the database before a test to establish a known starting state. In PHP there are several approaches: SQL fixtures as direct INSERT statements in the test, PHP fixtures via factory classes, and Doctrine Fixtures Bundle for complex scenarios. The simplest and most transparent variant for unit-adjacent integration tests is direct SQL insertion in setUp(): every test sees exactly the data it needs, nothing more.

The factory pattern for fixtures is cleaner than direct SQL inserts: a UserFactory::create() method creates a test user with sensible defaults and lets you override only the relevant fields. This reduces duplication and makes tests more readable. With the league/factory-muffin package or the Symfony extension zenstruck/foundry, factory patterns can be built systematically for all entities and used in rollback tests.

<?php

declare(strict_types=1);

namespace Tests\Support;

use Doctrine\ORM\EntityManagerInterface;
use App\Domain\User;
use App\Domain\Order;

/**
 * Factory helpers for creating test fixtures cleanly.
 * All created entities exist only within the test's rollback transaction.
 */
final class TestFixtures
{
    public function __construct(
        private readonly EntityManagerInterface $em,
    ) {}

    /**
     * Creates and persists a user with sensible defaults.
     *
     * @param array<string, mixed> $overrides
     */
    public function createUser(array $overrides = []): User
    {
        $user = User::create(
            email: $overrides['email'] ?? 'user-' . uniqid() . '@example.com',
            name: $overrides['name'] ?? 'Test User',
        );

        if (isset($overrides['status'])) {
            $user->setStatus($overrides['status']);
        }

        $this->em->persist($user);
        $this->em->flush();

        return $user;
    }

    /**
     * Creates and persists an order for a given user.
     *
     * @param array<string, mixed> $overrides
     */
    public function createOrder(User $user, array $overrides = []): Order
    {
        $order = Order::draft($user);

        foreach ($overrides['items'] ?? [] as $item) {
            $order->addItem($item);
        }

        $this->em->persist($order);
        $this->em->flush();

        return $order;
    }
}

// Usage in tests:
final class OrderRepositoryTest extends DatabaseTestCase
{
    private TestFixtures $fixtures;
    private OrderRepository $repo;

    protected function setUp(): void
    {
        parent::setUp();
        $this->fixtures = new TestFixtures($this->getEntityManager());
        $this->repo     = new OrderRepository($this->getEntityManager());
    }

    /** @test */
    public function finds_orders_by_user(): void
    {
        $user   = $this->fixtures->createUser(['email' => 'buyer@example.com']);
        $order1 = $this->fixtures->createOrder($user);
        $order2 = $this->fixtures->createOrder($user);

        $found = $this->repo->findByUser($user);

        $this->assertCount(2, $found);
        $this->assertCollectionContainsIds([$order1->getId(), $order2->getId()], $found);
    }
}

5. Doctrine ORM: Repository Integration Tests

Doctrine repositories are a particularly common test target. The typical mistake: testing repositories with a mock EntityManager. That tests the internal implementation, not the actual behavior. A Doctrine repository produces DQL queries that get translated into SQL, executed against a real database, and return real results. A mock cannot correctly simulate any of these steps.

For Doctrine integration tests, Symfony\Bundle\FrameworkBundle\Test\KernelTestCase is a good fit, using the real container that holds a real EntityManager. The EntityManager operates against a dedicated test database that has the same schema version as the production database. With the transaction rollback pattern, every test runs in its own transaction, without affecting each other. The EntityManager cache must be cleared after the rollback ($em->clear()) so subsequent tests don't see cached entities from the previous test.

6. SQLite-in-Memory as an Alternative

SQLite-in-memory is a fast alternative for database tests when the application works with Doctrine and standard SQL. The entire database exists only in RAM, is rebuilt for every test run, and is therefore automatically isolated. Setup is simple: in the test configuration, set the database connection to pdo_sqlite and the path to :memory:, and build the schema with the Doctrine schema tool at the start of the test.

The decisive drawback: SQLite does not support all MySQL or PostgreSQL features. Stored procedures, certain data types (JSON operations, ENUM, FULLTEXT), database-specific functions, and locking behavior differ. Tests that use SQLite-in-memory cannot find MySQL-specific bugs. The recommendation: SQLite-in-memory for speed and simplicity in development, a real MySQL/PostgreSQL database in CI for integration tests.

7. Common Mistakes in Database Tests

The most common mistake: tests that depend on each other because database state is not isolated. Test A creates a user, test B finds this user and expects it. If test A does not run, test B fails. Or the reverse: test A expects that no user exists, but test B has created one. This dependency often only shows up when the execution order is changed by PHPUnit's process randomization.

A second common mistake: the EntityManager cache is not cleared between tests. Doctrine caches entities in its identity map. If test A loads an entity, modifies it, and then the rollback happens, but $em->clear() is not called, test B may see a cached version of the entity from test A, from memory, not from the database. $em->clear() must be called after the rollback. A third mistake: using SQLite-in-memory for tests and then being surprised by MySQL bugs that show up in production but were not visible in the tests.

<?php

declare(strict_types=1);

namespace Tests\Integration;

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

/**
 * Base class for Doctrine repository tests.
 * Uses transaction rollback for fast, reliable isolation.
 */
abstract class DoctrineRepositoryTestCase extends KernelTestCase
{
    protected EntityManagerInterface $em;

    protected function setUp(): void
    {
        parent::setUp();
        self::bootKernel(['environment' => 'test']);

        $this->em = self::getContainer()->get(EntityManagerInterface::class);

        // Begin wrapping transaction for isolation
        $this->em->getConnection()->beginTransaction();
    }

    protected function tearDown(): void
    {
        // Roll back all test data
        $this->em->getConnection()->rollBack();

        // CRITICAL: clear identity map so next test doesn't see stale cached entities
        $this->em->clear();

        parent::tearDown();
    }

    /**
     * Flushes pending changes to DB (within rollback transaction).
     * Call this after persist() to make entities queryable in the test.
     */
    protected function flushAndClear(): void
    {
        $this->em->flush();
        $this->em->clear(); // Clear cache so next find goes to DB, not memory
    }
}

// Example repository test
final class ProductRepositoryTest extends DoctrineRepositoryTestCase
{
    private ProductRepository $repo;

    protected function setUp(): void
    {
        parent::setUp();
        $this->repo = self::getContainer()->get(ProductRepository::class);
    }

    /** @test */
    public function finds_active_products_ordered_by_name(): void
    {
        // Arrange: create test data inside the rollback transaction
        $this->em->persist(Product::create('Zebra Widget', status: 'active'));
        $this->em->persist(Product::create('Alpha Gadget', status: 'active'));
        $this->em->persist(Product::create('Hidden Item', status: 'inactive'));
        $this->flushAndClear(); // flush so queries hit DB, clear cache

        $products = $this->repo->findAllActive();

        $this->assertCount(2, $products);
        $this->assertSame('Alpha Gadget', $products[0]->getName());
        $this->assertSame('Zebra Widget', $products[1]->getName());
        // tearDown rolls back, no cleanup needed
    }
}

8. Test Strategies Compared

The choice of test strategy for database-accessing code depends on what needs to be tested. For business logic in service classes, a mock repository is the right choice: fast, isolated, focused. For data-access logic in repository classes, real DB access with transaction rollback is the right choice. For complex scenarios with several interacting repositories, an integration test with a real container and rollback is the right tool.

The most important insight: this decision is not an either-or question. A complete test suite for an application with database access includes all three levels. Mock repositories in service tests for speed. Real DB in repository tests for correctness. Integration tests for the interplay. And always: clear isolation, so tests stay independent and reproducible.

9. Summary

Database tests with PHPUnit make sense when SQL correctness, ORM mapping, constraint behavior, or query results need to be verified. The transaction rollback approach is the most efficient isolation strategy. Doctrine repository tests with a real EntityManager and rollback find bugs that no mock can find. SQLite-in-memory is fast, but cannot uncover MySQL-specific bugs. The EntityManager cache must be cleared after the rollback. Fixtures via the factory pattern keep tests readable and maintainable.

Real DB access in tests is not a luxury, it is the only way to truly test the data-access layer. The question is not whether, but where and how.

10. FAQ: PHPUnit Database Tests