03 - CQRS: Separate Read and Write for Independent Scaling

When the same model has to do two opposite jobs

A classic API uses a single model to write and to read. It works as long as the two needs coincide, then they diverge. The writer wants strict rules, a normalized model and transactions; the reader wants a row already prepared for the “My orders” screen, with the customer name, the total and the number of items, without three JOINs on every request. If you use the same model for both you get the slow queries of one or the bloated tables of the other.

CQRS (Command Query Responsibility Segregation) separates the two jobs into two models: the command side changes the state and applies the rules, the query side reads from a shape designed for the reader. A synchronization mechanism, usually asynchronous, keeps the two aligned. It is a cost decision: Martin Fowler warns that CQRS is a significant mental leap and that it should not be tackled if the benefit does not justify it. This article builds the complete mechanism in miniature, with the write side, the read view, the projection that aligns them and the behaviour the user sees in the interval in which they are not aligned.

Command and query: the rule that comes from Meyer

CQRS extends an older principle, Bertrand Meyer's command-query separation: an operation either changes the state or returns a datum, not both. A reserveInventory method that reserves the stock and returns the updated state mixes the two roads; the command should only reserve, and whoever wants the stock level asks for it with a query.

Fowler points out that Meyer applies the rule in an absolute way but admits exceptions, such as popping an element off a stack (a read that modifies). In an asynchronous system the separation stops being a matter of style: after the command the read view may not be updated yet, so returning a state from it as the answer to the command would be wrong.

The complete system in a single program

The program uses in-memory SQLite and a queue in place of a broker. The tables orders, order_items and customers are the write side, normalized; order_list_view is the read side, with one row per order. In production the view would typically live in a separate database or service, here it sits in the same file for simplicity. There is no event sourcing: the write side saves the state, and the projection rereads it. It is the simplest way to do CQRS, and it is enough to show all the behaviours that matter.

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  -- Write side: normalized, it is the source of truth.
  CREATE TABLE customers (id TEXT PRIMARY KEY, full_name TEXT NOT NULL);
  CREATE TABLE orders (id TEXT PRIMARY KEY, customer_id TEXT NOT NULL, status TEXT NOT NULL, version INTEGER NOT NULL);
  CREATE TABLE order_items (order_id TEXT NOT NULL, product_id TEXT NOT NULL, quantity INTEGER NOT NULL, unit_price REAL NOT NULL);
  -- Read side: one row for the "My orders" screen, no JOIN at query time.
  CREATE TABLE order_list_view (order_id TEXT PRIMARY KEY, customer_id TEXT NOT NULL, customer_name TEXT NOT NULL,
                                total REAL NOT NULL, item_count INTEGER NOT NULL, status TEXT NOT NULL, version INTEGER NOT NULL);
`);
db.prepare('INSERT INTO customers VALUES (?, ?)').run('cli-3', 'Maria Rossi');

type DomainEvent = { type: 'OrderPlaced' | 'OrderCancelled' | 'CustomerRenamed'; orderId?: string; customerId?: string };
const queue: DomainEvent[] = []; // in place of a broker: the queue between the two sides

// ---- Command side: validates, writes, announces. It returns no read data.
function placeOrder(orderId: string, customerId: string, items: { productId: string; quantity: number; unitPrice: number }[]): number {
  if (items.length === 0) throw new Error('Order without items');
  if (items.some((i) => i.quantity <= 0)) throw new Error('Invalid quantity');
  db.exec('BEGIN');
  db.prepare('INSERT INTO orders VALUES (?, ?, ?, ?)').run(orderId, customerId, 'Confirmed', 1);
  for (const i of items) db.prepare('INSERT INTO order_items VALUES (?, ?, ?, ?)').run(orderId, i.productId, i.quantity, i.unitPrice);
  db.exec('COMMIT');
  queue.push({ type: 'OrderPlaced', orderId });
  return 1; // the version produced: the client will use it to know when the read side is up to date
}
function cancelOrder(orderId: string): number {
  db.prepare("UPDATE orders SET status = 'Cancelled', version = version + 1 WHERE id = ?").run(orderId);
  queue.push({ type: 'OrderCancelled', orderId });
  return (db.prepare('SELECT version FROM orders WHERE id = ?').get(orderId) as { version: number }).version;
}
function renameCustomer(customerId: string, fullName: string): void {
  db.prepare('UPDATE customers SET full_name = ? WHERE id = ?').run(fullName, customerId);
  queue.push({ type: 'CustomerRenamed', customerId });
}

// ---- Projection: one event at a time, rewrites the view starting from the write side.
function tick(): boolean {
  const e = queue.shift();
  if (!e) return false;
  if (e.type === 'OrderPlaced' || e.type === 'OrderCancelled') {
    db.prepare(`INSERT OR REPLACE INTO order_list_view
      SELECT o.id, o.customer_id, c.full_name, SUM(i.quantity * i.unit_price), COUNT(*), o.status, o.version
      FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items i ON i.order_id = o.id
      WHERE o.id = ? GROUP BY o.id`).run(e.orderId!);
  } else {
    db.prepare('UPDATE order_list_view SET customer_name = (SELECT full_name FROM customers WHERE id = ?) WHERE customer_id = ?')
      .run(e.customerId!, e.customerId!);
  }
  return true;
}

// ---- Query side: only SELECTs on the view.
const lista = (customerId: string) =>
  db.prepare('SELECT order_id, customer_name, total, item_count, status, version FROM order_list_view WHERE customer_id = ?').all(customerId);

// The client knows the version that the command produced and waits for the view to reach it.
// Here the wait is "run the projection once"; in production it is a real pause.
function aspettaVersione(orderId: string, version: number, maxTentativi = 10): number {
  for (let t = 1; t <= maxTentativi; t++) {
    const r = db.prepare('SELECT version FROM order_list_view WHERE order_id = ?').get(orderId) as { version: number } | undefined;
    if (r && r.version >= version) return t;
    tick();
  }
  throw new Error('View not up to date within the limit');
}

const v1 = placeOrder('o-1', 'cli-3', [{ productId: 'pen', quantity: 2, unitPrice: 3.5 }, { productId: 'notebook', quantity: 1, unitPrice: 4 }]);
console.log('right after the command   :', JSON.stringify(lista('cli-3')));
console.log('attempts to see v' + v1 + '        :', aspettaVersione('o-1', v1));
console.log('after the projection      :', JSON.stringify(lista('cli-3')));

renameCustomer('cli-3', 'Maria Bianchi');
console.log('name after rename (view)  :', (lista('cli-3')[0] as { customer_name: string }).customer_name);
tick();
console.log('name after the projection :', (lista('cli-3')[0] as { customer_name: string }).customer_name);

const v2 = cancelOrder('o-1');
console.log('attempts to see v' + v2 + '        :', aspettaVersione('o-1', v2));
console.log('final status              :', (lista('cli-3')[0] as { status: string }).status);

try { placeOrder('o-2', 'cli-3', [{ productId: 'pen', quantity: 0, unitPrice: 3.5 }]); } catch (e) { console.log('refused:', (e as Error).message, '| events in queue:', queue.length); }

// Output:
// right after the command   : []
// attempts to see v1        : 2
// after the projection      : [{"order_id":"o-1","customer_name":"Maria Rossi","total":11,"item_count":2,"status":"Confirmed","version":1}]
// name after rename (view)  : Maria Rossi
// name after the projection : Maria Bianchi
// attempts to see v2        : 2
// final status              : Cancelled
// refused: Invalid quantity | events in queue: 0

Three things to look at in the output. Right after placeOrder the view is empty: the command queued an event, but nobody has projected it yet. This is the period of inconsistency that asynchronous CQRS introduces, and it is not a malfunction. The aspettaVersione function is the simplest answer: the command returns the version it produced (1), and the client waits for the view to reach that version; here the wait is tick(), that is, letting the projection work, while in production it is a real pause with a limit on the number of attempts.

The second thing: after renameCustomer the name in the view stays the old one until the event is projected, because denormalization copies the data and lets it go stale. Third: the request with quantity zero is refused by the command side and puts no event in the queue, so validation sits before the write, not in the view.

A difficulty that the program hides: placeOrder writes to the database and then puts the event in the queue. If the process stops between the two lines, the order exists and the view will never know about it. On a real broker this is the dual write that the outbox pattern solves, and it has to be solved before putting CQRS in production. Another: the projection rereads the state from the write side instead of trusting the event, which is simpler but couples it to the command database; with an event rich in data you can avoid this, at the price of bigger events, as you see in the comparison on the shape of events.

Read view: table, materialized view, cache

The read model does not have to be a separate database. There are three forms, in increasing order of detachment from the write side:

• a materialized view in the same database, which requires a REFRESH and has no messages in between;
• a read table maintained by a projection, as in the program above, in the same database or in a different one;
• a dedicated store, such as a cache or a search engine, fed by the events.

Order them by how much they cost to run: the first is almost free, the third requires a component to manage and to realign in case of error. A caution about the first: the PostgreSQL documentation says that REFRESH MATERIALIZED VIEW completely replaces the contents of the view, and that with the CONCURRENTLY option it is updated without blocking concurrent reads.

There is no automatic incremental refresh in the command: an article that promises one describes something different from what the command does. For orders of magnitude (“how many times faster”) there is no universal figure: it depends on the queries, the data and the indexes, and it has to be measured on your load.

Scaling the two parts separately

The gain promised by CQRS is scaling the two sides independently. In a shop reads are many times the writes: you can give the command side a few instances with a primary database, and the query side many stateless instances that read from replicas or from a cache. The separation allows you to do so without changing the model of the other part.

But it is not the reason to adopt it from the start: if you have never had a read bottleneck, a read-only replica and one more index cost far less than two models to synchronize. Use it when the two models are really different, not when they are equal but slow.

When CQRS is a bad idea

The cost is concrete: more components to deploy (command side, query side, projection, read database), eventual consistency to explain to the interface, and validations that sometimes have to appear in two places. The signal not to do it is a CRUD domain with no complex business rules, where a single well-indexed model is enough. Fowler indicates that CQRS suits complex domains, those that also benefit from Domain-Driven Design, and the Azure Architecture Center warns that synchronization leads to eventual consistency.

• the read views look very much like the write model: you do not need it;
• the interface does not tolerate a delay, not even a short one: you need an alternative or the version wait;
• the team cannot manage one more component: start from a materialized view.

These are three questions to ask yourself in this order. If the first answer is yes, the discussion ends there. If the second is yes, you can still proceed with the version wait shown above or with an optimistic update of the interface (you show the expected result right away and reconcile it when the view is ready). The third is the most underestimated: the operational cost of a read database to realign after an error can exceed that of writing the code.

What you can do now

You can split a system into a command side and a query side, build a read view with a projection and recognize the two symptoms of eventual consistency: the empty view right after a command and the copied datum that goes stale. You can handle the delay by waiting for the version produced by the command. To use events as the source of truth of the write side, the next step is Event Sourcing + CQRS; for the user experience when the view is late, the article on eventual consistency. Whoever arrives from the events can restart from Event Sourcing.