I want to show you a piece of code that looks perfectly fine when you first write it, but becomes a nightmare the moment your project grows. I’ve seen this happen in dozens of production apps. You start by putting your database queries directly in your controllers because it's faster to type, and everything works great for the first month.

// UserController.php
public function show($id) {
    $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    $user = $stmt->fetch();

    if (!$user) {
        throw new Exception("User not found");
    }

    return $this->render('profile', ['user' => $user]);
}

public function updateStatus($id, $status) {
    $stmt = $this->pdo->prepare("UPDATE users SET user_status = ? WHERE id = ?");
    $stmt->execute([$status, $id]);
    
    return $this->redirect('/profile');
}

The "Find and Replace" Nightmare

Now, imagine your boss tells you that the database schema is changing. The column user_status is being renamed to account_status for better clarity across the organization. If this is a small app, you just change it here and you're done. But in a real-world app, that same query likely exists in the UserController, the AdminController, a UserApiService, and maybe a ConsoleCommand for cron jobs.

You start searching for user_status. You find six instances. You change them. Then, two weeks later, a bug report comes in because you missed one instance in a legacy report generator. This is the "leakage" problem: your database implementation has leaked all over your business logic. Your controllers shouldn't know that your data is stored in a table called users or that a column is named user_status. They should just know how to ask for a "User" object.

Decoupling with a Repository

To fix this, we introduce the Repository Pattern. Think of a repository as a mediator. The controller says, "Give me user #10," and the repository handles the messy details of SQL, PDO, or even a third-party API.

First, we define an interface. I always use interfaces for repositories because it allows you to swap the underlying storage (like moving from MySQL to MongoDB or adding a Redis cache) without touching a single line of code in your controllers.

interface UserRepositoryInterface {
    public function findById(int $id): ?User;
    public function updateStatus(int $id, string $status): bool;
}

class PdoUserRepository implements UserRepositoryInterface {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    public function findById(int $id): ?User {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        $data = $stmt->fetch();
        
        return $data ? new User($data) : null;
    }

    public function updateStatus(int $id, string $status): bool {
        // All the SQL lives HERE. If the column name changes, 
        // you change it in this one method, and the whole app is fixed.
        $stmt = $this->pdo->prepare("UPDATE users SET account_status = ? WHERE id = ?");
        return $stmt->execute([$status, $id]);
    }
}

Cleaning up the Controller

Now, let's look at how the controller changes. We stop injecting the PDO connection and start injecting the UserRepositoryInterface. Notice how much more "human" the code reads now.

class UserController {
    private $userRepo;

    public function __construct(UserRepositoryInterface $userRepo) {
        $this->userRepo = $userRepo;
    }

    public function show($id) {
        $user = $this->userRepo->findById($id);

        if (!$user) {
            throw new Exception("User not found");
        }

        return $this->render('profile', ['user' => $user]);
    }

    public function updateStatus($id, $status) {
        $this->userRepo->updateStatus($id, $status);
        return $this->redirect('/profile');
    }
}

The controller is now blissfully ignorant of the database. It doesn't know if you're using MySQL, a JSON file, or an external API. I personally find this makes testing significantly easier, too. If you want to write a unit test for the UserController, you don't need a live database anymore; you can just pass in a "mock" version of the repository that returns a hard-coded User object.

📋 Practical Task

Refactoring the Order Management System

You have been handed a legacy OrderService class that is riddled with direct SQL queries. Your task is to extract the data access logic into a Repository pattern to prepare the app for a migration to a new database schema.

Requirements:

• Create an OrderRepositoryInterface with two methods: getOrderById(int $id) and markAsShipped(int $id).
• Create a concrete SqlOrderRepository that implements this interface using a PDO instance.
• Refactor the OrderService class provided below to remove all PDO logic and instead use the OrderRepositoryInterface via constructor injection.

// The Legacy Code to Refactor
class OrderService {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    public function shipOrder($orderId) {
        // This logic needs to move to the repository
        $stmt = $this->pdo->prepare("SELECT * FROM orders WHERE id = ?");
        $stmt->execute([$orderId]);
        $order = $stmt->fetch();

        if ($order && $order['status'] !== 'cancelled') {
            $update = $this->pdo->prepare("UPDATE orders SET status = 'shipped' WHERE id = ?");
            $update->execute([$orderId]);
            return "Order shipped successfully!";
        }

        return "Order cannot be shipped.";
    }
}