testing php applications

Testing Database Code

Database code needs tests against a real database engine when behaviour depends on SQL, constraints, transactions, migrations, or driver differences. A mocked repository cannot prove that a query is correct.

Test The Boundary You Depend On

Repository tests should exercise prepared statements, filters, joins, ordering, pagination, and constraint failures that matter to the application. Use an isolated test database and apply migrations predictably.

Keep State Independent

Reset data between tests with transactions, truncation, recreated schemas, or framework helpers. The strategy must fit the code under test: code that commits transactions may need a different isolation approach.

PHP example

<?php

declare(strict_types=1);

use PHPUnit\Framework\TestCase;

final class PublishedProductsQueryTest extends TestCase
{
    private PDO $pdo;

    protected function setUp(): void
    {
        $this->pdo = new PDO('sqlite::memory:');
        $this->pdo->exec(
            'CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, status TEXT)'
        );
        $this->pdo->exec(
            "INSERT INTO products (name, status)
             VALUES ('Desk lamp', 'published'), ('Draft chair', 'draft')"
        );
    }

    public function testQueryReturnsPublishedProductsOnly(): void
    {
        $statement = $this->pdo->prepare(
            'SELECT name FROM products WHERE status = :status ORDER BY name'
        );
        $statement->execute(['status' => 'published']);

        $this->assertSame(['Desk lamp'], $statement->fetchAll(PDO::FETCH_COLUMN));
    }
}

Common Mistakes

Mocking PDO and assuming SQL works.
Sharing dirty database state between tests.
Using SQLite when production-specific SQL needs the production engine.
Skipping migration checks.

What To Practise

Run repository checks against a real database.
Reset state independently.
Choose SQLite or the production engine deliberately.

Practice

Practice: Test A Published Product Query

Create an in-memory database example that returns published products only.

Requirements

Create a table with published and draft rows.
Use a prepared statement.
Assert only the published row is returned.
Explain when SQLite would be insufficient.

Show solution

This runnable integration example proves the SQL against SQLite.

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, status TEXT)');
$pdo->exec("INSERT INTO products (name, status) VALUES ('Desk lamp', 'published'), ('Draft chair', 'draft')");

$statement = $pdo->prepare('SELECT name FROM products WHERE status = :status ORDER BY name');
$statement->execute(['status' => 'published']);
$names = $statement->fetchAll(PDO::FETCH_COLUMN);

echo implode(', ', $names) . PHP_EOL;

// Prints:
// Desk lamp

Use the production database engine in CI when engine-specific types, indexes, locking, or SQL syntax are part of the behaviour.