Chapter 27: Database Operations with Effect + Prisma

This chapter shows how to wrap Prisma in Effect services, creating a type-safe database layer with proper error handling, transactions, and connection management.

The PrismaClient Service

// shared/services/prisma-service.ts
import { Context, Effect, Layer } from "effect";
import { PrismaClient as PrismaORM } from "@prisma/client";

export class PrismaClient extends Context.Tag("PrismaClient")<
  PrismaClient,
  PrismaORM
>() {
  static Live = Layer.scoped(
    this,
    Effect.gen(function* () {
      const prisma = new PrismaORM({
        log: process.env.NODE_ENV === "development"
          ? ["query", "warn", "error"]
          : ["error"],
      });

      yield* Effect.tryPromise({
        try: () => prisma.$connect(),
        catch: (e) => new DatabaseConnectionError({ cause: e }),
      });

      yield* Effect.addFinalizer(() =>
        Effect.promise(() => prisma.$disconnect()).pipe(Effect.orDie)
      );

      return prisma;
    })
  );

  // Singleton for non-Effect contexts (e.g., scripts, migrations)
  static singleton = (() => {
    const globalForPrisma = globalThis as unknown as { prisma: PrismaORM | undefined };
    const prisma = globalForPrisma.prisma ?? new PrismaORM();
    if (process.env.NODE_ENV !== "production") {
      globalForPrisma.prisma = prisma;
    }
    return prisma;
  })();
}

Repository Pattern with Effect

// features/projects/infrastructure/prisma-project-repository.ts
import { Effect, Layer, Data } from "effect";
import { PrismaClient } from "@/shared/services/prisma-service";
import { ProjectRepository } from "../domain/ports/project-repository";
import { ProjectNotFoundError } from "../domain/errors/project-errors";

class DatabaseError extends Data.TaggedError("DatabaseError")<{
  readonly operation: string;
  readonly cause: unknown;
}> {}

export const PrismaProjectRepository = Layer.effect(
  ProjectRepository,
  Effect.gen(function* () {
    const prisma = yield* PrismaClient;

    return {
      findById: (id) =>
        Effect.tryPromise({
          try: () => prisma.project.findUnique({
            where: { id, deletedAt: null },
            include: { tags: { include: { tag: true } } },
          }),
          catch: (e) => new DatabaseError({ operation: "project.findById", cause: e }),
        }).pipe(
          Effect.flatMap((project) =>
            project
              ? Effect.succeed(mapToEntity(project))
              : Effect.fail(new ProjectNotFoundError({ projectId: id }))
          )
        ),

      findByOrganization: (filters) =>
        Effect.tryPromise({
          try: () => prisma.project.findMany({
            where: {
              organizationId: filters.organizationId,
              status: filters.status ?? undefined,
              deletedAt: null,
              ...(filters.search
                ? {
                    OR: [
                      { name: { contains: filters.search, mode: "insensitive" } },
                      { description: { contains: filters.search, mode: "insensitive" } },
                    ],
                  }
                : {}),
            },
            orderBy: { [filters.sortBy ?? "createdAt"]: filters.sortOrder ?? "desc" },
            skip: ((filters.page ?? 1) - 1) * (filters.pageSize ?? 20),
            take: filters.pageSize ?? 20,
            include: { tags: { include: { tag: true } } },
          }),
          catch: (e) => new DatabaseError({ operation: "project.findByOrganization", cause: e }),
        }).pipe(Effect.map((projects) => projects.map(mapToEntity))),

      create: (data) =>
        Effect.tryPromise({
          try: () => prisma.project.create({
            data: {
              name: data.name,
              description: data.description,
              priority: data.priority,
              organizationId: data.organizationId,
              createdById: data.createdById,
              dueDate: data.dueDate,
              ...(data.tagIds?.length
                ? {
                    tags: {
                      create: data.tagIds.map((tagId) => ({ tagId })),
                    },
                  }
                : {}),
            },
            include: { tags: { include: { tag: true } } },
          }),
          catch: (e) => new DatabaseError({ operation: "project.create", cause: e }),
        }).pipe(Effect.map(mapToEntity)),

      update: (id, data) =>
        Effect.tryPromise({
          try: () => prisma.project.update({
            where: { id },
            data: {
              ...(data.name !== undefined ? { name: data.name } : {}),
              ...(data.description !== undefined ? { description: data.description } : {}),
              ...(data.status !== undefined ? { status: data.status } : {}),
              ...(data.priority !== undefined ? { priority: data.priority } : {}),
            },
            include: { tags: { include: { tag: true } } },
          }),
          catch: (e) => new DatabaseError({ operation: "project.update", cause: e }),
        }).pipe(Effect.map(mapToEntity)),

      softDelete: (id) =>
        Effect.tryPromise({
          try: () => prisma.project.update({
            where: { id },
            data: { deletedAt: new Date() },
          }),
          catch: (e) => new DatabaseError({ operation: "project.softDelete", cause: e }),
        }).pipe(Effect.asVoid),

      count: (filters) =>
        Effect.tryPromise({
          try: () => prisma.project.count({
            where: {
              organizationId: filters.organizationId,
              status: filters.status ?? undefined,
              deletedAt: null,
            },
          }),
          catch: (e) => new DatabaseError({ operation: "project.count", cause: e }),
        }),
    };
  })
);

Transactions

Prisma transactions wrapped in Effect:

// Atomic operation: create project with initial tasks
const createProjectWithTasks = (input: CreateProjectWithTasks) =>
  Effect.gen(function* () {
    const prisma = yield* PrismaClient;

    const result = yield* Effect.tryPromise({
      try: () =>
        prisma.$transaction(async (tx) => {
          // Create project
          const project = await tx.project.create({
            data: {
              name: input.name,
              description: input.description,
              organizationId: input.organizationId,
              createdById: input.userId,
            },
          });

          // Create initial tasks
          if (input.tasks?.length) {
            await tx.task.createMany({
              data: input.tasks.map((task, index) => ({
                title: task.title,
                projectId: project.id,
                createdById: input.userId,
                position: index,
              })),
            });
          }

          return project;
        }),
      catch: (e) => new DatabaseError({ operation: "createProjectWithTasks", cause: e }),
    });

    return result;
  });

Connection Management

Singleton Pattern (Prevents Connection Pool Exhaustion)

// In development, hot reloading creates new PrismaClient instances
// The singleton pattern prevents this
const globalForPrisma = globalThis as unknown as { prisma: PrismaORM | undefined };
export const prisma = globalForPrisma.prisma ?? new PrismaORM();
if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}

Connection Pool Configuration

# .env
DATABASE_URL="postgresql://user:password@localhost:5432/taskforge?schema=public&connection_limit=10&pool_timeout=30"

Environment	connection_limit	Recommendation
Development	5	Low — single user
Serverless	1-3	Minimal — functions scale horizontally
Traditional server	10-20	Based on expected concurrency
Production (high traffic)	20-50	Monitor and adjust

Summary

• ✅ PrismaClient as an Effect service with scoped lifecycle management
• ✅ Repository pattern wrapping Prisma operations in typed Effects
• ✅ Transactions with prisma.$transaction wrapped in Effect.tryPromise
• ✅ Singleton pattern prevents connection pool exhaustion during hot reload
• ✅ Soft deletes filtered at the repository level (deletedAt: null)
• ✅ DatabaseError type provides consistent error handling across operations