Nov 18, 20269 min read

CQRS Pattern: Separating Reads and Writes in a Backend

What Command Query Responsibility Segregation is, when a separate read model pays for itself, how to keep it in sync with events, and how to handle eventual consistency, redelivery and out-of-order updates.

Illustration for CQRS Pattern: Separating Reads and Writes in a Backend

CQRS, Command Query Responsibility Segregation, separates the code that changes state from the code that reads it. Commands go through a write model that validates input and enforces invariants. Queries read from a read model built for the way data is displayed, often denormalized into exactly the rows a screen needs. Martin Fowler's description puts the idea in one line: use a different model to update information than the model you use to read it.

Splitting the code is straightforward compared with keeping the read model in step: how changes reach it, what happens when an event is delivered twice or out of order, how far behind it may fall, and what a user sees right after they make a change. Those choices decide whether CQRS removes load from your write path or adds a class of bugs.

This guide builds an order system with a write service and a reporting read model fed by events in Encore, and then shows a single-database version with a materialized view.

CQRS designs at a glance

Separate read store fed by events (Encore example)Materialized view in the same databaseOne model for reads and writes
Read modelIts own table or database, updated per eventView refreshed from the write tablesThe write tables
LagEvent delivery time, usually sub-secondUntil the next refreshNone
Write path costPublish an eventNoneNone
Refresh costOne upsert per changeRecomputes the whole viewNone
Scales reads independentlyYesPartly, same databaseNo
FitsRead models owned by another service or storeReporting on one databaseCRUD over a few tables

How do you implement CQRS with Encore?

Encore

Encore is a backend framework with built-in infrastructure, and the pieces of event-fed CQRS map onto its primitives. The write side and the read side are separate services, each with its own database. The change event is a Pub/Sub topic, and the projection is a subscription. encore run runs both databases and delivers events locally, and a deploy provisions them in your own AWS or GCP account.

Application code passes through Encore and infrastructure guardrails to resources provisioned in your own AWS or GCP account.

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

~
$ encore app create shop --example=hello-world$ cd shop && mkdir -p orders/migrations reporting/migrations

The write model

The write side is normalized, with constraints that enforce the rules. Each order carries a version that increases with every change:

CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'placed', version INTEGER NOT NULL DEFAULT 1, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE order_lines ( order_id BIGINT NOT NULL REFERENCES orders (id), sku TEXT NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), price_cents INTEGER NOT NULL CHECK (price_cents >= 0), PRIMARY KEY (order_id, sku) ); CREATE TABLE outbox ( id BIGSERIAL PRIMARY KEY, topic TEXT NOT NULL, data JSONB NOT NULL, inserted_at TIMESTAMPTZ NOT NULL ); CREATE INDEX outbox_topic_idx ON outbox (topic, id);

Commands validate, write in a transaction and publish one event with the full state the read side needs, so the projection never has to call back into the orders service:

// Service orders is the write side: it validates commands and owns the order tables. package orders import ( "context" "errors" "encore.dev/beta/auth" "encore.dev/beta/errs" "encore.dev/pubsub" "encore.dev/storage/sqldb" "x.encore.dev/infra/pubsub/outbox" ) var db = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{Migrations: "./migrations"}) // OrderChanged carries the full state the read side needs, plus a version. type OrderChanged struct { OrderID int64 `json:"order_id"` CustomerID string `json:"customer_id"` Status string `json:"status"` ItemCount int `json:"item_count"` TotalCents int64 `json:"total_cents"` Version int `json:"version"` } var OrderEvents = pubsub.NewTopic[*OrderChanged]("order-changed", pubsub.TopicConfig{ DeliveryGuarantee: pubsub.AtLeastOnce, }) func init() { relay := outbox.NewRelay(outbox.SQLDBStore(db)) outbox.RegisterTopic(relay, pubsub.TopicRef[pubsub.Publisher[*OrderChanged]](OrderEvents)) go relay.PollForMessage(context.Background(), -1) } type Line struct { SKU string `json:"sku"` Quantity int `json:"quantity"` PriceCents int `json:"price_cents"` } type PlaceOrder struct { Lines []Line `json:"lines"` } type PlaceResult struct { OrderID int64 `json:"order_id"` } // Place is a command: it validates, writes in one transaction, then announces the change. // //encore:api auth method=POST path=/orders func Place(ctx context.Context, cmd *PlaceOrder) (*PlaceResult, error) { customer, _ := auth.UserID() if len(cmd.Lines) == 0 { return nil, &errs.Error{Code: errs.InvalidArgument, Message: "an order needs at least one line"} } tx, err := db.Begin(ctx) if err != nil { return nil, err } defer tx.Rollback() var id int64 if err := tx.QueryRow(ctx, `INSERT INTO orders (customer_id) VALUES ($1) RETURNING id`, string(customer)).Scan(&id); err != nil { return nil, err } ev := &OrderChanged{OrderID: id, CustomerID: string(customer), Status: "placed", Version: 1} for _, l := range cmd.Lines { if _, err := tx.Exec(ctx, `INSERT INTO order_lines (order_id, sku, quantity, price_cents) VALUES ($1, $2, $3, $4)`, id, l.SKU, l.Quantity, l.PriceCents); err != nil { return nil, err } ev.ItemCount += l.Quantity ev.TotalCents += int64(l.Quantity * l.PriceCents) } ref := outbox.Bind( pubsub.TopicRef[pubsub.Publisher[*OrderChanged]](OrderEvents), outbox.TxPersister(tx), ) if _, err := ref.Publish(ctx, ev); err != nil { return nil, err } if err := tx.Commit(); err != nil { return nil, err } return &PlaceResult{OrderID: id}, nil } type CancelOrder struct { OrderID int64 `json:"order_id"` } //encore:api auth method=POST path=/orders/cancel func Cancel(ctx context.Context, cmd *CancelOrder) error { customer, _ := auth.UserID() tx, err := db.Begin(ctx) if err != nil { return err } defer tx.Rollback() ev := &OrderChanged{OrderID: cmd.OrderID} err = tx.QueryRow(ctx, ` UPDATE orders SET status = 'cancelled', version = version + 1 WHERE id = $1 AND status = 'placed' AND customer_id = $2 RETURNING customer_id, status, version, (SELECT COALESCE(SUM(quantity), 0) FROM order_lines WHERE order_id = $1), (SELECT COALESCE(SUM(quantity * price_cents), 0) FROM order_lines WHERE order_id = $1)`, cmd.OrderID, string(customer)).Scan(&ev.CustomerID, &ev.Status, &ev.Version, &ev.ItemCount, &ev.TotalCents) if err != nil { if errors.Is(err, sqldb.ErrNoRows) { return &errs.Error{Code: errs.FailedPrecondition, Message: "order is not open"} } return err } ref := outbox.Bind( pubsub.TopicRef[pubsub.Publisher[*OrderChanged]](OrderEvents), outbox.TxPersister(tx), ) if _, err := ref.Publish(ctx, ev); err != nil { return err } return tx.Commit() }

The read model

The reporting service stores one denormalized row per order, indexed for the customer's order list:

-- Denormalized for the queries the dashboard runs; no joins at read time. CREATE TABLE order_summaries ( order_id BIGINT PRIMARY KEY, customer_id TEXT NOT NULL, status TEXT NOT NULL, item_count INTEGER NOT NULL, total_cents BIGINT NOT NULL, version INTEGER NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX order_summaries_customer_idx ON order_summaries (customer_id, order_id DESC);

The projection is an upsert guarded by the version. A redelivered event matches the stored version and changes nothing, and an older event that arrives late cannot overwrite a newer state:

// Service reporting is the read side: a projection built from order events. package reporting import ( "context" "encore.app/orders" "encore.dev/beta/auth" "encore.dev/pubsub" "encore.dev/storage/sqldb" ) var db = sqldb.NewDatabase("reporting", sqldb.DatabaseConfig{Migrations: "./migrations"}) var _ = pubsub.NewSubscription(orders.OrderEvents, "project-order-summaries", pubsub.SubscriptionConfig[*orders.OrderChanged]{Handler: project}) // project upserts the summary. The version check makes redelivered and // out-of-order events harmless: an older version never overwrites a newer one. func project(ctx context.Context, e *orders.OrderChanged) error { _, err := db.Exec(ctx, ` INSERT INTO order_summaries (order_id, customer_id, status, item_count, total_cents, version) VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT (order_id) DO UPDATE SET status = EXCLUDED.status, item_count = EXCLUDED.item_count, total_cents = EXCLUDED.total_cents, version = EXCLUDED.version, updated_at = now() WHERE order_summaries.version < EXCLUDED.version`, e.OrderID, e.CustomerID, e.Status, e.ItemCount, e.TotalCents, e.Version) return err } type Summary struct { OrderID int64 `json:"order_id"` Status string `json:"status"` ItemCount int `json:"item_count"` TotalCents int64 `json:"total_cents"` } type CustomerOrders struct { Orders []*Summary `json:"orders"` } // CustomerOrders is a query: it only reads the projection. // //encore:api auth method=GET path=/orders func ListCustomerOrders(ctx context.Context) (*CustomerOrders, error) { customer, _ := auth.UserID() rows, err := db.Query(ctx, ` SELECT order_id, status, item_count, total_cents FROM order_summaries WHERE customer_id = $1 ORDER BY order_id DESC LIMIT 50`, string(customer)) if err != nil { return nil, err } defer rows.Close() out := &CustomerOrders{} for rows.Next() { s := &Summary{} if err := rows.Scan(&s.OrderID, &s.Status, &s.ItemCount, &s.TotalCents); err != nil { return nil, err } out.Orders = append(out.Orders, s) } return out, rows.Err() }

encore check compiled this app. Our build machine has no Docker, which the local databases need, so we ran the SQL on Postgres 17: an order with two lines produced a summary of three items and 1250 cents; cancelling it returned version 2 and a second cancel matched no row; and applying version 2, then version 1, then version 2 again left the summary at cancelled, version 2.

Place and Cancel write the event to the transactional outbox in the same transaction as the order row, and a relay publishes it after the commit. Place returns the new order ID directly, so the client never has to read its own write from the lagging read model.

Best fit: Systems where a separate team, service or store owns the read side, such as dashboards, search indexes or customer-facing order history, deployed to your own AWS or GCP account.

How do you implement CQRS in a single Postgres database?

When the read side is reporting on the same data, a separate service and broker can be more than you need. A materialized view over the write tables is a read model with no events at all:

-- Read model as a materialized view over the write tables. CREATE MATERIALIZED VIEW order_summaries AS SELECT o.id AS order_id, o.customer_id, o.status, COALESCE(SUM(l.quantity), 0)::int AS item_count, COALESCE(SUM(l.quantity * l.price_cents), 0)::bigint AS total_cents FROM orders o LEFT JOIN order_lines l ON l.order_id = o.id GROUP BY o.id; -- CONCURRENTLY needs a unique index, and lets readers keep querying during a refresh. CREATE UNIQUE INDEX order_summaries_pk ON order_summaries (order_id); CREATE INDEX order_summaries_customer_idx ON order_summaries (customer_id, order_id DESC);

Refresh it on a schedule with REFRESH MATERIALIZED VIEW CONCURRENTLY, which keeps the view readable during the refresh and requires the unique index. In our run on Postgres 17, a cancelled order still showed placed until the refresh, and cancelled after it.

The trade is that every refresh recomputes the whole view, so the cost grows with the table, not with the rate of change, and the lag is the refresh interval. Past a few million rows or a need for second-level freshness, a summary table maintained per change, by triggers or by the event approach above, costs less.

Best fit: Reporting and list screens over one database, where a lag of minutes is acceptable.

How to choose

Start with one model, and introduce CQRS for the specific queries whose shape or load fights the write model. Use a materialized view when the read model lives in the same database and minutes of lag are fine. Use an event-fed projection with versioned upserts when the read side belongs to another service or store or needs to be close to real time, and decide early whether you need the outbox. Pick Encore when you want the services, their databases and the topic between them declared in Go or TypeScript and provisioned in your own AWS or GCP account.

Frequently asked questions

What is CQRS?

Command Query Responsibility Segregation splits an application's model in two: commands change state through a write model that enforces the business rules, and queries read from a separate read model shaped for the screens and reports that use it. The two can share a database or live in different stores, kept in sync by events or a refresh.

When should you use CQRS?

When reads and writes want different shapes or different scaling, for example a normalized order model that must validate every change and a dashboard that needs pre-joined, pre-aggregated rows. For a CRUD screen over one table it adds code and eventual consistency without a benefit.

Does CQRS require event sourcing?

No. Event sourcing stores state as a log of events, and it pairs well with CQRS because the log feeds projections. CQRS works equally well with an ordinary relational write model that publishes change events or with a read model refreshed from the write tables.

How do you handle eventual consistency in CQRS?

Accept that the read model lags the write model by the time it takes to deliver and apply an event, and design for it: return the result of a command directly instead of re-querying the read model, version each update so stale events cannot overwrite newer ones, and show users their own writes from the command response.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders