Database per Service Pattern: Microservices Data Architecture

The promise of microservices — independent deployability, technology heterogeneity, and fault isolation — evaporates the moment two services share a database. A schema change in the orders table forces a coordinated release with the inventory service. A long-running report query from the analytics service holds locks that stall order writes. A poorly indexed join in billing brings down the entire cluster. These are not theoretical concerns; they are Tuesday morning incidents at companies that adopted microservices topologically (splitting the code) but not architecturally (splitting the data).

The database-per-service pattern is the foundational data rule of microservices. It states that each service is the sole owner and gatekeeper of its persistent state. No other service may read from or write to that database directly. All data exchange happens through well-defined APIs or asynchronous events. This sounds simple, and it is — but the consequences are profound and the implementation challenges are real.

The first challenge engineers hit is cross-service queries. In a monolith, joining orders and customers is a single SQL statement. In a database-per-service world, that join must become a service call, an event-driven denormalisation, or a dedicated read model assembled from both services. Each approach has trade-offs in latency, consistency, and operational complexity.

The second challenge is distributed transactions. Moving money between accounts in a monolith is a single ACID transaction that either commits or rolls back. In a microservices world, the accounts service and the ledger service each own their data. The Saga pattern — implemented via choreography (events) or orchestration (a saga orchestrator) — replaces 2PC with a sequence of local transactions and compensating actions. Understanding when to use each flavour is one of the most important architectural decisions you will make.

The Outbox pattern solves the dual-write problem: how do you atomically update your database and publish an event without a distributed transaction? The answer is to write the event into an outbox table in the same local transaction, then have a relay process publish it asynchronously. This guarantees at-least-once delivery even if the broker is down at commit time.

Finally, CQRS (Command Query Responsibility Segregation) acknowledges that the write model optimised for consistency is rarely the same shape as the read model optimised for query performance. By maintaining separate models — often a normalised write store and a denormalised read store — teams can serve complex dashboards without burdening transactional databases.

Why Shared Databases Break Microservices

The entire value proposition of microservices rests on independent deployability. A team should be able to release their service on a Friday afternoon without coordinating with five other teams. Shared databases make this impossible in practice, even when service APIs are well-defined.

The mechanisms of coupling are insidious. Schema coupling: Service B reads columns from Service A's table. When A wants to rename or remove a column, it must first verify B has been updated — a cross-team coordination dance. Temporal coupling: A's migration holds a table lock. B's queries time out. The order of deployment matters and teams must coordinate release windows. Load coupling: B's report query does a full-table scan on A's orders table at 09:00 every morning, causing write latency spikes for A's customers.

None of these problems are visible in architecture diagrams that show only service-to-service API calls. They only surface in production, usually at the worst possible moment. Database-per-service eliminates all three forms of coupling by making the data boundary as explicit as the API boundary.

The transition from a shared database to database-per-service is rarely a big-bang migration. The strangler fig pattern applies here: identify seams in the existing schema where data belongs clearly to one domain, extract those tables into a service-owned schema, and replace direct DB queries in other services with API calls. The process is iterative and can take months for a large monolith, but each seam extracted reduces the blast radius of future incidents.

Saga Pattern: Choreography vs Orchestration

A saga is a sequence of local transactions, each updating its own service's database and publishing an event or message to trigger the next step. If any step fails, compensating transactions undo the work of preceding steps. This replaces the two-phase commit (2PC) protocol, which requires a distributed transaction coordinator and is incompatible with independently deployed services.

Choreography-based sagas have no central coordinator. Each service listens for events and decides independently what to do next. The order service publishes OrderPlaced. The inventory service listens, reserves stock, and publishes StockReserved. The payment service listens to StockReserved, charges the card, and publishes PaymentProcessed. Each service knows its role in the dance without anyone calling the shots. This approach is simple to implement, scales well, and has no single point of failure. The downside is that the overall flow is implicit — it exists only in the aggregate behaviour of all participants. Debugging a stuck saga means correlating events across multiple service logs, which requires good distributed tracing and a shared correlation ID.

Orchestration-based sagas have a central saga orchestrator (often a separate service or a durable workflow engine like Temporal or Axon). The orchestrator drives the saga: it calls inventory to reserve stock, waits for confirmation, then calls payment, waits, then calls fulfilment. If payment fails, the orchestrator explicitly calls inventory to release the reservation. The flow is centralised and visible — you can query the orchestrator for the current state of any saga instance. The trade-off is a new service to maintain and a potential bottleneck if the orchestrator becomes overloaded.

Choose choreography when: the saga has 2-3 steps, participants are well-defined, and the team values simplicity. Choose orchestration when: the saga has 5+ steps, conditional branching is needed (retry payment 3 times before cancelling), long-running waits are involved (wait for human approval), or business teams need visibility into saga state via a dashboard.

Outbox Pattern: Reliable Event Publishing

The dual-write problem is simple to state: your business logic needs to update its database AND publish an event to a message broker. If you do both in sequence, you have a race condition. Update DB, then crash before publishing? Event is lost. Publish event, then crash before committing? Event fires but DB has no record. Two-phase commit between a relational DB and Kafka is theoretically possible but operationally complex and almost never used in practice.

The Outbox pattern solves this with a local transaction guarantee. Instead of publishing directly to Kafka, you insert the event into an outbox table in the SAME database transaction as your business update. If the transaction commits, both the business data and the outbox record are durably saved. If the transaction rolls back, neither survives. The dual-write problem disappears because there is only one write target: your own database.

A separate relay process then reads unpublished outbox rows and publishes them to Kafka, marking them published after acknowledgement. The relay can be implemented as a polling publisher (a scheduled Spring job that queries for unprocessed outbox rows every 100ms) or as CDC (Change Data Capture) using Debezium, which tails the database's write-ahead log and publishes changes to Kafka with near-zero latency and no polling overhead.

Debezium is the production choice for high-throughput systems. It reads the Postgres WAL or MySQL binlog, requires no polling, and adds no write amplification to the application. The outbox rows themselves can be used as the event payload, or the Debezium Outbox Event Router SMT (Single Message Transform) can route events to per-aggregate topics automatically.

Critical detail: the relay guarantees at-least-once delivery, not exactly-once. Consumers must be idempotent. The standard approach is to include the outbox row's UUID as the event ID, and consumers check IF NOT EXISTS (SELECT 1 FROM processed_events WHERE event_id = ?) before applying the event.

CQRS: Separating Write and Read Models

CQRS (Command Query Responsibility Segregation) is the recognition that the data model optimised for writing (normalised, consistent, transactional) is almost never the same shape as the data model optimised for reading (denormalised, pre-joined, query-friendly). Most systems use a single model for both, and both suffer as a result: writes deal with complex joins to maintain normalisation, reads deal with expensive joins to reconstruct domain objects.

In a microservices context, CQRS pairs naturally with the database-per-service pattern. The write-side service owns its transactional store and publishes domain events on every state change. One or more read-side projectors subscribe to these events and maintain denormalised read models optimised for specific query patterns. The customer dashboard projector might maintain a customer_order_summary table with pre-computed totals. The inventory report projector might maintain a daily_stock_snapshot table. Each read model is a materialised view, but built from events rather than from a database view.

The key operational insight is that read models are disposable and rebuildable. Because they are derived from an immutable event stream, you can drop a read model, replay the event stream from the beginning, and rebuild it with a new schema. This makes schema evolution on the read side trivial — something that is very expensive on a shared transactional database.

CQRS does introduce eventual consistency between write and read models. A customer who just placed an order may not see it immediately in the orders list if the projector has not yet processed the event. This is usually acceptable with a clear UX pattern: after the write succeeds, show the new item optimistically in the UI while the event propagates. If strict consistency is required for a specific query, serve it from the write-side service directly, accepting the performance cost.

Eventual Consistency: Designing for It, Not Against It

Eventual consistency means that, given no new updates, all replicas of a data item will eventually converge to the same value. In a database-per-service architecture, it is not an optional trade-off — it is the fundamental consistency model for cross-service data. The question is not whether to accept eventual consistency but how to design systems that are correct in its presence.

The first design principle is to make writes return as fast as possible and let the system catch up asynchronously. An order placement writes to the orders database and publishes an event. The inventory update happens asynchronously. The payment charge happens asynchronously. The confirmation email is sent asynchronously. The customer sees immediate feedback ('Your order has been placed') while the downstream effects propagate over the next few hundred milliseconds.

The second principle is to design UIs for optimistic updates. When a customer places an order, show it immediately in their order history with a 'Processing' status. Do not wait for the CQRS read model to catch up. Use a local state update (Redux, Zustand, or React Query optimistic update) that will be reconciled when the next poll or WebSocket push delivers the confirmed state.

The third principle is to design for idempotency everywhere. Events will be delivered at least once. Consumers may process the same event multiple times due to redelivery after a crash. Every event handler must be safe to call multiple times with the same event. The standard pattern is to maintain a processed_events table with the event ID and check it before applying — if already processed, skip and ack the message.

Business processes that require strong consistency (financial ledgers, inventory allocation at checkout) should be modelled as sagas with explicit compensating transactions, or should remain within a single service's database boundary where ACID transactions apply.

Migration Strategy: Strangler Fig to Database-per-Service

Migrating a monolith with a shared database to a database-per-service architecture is a multi-month journey. The strangler fig pattern provides the framework: build new service-owned data stores alongside the monolith, gradually route traffic to them, and strangle the shared database one domain at a time.

Phase 1 — Identify seams: Map which tables belong exclusively to which domain. Tables that are truly shared (accessed by many domains) are the hardest — start with the easy wins: tables accessed by only one domain team. Use a database dependency graph tool or simply grep the codebase for table name references.

Phase 2 — Create the shadow service: Stand up the new service with its own database. Implement a dual-write adapter in the monolith: on every write to the shared table, also write to the new service's API (or outbox). Run both databases in sync for 2-4 weeks, comparing output.

Phase 3 — Migrate reads: Switch the consumers of this data (other services, the monolith itself) to call the new service's API instead of querying the table directly. Do this one consumer at a time, running A/B comparisons.

Phase 4 — Cut over writes: Remove the dual-write adapter. The shared table becomes read-only. After a confidence period, drop the table from the shared database.

Pitfalls: Cross-domain transactions that span both old and new data (must be converted to sagas before cutover). Reporting queries that join multiple domains (must become read models before cutover). Long-lived transactions that pin the shared DB (identify and shorten before migration).

Why Your EntityManagerFactory Setup Will Fail in Production

You configured two databases in Spring Boot. Great. Now watch your app fail when it tries to create the second persistence unit. Default Spring Boot auto-configuration assumes one DataSource, one EntityManagerFactory. When you define a second, Spring doesn't know which is which. You get duplicate bean definitions, transaction managers fighting, and repositories pointing at wrong databases. The fix is explicit: mark one Primary, name the others. Every bean needs a distinct qualifier. Every repository package needs its own @EnableJpaRepositories with explicit entityManagerFactoryRef and transactionManagerRef. If you skip this, your user service will write products into the user table, silent data corruption until a report breaks. Spring Boot's magic works for one database. For multiple, you're the magician now.

// io.thecodeforge — java tutorial
@Configuration
@EnableJpaRepositories(
    basePackages = "com.acme.orderservice.repo",
    entityManagerFactoryRef = "orderEntityManager",
    transactionManagerRef = "orderTransactionManager"
)
public class PersistenceOrderConfig {
    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean orderEntityManager() {
        var em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(orderDataSource());
        em.setPackagesToScan("com.acme.orderservice.domain");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        return em;
    }
    @Bean
    @Primary
    public DataSource orderDataSource() {
        return DataSourceBuilder.create()
            .url("jdbc:postgresql://order-db:5432/orders")
            .username("order_svc")
            .password("${ORDER_DB_PASS}")
            .build();
    }
    @Bean
    @Primary
    public PlatformTransactionManager orderTransactionManager() {
        return new JpaTransactionManager(orderEntityManager().getObject());
    }
}

Testing Cross-Database Transactions: The Hidden Race Condition

You have two databases. One JTA transaction manager. One write fails. The other commits anyway. This is the classic distributed transaction lie. Spring's @Transactional across multiple DataSources only works with XA transactions, which break under network partitions and slow your writes to molasses. Real production services don't use XA. They use the Saga pattern, but you need to prove your compensation logic actually fires. Write a test that simulates a partial failure: insert into Order DB, then throw a RuntimeException before the Payment DB write. Assert the Order DB rollback. If it doesn't roll back, your Saga compensation code is dead code. Use Testcontainers with two Postgres containers. Annotate your test with @Transactional per data source, not across both. Verify the compensation logic by checking the state of the first DB after the failure. You'll catch the race condition that only shows up at 2 AM under load.

// io.thecodeforge — java tutorial
@Test
void sagaCompensatesOnPaymentFailure() {
    // simulate partial Saga — order created, payment fails
    var orderId = orderService.createOrder(new CreateOrderRequest("SKU-1", 1));
    
    // force failure — payment service throws
    Mockito.when(paymentClient.processPayment(any()))
        .thenThrow(new PaymentFailedException("insufficient funds"));
    
    assertThrows(PaymentFailedException.class,
        () -> orderService.completeOrder(orderId));
    
    // verify compensation — order is rolled back
    var order = orderRepository.findById(orderId).orElse(null);
    assertNull(order, "Saga should have compensated: order not null");
}