Database

1. Repository Pattern

To add persistence, introduce a repository interface and have services depend on it rather than a concrete implementation. Start with a file-system-backed implementation for early development, then swap to a real database without touching feature code. A typical interface looks like this:

1
// src/features/users/server/user.repository.ts
2
export interface UserRepository {
3
  findById(id: string): Promise<User | null>;
4
  findByEmail(email: string): Promise<User | null>;
5
  create(input: CreateUserInput): Promise<User>;
6
  update(id: string, input: UpdateUserInput): Promise<User>;
7
}
8
9
// FS-backed impl for early development
10
export class FsUserRepository implements UserRepository { /* ... */ }
11
12
// Swap to Prisma later without touching service layer
13
export class PrismaUserRepository implements UserRepository { /* ... */ }

2. Adding Prisma

Install Prisma and initialize the schema. Pick PostgreSQL for production; SQLite for local development is fine.

yarn add prisma @prisma/client
yarn add -D prisma
npx prisma init --datasource-provider postgresql

Define your schema in prisma/schema.prisma.

1
// prisma/schema.prisma
2
generator client {
3
  provider = "prisma-client-js"
4
}
5
6
datasource db {
7
  provider = "postgresql"
8
  url      = env("DATABASE_URL")
9
}
10
11
model User {
12
  id        String   @id @default(cuid())
13
  email     String   @unique
14
  name      String?
15
  role      Role     @default(USER)
16
  createdAt DateTime @default(now())
17
  updatedAt DateTime @updatedAt
18
}
19
20
enum Role {
21
  USER
22
  ADMIN
23
}

3. Singleton Client

Next.js hot-reloads can create multiple Prisma clients in dev. Cache the instance on globalThis to avoid exhausting connection pools.

1
// src/server/db/prisma.ts
2
import { PrismaClient } from "@prisma/client";
3
4
const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };
5
6
export const prisma =
7
  globalForPrisma.prisma ??
8
  new PrismaClient({
9
    log: process.env.NODE_ENV === "development" ? ["query", "error"] : ["error"],
10
  });
11
12
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

4. Prisma Repository Implementation

Wrap Prisma calls behind your repository interface. Once services depend on the interface, swapping a file-system-backed implementation for Prisma is invisible to callers.

1
// src/features/users/server/prisma-user.repository.ts
2
import { prisma } from "@/server/db/prisma";
3
import type { UserRepository } from "./user.repository";
4
5
export class PrismaUserRepository implements UserRepository {
6
  async findById(id: string) {
7
    return prisma.user.findUnique({ where: { id } });
8
  }
9
10
  async findByEmail(email: string) {
11
    return prisma.user.findUnique({ where: { email } });
12
  }
13
14
  async create(input: CreateUserInput) {
15
    return prisma.user.create({ data: input });
16
  }
17
18
  async update(id: string, input: UpdateUserInput) {
19
    return prisma.user.update({ where: { id }, data: input });
20
  }
21
}

5. Migrations

Use Prisma Migrate for schema changes. Migrations live in prisma/migrations/ and are version-controlled.

# Create and apply a new migration (development)
npx prisma migrate dev --name add_user_role

# Apply pending migrations (production)
npx prisma migrate deploy

# Reset database (destructive — dev only)
npx prisma migrate reset

# Open visual data browser
npx prisma studio

6. Environment Variables

Add DATABASE_URL to your validated env config at src/server/env.ts. Fail-fast at startup, never read process.env directly outside the config layer.

1
// src/server/env.ts
2
import { z } from "zod";
3
4
const envSchema = z.object({
5
  DATABASE_URL: z.string().url(),
6
  // ... other vars
7
});
8
9
export const env = envSchema.parse(process.env);

7. Alternative ORMs

The repository pattern keeps your service layer ORM-agnostic. Common swaps:

• Drizzle ORM — TypeScript-first, lighter runtime, SQL-like API. Good fit for edge runtime.
• Kysely — type-safe query builder, no codegen, full SQL control.
• Mongoose — MongoDB. Use when your data is document-shaped (events, logs, flexible schemas).