Database Per Service: Breaking the Shared Database Anti-Pattern

A shared database is the fastest way to turn microservices into a distributed monolith. When services share tables, every schema change becomes a cross-team coordination nightmare, and you lose every benefit microservices promised.

The Shared Database Anti-Pattern#

Order Service ---\
                  \
User Service ------> [Single Database] <------ Inventory Service
                  /
Payment Service -/

Why teams do it: it is easy. Joins work. Transactions work. Everything feels familiar.

Why it fails:

Database Per Service#

Each service owns its data store. No other service can access it directly.

Order Service ------> [Orders DB]
User Service -------> [Users DB]
Inventory Service --> [Inventory DB]
Payment Service ----> [Payments DB]

Rules:

1. A service's database is a private implementation detail
2. Other services access data only through the service's API
3. No direct database-to-database connections
4. No shared tables, no shared schemas

Benefits#

• Independent deployability — change your schema without coordinating with other teams
• Technology freedom — use Postgres for orders, Redis for sessions, Elasticsearch for search
• Fault isolation — one database going down does not take everything with it
• Scalability — scale each database independently based on its workload
• Clear ownership — every table has exactly one owning service

Data Consistency Challenges#

Without distributed transactions, how do you keep data consistent across services?

The Problem#

// This no longer works across services:
BEGIN TRANSACTION
  INSERT INTO orders (...)
  UPDATE inventory SET stock = stock - 1
  INSERT INTO payments (...)
COMMIT

Each service has its own database. You cannot wrap them in a single ACID transaction.

Solution 1: Saga Pattern#

A saga is a sequence of local transactions where each step publishes an event or command that triggers the next step.

Choreography approach:

1. Order Service: Create order (PENDING)
   --publishes--> OrderCreated

2. Payment Service: Charge card
   --publishes--> PaymentSucceeded

3. Inventory Service: Reserve stock
   --publishes--> StockReserved

4. Order Service: Confirm order (CONFIRMED)

// Compensation on failure at step 3:
   StockUnavailable --> Payment Service refunds
   PaymentRefunded --> Order Service cancels

Orchestration approach:

Order Saga Orchestrator:
  step 1: CreateOrder     --> Order Service
  step 2: ProcessPayment  --> Payment Service
  step 3: ReserveStock    --> Inventory Service
  step 4: ConfirmOrder    --> Order Service

  compensate 3: ReleaseStock   --> Inventory Service
  compensate 2: RefundPayment  --> Payment Service
  compensate 1: CancelOrder    --> Order Service

When to use which:

• Choreography: 2-4 steps, simple flow, loosely coupled teams
• Orchestration: 5+ steps, complex business logic, need visibility into the flow

Solution 2: API Composition for Queries#

When you need data from multiple services for a single response, compose at the API layer.

// Client asks: "Show me order #123 with customer and shipping details"

API Gateway:
  GET /orders/123          --> Order Service     --> { orderId, items, total }
  GET /users/456           --> User Service      --> { name, email }
  GET /shipping/order/123  --> Shipping Service  --> { status, eta }

  // Merge and return
  return { order, customer, shipping }

Trade-offs:

• Increased latency (multiple network calls)
• No cross-service filtering or sorting
• Availability risk (if one service is down, the whole query can fail)

Solution 3: Event-Driven Data Sync#

Services maintain local read-only copies of data they need from other services.

User Service --publishes--> UserUpdated event
  |
  +--> Order Service stores { userId, name, email } locally
  +--> Shipping Service stores { userId, address } locally

Benefits: fast local queries, no runtime dependency on other services. Cost: eventual consistency, data duplication, sync logic to maintain.

Solution 4: CQRS (Command Query Responsibility Segregation)#

Separate the write model from the read model.

WRITE SIDE (Commands):
  Order Service --> [Orders DB] (normalized, transactional)
  Payment Service --> [Payments DB]

  Both publish events --> Event Bus

READ SIDE (Queries):
  Event Consumer builds --> [Read-Optimized View DB]
  (denormalized, pre-joined, fast queries)

When CQRS shines:

• Read and write patterns differ significantly
• You need complex cross-service queries
• Read scale vastly exceeds write scale
• You want to optimize read models for specific UI views

When CQRS is overkill:

• Simple CRUD applications
• Read and write patterns are similar
• Small team that does not need the complexity

Polyglot Persistence#

With database-per-service, each service can choose the best storage engine for its workload.

Service             | Database        | Why
--------------------|-----------------|---------------------------
User Profiles       | PostgreSQL      | Relational data, ACID
Product Catalog     | MongoDB         | Flexible schema, nested docs
Session Store       | Redis           | Low latency, TTL support
Search              | Elasticsearch   | Full-text search, facets
Analytics           | ClickHouse      | Columnar, fast aggregations
Social Graph        | Neo4j           | Graph relationships
Event Store         | Apache Kafka    | Append-only event log

Guidelines:

• Do not use a different database just because you can — operational complexity has a cost
• Start with one or two database technologies for the whole system
• Add specialized databases only when there is a measurable benefit
• Make sure your team can operate every database they choose

Migration Strategy#

Going from shared database to database-per-service is not a flag day. Migrate incrementally.

Phase 1: Identify data ownership per service
Phase 2: Create service APIs for data access
Phase 3: Redirect reads through APIs (keep shared DB)
Phase 4: Split data into separate schemas/databases
Phase 5: Remove direct database access
Phase 6: Implement events for cross-service data needs

Critical rule: never try to split everything at once. Pick the service with the clearest data boundary first.

Key Takeaways#

1. Shared databases kill microservice independence — split them
2. Sagas replace distributed transactions — accept eventual consistency
3. API composition works for simple cross-service queries
4. Event-driven sync gives fast local reads at the cost of eventual consistency
5. CQRS is powerful for complex read patterns but adds significant complexity
6. Polyglot persistence is a benefit, not a goal — use it when justified
7. Migrate incrementally — one service at a time

The database-per-service pattern is non-negotiable for true microservice independence. The consistency patterns above are how you make it work in practice.

---

This is article #261 in the Codelit engineering series. We publish in-depth technical guides on architecture, infrastructure, and modern engineering practices. Explore more at codelit.dev.