The repository class

SQL queries scattered across actions are hard to find and change. They are gathered into one class — a repository: it has methods like "all posts" and "post by number", and the rest of the code does not know that SQL stands behind them. This class takes the place of a model from the big frameworks.

Where it lives

Repositories go into src/Repository/. The PostRepository class is already there — with an array instead of a database. Rewrite it.

Reading the list

// src/Repository/PostRepository.php
namespace App\Repository;
 
use PDO;
 
final class PostRepository
{
    public function __construct(private PDO $pdo) {}
 
    public function all(): array
    {
        return $this->pdo
            ->query('SELECT id, title, created_at FROM posts ORDER BY id DESC')
            ->fetchAll();
    }
}

The PDO object arrives through the constructor: the container creates it from the definition in the previous lesson. ListPostsAction does not change — it still calls all(). Open /posts: the list now comes from the database.

A single record

Values from the user are never pasted into the query text. A question mark stands in their place and the value is passed separately — this is a prepared statement:

// src/Repository/PostRepository.php
public function find(int $id): ?array
{
    $statement = $this->pdo->prepare('SELECT * FROM posts WHERE id = ?');
    $statement->execute([$id]);
 
    return $statement->fetch() ?: null;
}

The method returns an array with the post's fields, or null if no row has that number.

Using it in an action

// src/Action/ShowPostAction.php
namespace App\Action;
 
use App\Repository\PostRepository;
use Psr\Http\Message\ResponseInterface as Response;
use Psr\Http\Message\ServerRequestInterface as Request;
use Slim\Exception\HttpNotFoundException;
use Slim\Views\Twig;
 
final class ShowPostAction
{
    public function __construct(private PostRepository $posts, private Twig $twig) {}
 
    public function __invoke(Request $request, Response $response, array $args): Response
    {
        $post = $this->posts->find((int) $args['id']);
 
        if ($post === null) {
            throw new HttpNotFoundException($request);
        }
 
        return $this->twig->render($response, 'posts/show.twig', ['post' => $post]);
    }
}

{# templates/posts/show.twig #}
{% extends 'layout.twig' %}
 
{% block content %}
    <h1>{{ post.title }}</h1>
    <p>{{ post.body }}</p>
{% endblock %}

The address /posts/1 shows the post, and /posts/999 shows a 404 page: the HttpNotFoundException is caught by the error middleware.

Adding a record

// src/Repository/PostRepository.php
public function create(string $title, string $body): int
{
    $statement = $this->pdo->prepare('INSERT INTO posts (title, body) VALUES (?, ?)');
    $statement->execute([$title, $body]);
 
    return (int) $this->pdo->lastInsertId();
}

The method returns the number of the new row. It will be needed in the forms lesson.

Ready-made libraries

When queries multiply, a library replaces hand-written SQL. Eloquent, the ORM from Laravel, is installed with the illuminate/database package and works apart from Laravel. Doctrine DBAL (doctrine/dbal) offers a query builder without models. Both are connected the same way as PDO: defined in the container and received through the constructor.

Common mistake

A query like "SELECT * FROM posts WHERE id = " . $args['id'] opens the way to SQL injection — a visitor can append their own SQL to the query. The [0-9]+ route constraint is no protection: tomorrow it may be removed. Pass every outside value through prepare() and execute().

Thanks for reading!