Read Models Without a Second Database: CQRS-Lite Projections in PHP

#php #architecture #postgres #backend

Book: Decoupled PHP — Clean and Hexagonal Architecture for Applications That Outlive the Framework
Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
Me: xgabriel.com | GitHub

You build a clean Order aggregate. Private constructor, named factories, invariants in one place. The write side is a pleasure to work with.

Then the order-history screen lands in the backlog. It needs the customer name, the last four digits of the card, a shipment-status label, and a running total across forty orders, paged twenty at a time, sortable by date and amount.

So you load forty Order aggregates. Each one hydrates its line items, its money value objects, its status enum. You join three repositories in PHP because the aggregates do not reference each other. The page takes 800ms and the code reads like a tax form.

The aggregate was built to protect writes. You are asking it to feed a table. Those are two different jobs, and forcing one object to do both is where the pain starts.

The split nobody tells you is optional

CQRS gets explained with event stores, message buses, and a second database that a projector keeps in sync. That is the full version. It is also the version most teams do not need and cannot afford to operate.

The part worth keeping is smaller: the write side and the read side are different shapes, so give them different code paths. The write side stays your aggregate and repository. The read side becomes a plain query that returns exactly what the screen wants, reading from the same Postgres you already have.

No event store. No projector process. No eventual consistency to explain to support. One database, two doors.

The write side stays exactly as it is

Nothing here changes. The aggregate protects its invariants, the repository loads and saves it by ID.

<?php

declare(strict_types=1);

namespace App\Application\Port;

use App\Domain\Order\Order;
use App\Domain\Order\OrderId;

interface OrderRepository
{
    public function save(Order $order): void;
    public function findById(OrderId $id): ?Order;
}

findById returns one fully-formed aggregate. That is correct for a use case that has to change an order: load it, call cancel, save it. It is the wrong tool for a list view, and that is fine. You do not widen this interface to add findAllForCustomerPagedSorted. You add a separate read path.

A projection query object

The read side gets its own interface, in domain language, returning a flat DTO built for the screen. No aggregate. No repository.

<?php

declare(strict_types=1);

namespace App\Application\Read;

interface OrderHistoryQuery
{
    /** @return list<OrderHistoryRow> */
    public function forCustomer(
        string $customerId,
        int $limit,
        int $offset,
    ): array;
}

The DTO carries the columns the table renders, nothing else.

<?php

declare(strict_types=1);

namespace App\Application\Read;

final readonly class OrderHistoryRow
{
    public function __construct(
        public string $orderId,
        public string $customerName,
        public int $totalCents,
        public string $currency,
        public string $status,
        public string $placedAt,
        public int $itemCount,
    ) {}
}

This object has no behavior and no invariants. It is a row, not an entity. It will be JSON-encoded and sent to a browser, and it never travels back into the domain to be changed.

The adapter is plain SQL

Here is where you stop fighting the ORM. The read adapter writes the query the screen needs, joins what it needs, and maps rows straight to DTOs. It lives in Infrastructure/, behind the OrderHistoryQuery port.

<?php

declare(strict_types=1);

namespace App\Infrastructure\Read;

use App\Application\Read\OrderHistoryQuery;
use App\Application\Read\OrderHistoryRow;
use Doctrine\DBAL\Connection;

final readonly class SqlOrderHistoryQuery implements OrderHistoryQuery
{
    public function __construct(private Connection $db) {}

    public function forCustomer(
        string $customerId,
        int $limit,
        int $offset,
    ): array {
        $sql = <<<SQL
            SELECT
                o.id            AS order_id,
                c.name          AS customer_name,
                o.total_cents   AS total_cents,
                o.currency      AS currency,
                o.status        AS status,
                o.placed_at     AS placed_at,
                count(i.id)     AS item_count
            FROM orders o
            JOIN customers c ON c.id = o.customer_id
            LEFT JOIN order_items i ON i.order_id = o.id
            WHERE o.customer_id = :cid
            GROUP BY o.id, c.name
            ORDER BY o.placed_at DESC
            LIMIT :limit OFFSET :offset
            SQL;

        $rows = $this->db->fetchAllAssociative($sql, [
            'cid' => $customerId,
            'limit' => $limit,
            'offset' => $offset,
        ], [
            'limit' => \PDO::PARAM_INT,
            'offset' => \PDO::PARAM_INT,
        ]);

        return array_map(
            static fn (array $r) => new OrderHistoryRow(
                orderId: $r['order_id'],
                customerName: $r['customer_name'],
                totalCents: (int) $r['total_cents'],
                currency: $r['currency'],
                status: $r['status'],
                placedAt: $r['placed_at'],
                itemCount: (int) $r['item_count'],
            ),
            $rows,
        );
    }
}

One round trip. One join. The count and the paging happen in Postgres, which is what Postgres is good at. The forty-aggregate hydration is gone, and so is the in-PHP joining.

The controller asks for the port and renders the rows. It never sees Order.

<?php

declare(strict_types=1);

namespace App\Infrastructure\Http\Controller;

use App\Application\Read\OrderHistoryQuery;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;

final readonly class OrderHistoryController
{
    public function __construct(private OrderHistoryQuery $query) {}

    public function __invoke(Request $request): JsonResponse
    {
        $rows = $this->query->forCustomer(
            customerId: $request->query->getString('customer'),
            limit: 20,
            offset: $request->query->getInt('offset'),
        );

        return new JsonResponse($rows);
    }
}

Why a port for raw SQL

Putting an interface in front of SqlOrderHistoryQuery looks like ceremony until you write the test. The use case and controller depend on OrderHistoryQuery, so a fake that returns hand-built OrderHistoryRow objects covers the application logic with no database at all.

<?php

declare(strict_types=1);

namespace App\Tests\Read;

use App\Application\Read\OrderHistoryQuery;
use App\Application\Read\OrderHistoryRow;

final class FakeOrderHistoryQuery implements OrderHistoryQuery
{
    /** @param list<OrderHistoryRow> $rows */
    public function __construct(private readonly array $rows) {}

    public function forCustomer(
        string $customerId,
        int $limit,
        int $offset,
    ): array {
        return array_slice($this->rows, $offset, $limit);
    }
}

The SQL itself gets one integration test against a real Postgres container, the same way you would test any other adapter. The screen logic and the query implementation are tested at the levels that catch their own kind of bug.

The port also keeps the read model honest about its direction. It returns DTOs and takes scalars. There is no save, no aggregate, no way for a list view to accidentally mutate state. The compiler enforces that the read path stays read-only.

When you do not need a read store

For most CRUD-shaped apps, you stop here. The single-Postgres split gets you:

Fast list and detail screens that read the exact columns they show.
A write model that stays small, because no reporting requirement bends the aggregate.
Tests that run without a database for the application layer.

You do not need a second store when the read and write models live in the same database, the queries fit comfortably in SQL, and a join across a few tables answers the screen. That covers the order history, the admin dashboard, the customer profile, most of what a normal product ships.

The cost of a second store is real: a projector to write, a lag to monitor, a rebuild path when a projection is wrong, and a support conversation every time a user refreshes and the number has not caught up yet. Pay it only when you have a reason.

When a read store starts to earn its keep

The single-database split runs out of room in a few specific places.

The read query touches tables under heavy write contention, and the analytical scan fights the transactional load on the same rows. Moving reads to a replica or a denormalized table takes that pressure off.

The shape the screen wants cannot come from a join at acceptable cost. A search page over millions of rows with fuzzy matching belongs in Elasticsearch or a Postgres full-text index that you maintain separately, not in a LIKE '%term%' that scans the table.

The read model aggregates across so many tables that the query is slow no matter how you index it, and the data changes rarely enough that a materialized view, refreshed on a schedule or on write, pays for itself.

Even then, the move is incremental. The OrderHistoryQuery port does not change. You write a new adapter — MaterializedViewOrderHistoryQuery, ElasticOrderHistoryQuery — and swap one binding in the composition root. The controller, the tests, and the application layer never notice. That is the payoff of having drawn the seam early: the read store becomes a deployment decision, not a rewrite.

The rule of thumb

Read queries and write aggregates are different shapes. Give them different code paths from day one, both against the same database. Keep the write side as your aggregate and repository. Make the read side a query object that returns flat DTOs straight from SQL.

That is CQRS-lite. You get the separation that keeps the domain clean without the operational weight of a second store. When a screen finally outgrows a single Postgres, the seam is already there, and you replace one adapter instead of redesigning the read path.

The next time a list view tempts you to add a findAll method to a repository that exists to load one aggregate, stop. That screen wants a query object, not a repository.

If this was useful

This read/write split is one slice of the larger argument in Decoupled PHP: keep the framework and the database as adapters, and the domain stays legible long after the stack underneath it changes. The book walks the same ports-and-adapters vocabulary used here through aggregates, use cases, the outbox, and the migration path for framework-coupled codebases that have been around long enough to feel permanent.