Shared Database Across Services — The Hidden Monolith

Day 1: We're splitting the monolith into microservices!
       "We'll deal with the database later."

Day 30: order-service reads from users table (owned by user-service)
Day 60: billing-service writes to orders table (owned by order-service)
Day 90: All services read from a shared "reference_data" table
Day 120: 8 services, 1 database, nobody knows who owns what table

The database became the integration layer.
Any schema change requires cross-team coordination.

-- user-service renames a column (their table, their decision)
ALTER TABLE users RENAME COLUMN user_name TO full_name;

-- order-service breaks immediately (reads user_name in a JOIN)
SELECT o.*, u.user_name FROM orders o JOIN users u ON o.user_id = u.id
-- ERROR: column "user_name" does not exist

-- billing-service breaks too (also reads user_name)
-- notification-service breaks too
-- 3 teams, broken in production, emergency coordination required

Each service owns its own database — no other service can directly query it.

user-service    → postgres database: user_db
order-service   → postgres database: order_db
billing-service → postgres database: billing_db

Cross-service data access happens ONLY via:
  1. Service API calls (synchronous queries)
  2. Domain events (asynchronous data replication)

// Pattern: each service maintains its own copy of data it needs from others
// Kept up to date via domain events

// user-service publishes when a user updates their name
eventBus.publish('user.updated', {
  userId: 'u123',
  fullName: 'Sanjeev Sharma',
  email: 'sanjeev@example.com',
})

// order-service subscribes and maintains its own denormalized copy
eventBus.subscribe('user.updated', async (event) => {
  await orderDb.query(`
    INSERT INTO order_user_cache (user_id, full_name, email)
    VALUES ($1, $2, $3)
    ON CONFLICT (user_id) DO UPDATE
    SET full_name = $2, email = $3
  `, [event.userId, event.fullName, event.email])
})

// Now order-service can join against its own cache without calling user-service
const orders = await orderDb.query(`
  SELECT o.*, uc.full_name, uc.email
  FROM orders o
  JOIN order_user_cache uc ON o.user_id = uc.user_id
  WHERE uc.user_id = $1
`, [userId])

// For queries that genuinely need data from multiple services,
// compose at the API layer (or in a dedicated read model)
class OrderHistoryQuery {
  async execute(userId: string) {
    // Parallel calls to each service's API
    const [orders, user, payments] = await Promise.all([
      orderService.getOrdersByUser(userId),
      userService.getUser(userId),
      billingService.getPaymentsByUser(userId),
    ])

    return orders.map(order => ({
      ...order,
      customer: user,
      payment: payments.find(p => p.orderId === order.id),
    }))
  }
}

Migrating from shared database to per-service databases:

Phase 1: Define ownership
  - Map every table to a single "owning" service
  - Document who else reads/writes each table

Phase 2: Add API layer over owned tables
  - Build the API endpoints that let other services access the data
  - Keep direct DB access working during transition

Phase 3: Migrate consumers one by one
  - Update other services to use the API instead of direct DB access
  - Verify with integration tests

Phase 4: Enforce boundary
  - Remove other services' database credentials
  - Separate the databases physically