Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

Database per Service

One service is the only reader and writer of its tables: what that buys, what it takes away (the cross-service join, the cross-service transaction, shared reference data), how each comes back through API composition, read models, sagas and copies kept by events, and how far to isolate them, from private tables to a server per service.

Can Orders still run the join?

A concert-ticketing company is splitting its monolith into three services: Shows (artists, venues, dates and prices), Seating (each venue's seat map, and which seats are held or sold) and Orders (who bought which tickets, and for how much). Whether to split at all is the question Microservices vs monolith weighs; this team has decided. In the design review, someone asks what every split runs into: the "My tickets" page is one SQL query today, joining orders, tickets, seats and shows. Afterwards, can Orders still run it?

Under Database per Service, no. Each service is the only one that reads or writes its tables. Orders cannot select from shows, even when both sets of tables happen to sit on the same PostgreSQL server. To learn a show's date it asks the Shows service through its API, or reads a copy of its own that Shows' events keep current.

What the rule buys

Shows starts selling festivals, so a show's single starts_at column becomes one row per day in a new show_dates table. With its tables private, that is Shows' change alone: its API keeps answering GET /shows/{id} with a start date, now the first day, the team migrates and deploys on its own schedule, and Orders never hears about it. Had Orders joined shows directly, Shows' migration would have had to keep that query working or be planned with the Orders team, the coupling that Shared Database follows to its end.

Privacy also frees the choice of store. No other service depends on how Seating keeps its data, so it can hold seats for ten minutes in a key-value store with expiring keys, while Orders keeps relational tables for refunds and accounting.

A join becomes calls, or a copy

The first way back for "My tickets" is API composition: one component, Orders itself or a gateway in front of it, fetches the fan's orders, asks Seating for the seat labels and Shows for the names and dates, and joins the answers in memory. The data comes from each owner at request time, though the three answers are not one snapshot. The cost is paid on every page view: the page waits for Orders and then for the slower of Seating and Shows, and when one is down it fails or shows gaps. Composed in a loop, one call to Shows per ticket, it also repeats the one-query-per-row mistake over the network, which N+1 queries: lazy loading, joins & batching takes apart.

The second way is a replicated read model: Orders subscribes to the events Shows and Seating publish, such as ShowRescheduled and SeatAssigned, and keeps a local table shaped like the page, so "My tickets" is one query against Orders' own store again. Separating that read side from the write side is CQRS; keeping such a table current, and rebuilding it when it drifts, is what Materialized View explains. The copy is behind the source by however long events take to be published, delivered and applied, and further behind whenever a consumer falls behind. A fan who opens the page just after a reschedule can see the old date.

Shows owns show data

Only the Shows service writes show data. Orders reads it through one batch call, or from its own copy, which Shows' events keep current.

A transaction becomes a Saga

Checkout used to be one database transaction: mark seat 14F sold, insert the order, commit, both or neither. Now the seat lives in Seating's store and the order in Orders', and no local transaction reaches both. Two-phase commit could bind them, but it holds the seat lock until the coordinator's decision arrives, leaves participants waiting when the coordinator fails at the wrong moment, and needs both stores to support the protocol.

The usual answer is a chain of local transactions, with a compensating step that releases the seat hold if the payment fails: a Saga, where the ordering of the steps and what other requests see in between are worked out. Each step changes its own database and then tells the next service, two writes that a crash can separate, which Transactional Outbox closes by writing the message in the same local transaction.

Reference data travels as copies

Orders prints the show's name, venue and date on every ticket, and calling Shows each time would stop tickets going out whenever Shows is down. So Orders keeps its own copy of the few show fields it uses, written only by the code that applies Shows' events, and rebuilt from those events, when the broker still keeps them, or from a full export by Shows.

Copying raises a question the single database never asked: which fields should follow the source, and which are facts of the order? The date should follow, because the fan needs the new one. The price should not. When Shows raises the remaining seats from €45 to €60, an order placed at €45 still says €45, because Orders recorded the price paid as part of the order at checkout and never updates it from events.

How private is private?

The rule can be enforced at three levels, each buying more isolation for more to run:

  • Private tables per service. One database server, a login per service, and grants that stop Orders' login from reading shows. The cheapest start, but the services still share the server's CPU, memory and connections, and an engine upgrade is everyone's upgrade.
  • A schema per service. The same server, with each service's tables in a schema of its own: ownership shows in every table's name, and the database's permissions can keep other services out of the whole schema, tables added later included. The resources are just as shared.
  • A server per service. Each service's store runs on a server of its own, sized, upgraded, backed up and failed over on its own, with a different engine if it needs one, and there are three servers to run.

Whatever the level, the common mistake is the same: letting one service reach across and query another's tables directly "just this once", for a report on a deadline. That one exception recreates the coupling the split was meant to remove, and the separate logins and grants at every level are there so that exception cannot happen quietly.

Which reads may be stale?

Every read that crosses a boundary now carries a decision the join used to hide. The seat a fan is about to pay for must not be wrong, so checkout has Seating check and hold it in one step; an order history that may lag by a few seconds reads Orders' copy. The Database per Service Challenge, which asks for private stores shared only through APIs and events in at most 8 nodes, is where to make those decisions for the ticketing split: give Shows, Seating and Orders a store each, choose a store type for Seating's ten-minute holds, and decide for "My tickets" whether Orders calls Shows or keeps a copy fed by its events, and how old that page may be. The palette also offers Shared Database and Two-Phase Commit nodes; a design that meets the brief uses neither.

Practice this