Using PHP's Typed Class Properties with PDO

Ever since PHP introduced typed class properties in PHP 7.4, I've been making a lot of use of them. One of the places I find them most useful is when working with data models, for example when querying a database. PHP's PDO supports fetching result sets as objects, which when used in conjunction with the PostgreSQL driver or the MySQLnd driver can make use of typed properties.

After trying many different ORMs over the years such as Eloquent and Doctrine, I've eventually settled on using raw SQL for most smaller projects I work on. For more complex queries, I tend to reach for a query builder rather than a fully fledged ORM - generally this is the query system from Atlas PHP which I use in conjunction with the extended PDO library from Atlas too. I find this gives me the right degree of control and ease of development that lets me focus on the actual problem at hand rather than fighting with a rigid ORM's ways of doing things.

There are a few things to be aware of when working with typed properties alongside PDO though, for example:

• Only some basic types are supported, such as int, bool, string. Nullability can also be handled, with type annotations such as ?int, ?bool, ?string.
• PDO assigns class properties before running class constructors, so any logic in class constructors must be aware of properties that may already be set. However, PDO's methods related to fetching results as objects may pass arguments to this constructor.

There is an approach I've settled on that works quite well for me, which I wanted to share. A quick example:

<?php

declare(strict_types=1);

final class Post
{
    public int $id;

    public string $title;

    public string $content;

    public int $authorId;

    public bool $isDraft;

    public \DateTimeInterface $createdAt;

    public \DateTimeInterface $updatedAt;

    public function __set(string $name, $value): void
    {
        switch ($name)
        {
            case 'created_at':
                $this->createdAt = new \DateTimeImmutable($value);
                break;
            case 'updated_at':
                $this->updatedAt = new \DateTimeImmutable($value);
                break;
        }
    }
}

We handle types that aren't natively supported here using PHP's magic __set method, which is called with the name of the column from the database and its value. We then simply switch on the name of the column and do whatever we need to in order to create the actual type. In this case, we're handling date types but the same logic could be expanded to other types such as JSON.

There is usually also a repository class for each entity, which handles the actual query and marshalling results which looks a little like the following:

<?php

declare(strict_types=1);

final class PostRepository
{
    private \PDO $pdo;

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

    public function allPosts(): \Generator
    {
        $query = <<<SQL
SELECT id, 
       title, 
       content, 
       author_id AS "authorId", 
       is_draft AS "isDraft", 
       created_at, 
       updated_at
FROM posts
ORDER BY created_at DESC;
SQL;

        $stmt = $this->pdo->query($query);

        while (($row = $stmt->fetchObject(Post::class)) !== false) {
            yield $row;
        }
    }

    public function find(int $id): ?Post
    {
        $query = <<<SQL
SELECT id, 
       title, 
       content, 
       author_id AS "authorId", 
       is_draft AS "isDraft", 
       created_at, 
       updated_at
FROM posts
WHERE id = :id;
SQL;

        $stmt = $this->pdo->prepare($query);

        $stmt->execute(
            [
                'id' => $id,
            ]
        );

        $result = $stmt->fetchObject(Post::class);

        if (false === $result) {
            return null;
        }

        return $result;
    }
}

Of note here is that we make use of a Generator when fetching multiple results, allowing us to choose at the call site whether we need to collect all the results instantly or can do so lazily when we (for example) build some HTML or a JSON result.

We also have to do some simple handling for the fact that in the case of no results being found, PDO returns false. As I like to work with strict types, my functions are declared as only returning a single type such as a nullable Post instance.

Usually there would also be a repository interface rather than just an implementation, so that we can make use of the decorator design pattern to add things such as caching where needed.

I've put together a repository on GitHub which showcases this approach along with a basic test to show it in action.

I've been using this approach for over a year, with quite a lot of success. Having raw SQL queries in our repository files makes it easy to troubleshoot queries directly against a database backup as well as being able to share queries between PHP projects and projects in other languages such as C#.