Persisting One Aggregate Across Multiple Tables, ORM-Agnostic

Source: Dev.to

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 have an Order. It owns line items and a shipping address. In the

database that is three tables: orders, order_items,

order_addresses. The aggregate is one thing in the domain and three

rows-with-children in storage.

Now look at what most codebases do with that. The service loads the

order, loops the items, saves each one in its own statement. Halfway

through, a unique-constraint violation throws. The order header is

already committed. Two items are in. One address is missing. You have a

row in orders that no part of the system considers valid, and no

single place to point at when you ask how it got there.

The aggregate is supposed to be a consistency boundary. Saving its

parts in separate, independently-committing operations breaks that

boundary on the way to disk. This post is about closing it: one

repository, one transactional save across every table, and a read path

that rebuilds the whole object from rows without leaking the ORM into

the domain.

The aggregate the domain sees

The domain class has no idea it lives in three tables. It holds its

children directly and guards their invariants.

 */
    private array $items;

    private function __construct(
        private readonly OrderId $id,
        private readonly CustomerId $customerId,
        array $items,
        private Address $shipTo,
        private OrderStatus $status,
    ) {
        if ($items === []) {
            throw new InvalidOrder('order needs an item');
        }
        $this->items = array_values($items);
    }

    public static function place(
        OrderId $id,
        CustomerId $customerId,
        array $items,
        Address $shipTo,
    ): self {
        return new self(
            $id, $customerId, $items,
            $shipTo, OrderStatus::Placed,
        );
    }

    public static function restore(
        OrderId $id,
        CustomerId $customerId,
        array $items,
        Address $shipTo,
        OrderStatus $status,
    ): self {
        return new self(
            $id, $customerId, $items, $shipTo, $status,
        );
    }

    public function total(): Money
    {
        $sum = Money::zero($this->items[0]->price->currency);
        foreach ($this->items as $item) {
            $sum = $sum->plus($item->lineTotal());
        }
        return $sum;
    }

    public function id(): OrderId { return $this->id; }
    public function status(): OrderStatus { return $this->status; }
    /** @return list */
    public function items(): array { return $this->items; }
    public function shipTo(): Address { return $this->shipTo; }
    public function customerId(): CustomerId
    {
        return $this->customerId;
    }
}

Enter fullscreen mode


Exit fullscreen mode

LineItem and Address are small value objects in the same namespace.

The point: the items are loaded the moment the order is. There is no

proxy, no getItems() that fires a query on first access. A loaded

Order is fully present in memory or it does not exist yet.

The port the use case depends on

The use case asks for an OrderRepository. The interface speaks domain

types and hides every table.

pdo->beginTransaction();
        try {
            $this->upsertHeader($order);
            $this->replaceItems($order);
            $this->replaceAddress($order);
            $this->pdo->commit();
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    private function upsertHeader(Order $order): void
    {
        $sql = 'INSERT INTO orders
                  (id, customer_id, status)
                VALUES (:id, :cust, :status)
                ON DUPLICATE KEY UPDATE
                  status = VALUES(status)';
        $this->pdo->prepare($sql)->execute([
            'id' => $order->id()->value,
            'cust' => $order->customerId()->value,
            'status' => $order->status()->value,
        ]);
    }

    private function replaceItems(Order $order): void
    {
        $del = $this->pdo->prepare(
            'DELETE FROM order_items WHERE order_id = :id'
        );
        $del->execute(['id' => $order->id()->value]);

        $ins = $this->pdo->prepare(
            'INSERT INTO order_items
               (order_id, sku, qty, price_minor, currency)
             VALUES (:oid, :sku, :qty, :price, :cur)'
        );
        foreach ($order->items() as $item) {
            $ins->execute([
                'oid' => $order->id()->value,
                'sku' => $item->sku,
                'qty' => $item->quantity,
                'price' => $item->price->amountMinor,
                'cur' => $item->price->currency,
            ]);
        }
    }

    private function replaceAddress(Order $order): void
    {
        $a = $order->shipTo();
        $sql = 'INSERT INTO order_addresses
                  (order_id, line1, city, country)
                VALUES (:id, :l1, :city, :country)
                ON DUPLICATE KEY UPDATE
                  line1 = VALUES(line1),
                  city = VALUES(city),
                  country = VALUES(country)';
        $this->pdo->prepare($sql)->execute([
            'id' => $order->id()->value,
            'l1' => $a->line1,
            'city' => $a->city,
            'country' => $a->country,
        ]);
    }
}

Enter fullscreen mode


Exit fullscreen mode

Three things earn their place here.

The whole write is in one transaction. If the address insert fails, the

header and items roll back with it. The database never holds a

half-written aggregate. That is the consistency boundary, honored at the

storage edge.

Children are replaced, not diffed. DELETE every order_item for this

order, then re-insert the current set. You skip the bookkeeping of

“which item is new, which changed, which was removed.” The aggregate in

memory is the source of truth; the rows become a copy of it. For a

small child collection this is the simplest correct option, and it is

within one transaction, so no reader ever sees the gap between delete

and insert.

The header upsert means save works for both a new order and an edited

one. The use case calls save and does not branch on whether the order

existed.

The read path: rebuild the whole from rows

Loading is the mirror image. Pull every table for the id, then hand the

rows to a factory that rebuilds the aggregate.

public function findById(OrderId $id): ?Order
{
    $header = $this->fetchHeader($id->value);
    if ($header === null) {
        return null;
    }

    $items = $this->fetchItems($id->value);
    $address = $this->fetchAddress($id->value);

    return OrderMapper::toDomain($header, $items, $address);
}

private function fetchHeader(string $id): ?array
{
    $stmt = $this->pdo->prepare(
        'SELECT id, customer_id, status
         FROM orders WHERE id = :id'
    );
    $stmt->execute(['id' => $id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row === false ? null : $row;
}

private function fetchItems(string $id): array
{
    $stmt = $this->pdo->prepare(
        'SELECT sku, qty, price_minor, currency
         FROM order_items WHERE order_id = :id'
    );
    $stmt->execute(['id' => $id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Enter fullscreen mode


Exit fullscreen mode

Three queries, all eager. The order, its items, its address, fetched

before the aggregate is built. No state in the returned Order is a

promise to query later. That is the difference between this and a lazy

ORM relation, and it is the difference that bites in production.

A lazy collection looks loaded. You read $order->items() inside a

controller and it works, because the entity manager is still open. The

same call inside a queue worker, after the unit of work has closed,

throws a LazyInitializationException or hands you an empty set. The

read path above cannot do that. The items are plain value objects in an

array the moment findById returns.

The mapper keeps the ORM out of the domain

The mapper is the only code that knows column names. It turns rows into

value objects and calls the domain’s restore factory.

 new LineItem(
                sku: $r['sku'],
                quantity: (int) $r['qty'],
                price: new Money(
                    (int) $r['price_minor'],
                    $r['currency'],
                ),
            ),
            $itemRows,
        );

        return Order::restore(
            id: new OrderId($header['id']),
            customerId: new CustomerId(
                $header['customer_id']
            ),
            items: $items,
            shipTo: new Address(
                $addressRow['line1'],
                $addressRow['city'],
                $addressRow['country'],
            ),
            status: OrderStatus::from($header['status']),
        );
    }
}

Enter fullscreen mode


Exit fullscreen mode

restore rebuilds the object without re-running placement invariants.

The rows were valid when they were written; re-checking them on load

hides the bug in whatever wrote a bad row. The domain Order has zero

imports from Infrastructure. Swap PDO for Doctrine’s DBAL, or for a

document store that keeps the whole aggregate in one JSON column, and

only this adapter and mapper change.

Where the transaction actually lives

In the PDO adapter above, save owns its transaction, which is fine

when one save is one unit of work. The moment a use case saves two

aggregates that must commit together, the transaction belongs one layer

out, around the use case, behind a port:

interface TransactionRunner
{
    /** @template T  @param callable():T $work  @return T */
    public function run(callable $work): mixed;
}

Enter fullscreen mode


Exit fullscreen mode

The use case wraps its work in run(...); the repository’s save joins

the open transaction instead of starting its own. The same delete-insert

write stays correct; it just stops being the outermost boundary. Either

way, the rule holds: the aggregate lands whole or not at all.

The discipline in one line

One aggregate maps to one repository. The repository owns the spread

across tables on write and the gather across tables on read. Children

load eagerly, save inside a single transaction, and the domain class

never learns a table name. That is the whole pattern, and it is the same

whether the ORM is Doctrine, Eloquent mapped to a side class, or raw

PDO.

If this was useful

This mapping problem — one aggregate, many tables, no leaks — gets a

full treatment in Decoupled PHP, next to the repository, transaction,

and unit-of-work patterns it leans on. The book’s whole argument is that

storage is an adapter your domain shouldn’t feel, and the order

aggregate here is the running example it builds on.

Available on Kindle, Paperback, and Hardcover. English, German, and Japanese editions out now — Portuguese and Spanish coming soon.