Building a Repository Class with PDO

A repository encapsulates persistence queries behind methods that return domain-friendly values.

The examples focus on the operation itself, including the important distinction: Controllers coordinate requests; repositories own database access.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

Dependency injection separates connection setup from data access; repositories should not return raw HTTP responses.

<?php
final class UserRepository {
    public function __construct(private PDO $db) {}
    public function find(int $id): ?array {
        $statement = $this->db->prepare("SELECT id, email FROM users WHERE id = :id");
        $statement->execute(["id" => $id]);
        return $statement->fetch(PDO::FETCH_ASSOC) ?: null;
    }
}
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
echo json_encode((new UserRepository($pdo))->find(3));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

Dependency injection separates connection setup from data access; repositories should not return raw HTTP responses.

<?php
final class UserRepository {
    public function __construct(private PDO $db) {}
    public function find(int $id): ?array {
        $statement = $this->db->prepare("SELECT id, email FROM users WHERE id = :id");
        $statement->execute(["id" => $id]);
        return $statement->fetch(PDO::FETCH_ASSOC) ?: null;
    }
}
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
echo json_encode((new UserRepository($pdo))->find(8));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

Dependency injection separates connection setup from data access; repositories should not return raw HTTP responses.

<?php
final class UserRepository {
    public function __construct(private PDO $db) {}
    public function find(int $id): ?array {
        $statement = $this->db->prepare("SELECT id, email FROM users WHERE id = :id");
        $statement->execute(["id" => $id]);
        return $statement->fetch(PDO::FETCH_ASSOC) ?: null;
    }
}
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
echo json_encode((new UserRepository($pdo))->find(21));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

Keep connection construction out of repository methods and avoid leaking PDO exceptions as HTTP responses.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• A repository encapsulates persistence queries behind methods that return domain-friendly values.
• Keep connection construction out of repository methods and avoid leaking PDO exceptions as HTTP responses.
• Controllers coordinate requests; repositories own database access.

Use Building a Repository Class with PDO when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.