Fetching Symfony read models with CQRS and Doctrine DBAL

A page that displays ten rows should not need to hydrate several Doctrine entities and initialize their relationships. That work belongs to aggregate persistence, not every dashboard or search result.

An ORM is excellent when I want to load an aggregate, change its state, and persist it safely. But a dashboard, search result, export, or administrative table usually has a different need: fetch a specific shape of data as efficiently as possible.

Imagine asking a librarian for a list containing each book's title, author, and publication year. You do not need every complete book record, its borrowing history, and all related objects. You need one projection designed for one question.

That is how I approach reads in my Symfony application. I use ORM repositories for the write side and CQRS read models backed by Doctrine DBAL for the read side.

The implementation follows one request through a Symfony controller, query bus, application-owned read-model contract, Doctrine DBAL adapter, and response projection.

CQRS in a nutshell

Command Query Responsibility Segregation, or CQRS, separates operations that change state from operations that return data.

A command expresses an intention and may change the system.
A query asks a question and must not change the system.

The two sides can use different models because they solve different problems.

Mermaid

CQRS does not automatically mean microservices, event sourcing, asynchronous projections, or separate databases.

My implementation starts with the simplest useful form:

One Symfony application.
One PostgreSQL database.
Separate command and query code paths.
ORM aggregates for writes.
DBAL projections for reads.

The data is immediately available to queries because both sides use the same database. I gain separate models without introducing eventual consistency or extra infrastructure.

Why not use ORM entities for every read?

Suppose an administration page needs to display:

JSON

{
    "userId": "019b...",
    "name": "Jane Doe",
    "email": "jane@example.com",
    "emailVerified": true,
    "roles": ["ROLE_EDITOR"],
    "lastLoginAt": "2026-07-20T14:30:00+00:00"
}

The screen does not want a mutable User aggregate. It wants a row shaped for presentation.

Using the ORM for this read can introduce unnecessary work:

Hydrating fields that the response never uses.
Loading associations only to flatten them again.
Exposing domain entities to the serializer.
Adding joins to a repository whose real responsibility is aggregate persistence.
Making the query difficult to optimize without affecting the write model.

With a read model, I select only the required columns and return an immutable application model.

Doctrine DBAL is particularly useful here. DBAL provides connections, parameter binding, query builders, and result APIs. It does not hydrate ORM entities or track them in a Unit of Work.

Step 1: design the response model first

On the read side, start from the question the caller is asking.

For a user list, I can define an immutable projection:

PHP

namespace App\Identity\Application\Model;

final readonly class UserView
{
    /** @param list<string> $roles */
    public function __construct(
        public string $userId,
        public string $name,
        public string $email,
        public array $roles,
        public string $status,
        public bool $emailVerified,
        public DateTimeImmutable $createdAt,
        public ?DateTimeImmutable $lastLoginAt,
    ) {}
}

This object is not an anemic domain entity. It is not a domain entity at all.

It has no mutation methods and no business invariants because its responsibility is only to carry the answer to a query.

For a paginated result, I compose it with pagination metadata:

PHP

final readonly class UserList
{
    /** @param list<UserView> $items */
    public function __construct(
        public array $items,
        public PaginationInfo $pagination,
    ) {}
}

The pagination input and output are also ordinary application objects:

PHP

final readonly class PaginationFilters
{
    public function __construct(
        public int $page = 1,
        public int $limit = 20,
    ) {}
}

final readonly class PaginationInfo
{
    public function __construct(
        public int $current,
        public int $limit,
        public int $offset,
        public int $total,
        public int $pages,
    ) {}
}

The response model belongs to the Application layer. This is important: the API response does not depend on a DBAL row or an ORM entity.

Step 2: define a read-model contract

Next, I define what the application needs without deciding how it will be fetched:

PHP

namespace App\Identity\Application\ReadModel;

use App\Identity\Application\Model\UserList;
use App\Shared\Application\Pagination\PaginationFilters;

interface UsersReadModel
{
    public function list(PaginationFilters $pagination): UserList;
}

This interface is a read port.

The query handler depends on it, but it does not know whether the implementation uses PostgreSQL, Elasticsearch, an HTTP API, a cache, or an in-memory collection.

Unlike a domain repository, the contract is designed around a read use case. It can return display-ready data, join several tables, and calculate values that do not belong to a single aggregate.

Step 3: represent the question as a query

The query object carries the caller's criteria:

PHP

namespace App\Identity\Application\Query;

final readonly class ListUsers
{
    public function __construct(
        public PaginationFilters $pagination = new PaginationFilters(),
    ) {}
}

A query is named after a question: ListUsers, GetUserDetails, or GetRevenueSummary.

It does not contain SQL and does not execute itself. It is only a message crossing the application boundary.

Step 4: add the query handler

The handler receives the query and delegates to the read model:

PHP

namespace App\Identity\Application\QueryHandler;

use App\Identity\Application\Model\UserList;
use App\Identity\Application\Query\ListUsers;
use App\Identity\Application\ReadModel\UsersReadModel;
use Symfony\Component\Messenger\Attribute\AsMessageHandler;

#[AsMessageHandler(bus: 'query.bus')]
final readonly class ListUsersHandler
{
    public function __construct(
        private UsersReadModel $users,
    ) {}

    public function __invoke(ListUsers $query): UserList
    {
        return $this->users->list($query->pagination);
    }
}

The handler looks almost too simple, but that simplicity is useful.

It creates a stable application entry point, keeps the controller independent from Infrastructure, and gives me a place to add use-case orchestration or authorization that is not an SQL concern.

The SQL remains in the adapter that is responsible for SQL.

Step 5: dispatch queries through a dedicated bus

I expose a small application contract instead of injecting Symfony Messenger everywhere:

PHP

namespace App\Shared\Application\Bus;

interface QueryBus
{
    public function handle(object $query): mixed;
}

The Symfony adapter belongs to Infrastructure:

PHP

namespace App\Shared\Infrastructure\Bus;

use App\Shared\Application\Bus\QueryBus;
use Symfony\Component\DependencyInjection\Attribute\Target;
use Symfony\Component\Messenger\HandleTrait;
use Symfony\Component\Messenger\MessageBusInterface;

final class SymfonyQueryBus implements QueryBus
{
    use HandleTrait {
        HandleTrait::handle as private messengerHandle;
    }

    public function __construct(
        #[Target('query.bus')]
        MessageBusInterface $queryBus,
    ) {
        $this->messageBus = $queryBus;
    }

    public function handle(object $query): mixed
    {
        return $this->messengerHandle($query);
    }
}

Then I register a dedicated synchronous bus:

YAML

# config/packages/messenger.yaml
framework:
    messenger:
        buses:
            command.bus: ~
            query.bus: ~

Because the queries are not routed to an asynchronous transport, Symfony handles them immediately and returns the handler result in the same request.

Separating the buses also lets me give them different middleware. Commands may need transactions. Queries usually do not.

Step 6: implement the read model with Doctrine DBAL

Now I can write a query shaped for the user list:

PHP

namespace App\Identity\Infrastructure\DBAL;

use App\Identity\Application\Model\UserList;
use App\Identity\Application\Model\UserView;
use App\Identity\Application\ReadModel\UsersReadModel;
use Doctrine\DBAL\Connection;
use Knp\Component\Pager\PaginatorInterface;

final readonly class DbalUsersReadModel implements UsersReadModel
{
    public function __construct(
        private Connection $connection,
        private PaginatorInterface $paginator,
    ) {}

    public function list(PaginationFilters $pagination): UserList
    {
        $query = $this->connection->createQueryBuilder()
            ->select(
                'u.id::text AS "userId"',
                'u.name AS "userName"',
                'u.email AS "userEmail"',
                'u.roles::text AS "userRoles"',
                'u.status AS "userStatus"',
                '(u.email_verified_at IS NOT NULL) AS "emailVerified"',
                'u.created_at AS "createdAt"',
                'u.last_login_at AS "lastLoginAt"',
            )
            ->from('users', 'u')
            ->orderBy('u.created_at', 'DESC');

        $page = $this->paginator->paginate(
            $query,
            $pagination->page,
            $pagination->limit,
        );

        $items = $page->getItems();
        $items = is_array($items) ? $items : iterator_to_array($items);
        $total = $page->getTotalItemCount();

        return new UserList(
            items: array_map($this->mapUser(...), $items),
            pagination: new PaginationInfo(
                current: $pagination->page,
                limit: $pagination->limit,
                offset: ($pagination->page - 1) * $pagination->limit,
                total: $total,
                pages: (int) ceil($total / $pagination->limit),
            ),
        );
    }

    /** @param array<string, mixed> $row */
    private function mapUser(array $row): UserView
    {
        return new UserView(
            userId: (string) $row['userId'],
            name: (string) $row['userName'],
            email: (string) $row['userEmail'],
            roles: array_values(array_filter(
                json_decode((string) $row['userRoles'], true) ?: [],
                is_string(...),
            )),
            status: (string) $row['userStatus'],
            emailVerified: (bool) $row['emailVerified'],
            createdAt: new DateTimeImmutable((string) $row['createdAt']),
            lastLoginAt: isset($row['lastLoginAt'])
                ? new DateTimeImmutable((string) $row['lastLoginAt'])
                : null,
        );
    }
}

There is no User entity hydration, no lazy-loading proxy, and no Unit of Work. The query selects the response data and maps each row directly to an immutable model.

In production code, I use a small typed row reader instead of repeating raw casts. It provides methods such as string(), bool(), dateTime(), and nullableDateTime(), and fails at the mapping boundary when the database returns an unexpected shape.

PHP

private function mapUser(array $row): UserView
{
    $reader = RowReader::from($row);

    return new UserView(
        userId: $reader->string('userId'),
        name: $reader->string('userName'),
        email: $reader->string('userEmail'),
        roles: $reader->stringList('userRoles'),
        status: $reader->string('userStatus'),
        emailVerified: $reader->bool('emailVerified'),
        createdAt: $reader->dateTime('createdAt'),
        lastLoginAt: $reader->nullableDateTime('lastLoginAt'),
    );
}

This mapping step is worth keeping explicit. It prevents database types from leaking into the rest of the application and gives every projection one clear construction point.

Step 7: wire the contract to the DBAL adapter

If Symfony discovers only one implementation, autowiring may resolve the read-model interface automatically. I still recommend understanding the explicit configuration because it becomes necessary as soon as multiple implementations exist.

YAML

# config/services.yaml
services:
    _defaults:
        autowire: true
        autoconfigure: true

    App\:
        resource: '../src/'

    App\Identity\Application\ReadModel\UsersReadModel:
        alias: App\Identity\Infrastructure\DBAL\DbalUsersReadModel

    App\Shared\Application\Bus\QueryBus:
        alias: App\Shared\Infrastructure\Bus\SymfonyQueryBus

The application now depends on UsersReadModel, while the composition root selects DBAL.

Later, you can decorate the adapter with a cache or replace it with a search-index implementation without changing the query handler.

Step 8: keep the controller boring

The HTTP controller maps query-string input, dispatches the query, and returns its result:

PHP

#[Route('/admin/users', name: 'identity.list_users', methods: ['GET'])]
final class ListUsersController
{
    public function __construct(
        private QueryBus $queryBus,
    ) {}

    public function __invoke(
        #[MapQueryString] PaginationRequest $request,
    ): JsonResponse {
        $result = $this->queryBus->handle(
            new ListUsers($request->toPaginationFilters()),
        );

        return new JsonResponse($result);
    }
}

The complete flow is now:

Mermaid

Every layer has one reason to change. HTTP details stay in Presentation, the use case stays in Application, and SQL stays in Infrastructure.

Filtering and sorting without exposing SQL

Dynamic listings introduce an important security problem.

SQL values can be safely parameterized, but column names and sort expressions cannot be treated as arbitrary user input. Never pass ?sort=email;DROP TABLE users directly into ORDER BY.

My read models expose a whitelist that maps public filter names to trusted SQL expressions:

PHP

/** @return array<string, string> */
private function sortFields(): array
{
    return [
        'name' => 'u.name',
        'email' => 'u.email',
        'status' => 'u.status',
        'createdAt' => 'u.created_at',
        'lastLoginAt' => 'u.last_login_at',
    ];
}

Then the filtering component accepts only a known key:

PHP

private function applySort(
    QueryBuilder $query,
    ?SortFilter $sort,
): void {
    $fields = $this->sortFields();
    $column = $fields[$sort?->field ?? 'createdAt']
        ?? $fields['createdAt'];

    $direction = strtoupper($sort?->direction ?? 'DESC');
    $direction = $direction === 'ASC' ? 'ASC' : 'DESC';

    $query->orderBy($column, $direction);
}

The same pattern works for search and column filters:

PHP

private function filterFields(): array
{
    return [
        'status' => 'u.status',
        'role' => 'u.roles::text',
        'emailVerified' => '(u.email_verified_at IS NOT NULL)',
    ];
}

Values are still parameterized:

PHP

$query
    ->andWhere(sprintf('%s = :filter_value', $trustedColumn))
    ->setParameter('filter_value', $filter->value);

I reuse this mechanism across read models for:

Text search over approved columns.
Exact filters and multiple values.
Date ranges.
Sorting.
Pagination metadata.

The application-level filter objects remain independent from SQL. Each DBAL adapter decides which fields it supports and how those public names map to its schema.

Where direct SQL becomes much better than ORM

The difference becomes clearer with dashboards.

Suppose a revenue dashboard needs one point per day, including days with no transactions, captured amounts, paid orders, and failed orders.

With entities, I may load many transactions and orders into PHP, group them by date, and fill the missing days myself. The database is already much better at this work.

A read model can use a purpose-built query:

SQL

WITH days AS (
    SELECT generate_series(
        date_trunc('day', :start_at::timestamptz),
        GREATEST(
            date_trunc('day', :end_at::timestamptz) - interval '1 day',
            date_trunc('day', :start_at::timestamptz)
        ),
        interval '1 day'
    ) AS day_start
),
revenue AS (
    SELECT
        date_trunc('day', initiated_at) AS day_start,
        SUM((amount ->> 'amount')::integer) AS captured_amount
    FROM payment_transactions
    WHERE status = 'succeeded'
      AND initiated_at >= :start_at
      AND initiated_at < :end_at
    GROUP BY 1
),
orders_by_day AS (
    SELECT
        date_trunc('day', created_at) AS day_start,
        COUNT(*) FILTER (WHERE status IN ('paid', 'fulfilled')) AS successful,
        COUNT(*) FILTER (WHERE status IN ('failed', 'cancelled')) AS unsuccessful
    FROM orders
    WHERE created_at >= :start_at
      AND created_at < :end_at
    GROUP BY 1
)
SELECT
    to_char(days.day_start, 'YYYY-MM-DD') AS date,
    COALESCE(revenue.captured_amount, 0) AS "capturedAmount",
    COALESCE(orders_by_day.successful, 0) AS "successfulOrderCount",
    COALESCE(orders_by_day.unsuccessful, 0) AS "unsuccessfulOrderCount"
FROM days
LEFT JOIN revenue USING (day_start)
LEFT JOIN orders_by_day USING (day_start)
ORDER BY days.day_start;

This query returns the exact chart shape. It does not pretend that the chart is a domain aggregate.

PHP

final readonly class TrendPoint
{
    public function __construct(
        public string $date,
        public int $capturedAmount,
        public int $successfulOrderCount,
        public int $unsuccessfulOrderCount,
    ) {}
}

This is one of the strongest benefits of the CQRS split: the write model can stay expressive and behavior-oriented while the read model can use joins, common table expressions, JSON operators, window functions, and database-specific optimizations.

One read model does not have to mean one table

An ORM repository normally represents access to one aggregate type. A read model represents an answer.

That answer may combine several sources:

SQL

SELECT
    u.id AS "userId",
    u.name AS "userName",
    o.name AS "organizationName",
    s.status AS "subscriptionStatus",
    COUNT(ag.id) AS "activeGrantCount"
FROM users u
LEFT JOIN organization_members om ON om.user_id = u.id
LEFT JOIN organizations o ON o.id = om.organization_id
LEFT JOIN subscriptions s ON s.organization_id = o.id
LEFT JOIN access_grants ag
    ON ag.user_id = u.id
    AND ag.revoked_at IS NULL
WHERE u.id = :user_id
GROUP BY u.id, u.name, o.name, s.status;

Creating a User aggregate with nested Organization, Subscription, and AccessGrant entities only to produce this response would couple several write models together.

The projection avoids that. It can cross tables while still returning a narrow, application-owned model.

That does not mean every query should join the entire database. The read model should still belong to a bounded context and expose a deliberate contract. But it is free from aggregate hydration rules.

Streaming large reads

Pagination is appropriate for a user-facing table. Exports and background processing have another requirement: do not load the complete result into memory.

DBAL can iterate rows:

PHP

/** @return iterable<UserView> */
public function streamAll(): iterable
{
    $query = $this->connection->createQueryBuilder()
        ->select(/* required columns */)
        ->from('users', 'u')
        ->orderBy('u.id', 'ASC');

    foreach ($query->executeQuery()->iterateAssociative() as $row) {
        yield $this->mapUser($row);
    }
}

The caller consumes one projection at a time. This is simpler and more memory-efficient than hydrating thousands of managed entities and asking the ORM to track them.

Testing the read side

The read-model contract gives me two useful testing levels.

First, application tests can replace DBAL with an in-memory implementation:

PHP

#[AsAlias(UsersReadModel::class, when: 'test')]
final class InMemoryUsersReadModel implements UsersReadModel
{
    /** @var list<UserView> */
    private array $users = [];

    public function list(PaginationFilters $filters): UserList
    {
        $offset = ($filters->page - 1) * $filters->limit;
        $items = array_slice($this->users, $offset, $filters->limit);

        return new UserList(
            items: $items,
            pagination: new PaginationInfo(
                current: $filters->page,
                limit: $filters->limit,
                offset: $offset,
                total: count($this->users),
                pages: (int) ceil(count($this->users) / $filters->limit),
            ),
        );
    }
}

This lets a behavior test dispatch the real ListUsers query through the real query bus without requiring SQL fixtures for every scenario.

Second, the DBAL adapter gets integration tests against the real database. These tests should verify the things the in-memory implementation cannot:

SQL joins and aliases.
Database type conversion.
Search, filter, and sort mappings.
Pagination totals.
Empty results and nullable columns.
Database-specific expressions.

Do not mock Connection and assert that a particular SQL string was called. That only tests the mock setup. Run the adapter against the database engine it was written for and assert the returned projection.

Read models need observability too

Direct SQL gives the adapter precise control over joins and projections, but failures still occur at an integration boundary. I log read-model failures with a unique message and relevant business context:

PHP

try {
    return $this->executeList($pagination);
} catch (Throwable $throwable) {
    $this->logger->critical('Unable to list identity users from DBAL read model', [
        'exception' => $throwable,
        'exceptionClass' => $throwable::class,
    ]);

    throw $throwable;
}

For a detail query, include the identifier. For a date-based dashboard, include the date range. Avoid dumping complete filters or result data into logs, especially when they may contain personal information.

Slow query monitoring and EXPLAIN ANALYZE are also part of read-model engineering. CQRS makes optimization safer because adding an index or rewriting one projection does not require changing the aggregate model.

ORM or DBAL? Use each where it is strongest

This architecture is not an argument for removing Doctrine ORM.

Use an ORM repository when you need to:

Load an aggregate and enforce its invariants.
Change domain state.
Track entity changes in a Unit of Work.
Persist relationships that belong to the aggregate boundary.

Use a DBAL read model when you need to:

Return a list, dashboard, report, search result, or export.
Select a small subset of columns.
Join data for presentation.
Aggregate, group, count, or calculate trends.
Use database-specific features.
Stream a large result without tracking entities.

A useful rule is: entities are optimized for behavior; read models are optimized for questions.

Common mistakes

CQRS can become unnecessary ceremony if I apply it without discipline.

Returning ORM entities from query handlers

This reconnects the read API to the write model and can trigger lazy loading during serialization. Return explicit projections instead.

Putting SQL in the controller

Removing the ORM does not remove architecture. SQL still belongs in an Infrastructure adapter behind an application contract.

Reusing one universal read model

A class named DatabaseReadModel with fifty unrelated methods becomes another service layer. Keep read contracts cohesive and owned by a business capability.

Accepting raw sort and filter columns

Parameters protect values, not identifiers. Map public filter names through a strict whitelist.

Hiding business decisions in SQL

SQL should shape and aggregate data. A rule that changes the meaning of the business belongs in the domain or application layer. The fact that a condition can be written in SQL does not mean SQL should own it.

Building two databases too early

A separate read store adds projections, delivery guarantees, replay, monitoring, and eventual consistency. Start with separate code paths on one database. Introduce another store only when a measured requirement justifies it.

Use ORM and DBAL for different jobs

You do not need an ORM for every data fetch in a Symfony application.

Commands load aggregates and change state through ORM repositories.
Queries describe questions and run through a synchronous query bus.
Query handlers depend on application-owned read-model interfaces.
DBAL adapters execute purpose-built SQL.
Immutable projections return exactly what the caller needs.
Whitelisted filters keep dynamic queries controlled.
In-memory adapters test use cases, while database integration tests verify SQL.

The split also clarifies intent. Commands use ORM repositories to load and protect aggregates. Queries use read models to return the exact shape a caller needs. Neither model has to perform the other's job.

…

Related writing

Need a software engineer?