Designing Databases for Scale: Isolated Data Per Service

The "Database per Service" pattern ensures loose coupling in microservices. This guide covers ORM fundamentals, how Prisma works under the hood, schema design with relations, and configuration for multi-database architectures.

What is an ORM?

An Object-Relational Mapping (ORM) is a technique that lets you interact with databases using your programming language's objects instead of writing raw SQL queries.

WITHOUT ORM                          WITH ORM

Application Code                     Application Code
       │                                    │
       ▼                                    ▼
   Raw SQL                            ORM Layer
"SELECT * FROM users                 prisma.user.findMany()
 WHERE id = 1"                              │
       │                                    ▼
       ▼                             Generated SQL
   Database                                 │
                                           ▼
                                       Database

ORM Benefits

• Type Safety: Compile-time checks prevent invalid queries
• Abstraction: Database-agnostic code (switch PostgreSQL to MySQL)
• Productivity: Less boilerplate, more readable code
• Security: Built-in protection against SQL injection
• Migrations: Version-controlled schema changes

What is Prisma?

Prisma is a next-generation TypeScript ORM that takes a different approach from traditional ORMs. Instead of mapping classes to tables, Prisma uses a declarative schema to generate a type-safe query builder.

Prisma Components

• Prisma Schema: Single source of truth for your database structure
• Prisma Client: Auto-generated, type-safe query builder
• Prisma Migrate: Declarative migration system
• Prisma Studio: Visual database browser

How Prisma Works Under the Hood

PRISMA ARCHITECTURE

┌─────────────────────────────────────────────────────┐
│                   Your Application                   │
│                                                     │
│   prisma.user.findMany({ where: { active: true }}) │
└─────────────────────┬───────────────────────────────┘
                      │
                      ▼
┌─────────────────────────────────────────────────────┐
│              Prisma Client (Generated)              │
│                                                     │
│   • Type definitions from schema                    │
│   • Query builder methods                           │
│   • Serializes queries to protocol                  │
└─────────────────────┬───────────────────────────────┘
                      │ Binary Protocol
                      ▼
┌─────────────────────────────────────────────────────┐
│              Prisma Query Engine                    │
│              (Rust Binary)                          │
│                                                     │
│   • Parses and validates queries                    │
│   • Generates optimized SQL                         │
│   • Connection pooling                              │
│   • Transaction management                          │
└─────────────────────┬───────────────────────────────┘
                      │ SQL
                      ▼
┌─────────────────────────────────────────────────────┐
│                    Database                         │
│           (PostgreSQL, MySQL, etc.)                 │
└─────────────────────────────────────────────────────┘

The Prisma Query Engine is a Rust binary that runs as a sidecar process. This architecture enables:

• Performance: Rust's speed for query parsing and optimization
• Connection Pooling: Efficient database connection management
• Cross-Platform: Different binaries for different OS (binaryTargets)

Database per Service Pattern

In microservices, each service should own its data. The Database per Service pattern keeps each microservice's persistent data private and accessible only via its API.

DATABASE PER SERVICE

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   Service   │     │   Service   │     │   Service   │
│      A      │     │      B      │     │      C      │
└──────┬──────┘     └──────┬──────┘     └──────┬──────┘
       │                   │                   │
       ▼                   ▼                   ▼
┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│ Database A  │     │ Database B  │     │ Database C  │
│ (PostgreSQL)│     │  (MongoDB)  │     │   (Redis)   │
└─────────────┘     └─────────────┘     └─────────────┘

Implementation Options

• Private tables: Each service owns specific tables in a shared database
• Schema per service: Separate schemas within the same database server
• Database per service: Dedicated database server for each service

Benefits

• Loose coupling: Changes to one service's data layer don't affect others
• Technology flexibility: Each service can use the database type best suited to its needs
• Independent scaling: Scale databases based on individual service requirements
• Fault isolation: Database failures don't cascade across services

Challenges

• Distributed transactions: Business logic spanning multiple services requires coordination
• Cross-service queries: Joining data across service boundaries is not possible
• Data consistency: Eventual consistency instead of ACID guarantees
• Operational complexity: Managing multiple database systems

Solutions for Challenges

• Saga Pattern: Coordinate transactions across services using events
• API Composition: Aggregate data from multiple services at the API Gateway
• CQRS: Separate read and write models for complex queries
• Event Sourcing: Store state changes as events for consistency

Generator Configuration

Each microservice has its own prisma/schema.prisma file. Custom output paths prevent generated clients from overwriting each other.

generator client {
  provider      = "prisma-client-js"
  output        = "./generated/client"
  binaryTargets = ["native", "linux-musl-openssl-3.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

• output: Custom path prevents client conflicts between services
• binaryTargets: Required for Docker Alpine images (linux-musl-openssl-3.0.x)
• native: Enables local development on macOS/Linux

Schema Design

Models with Relations

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("users")
}

model Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?
  userId String  @unique

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  author     User       @relation(fields: [authorId], references: [id], onDelete: Cascade)
  categories Category[]

  @@map("posts")
}

model Category {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]

  @@map("categories")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Relation Types

• One-to-One: User has one Profile (Profile? + @unique on foreign key)
• One-to-Many: User has many Posts (Post[])
• Many-to-Many: Posts have many Categories (implicit join table)

Schema Attributes

• @@map("table_name"): Custom table name in database
• @map("column_name"): Custom column name
• onDelete: Cascade: Delete related records automatically
• @@unique([field1, field2]): Composite unique constraint
• @default(cuid()): Collision-resistant unique identifiers
• @updatedAt: Auto-update timestamp on changes

Using the Client

Import the specific generated client instead of the generic one.

import { Injectable } from "@nestjs/common";
import { PrismaClient } from "../../prisma/generated/client";

@Injectable()
export class UserService {
  private prisma = new PrismaClient();

  async findByEmail(email: string) {
    return this.prisma.user.findUnique({
      where: { email },
      include: { posts: true, profile: true }
    });
  }

  async createWithProfile(data: CreateUserDto) {
    return this.prisma.user.create({
      data: {
        email: data.email,
        name: data.name,
        profile: {
          create: {
            bio: data.bio
          }
        }
      },
      include: { profile: true }
    });
  }

  async updateUser(id: string, data: UpdateUserDto) {
    return this.prisma.user.update({
      where: { id },
      data
    });
  }
}

Migration Strategy

Each service manages its own migrations independently.

npx prisma migrate dev --schema=apps/auth-service/prisma/schema.prisma
npx prisma migrate dev --schema=apps/content-service/prisma/schema.prisma
npx prisma migrate dev --schema=apps/analytics-service/prisma/schema.prisma

This strict isolation means services physically cannot query each other's tables, forcing communication through APIs or message queues.

Docker Configuration

PostgreSQL Service

services:
  database:
    image: postgres
    container_name: database
    ports:
      - "5432:5432"
    restart: always
    environment:
      - POSTGRES_DB=myapp
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
    volumes:
      - database_volume:/var/lib/postgresql

volumes:
  database_volume:

Multi-Stage Dockerfile

FROM node:18-alpine AS base

FROM base AS builder
RUN apk add --no-cache libc6-compat
WORKDIR /app
RUN npm install -g turbo
COPY . .
RUN turbo prune --scope=auth-service --docker

FROM base AS installer
RUN apk add --no-cache libc6-compat openssl
WORKDIR /app
COPY --from=builder /app/out/json/ .
COPY --from=builder /app/turbo.json ./turbo.json
COPY --from=builder /app/apps/auth-service/prisma ./prisma
RUN npm install
RUN npx prisma generate

FROM base AS sourcer
WORKDIR /app
COPY --from=installer /app/ .
COPY --from=builder /app/out/full/ .
RUN npx turbo run build --filter=auth-service...

FROM base AS runner
WORKDIR /app
RUN apk add --no-cache openssl
COPY --from=sourcer /app/ .
WORKDIR /app/apps/auth-service/

EXPOSE 3000

CMD ["node", "dist/main.js"]

Build Stages

• builder: Prunes monorepo to target service and dependencies
• installer: Installs packages and generates Prisma client with correct binaries
• sourcer: Builds application with Turborepo caching
• runner: Minimal production image

The openssl package is required in installer and runner stages for Prisma's query engine.

Conclusion

Prisma bridges the gap between type safety and database performance through its unique architecture. The Rust-based query engine handles connection pooling and SQL optimization, while the generated TypeScript client provides compile-time guarantees that prevent runtime errors.

The database-per-service pattern enforces microservice boundaries at the data layer. Services cannot accidentally query each other's tables, forcing explicit communication through APIs or message queues. This isolation enables independent deployments, technology flexibility, and fault containment.

With custom generator outputs, each service maintains its own Prisma client without conflicts. Combined with multi-stage Docker builds and proper binary targets, this architecture scales from local development to production containers seamlessly.