CQRS: Separate Read and Write Models, One Step at a Time

By Sergey Nosov

13 October 2026

In April 2011, Udi Dahan, an early advocate of Command Query Responsibility Segregation (CQRS), opened a blog post with an apology. “Please accept my apologies for my part in the overly-complex software being created because of it,” he wrote. A few lines later he added, “Most people using CQRS (and Event Sourcing too) shouldn’t have done so.” That July, Martin Fowler described the pattern on his website and gave it a warning of its own: “for most systems CQRS adds risky complexity.”

The idea at the center is small: change data through one model, and read it through another. The complexity comes from what tends to arrive with it: a second database, a message bus, eventual consistency, and event sourcing. Those are separate decisions. Each solves a different problem, and each has its own price.

So I treat CQRS as a series of steps. First, separate the code paths. Next, add a read model to the same database, updated in the same transaction. Move the read model to its own store only when it must scale separately or needs another kind of database, such as a search index. Accept that it will then lag. Decide on event sourcing on its own merits. Take each step for a problem you can name, and stop at the first one that solves it. Runnable C# and JavaScript samples cover the second and third.

From Command-Query Separation to CQRS

CQRS grew out of an older and smaller rule. Bertrand Meyer named it command-query separation (CQS) in Object-Oriented Software Construction, first published in 1988. In Fowler’s summary, a query returns a result and leaves the system’s observable state alone, while a command changes state and returns nothing. He also gives an exception, a stack’s pop, which does both: “I’m prepared to break it to get my pop.”

Greg Young introduced CQRS, as Microsoft’s .NET microservices guide notes. His 2010 CQRS Documents puts the difference from CQS simply: an object splits in two, one for the commands and one for the queries. His example divides a CustomerService into a CustomerWriteService, with MakeCustomerPreferred, and a CustomerReadService, with GetPreferredCustomers.

The word command misleads, because it suggests create, update, and delete. A command in CQRS should name a business task. Microsoft’s pattern guide gives a hotel-booking example: “Book hotel room” rather than “Set ReservationStatus to Reserved.”

Dahan’s 2009 essay shows why this matters. A customer service representative makes a customer preferred and changes the address and last name in one save, but billing has just flagged the customer as delinquent. Handed the whole record, the server must guess what each change meant, and, as Dahan notes, “we tend to reject the whole thing if any part of it is off.” Separate commands for making a customer preferred and for recording a marriage or a move let the server refuse the preferred status and accept the rest.

Queries are the simpler half. In Microsoft’s words, they “never alter data.” They return data transfer objects (DTOs) shaped for the screen, with no domain logic. My Software Development Principles series has entries on command-query separation and on CQRS, with the signs that each is applied well or badly.

The Steps, Cheapest First

Each step buys something the one before cannot, at a new cost. Event sourcing is not a fourth step: it can feed read models in the write transaction or asynchronously, so it is a separate decision.

Step 1: Separate the Paths

The first step changes code, not data. Command handlers enforce the business rules and save the changes, using a domain model or, as the samples below do, a plain transaction script. Queries skip all of that: they read the tables directly and shape the result for the screen. Microsoft’s .NET guide calls this simplified CQRS: “Each layer has its own data model (note that we say model, not necessarily a different database).” Dahan, commenting under his 2011 post, would call it CQS, not CQRS, because the data is not yet separated.

Young calls such a query side a “Thin Read Layer.” He lists the smells of one model that both enforces rules and serves screens. One is the “use of prefetch paths on the read use cases,” which in Entity Framework Core means a chain of Include calls. At worst, Young estimates, the separation costs the same as one model, because “all that has really been done is the moving of a responsibility.”

Step 2: Add a Read Model in the Same Transaction

The second step adds data shaped for reading. Dahan suggests “one table for each view,” with the joins and totals already done. He proposed such tables for a separate store, but they work in the same database too. The command handler updates the table in the same transaction as the write model. Reads become simple lookups, and the read model is never behind: the order and its summary commit together, or neither does. Some databases maintain such a table themselves: SQL Server updates an indexed view during the statement that changes its base tables.

The price lands on every write, which now updates two shapes of the same data. For indexed views, Microsoft warns that with many of them, or a few complex ones, “DML query performance can degrade significantly.” Writes to the same summary row also queue, because each holds a lock on it until it commits. And the two sides become coupled: the command handlers must know every read model they keep current, so each new read model means another change to them.

Step 3: Move the Read Model to Its Own Store

The third step moves the read model to a separate store, fed by events from the write side. It can then run on its own hardware and use whatever kind of store its readers need. Microsoft’s pattern guide pairs a document database for reads with a relational one for writes; a search index or a cache also fits. The events also loosen the coupling of the second step: a new read model can subscribe without changing the command handlers, if the events carry the facts it needs.

The price is lag: the read store hears of each change only after the write commits. This step also brings a new way to fail, shown below with the fix Microsoft recommends.

Step 2 in C#: One Database, One Transaction

The sample is a small order system. The write model is two normalized tables, orders and their lines. The read model is one row per customer, with an order count and the total spent. The five blocks below form one .NET 10 file-based app: save them in order as cqrs-sync.cs and run dotnet run cqrs-sync.cs.

The sample uses SQLite in memory through plain ADO.NET. Its upsert also runs on PostgreSQL. SQL Server has no ON CONFLICT clause and needs another form, such as MERGE with the HOLDLOCK hint. The calls are synchronous because “SQLite doesn’t support asynchronous I/O,” as Microsoft’s documentation for Microsoft.Data.Sqlite says. Against a database server, use the asynchronous methods.

The first block is the program:

#:package Microsoft.Data.Sqlite@10.0.12

using Microsoft.Data.Sqlite;

using var db = new SqliteConnection("Data Source=:memory:");
db.Open();
using (var create = db.CreateCommand())
{
    create.CommandText = Schema.Sql;
    create.ExecuteNonQuery();
}

var placeOrder = new PlaceOrderHandler(db);
var getSummary = new GetCustomerSummaryHandler(db);

var order = new PlaceOrder(Guid.NewGuid(), "c-42",
    [new("book", 2, 1_500), new("pen", 1, 250)]);
placeOrder.Handle(order);

// Prints: CustomerSummary {
//   CustomerId = c-42,
//   OrderCount = 1,
//   TotalSpentCents = 3250 }
Console.WriteLine(getSummary.Handle(new("c-42")));

try
{
    // A retry of the same command
    placeOrder.Handle(order);
}
catch (SqliteException e) when (e.SqliteErrorCode == 19)
{
    // 19 is SQLITE_CONSTRAINT.
    // Prints: Rejected: SQLite
    //   Error 19: 'UNIQUE
    //   constraint failed:
    //   orders.id'.
    Console.WriteLine($"Rejected: {e.Message}");
}

// Prints the same summary:
// the retry wrote nothing.
Console.WriteLine(getSummary.Handle(new("c-42")));

Commands and queries are plain immutable records. The client chooses the order’s ID, so placing an order has nothing to return:

// A command names a task.
record PlaceOrder(
    Guid OrderId,
    string CustomerId,
    IReadOnlyList<OrderLine> Lines);

record OrderLine(string Sku, int Quantity, long UnitPriceCents);

// A query asks a question and
// gets back a DTO.
record GetCustomerSummary(string CustomerId);

record CustomerSummary(
    string CustomerId, long OrderCount, long TotalSpentCents);

The schema keeps the two models apart:

static class Schema
{
    public const string Sql = """
        CREATE TABLE orders (
            id TEXT NOT NULL PRIMARY KEY,
            customer_id TEXT NOT NULL);
        CREATE TABLE order_lines (
            order_id TEXT NOT NULL REFERENCES orders (id),
            sku TEXT NOT NULL,
            quantity INTEGER NOT NULL CHECK (quantity > 0),
            unit_price_cents INTEGER NOT NULL,
            PRIMARY KEY (order_id, sku));
        CREATE TABLE customer_summaries (
            customer_id TEXT PRIMARY KEY,
            order_count INTEGER NOT NULL,
            total_spent_cents INTEGER NOT NULL);
        """;
}

The command handler checks the rules, then writes the order, its lines, and the customer’s summary in one transaction. If any statement fails, the rollback takes every change with it:

class PlaceOrderHandler(SqliteConnection db)
{
    public void Handle(PlaceOrder command)
    {
        if (command.Lines.Count == 0)
            throw new ArgumentException("An order needs a line.");
        if (command.Lines.Any(line => line.Quantity <= 0))
            throw new ArgumentException("Quantities must be positive.");

        long total = command.Lines.Sum(
            line => line.Quantity * line.UnitPriceCents);

        using var tx = db.BeginTransaction();

        // The write model
        Run(tx, "INSERT INTO orders VALUES ($id, $customer)",
            ("$id", command.OrderId.ToString()),
            ("$customer", command.CustomerId));
        foreach (var line in command.Lines)
        {
            Run(tx, """
                INSERT INTO order_lines
                VALUES ($id, $sku, $quantity, $price)
                """,
                ("$id", command.OrderId.ToString()),
                ("$sku", line.Sku),
                ("$quantity", line.Quantity),
                ("$price", line.UnitPriceCents));
        }

        // The read model, in the
        // same transaction
        Run(tx, """
            INSERT INTO customer_summaries
            VALUES ($customer, 1, $total)
            ON CONFLICT (customer_id) DO UPDATE SET
                order_count = customer_summaries.order_count + 1,
                total_spent_cents =
                    customer_summaries.total_spent_cents + $total
            """,
            ("$customer", command.CustomerId),
            ("$total", total));

        tx.Commit();
    }

    void Run(SqliteTransaction tx, string sql,
        params (string Name, object Value)[] parameters)
    {
        using var cmd = db.CreateCommand();
        cmd.Transaction = tx;
        cmd.CommandText = sql;
        foreach (var (name, value) in parameters)
            cmd.Parameters.AddWithValue(name, value);
        cmd.ExecuteNonQuery();
    }
}

The query handler reads one row and returns the DTO. It touches neither the orders nor any domain logic:

class GetCustomerSummaryHandler(SqliteConnection db)
{
    public CustomerSummary? Handle(GetCustomerSummary query)
    {
        using var cmd = db.CreateCommand();
        cmd.CommandText = """
            SELECT order_count, total_spent_cents
            FROM customer_summaries
            WHERE customer_id = $customer
            """;
        cmd.Parameters.AddWithValue("$customer", query.CustomerId);

        using var reader = cmd.ExecuteReader();
        return reader.Read()
            ? new CustomerSummary(query.CustomerId,
                reader.GetInt64(0), reader.GetInt64(1))
            : null;
    }
}

The output shows what this step promises: the summary is current the moment the command returns. The retry is rejected before it writes anything, because the primary key refuses a second row for the same order. Had a later statement failed, say on a repeated SKU, the rollback would have removed the order row too. Still, a client that retried because a response was lost deserves a success rather than an error. My article on idempotency in APIs covers how to give it one.

CQRS needs no special library: the handlers are plain classes. Microsoft’s .NET guide does put a mediator or a queue between controllers and handlers, since calling a handler from each controller “would be too coupled.” The guide uses MediatR; if you do too, check its license. Since version 13.0, released in July 2025, MediatR has been dual-licensed under the Reciprocal Public License 1.5 and a commercial license. A free community edition covers companies and individuals under $5 million in gross annual revenue.

Consistency Comes from Timing, Not from the Pattern

Does CQRS make reads stale? That depends on when the read model is updated, and the pattern allows three answers:

Young writes that “most systems can be eventually consistent on the Query side,” and Dahan adds that stale data is normal anyway. “Almost any system which makes use of a cache is serving stale data,” and a screen goes stale the moment another user changes what it shows. So the useful question is how stale each screen may be, and what happens when a user acts on stale data.

Some reads must reflect the latest write by anyone, not just the reader’s own. For those, eventual consistency complicates CQRS. An asynchronous read model can serve such a read only after applying every write committed before the read began. If that wait is too long, use a read model kept in the write transaction, or the write side itself.

Step 3 in JavaScript: A Separate Store and an Outbox

The obvious way to feed a separate read store looks like this (an illustration, not part of the sample):

await orders.save(order);
await bus.publish(orderPlaced);

If the process stops between the two lines, the order exists and the read model never hears of it. Swap the lines, and a failed insert leaves the read model showing an order that does not exist. As Microsoft notes, you usually cannot enlist a message broker and a database in one distributed transaction.

The remedy is the transactional outbox. Chris Richardson, author of Microservices Patterns, says the service should “first store the message in the database as part of the transaction that updates the business entities.” A separate process then passes each event on. It may pass one on more than once, Richardson warns, so “a message consumer must be idempotent.” Here, the projector itself reads the outbox and updates the read model.

The two sides are separate SQLite databases, opened through node:sqlite, which is built into Node.js 24 and has been a release candidate since version 24.15. Save the five blocks in order as cqrs-async.mjs and run node cqrs-async.mjs. First, the two stores:

// cqrs-async.mjs, Node.js 24
import { DatabaseSync } from 'node:sqlite';

// The write side: orders and an
// outbox of events
const writeDb = new DatabaseSync(':memory:');
writeDb.exec(`
  CREATE TABLE orders (
    id TEXT NOT NULL PRIMARY KEY,
    customer_id TEXT NOT NULL,
    total_cents INTEGER NOT NULL);
  CREATE TABLE outbox (
    position INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL,
    payload TEXT NOT NULL);`);

// The read side: summaries and how
// far it has read
const readDb = new DatabaseSync(':memory:');
readDb.exec(`
  CREATE TABLE customer_summaries (
    customer_id TEXT PRIMARY KEY,
    order_count INTEGER NOT NULL,
    total_spent_cents INTEGER NOT NULL);
  CREATE TABLE checkpoint (position INTEGER NOT NULL);
  INSERT INTO checkpoint VALUES (0);`);

function inTransaction(db, work) {
  db.exec('BEGIN');
  try {
    const result = work();
    db.exec('COMMIT');
    return result;
  } catch (error) {
    db.exec('ROLLBACK');
    throw error;
  }
}

The command handler checks the rules, writes the order and its event in one transaction, and returns the event’s position in the outbox:

function placeOrder({ orderId, customerId, lines }) {
  if (lines.length === 0) {
    throw new Error('An order needs a line.');
  }
  for (const { quantity, unitPriceCents } of lines) {
    if (!Number.isInteger(quantity) || quantity <= 0) {
      throw new Error('Quantities must be positive integers.');
    }
    if (!Number.isInteger(unitPriceCents)) {
      throw new Error('Prices must be whole cents.');
    }
  }
  const totalCents = lines.reduce(
    (sum, line) => sum + line.quantity * line.unitPriceCents, 0);

  // The order and its event commit
  // together, or not at all.
  return inTransaction(writeDb, () => {
    writeDb.prepare('INSERT INTO orders VALUES (?, ?, ?)')
      .run(orderId, customerId, totalCents);
    const event = { orderId, customerId, totalCents };
    const { lastInsertRowid } = writeDb
      .prepare('INSERT INTO outbox (type, payload) VALUES (?, ?)')
      .run('OrderPlaced', JSON.stringify(event));
    return { position: Number(lastInsertRowid) };
  });
}

The projector reads a bounded batch of events after its checkpoint, then claims and applies each one. Because node:sqlite runs every query synchronously, blocking the event loop, a production projector like this one belongs in its own worker thread or process:

function readCheckpoint() {
  return readDb.prepare('SELECT position FROM checkpoint').get()
    .position;
}

function project() {
  // A bounded batch per run
  const events = writeDb
    .prepare(`SELECT * FROM outbox WHERE position > ?
      ORDER BY position LIMIT 100`)
    .all(readCheckpoint());
  for (const event of events) {
    const { customerId, totalCents } = JSON.parse(event.payload);
    inTransaction(readDb, () => {
      // Claim the event by moving
      // the checkpoint past it.
      // The claim fails if another
      // projector already did.
      const { changes } = readDb
        .prepare('UPDATE checkpoint SET position = ? WHERE position < ?')
        .run(event.position, event.position);
      if (changes === 0 || event.type !== 'OrderPlaced') return;
      readDb.prepare(`
        INSERT INTO customer_summaries VALUES (?, 1, ?)
        ON CONFLICT (customer_id) DO UPDATE SET
          order_count = customer_summaries.order_count + 1,
          total_spent_cents = customer_summaries.total_spent_cents
            + excluded.total_spent_cents`)
        .run(customerId, totalCents);
    });
  }
}

The query handler reads only the read model; a second function waits for it to catch up:

function getCustomerSummary(customerId) {
  const row = readDb
    .prepare('SELECT * FROM customer_summaries WHERE customer_id = ?')
    .get(customerId);
  return row
    ? { customerId,
        orderCount: row.order_count,
        totalSpentCents: row.total_spent_cents }
    : null;
}

// Read-your-writes: wait for the
// projector to reach a position.
async function getCustomerSummaryAfter(customerId, position) {
  const deadline = Date.now() + 2000;
  while (readCheckpoint() < position) {
    if (Date.now() > deadline) {
      throw new Error('The read model is behind. Try again.');
    }
    await new Promise((resolve) => setTimeout(resolve, 20));
  }
  return getCustomerSummary(customerId);
}

The demo places an order and reads the summary twice:

const projector = setInterval(project, 100);

const { position } = placeOrder({
  orderId: 'o-1001',
  customerId: 'c-42',
  lines: [
    { sku: 'book', quantity: 2, unitPriceCents: 1500 },
    { sku: 'pen', quantity: 1, unitPriceCents: 250 },
  ],
});

// Prints: null (the projector
// has not run yet)
console.log(getCustomerSummary('c-42'));

// Prints: { customerId: 'c-42',
//   orderCount: 1,
//   totalSpentCents: 3250 }
console.log(await getCustomerSummaryAfter('c-42', position));

clearInterval(projector);

The first read returns null because the projector has not run yet. That is eventual consistency, lasting about a tenth of a second here. After a change of their own, users expect what Werner Vogels of Amazon calls read-your-writes consistency. Under it, a process, “after it has updated a data item, always accesses the updated value and will never see an older value.”

The position that placeOrder returns provides that guarantee, because the second read waits until the projector reaches it. Returning a position is a deliberate exception to command-query separation, much like Fowler’s pop. In a web application, the client keeps the highest position its completed writes have returned and sends it with later reads. The server then waits on the checkpoint of the same read store that answers. Two other ways hide the lag instead: show the change in the client at once, or send users to a confirmation page that needs no read model.

The claim makes this projector idempotent. Because it commits with the summary, a restarted projector, or a second one during a rolling deployment, never counts an event twice. That works only because this read store is transactional; a search index or a cache usually is not. There, keep the last applied position inside each record, and update the record and its position in one conditional write that succeeds only for a newer position. Deliver each record’s events in order, too, or that check will refuse a late one.

The checkpoint also assumes that positions commit in order. Here they do, because SQLite allows only one writer at a time. With concurrent writers, one transaction can commit position 8 before another commits position 7. A projector that reads in between applies 8, moves its checkpoint past 7, and never reads 7. Richardson lists “tricky to publish events in order” among the drawbacks of polling and offers tailing the database transaction log instead. With concurrent writers, do that and checkpoint in log order, or use an outbox library that handles the gap.

In production, alert on two numbers: the backlog, meaning the events after the checkpoint, and the age of the oldest of them, which is the lag users feel. Measuring that age needs a timestamp column in the outbox. Keep a way to rebuild each read model, too, from a source that still has the facts, such as retained events or the write tables.

When CQRS Pays

CQRS is not free, and its early writers disagree about the price. Young argued in 2010 that, compared with a layered domain-driven design that already uses commands, CQRS combined with event sourcing “is actually less expensive in most cases!” Fowler, writing in 2011, had seen successful uses but found that “the majority of cases I’ve run into have not been so good.” Dahan answered his own question of when to avoid CQRS: “The answer is most of the time.”

With Microsoft’s guide, they point to five signals:

On the other side, Microsoft says the pattern might not suit a simple domain or simple business rules, or a case where CRUD-style screens and data access are enough. Fowler agrees that many systems “do fit a CRUD mental model, and so should be done in that style.” He also counts the cost in people: CQRS is “a significant mental leap for all concerned.”

If demanding queries are the only problem, Fowler points to something narrower: a separate reporting database that takes only those queries and can be refreshed overnight. And where CQRS does fit, it “should only be used on specific portions of a system (a BoundedContext in DDD lingo) and not the system as a whole.”

Event Sourcing Is a Separate Decision

Event sourcing stores the system’s state as a series of events and replays them to get the current state. Young calls its relationship with CQRS symbiotic. In his design, CQRS leaves the domain no query but GetById, which an event store can answer. The stored events are both the write model and the feed for the read models. But CQRS does not require event sourcing. Microsoft says only that some CQRS implementations incorporate it, and Dahan calls it “another possible implementation” of the command side.

It has costs of its own. Microsoft’s guide warns that CQRS can add significant complexity, especially when combined with event sourcing. So decide on it with the people who pay for it. Dahan called an event log that the business never asked for “architectural gold-plating.” Choose event sourcing when the history is a requirement in its own right, not as a side effect of choosing CQRS.

Checklist

Further Reading