Data Ownership and Database per Service: Why Avoid a Shared Database?

Version note: The code in this post was built and run with C# 14 and .NET 10.

Guess First

Catalog and Ordering have been split into separate services at Bookstore, but both still use the same SQL (Structured Query Language) Server database. When Ordering creates an order line, it reads the book's title from the catalog.Books table; the thinking was "I'm only reading, not writing, what harm can it do". The monthly sales report also joins catalog.Books with ordering.Orders.

So that book titles can include the subtitle, the Catalog team renames the Title column to DisplayTitle and deploys only its own service. Who breaks? When is it noticed: at deployment, when the nightly report runs, or when a customer gets an invoice with a blank title? And how could the Catalog team have known who reads its table?

Shared Database: Hidden Coupling

In the previous post we saw common coupling among Sam Newman's types of coupling: several services sharing the same data. A shared database is its most common form and the sneakiest, because it shows up in no contract. You can see who calls Catalog's HTTP (Hypertext Transfer Protocol) API (Application Programming Interface) from logs, contract tests or the gateway; who reads its table you usually learn at the first breakage. The table structure becomes an API with no version, no documentation, and nobody who ever declared it "public".

That is why Chris Richardson's Database per Service pattern on microservices.io states the rule plainly: a service's persistent data is private to it and accessible only through its API. Shared Database stands as a separate anti-pattern entry describing the problems that come from microservices sharing a database. The benefit is clear: a change to one service's database doesn't affect the others, and each service can choose the database that suits it best (Elasticsearch for search, SQL Server for relational data). The cost is clear too: business transactions spanning several services, and joins across services, become hard.

A shared database also has a timing side. If two services write to the same tables, a long-running transaction in one turns into lock waits for the other; a bad query in one raises latency for everyone on the same database server. Separate processes and separate deployment pipelines get coupled again once they meet on the same database server.

Private Tables, Schema or Database

The rule "each service's data is private" doesn't mean setting up a separate database server for every service. Richardson lists three levels. Private-tables-per-service: each service has its own tables and only that service may access them; the cheapest option. Schema-per-service: each service has a schema of its own; ownership is more visible. Database-server-per-service: each service has its own database server; for high-traffic services.

Whatever level you choose, don't leave the rule to convention. Writing "nobody reads another schema" on the wiki won't stop a shortcut during campaign week. In SQL Server, giving each service its own database user and defining permissions at the schema level means handing the rule to the database itself:

CREATE SCHEMA catalog;
GO
CREATE SCHEMA ordering;
GO
CREATE TABLE catalog.Books (Id uniqueidentifier PRIMARY KEY, Isbn char(14) NOT NULL, Title nvarchar(200) NOT NULL, ListPrice decimal(10,2) NOT NULL);
CREATE TABLE ordering.Orders (Id uniqueidentifier PRIMARY KEY, CustomerId uniqueidentifier NOT NULL, Status varchar(20) NOT NULL);
CREATE TABLE ordering.BookReplicas (BookId uniqueidentifier PRIMARY KEY, Title nvarchar(200) NOT NULL, Version bigint NOT NULL);
GO
/* Each service has its own database user, with permissions only on its own schema */
CREATE USER catalog_svc WITHOUT LOGIN;
CREATE USER ordering_svc WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::catalog TO catalog_svc;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::ordering TO ordering_svc;
GO

Now let's run two queries as Ordering: one against its own table and one that is the "I'm only reading" shortcut from the opening:

USE ms_04;
EXECUTE AS USER = 'ordering_svc';
SELECT COUNT(*) AS Orders FROM ordering.Orders;
/* The "I'm only reading" shortcut */
SELECT Title, ListPrice FROM catalog.Books WHERE Isbn = '978-0441013593';
REVERT;

Orders     
-----------
          0

(1 rows affected)
Msg 229, Level 14, State 5, Server b8be943222fa, Line 5
The SELECT permission was denied on the object 'Books', database 'ms_04', schema 'catalog'.

Ordering can read its own table but not Catalog's. The shortcut from the opening is no longer stopped by a code review comment but by error Msg 229 on the first try. In a real environment these users are not WITHOUT LOGIN; they map to each service's own login, or in the cloud to its managed identity. But the principle is the same: the identity in the connection string can't see more than the data the service owns. It's the same technique the planned Modular Monolith series will apply to modules; the difference is that in microservices this boundary also coincides with a deployment boundary.

.NET's reference application dotnet/eShop shows a pragmatic version of this decision. Its Aspire AppHost defines a single PostgreSQL server and creates separate databases on it named catalogdb, orderingdb, identitydb and webhooksdb; the Catalog API references only catalogdb, the Ordering API only orderingdb. There is a notable detail too: the background OrderProcessor is also connected to orderingdb. That isn't a violation of the rule; both are deployables of the same bounded context and the same team. Ownership should be thought of per bounded context and team, not per deployable.

When You Need Another Service's Data

Once the data is private, a question follows at once: what does Ordering do when it needs a book's title? There are three ways, each with a different price.

API composition: ask the owner when you need it. While preparing the cart page, Ordering fetches book data from Catalog's API and combines it with its own data. In Richardson's description, the application or an API gateway does the combining. Simple and always fresh, but if Catalog isn't up there is no cart page either; with 40 books on a page you need either 40 calls or a batch endpoint. The right choice for data read rarely that must be fresh.

A local copy replicated through events: the owner publishes changes as events, and whoever needs the data keeps its own copy in its own database. Catalog publishes BookRenamed and BookPriceChanged; Ordering listens and updates its BookReplicas table. Ordering no longer goes to Catalog at all when an order is placed; Catalog deploying or crashing doesn't affect checkout. The price is eventual consistency: the copy may be stale for a while. In the Messaging and Event-Driven Architecture series post "Message Types: Commands, Events, and Queries; Event Notification and Event-Carried State Transfer", this approach was called event-carried state transfer.

A CQRS (Command Query Responsibility Segregation) read model: a separate view, designed only for reading, that combines data from several services. This is Richardson's second option: a projector subscribed to the services' events updates a materialized view. It suits screens that cut across many services, such as "the customer's order history with book titles and shipping status". How to build one is in the CQRS and Event Sourcing series post "A Separate Read Model: Materialized Views, Synchronization, and Living with Eventual Consistency".

Ordering's Copy of the Book: BookRenamed

Let's see the middle option in code. The event Catalog publishes carries a Version; every change to the book in Catalog increments that number:

namespace Bookstore.Catalog.Contracts;

// The event Catalog publishes: Version is the book's version in Catalog
public sealed record BookRenamed(Guid BookId, string Title, long Version);

using Bookstore.Catalog.Contracts;

namespace Bookstore.Ordering;

// The copy in Ordering's own database: only the properties Ordering needs
public sealed record BookReplica(Guid BookId, string Title, long Version);

// An order line keeps the title at order time as a snapshot
public sealed record OrderItem(Guid BookId, string Title, decimal UnitPrice, int Quantity);

public sealed class BookReplicaStore
{
    private readonly Dictionary<Guid, BookReplica> _books = [];

    public void Seed(BookReplica book) => _books[book.BookId] = book;

    public BookReplica Get(Guid bookId) => _books[bookId];

    // The copy must survive the same event twice or an old event arriving late
    public string Apply(BookRenamed e)
    {
        if (_books.TryGetValue(e.BookId, out BookReplica? current) && current.Version >= e.Version)
            return $"v{e.Version} ignored (replica already at v{current.Version})";

        _books[e.BookId] = new BookReplica(e.BookId, e.Title, e.Version);
        return $"v{e.Version} applied -> \"{e.Title}\"";
    }
}

Brokers usually deliver messages at least once: the same event can arrive twice, and the order of two events can get mixed up. So the handler that maintains the copy applies a version only if it is newer than its own:

CultureInfo.CurrentCulture = CultureInfo.InvariantCulture;
Guid duneId = Guid.Parse("0199f0c4-5c1e-7000-8000-000000000001");

BookReplicaStore replicas = new();
replicas.Seed(new BookReplica(duneId, "Dune", Version: 1));

// No call to Catalog when the order is placed: the title comes from the local copy
OrderItem item = new(duneId, replicas.Get(duneId).Title, 12.50m, 2);

// The broker delivers at least once: duplicates and out-of-order events are normal
BookRenamed[] delivered =
[
    new(duneId, "Dune (Deluxe Edition)", 2),
    new(duneId, "Dune (Deluxe Edition)", 2),
    new(duneId, "Dune: Deluxe Hardcover", 4),
    new(duneId, "Dune (Anniversary Edition)", 3)
];
foreach (BookRenamed e in delivered)
    Console.WriteLine($"BookRenamed {replicas.Apply(e)}");

Console.WriteLine($"Replica title    : {replicas.Get(duneId).Title}");
Console.WriteLine($"Placed order item: {item.Title} x{item.Quantity} @ {item.UnitPrice}");

BookRenamed v2 applied -> "Dune (Deluxe Edition)"
BookRenamed v2 ignored (replica already at v2)
BookRenamed v4 applied -> "Dune: Deluxe Hardcover"
BookRenamed v3 ignored (replica already at v4)
Replica title    : Dune: Deluxe Hardcover
Placed order item: Dune x2 @ 12.50

There are two things in the output. The first is the copy itself: the duplicate v2 and the late v3 were ignored, and the copy stayed at the newest title, v4. Without the version check, the copy would show the title of the last event to arrive, the older "Anniversary Edition". This is the replication counterpart of the technique in the Messaging series post "Idempotent Consumers: What Happens When the Same Message Arrives Twice?"; in a real database the same check is a single statement of the form UPDATE ... WHERE Version < @version.

The second is the order line. Ada's order was placed while the book was "Dune", and the title on the line stayed "Dune" after the copy changed. That isn't a bug but a deliberate design: OrderItem in the DDD (Domain-Driven Design) series keeps the title and price at order time as a snapshot. The invoice should show what the customer saw on the day the order was placed. The copy and the snapshot answer two different questions: the copy "what is this book called now?", the snapshot "what did the customer buy?". Mixing them up leads to old invoices quietly changing overnight.

Keeping a copy has its limits too. Ordering should keep only the properties it needs (title and price, not the description or the author's biography). The copy always has a single source: Ordering never changes the title in its copy; changes only come from Catalog. And the copy must be rebuildable from scratch: when a new service is added or the copy gets corrupted, it should be possible to refill it with a bulk read from Catalog.

Reference Data

The country list, currencies, book categories, VAT (Value Added Tax) rates: data many services need but that rarely changes is called reference data. Putting it in a shared table "because everyone uses it" brings the shared database back through the most innocent-looking door. In practice there are three ways. Truly static data (such as ISO (International Organization for Standardization) 4217 currency codes) can live in each service as an enum or configuration; it changes once a year and follows a standard. Data that has a business owner and changes (categories belong to Catalog, VAT rates to accounting rules) lives in the owning service and is distributed to the others with the copy approach above. For data that is queried often but has no clear owner, you first need to find an owner; ownerless reference data is the database counterpart of a shared "Common" library.

A Separate Path for Reporting and Analytics

The monthly sales report from the opening is where teams moving to database per service get stuck most often. The report wants to join Catalog and Ordering, and the two databases are now separate. The answer is not to let reporting connect to service databases but to give it its own data path. There are two common methods. CDC (Change Data Capture): reading changes from the database's transaction log and moving them into an analytics store. SQL Server's own CDC feature and tools like Debezium do this; it works without touching the service's code, but because it exposes the table structure to reporting it needs care when the schema changes. An event stream: collecting the integration events the services already publish in a data platform (a data warehouse or data lake). It is more stable because it is contracted and versioned, but it contains only what is published.

Either way, reporting queries put no load on operational databases, and the Catalog team renaming a column doesn't break the report; at worst a mapping in the CDC pipeline is updated. At Bookstore's scale this doesn't have to be a data warehouse project: a single reporting database fed from events every night is enough to start with.

Common Pitfalls

Saying "I'm only reading". Reading is a dependency too. When the service whose table you read changes it, you break, and nobody can know that in advance. A read need should be met through the API, a copy or a read model.

Separate databases, shared tables. Splitting the services but leaving common tables in a "shared" schema just gathers common coupling in one place. Every table should have a single owner.

Changing the copy independently of its owner. If Ordering "fixes" the title in its copy, two realities appear. The copy is a read-only projection; corrections are made at the source.

Replication without versions. Applying events in arrival order, with at-least-once delivery and parallel consumers, leaves the copy stuck at an old value. Every event should carry a version or a timestamp.

Confusing the snapshot with the copy. What the order should show is the value at order time, not the book's current title.

When Not to Use It

Database per service isn't mandatory in every case. Two deployables of the same bounded context owned by the same team (like eShop's Ordering API and OrderProcessor) can share a database; that's a deployment decision, not a service boundary. During a migration from a monolith, a shared database may be unavoidable for a while; in that case, writing down which table belongs to which service and narrowing permissions at the schema level as above keeps it from becoming a permanent shared database. The order in which to separate data from a monolith is the subject of the last post in the series. And if the system is in one team's hands, maybe what you need is not separate services yet but a modular monolith with separated schemas.

A Small Experiment

★☆☆ Run the SQL from this post on your own SQL Server. Then try an INSERT into ordering.Orders as catalog_svc. Which error code do you get? What changes if you replace one of the GRANT lines with DENY?

★★☆ Add support for BookPriceChanged(BookId, ListPrice, Version) to BookReplicaStore. If title and price share the same Version counter, what happens with an out-of-order pair of BookRenamed and BookPriceChanged? Should the counter be kept per property or per book?

★★★ Pick a database in a system you work on and list "who writes, who reads" for each table (in SQL Server an Extended Events session and application logs help). For tables written by more than one service, pick an owner; for the other readers, write down which of API, copy and read model fits, with your reasoning.

Separating the data raises another question: order, stock and payment are now in three separate databases, and no transaction keeps all three consistent together. In the next post we cover why we don't use distributed transactions, where a rule that needs consistency should live, a decision-level look at sagas, and how to show eventual consistency in the user interface.