databases storage and caching

Database Test Scenarios And Fixtures

A fixture is known test data. Good fixtures are small, explicit, and tied to the behaviour being tested. A failing database test should point to the rule that broke, not to a huge shared data setup nobody understands.

A Small Fixture

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)');

$fixture = ['email' => 'sam@example.com'];
$stmt = $pdo->prepare('INSERT INTO users (email) VALUES (:email)');
$stmt->execute($fixture);

echo $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn() . PHP_EOL;

// Prints:
// 1

This fixture sets up exactly one user. That is enough for tests about looking up a user by email or rejecting duplicate email addresses.

What To Test At The Database Boundary

Useful database tests often cover:

Insert and fetch behaviour for repositories.
Required fields and unique constraints.
Foreign key behaviour.
Transaction rollback on failure.
Query filters and sorting.
Pagination and batch processing.
Migrations creating the expected schema.
Data backfills changing the right rows.

Do not use a database test when a pure function test would prove the same thing more cheaply. Use a database test when the database behaviour is part of the risk.

Testing Constraints

Constraints are worth testing because they protect production data even when PHP code changes later.

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)');

$insert = $pdo->prepare('INSERT INTO users (email) VALUES (:email)');
$insert->execute(['email' => 'sam@example.com']);

try {
    $insert->execute(['email' => 'sam@example.com']);
    echo 'duplicate allowed' . PHP_EOL;
} catch (PDOException) {
    echo 'duplicate rejected' . PHP_EOL;
}

// Prints:
// duplicate rejected

This test proves the database, not just PHP validation, rejects duplicate emails.

Test Isolation

Each test should start from a known state. Common approaches include:

Recreate an in-memory SQLite database per test.
Wrap each test in a transaction and roll it back.
Run migrations into a temporary test database.
Truncate tables between tests.
Use factories to create only the rows the test needs.

Avoid tests that pass only because another test created data first. Order-dependent tests are fragile.

SQLite vs Production Database

SQLite is useful for fast examples and many tests, but it is not identical to MySQL or PostgreSQL. If a feature depends on database-specific behaviour, test it against that database engine in CI or an integration environment.

Examples that may need production-engine tests include:

PostgreSQL JSONB queries.
MySQL-specific collation behaviour.
Locking and deadlock handling.
Complex migrations.
Full-text search.
Foreign key behaviour if SQLite pragmas are not enabled.

What To Check

Before moving on, make sure you can:

Explain what a fixture is.
Keep database fixtures small and focused.
Test repository behaviour against real tables.
Test constraints and transaction rollback.
Keep tests isolated from each other.
Decide when SQLite is enough and when the production database engine matters.

Practice

Practice: Test A User Repository Fixture

Write a self-contained PHP example that uses a database fixture to test user lookup behaviour.

Requirements

Use SQLite in memory.
Create a users table with a unique email column.
Insert a small fixture.
Test the happy path: an existing email is found.
Test the edge case: a missing email returns null.
Test that a duplicate email is rejected by the database.
Keep the fixture focused on this behaviour.

Show solution

This example uses plain assertions so it can run without installing PHPUnit. The same setup could be moved into a PHPUnit integration test.

PHP example

<?php

declare(strict_types=1);

function createTestDatabase(): PDO
{
    $pdo = new PDO('sqlite::memory:');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)');
    $pdo->prepare('INSERT INTO users (email) VALUES (:email)')
        ->execute(['email' => 'sam@example.com']);

return $pdo;
}

function findUserByEmail(PDO $pdo, string $email): ?array
{
    $statement = $pdo->prepare('SELECT id, email FROM users WHERE email = :email');
    $statement->execute(['email' => $email]);

$row = $statement->fetch();

return $row === false ? null : $row;
}

$pdo = createTestDatabase();

assert(findUserByEmail($pdo, 'sam@example.com')['email'] === 'sam@example.com');
assert(findUserByEmail($pdo, 'missing@example.com') === null);

try {
    $pdo->prepare('INSERT INTO users (email) VALUES (:email)')
        ->execute(['email' => 'sam@example.com']);
    echo 'duplicate allowed' . PHP_EOL;
} catch (PDOException) {
    echo 'duplicate rejected' . PHP_EOL;
}

echo 'database fixture checks passed' . PHP_EOL;

// Prints:
// duplicate rejected
// database fixture checks passed

<?php

declare(strict_types=1);

function createTestDatabase(): PDO
{
    $pdo = new PDO('sqlite::memory:');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    $pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)');
    $pdo->prepare('INSERT INTO users (email) VALUES (:email)')
        ->execute(['email' => 'sam@example.com']);

    return $pdo;
}

function findUserByEmail(PDO $pdo, string $email): ?array
{
    $statement = $pdo->prepare('SELECT id, email FROM users WHERE email = :email');
    $statement->execute(['email' => $email]);

    $row = $statement->fetch();

    return $row === false ? null : $row;
}

$pdo = createTestDatabase();

assert(findUserByEmail($pdo, 'sam@example.com')['email'] === 'sam@example.com');
assert(findUserByEmail($pdo, 'missing@example.com') === null);

try {
    $pdo->prepare('INSERT INTO users (email) VALUES (:email)')
        ->execute(['email' => 'sam@example.com']);
    echo 'duplicate allowed' . PHP_EOL;
} catch (PDOException) {
    echo 'duplicate rejected' . PHP_EOL;
}

echo 'database fixture checks passed' . PHP_EOL;

// Prints:
// duplicate rejected
// database fixture checks passed

The fixture contains one user because that is all this behaviour needs. Larger shared fixtures would make the test harder to understand and debug.