Test database code in PHP with transactions

Approach

Run tests against the same database engine as production (Docker service in CI), apply migrations once, and wrap each test in a transaction rolled back in tearDown(). Use small factory helpers to create data explicitly per test.

Solution

<?php
declare(strict_types=1);

use PHPUnit\Framework\TestCase;

abstract class DatabaseTestCase extends TestCase
{
    protected static PDO $pdo;
    protected function setUp(): void
    {
        self::$pdo ??= new PDO(getenv('TEST_DB_DSN'), getenv('TEST_DB_USER'), getenv('TEST_DB_PASSWORD'),
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
        self::$pdo->beginTransaction();
    }
    protected function tearDown(): void
    {
        self::$pdo->rollBack();
    }
    protected function createUser(array $overrides = []): int
    {
        $data = $overrides + ['email' => 'user' . bin2hex(random_bytes(4)) . '@example.com', 'name' => 'Test User'];
        self::$pdo->prepare('INSERT INTO users (email, name) VALUES (:email, :name)')->execute($data);
        return (int) self::$pdo->lastInsertId();
    }
}

final class PdoOrderRepositoryTest extends DatabaseTestCase
{
    public function test_it_lists_only_the_users_orders(): void
    {
        $alice = $this->createUser();
        $bob = $this->createUser();
        $repo = new PdoOrderRepository(self::$pdo);
        $repo->create($alice, totalCents: 1000);
        $repo->create($bob, totalCents: 500);

        $orders = $repo->forUser($alice);

        $this->assertCount(1, $orders);
        $this->assertSame(1000, $orders[0]->totalCents);
    }
}

Pitfalls

• Code under test that commits its own transactions (use savepoints or truncate between tests).
• SQLite stand-ins for MySQL/PostgreSQL behavior.
• Shared fixtures that make tests order-dependent.

Variations

• Laravel: RefreshDatabase / DatabaseTransactions traits.
• Symfony: dama/doctrine-test-bundle for automatic per-test transactions.

Further reading: PHPUnit: Fixtures