Archiboard

Set B · Services & Boundaries

CQRS without the ceremony

Two models, one database, no mediator. Separate the shapes you write from the shapes you read and stop there until something forces you further.

In the summer of 2017 I counted the files needed to display a list of invoices in a codebase I had been asked to look at. Nine. A query class with two properties, a validator for the query class, a handler, a response DTO, a profile that mapped the entity to the DTO, a behaviour that logged the query, a behaviour that timed it, an interface nobody else implemented, and a registration line. The SQL that eventually came out of all this was a SELECT with a join and a TOP 50. The team called it CQRS and were rather proud of it.

None of those nine files were the idea. The idea is that the shape you need to change data and the shape you need to show data are different shapes, and forcing one class to be both is what makes list screens slow and aggregates fat. That is all. It does not require a mediator, a second database, an event store, or a folder called Behaviours.

I wrote the first version of this sheet the same month a consultancy was explaining to my team that what we really wanted was event sourcing: a write store, a read store, and a projection rebuild nobody in the room had ever timed. We did not buy it. I have shipped the cheap separation on four products since, and on exactly one of them did the read side ever move to its own store, four years in, for one screen.

Two models, not two databases

The Azure pattern documentation is careful about this and most blog posts are not: the first and most useful level of CQRS is separate read and write models over a single shared data store. Same tables, same connection string, same transaction log. What differs is the code path.

The write path loads an aggregate, calls a method on it that enforces the rules, and saves. It is tracked, it is small, and it never returns anything for the screen to render beyond an id. The read path issues one statement and materialises a flat record that matches what the screen draws, including counts and joined names that no entity in your model has a property for. It is untracked and it does not go anywhere near the domain model.

// Write path: the aggregate is loaded whole, changed, and saved. .NET 10.
app.MapPost("/invoices/{id:guid}/lines", async (
    Guid id, AddLine body, BillingDbContext db, CancellationToken ct) =>
{
    var invoice = await db.Invoices
        .Include(i => i.Lines)
        .SingleOrDefaultAsync(i => i.Id == id, ct);

    if (invoice is null) return Results.NotFound();

    invoice.AddLine(body.Description, body.Amount); // the invariants live in here
    await db.SaveChangesAsync(ct);
    return Results.NoContent();
});

That endpoint has no query object and no handler class, and I would not add one. The mediator earns its place when you have cross-cutting behaviour you genuinely want on every message and more than one transport feeding the same handlers. Two products in twenty-five years met that bar. On the others the mediator was a way to turn a method call into three files.

The read path is allowed to be SQL

For anything with a filter, a sort, a page and a total, I write the SQL. Dapper is fine, EF Core's SqlQuery over an unmapped type is fine, and I have used both in the same solution without embarrassment. The point is that the result type belongs to the screen, not to the model.

public sealed record InvoiceRow(
    Guid Id, string Number, DateOnly IssuedOn, decimal Total, string Customer, int LineCount);

// EF Core 10: SqlQuery projects into a type that is not in the model at all.
var rows = await db.Database.SqlQuery<InvoiceRow>($"""
    SELECT i.Id, i.Number, i.IssuedOn, i.Total, c.LegalName AS Customer,
           (SELECT COUNT(*) FROM billing.InvoiceLine l WHERE l.InvoiceId = i.Id) AS LineCount
    FROM billing.Invoice i
    JOIN billing.Customer c ON c.Id = i.CustomerId
    WHERE i.TenantId = {tenantId}
    ORDER BY i.IssuedOn DESC
    OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY
    """).ToListAsync(ct);

SqlQuery returning unmapped types arrived in EF Core 8 and it removed the last reason I had for pulling Dapper into a solution that already had EF. The interpolated values become parameters, so it is not a string concatenation hole; SqlQueryRaw is the one to be careful with.

If you prefer to stay in LINQ, project in the query and let EF do the work. A Select into a record with no entity types in it is not tracked at all, which is better than AsNoTracking on a query that materialises entities and then throws most of them away. Reach for raw SQL when the query has a subquery, a window function or a GROUP BY that LINQ turns into something you would not sign your name to.

The write path and the read path over one shared database write path POST /invoices/{id}/lines endpoint method Invoice aggregate DbContext, tracked read path GET /invoices?page=2 InvoiceListReader one SELECT flat record, untracked one database no ORM on this side same tables, same transaction log, two models
Fig. 1. The cheapest useful form of CQRS. The two paths share a database and share nothing else; the read side never loads an entity and the write side never returns a screen shape.

When a projection is worth building

A projection is a table you maintain yourself, written by the write side and read by the read side, holding data in the shape the screen wants. It is real CQRS with a real cost: you now have two copies of the truth and a way for them to drift.

The 2017 version of this article was dismissive of projections. I have since built three and kept two. The test I use now is one number. If the query you cannot make fast enough is run more than a few times a second, and you have already tried an index, and the plan is still doing work proportional to history rather than to the page you are showing, a projection is worth it. On one product that was the tenant dashboard: a count of open items per tenant per category, which was six aggregations over three years of rows, run every time anybody opened the home screen. It went from about 900 ms to 4 ms as a maintained table with eleven columns.

The one I threw away was a projection of invoice lists that was never slow. It was slow in the design meeting, in theory, and in practice the index did the job. That is the usual outcome.

If you do build one, write it in the same transaction as the change that causes it, or send yourself a message through the outbox and accept that it will be a second or two behind. What you should not do is have a nightly job rebuild it, because then you own a table that is silently wrong from Monday afternoon until Tuesday morning.

The framework trap

The failure mode with this pattern is not architectural, it is bureaucratic. You add a library, the library has a convention, the convention needs a class per operation, and within a year the codebase has four hundred handler classes of eleven lines each and nobody can find where anything happens. The separation you wanted was between two shapes of data. What you got was a filing system.

My rule is that the pattern has to be visible in the folder structure and invisible in the ceremony. Two folders, one holding aggregates and their methods, one holding readers and their records. No base classes. No pipeline. If you later need logging on every write, ASP.NET Core has middleware and EF Core has interceptors, and both of those already exist.

What I would build on Monday

Endpoints that call the domain model directly for writes, reader classes with hand-written SQL for reads, both against one database, and not one line of infrastructure between them. Then wait. If a screen gets slow, measure it, index it, and only then build a projection for that one screen. If the read load eventually outgrows the write store, a read replica is a connection string and buys you a year. The version of this design with two databases and an event store is a real answer to a real problem, and in twenty-five years I have needed it once.

Drawn from