TypeScript: Express.js Database Integration with Prisma

In the previous post, we built a complete task management API with Express.js — Zod validation, layered architecture, error handling, the works. One problem: all our data lives in a Map that disappears when the server restarts.

Time to fix that. We'll replace the in-memory store with PostgreSQL via Prisma ORM, and you'll see how the layered architecture we built pays off — only the repository layer changes. Everything else stays the same.

Time commitment: 2-3 hours
Prerequisites: TypeScript: Build REST API with Express.js

What You'll Learn

✅ Prisma ORM setup — schema, client generation, and VS Code integration
✅ Schema design — models, relations, enums, and field constraints
✅ Migrations — create, apply, and manage database changes safely
✅ Type-safe CRUD — replace in-memory store with real database queries
✅ Transactions — atomic operations that succeed or fail together
✅ Connection pooling — handle production traffic without exhausting connections
✅ Seeding — populate your database with initial or test data
✅ Testing — unit and integration tests for your database layer

1. Why Prisma?

In the ORM guide, we compared Prisma, TypeORM, and Drizzle in depth. Here's the short version of why Prisma fits this series:

Feature	Why It Matters
Auto-generated types	Your queries return typed objects — no manual type definitions needed
Schema-first design	Define your database in `schema.prisma`, generate everything from it
Migration system	SQL migrations generated from schema diffs, version-controlled
IntelliSense everywhere	Full autocomplete for queries, filters, includes — in VS Code
TypeScript-native	Built for TypeScript from day one, not bolted on

The real win: Prisma's generated types flow through your entire codebase. When you change a schema field, TypeScript catches every place that needs updating.

2. Project Setup

Start from where we left off in Post 3. Install Prisma and the PostgreSQL driver:

npm install @prisma/client
npm install -D prisma

Initialize Prisma:

npx prisma init

This creates:

prisma/schema.prisma — your database schema
.env — with a DATABASE_URL placeholder

Update .env with your PostgreSQL connection string:

# .env
DATABASE_URL="postgresql://postgres:password@localhost:5432/taskdb?schema=public"

Don't have PostgreSQL? Run it with Docker:

docker run --name taskdb -e POSTGRES_PASSWORD=password -e POSTGRES_DB=taskdb -p 5432:5432 -d postgres:16

Your updated project structure:

src/
├── index.ts
├── app.ts
├── config/
│   └── env.ts
├── routes/
│   ├── index.ts
│   └── task.routes.ts
├── controllers/
│   └── task.controller.ts
├── services/
│   └── task.service.ts
├── repositories/
│   └── task.repository.ts      # ← This is what changes
├── middleware/
│   ├── error-handler.ts
│   ├── validate.ts
│   ├── request-id.ts
│   └── not-found.ts
├── schemas/
│   └── task.schema.ts
├── types/
│   └── index.ts
├── utils/
│   └── errors.ts
└── lib/
    └── prisma.ts                # ← New: Prisma client singleton
prisma/
├── schema.prisma                # ← New: Database schema
├── migrations/                  # ← New: Migration files
└── seed.ts                      # ← New: Seed script

Notice how the layered architecture shines — we only touch task.repository.ts and add a Prisma client. Controllers, services, middleware, routes — all untouched.

3. Schema Design

Replace the generated prisma/schema.prisma with our task management schema:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
enum TaskStatus {
  TODO
  IN_PROGRESS
  DONE
}
 
enum TaskPriority {
  LOW
  MEDIUM
  HIGH
}
 
model Task {
  id          String       @id @default(uuid())
  title       String       @db.VarChar(255)
  description String?      @db.Text
  status      TaskStatus   @default(TODO)
  priority    TaskPriority @default(MEDIUM)
  dueDate     DateTime?    @map("due_date")
  createdAt   DateTime     @default(now()) @map("created_at")
  updatedAt   DateTime     @updatedAt @map("updated_at")
 
  tags        Tag[]
 
  @@index([status])
  @@index([priority])
  @@index([createdAt])
  @@map("tasks")
}
 
model Tag {
  id    String @id @default(uuid())
  name  String @unique @db.VarChar(50)
  tasks Task[]
 
  @@map("tags")
}

Let's break down the design decisions:

Enums vs Strings

enum TaskStatus {
  TODO
  IN_PROGRESS
  DONE
}

PostgreSQL native enums are enforced at the database level. No invalid values can sneak in, even from raw SQL or other applications hitting the same database. Prisma generates TypeScript enums that match, so you get compile-time safety too.

Field Mapping

dueDate     DateTime?    @map("due_date")

@map lets you use camelCase in TypeScript while keeping snake_case in the database — the convention each ecosystem expects. @@map("tasks") on the model does the same for table names.

Indexes

@@index([status])
@@index([priority])
@@index([createdAt])

We index the fields we filter and sort by. Without these, every query scans the entire table. With 100 rows it doesn't matter. With 100,000 rows, it matters a lot.

Relations

The Tag model creates a many-to-many relationship. Prisma handles the join table automatically — you don't need to create it yourself.

4. Migrations

Generate your first migration:

npx prisma migrate dev --name init

This does three things:

Creates prisma/migrations/20260310_init/migration.sql with the SQL
Applies the migration to your database
Regenerates the Prisma Client with updated types

Let's look at the generated SQL:

-- CreateEnum
CREATE TYPE "TaskStatus" AS ENUM ('TODO', 'IN_PROGRESS', 'DONE');
CREATE TYPE "TaskPriority" AS ENUM ('LOW', 'MEDIUM', 'HIGH');
 
-- CreateTable
CREATE TABLE "tasks" (
    "id" TEXT NOT NULL,
    "title" VARCHAR(255) NOT NULL,
    "description" TEXT,
    "status" "TaskStatus" NOT NULL DEFAULT 'TODO',
    "priority" "TaskPriority" NOT NULL DEFAULT 'MEDIUM',
    "due_date" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
 
    CONSTRAINT "tasks_pkey" PRIMARY KEY ("id")
);
 
-- CreateTable
CREATE TABLE "tags" (
    "id" TEXT NOT NULL,
    "name" VARCHAR(50) NOT NULL,
 
    CONSTRAINT "tags_pkey" PRIMARY KEY ("id")
);
 
-- CreateTable (join table)
CREATE TABLE "_TagToTask" (
    "A" TEXT NOT NULL,
    "B" TEXT NOT NULL,
 
    CONSTRAINT "_TagToTask_AB_pkey" PRIMARY KEY ("A","B")
);
 
-- CreateIndex
CREATE INDEX "tasks_status_idx" ON "tasks"("status");
CREATE INDEX "tasks_priority_idx" ON "tasks"("priority");
CREATE INDEX "tasks_created_at_idx" ON "tasks"("created_at");
CREATE UNIQUE INDEX "tags_name_key" ON "tags"("name");

Migration Workflow

The development and production migration workflows are different:

Development (prisma migrate dev): Generates migration SQL, applies it, regenerates client. Interactive — can reset the database if needed.

Production (prisma migrate deploy): Applies pending migrations only. Non-interactive, safe for CI/CD. Never generates new migrations.

Adding a New Field

Later, when you need to add a field:

model Task {
  // ... existing fields
  completedAt DateTime? @map("completed_at")
}

npx prisma migrate dev --name add-completed-at

Prisma diffs your schema against the database and generates only the necessary ALTER statement:

ALTER TABLE "tasks" ADD COLUMN "completed_at" TIMESTAMP(3);

5. Prisma Client Singleton

Never create multiple Prisma Client instances. In development with hot reload, every file change creates a new connection pool. This exhausts database connections fast.

// src/lib/prisma.ts
import { PrismaClient } from "@prisma/client";
 
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};
 
export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log:
      process.env.NODE_ENV === "development"
        ? ["query", "error", "warn"]
        : ["error"],
  });
 
if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}

Why globalThis? In development, module-level variables get recreated on hot reload. globalThis persists across reloads, so we reuse the same Prisma instance.

Logging levels:

Development: Log queries (for debugging), errors, and warnings
Production: Log errors only (performance)

6. Update the Repository Layer

Here's the payoff. We replace the in-memory Map with Prisma queries. The interface stays identical — the service layer doesn't know or care that we switched to a real database.

Before (In-Memory)

// Old: src/repositories/task.repository.ts
const tasks = new Map<string, Task>();
 
export class TaskRepository {
  async findMany(query: ListTasksQuery) {
    let results = Array.from(tasks.values());
    // Manual filtering, sorting, pagination...
    return { tasks: results, total };
  }
 
  async create(data: CreateTaskInput) {
    const task = { id: uuidv4(), ...data, createdAt: new Date(), updatedAt: new Date() };
    tasks.set(task.id, task);
    return task;
  }
}

After (Prisma)

// src/repositories/task.repository.ts
import { prisma } from "@/lib/prisma.js";
import type { Task, Prisma } from "@prisma/client";
import type { CreateTaskInput, UpdateTaskInput, ListTasksQuery } from "@/schemas/task.schema.js";
 
export class TaskRepository {
  async findMany(query: ListTasksQuery): Promise<{ tasks: Task[]; total: number }> {
    const where: Prisma.TaskWhereInput = {};
 
    if (query.status) {
      where.status = query.status;
    }
    if (query.priority) {
      where.priority = query.priority;
    }
 
    const [tasks, total] = await Promise.all([
      prisma.task.findMany({
        where,
        orderBy: { [query.sortBy]: query.order },
        skip: (query.page - 1) * query.limit,
        take: query.limit,
        include: { tags: true },
      }),
      prisma.task.count({ where }),
    ]);
 
    return { tasks, total };
  }
 
  async findById(id: string): Promise<Task | null> {
    return prisma.task.findUnique({
      where: { id },
      include: { tags: true },
    });
  }
 
  async create(data: CreateTaskInput): Promise<Task> {
    return prisma.task.create({
      data: {
        title: data.title,
        description: data.description,
        status: data.status ?? "TODO",
        priority: data.priority ?? "MEDIUM",
        dueDate: data.dueDate,
        tags: data.tags
          ? {
              connectOrCreate: data.tags.map((tag) => ({
                where: { name: tag },
                create: { name: tag },
              })),
            }
          : undefined,
      },
      include: { tags: true },
    });
  }
 
  async update(id: string, data: UpdateTaskInput): Promise<Task | null> {
    try {
      return await prisma.task.update({
        where: { id },
        data: {
          ...data,
          dueDate: data.dueDate,
          tags: data.tags
            ? {
                set: [],
                connectOrCreate: data.tags.map((tag) => ({
                  where: { name: tag },
                  create: { name: tag },
                })),
              }
            : undefined,
        },
        include: { tags: true },
      });
    } catch (error) {
      if (
        error instanceof Error &&
        "code" in error &&
        (error as { code: string }).code === "P2025"
      ) {
        return null;
      }
      throw error;
    }
  }
 
  async delete(id: string): Promise<boolean> {
    try {
      await prisma.task.delete({ where: { id } });
      return true;
    } catch (error) {
      if (
        error instanceof Error &&
        "code" in error &&
        (error as { code: string }).code === "P2025"
      ) {
        return false;
      }
      throw error;
    }
  }
 
  async existsByTitle(title: string, excludeId?: string): Promise<boolean> {
    const count = await prisma.task.count({
      where: {
        title,
        ...(excludeId ? { id: { not: excludeId } } : {}),
      },
    });
    return count > 0;
  }
}

What Changed, What Didn't

Layer	Changed?	Why
Repository	✅ Yes	Swapped `Map` operations for Prisma queries
Service	❌ No	Still calls the same repository methods
Controller	❌ No	Still calls the same service methods
Routes	❌ No	Same endpoints, same middleware
Schemas	❌ No	Zod still validates incoming requests
Error handling	❌ No	Same custom errors, same middleware

This is exactly why we built the layered architecture. The database is an implementation detail that lives behind the repository interface.

Key Prisma Patterns

Parallel queries with Promise.all:

const [tasks, total] = await Promise.all([
  prisma.task.findMany({ where, orderBy, skip, take }),
  prisma.task.count({ where }),
]);

Two queries run concurrently instead of sequentially. Same result, half the time.

connectOrCreate for tags:

tags: {
  connectOrCreate: data.tags.map((tag) => ({
    where: { name: tag },
    create: { name: tag },
  })),
}

If the tag exists, link it. If not, create it and link it. One operation handles both cases.

Error code P2025:

if ((error as { code: string }).code === "P2025") {
  return null; // Record not found
}

Prisma throws P2025 when update or delete targets a non-existent record. We catch it and return null so the service layer handles it with our domain errors.

7. Update the Zod Schemas

Our Zod schemas need minor updates to match the Prisma enums and add tag support:

// src/schemas/task.schema.ts
import { z } from "zod";
 
// Match Prisma enum values (uppercase)
export const taskStatusEnum = z.enum(["TODO", "IN_PROGRESS", "DONE"]);
export const taskPriorityEnum = z.enum(["LOW", "MEDIUM", "HIGH"]);
 
export const createTaskSchema = z.object({
  body: z.object({
    title: z.string().min(1).max(255),
    description: z.string().optional(),
    status: taskStatusEnum.optional(),
    priority: taskPriorityEnum.optional(),
    dueDate: z.coerce.date().optional(),
    tags: z.array(z.string().min(1).max(50)).optional(),
  }),
});
 
export const updateTaskSchema = z.object({
  params: z.object({
    id: z.string().uuid(),
  }),
  body: z.object({
    title: z.string().min(1).max(255).optional(),
    description: z.string().optional(),
    status: taskStatusEnum.optional(),
    priority: taskPriorityEnum.optional(),
    dueDate: z.coerce.date().optional(),
    tags: z.array(z.string().min(1).max(50)).optional(),
  }),
});
 
export const getTaskSchema = z.object({
  params: z.object({
    id: z.string().uuid(),
  }),
});
 
export const listTasksSchema = z.object({
  query: z.object({
    page: z.coerce.number().int().positive().default(1),
    limit: z.coerce.number().int().positive().max(100).default(20),
    status: taskStatusEnum.optional(),
    priority: taskPriorityEnum.optional(),
    sortBy: z.enum(["createdAt", "title", "priority", "dueDate"]).default("createdAt"),
    order: z.enum(["asc", "desc"]).default("desc"),
  }),
});
 
export type CreateTaskInput = z.infer<typeof createTaskSchema>["body"];
export type UpdateTaskInput = z.infer<typeof updateTaskSchema>["body"];
export type ListTasksQuery = z.infer<typeof listTasksSchema>["query"];

Key changes from the previous version:

Enum values are uppercase (TODO instead of todo) to match PostgreSQL enum conventions
Added dueDate field with z.coerce.date() — accepts both date strings and Date objects
Added tags array — strings that map to the Tag model
Added dueDate as a sortable field

8. Transactions

Some operations need to be atomic — they either all succeed or all fail. Prisma makes transactions straightforward.

Example: Bulk Task Assignment

Imagine assigning multiple tasks to "IN_PROGRESS" status with a new tag:

// src/repositories/task.repository.ts (add this method)
async bulkUpdateStatus(
  ids: string[],
  status: string,
  tag?: string,
): Promise<number> {
  const result = await prisma.$transaction(async (tx) => {
    // First, verify all tasks exist
    const existingCount = await tx.task.count({
      where: { id: { in: ids } },
    });
 
    if (existingCount !== ids.length) {
      throw new Error(
        `Expected ${ids.length} tasks, found ${existingCount}`,
      );
    }
 
    // Update all tasks
    const updated = await tx.task.updateMany({
      where: { id: { in: ids } },
      data: {
        status: status as "TODO" | "IN_PROGRESS" | "DONE",
        updatedAt: new Date(),
      },
    });
 
    // Add tag if provided
    if (tag) {
      const tagRecord = await tx.tag.upsert({
        where: { name: tag },
        update: {},
        create: { name: tag },
      });
 
      for (const id of ids) {
        await tx.task.update({
          where: { id },
          data: {
            tags: { connect: { id: tagRecord.id } },
          },
        });
      }
    }
 
    return updated.count;
  });
 
  return result;
}

How $transaction works:

Prisma opens a database transaction
All operations inside the callback use the tx client (not the global prisma)
If any operation throws, the entire transaction rolls back
If the callback completes, everything commits

When to use transactions:

Batch operations — update 50 tasks, all or nothing
Multi-table changes — create a task AND add tags atomically
Business rules requiring consistency — check + update must be atomic
Financial operations — debit one account, credit another

When you don't need them:

Single create, update, or delete — these are already atomic
Read-only queries — no data changes to roll back
Independent operations — tasks that don't depend on each other

9. Connection Pooling

Every Prisma Client maintains a connection pool. This is critical for production — opening a new database connection takes 20-50ms. Pooling reuses connections.

Default Behavior

Prisma uses a default pool size based on the formula:

pool_size = num_physical_cpus * 2 + 1

On a 4-core machine, that's 9 connections. For most applications, this is fine.

Configuring the Pool

For high-traffic applications, configure via the connection URL:

# .env
DATABASE_URL="postgresql://user:pass@host:5432/db?schema=public&connection_limit=20&pool_timeout=10"

Parameter	Default	Description
`connection_limit`	`num_cpus * 2 + 1`	Max connections in the pool
`pool_timeout`	`10` (seconds)	How long to wait for a free connection

Serverless Environments

If you deploy to serverless (Vercel, AWS Lambda), each function instance creates its own pool. With hundreds of concurrent functions, you'll exhaust database connections.

Use Prisma Accelerate or an external pooler like PgBouncer:

# With PgBouncer
DATABASE_URL="postgresql://user:pass@pgbouncer-host:6432/db?pgbouncer=true"

The pgbouncer=true flag tells Prisma to use compatible query modes.

Graceful Shutdown

Always disconnect on shutdown to release connections:

// src/index.ts
import { prisma } from "@/lib/prisma.js";
 
process.on("SIGINT", async () => {
  await prisma.$disconnect();
  process.exit(0);
});
 
process.on("SIGTERM", async () => {
  await prisma.$disconnect();
  process.exit(0);
});

10. Seeding

Seeds populate your database with initial data. Essential for:

Onboarding new developers (clone, seed, start coding)
Resetting development databases
Creating test scenarios

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";
 
const prisma = new PrismaClient();
 
async function main() {
  console.log("🌱 Seeding database...");
 
  // Clean existing data
  await prisma.task.deleteMany();
  await prisma.tag.deleteMany();
 
  // Create tags
  const tags = await Promise.all(
    ["backend", "frontend", "devops", "bug", "feature"].map((name) =>
      prisma.tag.create({ data: { name } }),
    ),
  );
 
  // Create tasks
  const tasks = [
    {
      title: "Set up CI/CD pipeline",
      description: "Configure GitHub Actions for automated testing and deployment",
      status: "TODO" as const,
      priority: "HIGH" as const,
      tags: { connect: [{ id: tags[2]!.id }, { id: tags[4]!.id }] },
    },
    {
      title: "Fix login redirect bug",
      description: "Users are redirected to 404 after successful login",
      status: "IN_PROGRESS" as const,
      priority: "HIGH" as const,
      tags: { connect: [{ id: tags[1]!.id }, { id: tags[3]!.id }] },
    },
    {
      title: "Add dark mode support",
      description: "Implement theme switching with system preference detection",
      status: "TODO" as const,
      priority: "MEDIUM" as const,
      tags: { connect: [{ id: tags[1]!.id }, { id: tags[4]!.id }] },
    },
    {
      title: "Optimize database queries",
      description: "Add indexes and review N+1 query patterns",
      status: "TODO" as const,
      priority: "MEDIUM" as const,
      tags: { connect: [{ id: tags[0]!.id }] },
    },
    {
      title: "Write API documentation",
      description: "Document all endpoints with request/response examples",
      status: "DONE" as const,
      priority: "LOW" as const,
      tags: { connect: [{ id: tags[0]!.id }] },
    },
  ];
 
  for (const task of tasks) {
    await prisma.task.create({ data: task });
  }
 
  console.log(`✅ Created ${tags.length} tags and ${tasks.length} tasks`);
}
 
main()
  .catch((e) => {
    console.error("❌ Seed failed:", e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Add the seed command to package.json:

{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Run it:

npx prisma db seed

Tip: prisma migrate reset automatically runs the seed script after resetting. Handy for starting fresh during development.

11. Testing Database Code

Testing database code requires a real database — mocking Prisma defeats the purpose. We'll use a test database with proper setup and teardown.

Install Test Dependencies

npm install -D vitest

Configure Vitest

// vitest.config.ts
import { defineConfig } from "vitest/config";
import path from "path";
 
export default defineConfig({
  test: {
    globals: true,
    environment: "node",
    setupFiles: ["./src/__tests__/setup.ts"],
    include: ["src/**/*.test.ts"],
  },
  resolve: {
    alias: {
      "@": path.resolve(__dirname, "./src"),
    },
  },
});

Test Setup

// src/__tests__/setup.ts
import { prisma } from "@/lib/prisma.js";
import { beforeEach, afterAll } from "vitest";
 
// Clean database before each test
beforeEach(async () => {
  await prisma.task.deleteMany();
  await prisma.tag.deleteMany();
});
 
// Disconnect after all tests
afterAll(async () => {
  await prisma.$disconnect();
});

Repository Tests

// src/__tests__/repositories/task.repository.test.ts
import { describe, it, expect } from "vitest";
import { TaskRepository } from "@/repositories/task.repository.js";
 
const repository = new TaskRepository();
 
describe("TaskRepository", () => {
  describe("create", () => {
    it("should create a task with default values", async () => {
      const task = await repository.create({
        title: "Test task",
      });
 
      expect(task.id).toBeDefined();
      expect(task.title).toBe("Test task");
      expect(task.status).toBe("TODO");
      expect(task.priority).toBe("MEDIUM");
      expect(task.createdAt).toBeInstanceOf(Date);
    });
 
    it("should create a task with tags", async () => {
      const task = await repository.create({
        title: "Tagged task",
        tags: ["backend", "feature"],
      });
 
      expect(task.tags).toHaveLength(2);
      expect(task.tags.map((t) => t.name)).toContain("backend");
      expect(task.tags.map((t) => t.name)).toContain("feature");
    });
 
    it("should reuse existing tags", async () => {
      await repository.create({
        title: "First task",
        tags: ["backend"],
      });
 
      const second = await repository.create({
        title: "Second task",
        tags: ["backend"],
      });
 
      // Same tag reused, not duplicated
      expect(second.tags).toHaveLength(1);
      expect(second.tags[0]!.name).toBe("backend");
    });
  });
 
  describe("findMany", () => {
    it("should filter by status", async () => {
      await repository.create({ title: "Todo task", status: "TODO" });
      await repository.create({ title: "Done task", status: "DONE" });
 
      const result = await repository.findMany({
        page: 1,
        limit: 20,
        status: "TODO",
        sortBy: "createdAt",
        order: "desc",
      });
 
      expect(result.tasks).toHaveLength(1);
      expect(result.tasks[0]!.title).toBe("Todo task");
      expect(result.total).toBe(1);
    });
 
    it("should paginate results", async () => {
      for (let i = 0; i < 5; i++) {
        await repository.create({ title: `Task ${i}` });
      }
 
      const page1 = await repository.findMany({
        page: 1,
        limit: 2,
        sortBy: "createdAt",
        order: "asc",
      });
 
      const page2 = await repository.findMany({
        page: 2,
        limit: 2,
        sortBy: "createdAt",
        order: "asc",
      });
 
      expect(page1.tasks).toHaveLength(2);
      expect(page2.tasks).toHaveLength(2);
      expect(page1.total).toBe(5);
      expect(page1.tasks[0]!.title).not.toBe(page2.tasks[0]!.title);
    });
  });
 
  describe("update", () => {
    it("should update task fields", async () => {
      const task = await repository.create({ title: "Original" });
      const updated = await repository.update(task.id, {
        title: "Updated",
        status: "DONE",
      });
 
      expect(updated!.title).toBe("Updated");
      expect(updated!.status).toBe("DONE");
      expect(updated!.updatedAt.getTime()).toBeGreaterThan(
        task.updatedAt.getTime(),
      );
    });
 
    it("should return null for non-existent task", async () => {
      const result = await repository.update(
        "00000000-0000-0000-0000-000000000000",
        { title: "Nope" },
      );
      expect(result).toBeNull();
    });
 
    it("should replace tags on update", async () => {
      const task = await repository.create({
        title: "Tagged",
        tags: ["backend", "bug"],
      });
 
      const updated = await repository.update(task.id, {
        tags: ["frontend", "feature"],
      });
 
      expect(updated!.tags).toHaveLength(2);
      expect(updated!.tags.map((t) => t.name)).toContain("frontend");
      expect(updated!.tags.map((t) => t.name)).not.toContain("backend");
    });
  });
 
  describe("delete", () => {
    it("should delete existing task", async () => {
      const task = await repository.create({ title: "To delete" });
      const result = await repository.delete(task.id);
 
      expect(result).toBe(true);
 
      const found = await repository.findById(task.id);
      expect(found).toBeNull();
    });
 
    it("should return false for non-existent task", async () => {
      const result = await repository.delete(
        "00000000-0000-0000-0000-000000000000",
      );
      expect(result).toBe(false);
    });
  });
 
  describe("existsByTitle", () => {
    it("should find existing title", async () => {
      await repository.create({ title: "Unique title" });
      const exists = await repository.existsByTitle("Unique title");
      expect(exists).toBe(true);
    });
 
    it("should exclude specific id", async () => {
      const task = await repository.create({ title: "My title" });
      const exists = await repository.existsByTitle("My title", task.id);
      expect(exists).toBe(false);
    });
  });
});

Running Tests

Add test scripts to package.json:

{
  "scripts": {
    "test": "vitest run",
    "test:watch": "vitest"
  }
}

Set up a test database:

# Create test database
createdb taskdb_test
 
# Set test DATABASE_URL
export DATABASE_URL="postgresql://postgres:password@localhost:5432/taskdb_test?schema=public"
 
# Apply migrations to test database
npx prisma migrate deploy
 
# Run tests
npm test

Best practice: Use a separate database for tests. Never run tests against your development database — deleteMany() in setup will wipe your data.

12. Error Handling for Database Operations

Prisma throws specific error codes that map to meaningful HTTP responses. Add a Prisma error handler to your middleware:

// src/middleware/error-handler.ts (updated)
import type { Request, Response, NextFunction } from "express";
import { Prisma } from "@prisma/client";
import { AppError } from "@/utils/errors.js";
 
export function errorHandler(
  err: Error,
  _req: Request,
  res: Response,
  _next: NextFunction,
) {
  // Handle Prisma errors
  if (err instanceof Prisma.PrismaClientKnownRequestError) {
    switch (err.code) {
      case "P2002": {
        const target = (err.meta?.target as string[])?.join(", ");
        res.status(409).json({
          error: {
            code: "CONFLICT",
            message: `Unique constraint violation on: ${target}`,
          },
        });
        return;
      }
      case "P2025":
        res.status(404).json({
          error: {
            code: "NOT_FOUND",
            message: "Record not found",
          },
        });
        return;
      case "P2003":
        res.status(400).json({
          error: {
            code: "FOREIGN_KEY_VIOLATION",
            message: "Referenced record does not exist",
          },
        });
        return;
    }
  }
 
  // Handle our custom errors
  if (err instanceof AppError) {
    res.status(err.statusCode).json({
      error: {
        code: err.code ?? "APP_ERROR",
        message: err.message,
      },
    });
    return;
  }
 
  // Unknown errors
  console.error("Unhandled error:", err);
  res.status(500).json({
    error: {
      code: "INTERNAL_ERROR",
      message:
        process.env.NODE_ENV === "production"
          ? "An unexpected error occurred"
          : err.message,
    },
  });
}

Common Prisma error codes:

Code	Meaning	HTTP Status
`P2002`	Unique constraint violation	409 Conflict
`P2025`	Record not found	404 Not Found
`P2003`	Foreign key constraint failure	400 Bad Request
`P2014`	Required relation violation	400 Bad Request

13. Testing the Complete API

Start the server and test with curl:

npm run dev

Create a Task with Tags

curl -s -X POST http://localhost:3000/api/v1/tasks \
  -H "Content-Type: application/json" \
  -d '{
    "title": "Integrate Prisma ORM",
    "description": "Replace in-memory store with PostgreSQL",
    "priority": "HIGH",
    "tags": ["backend", "database"]
  }' | jq

{
  "data": {
    "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "title": "Integrate Prisma ORM",
    "description": "Replace in-memory store with PostgreSQL",
    "status": "TODO",
    "priority": "HIGH",
    "dueDate": null,
    "createdAt": "2026-03-10T10:00:00.000Z",
    "updatedAt": "2026-03-10T10:00:00.000Z",
    "tags": [
      { "id": "...", "name": "backend" },
      { "id": "...", "name": "database" }
    ]
  }
}

List Tasks with Filters

curl -s "http://localhost:3000/api/v1/tasks?status=TODO&priority=HIGH&sortBy=createdAt&order=desc" | jq

Update a Task

curl -s -X PATCH http://localhost:3000/api/v1/tasks/a1b2c3d4-e5f6-7890-abcd-ef1234567890 \
  -H "Content-Type: application/json" \
  -d '{
    "status": "IN_PROGRESS",
    "tags": ["backend", "database", "prisma"]
  }' | jq

Unique Constraint Error

curl -s -X POST http://localhost:3000/api/v1/tasks \
  -H "Content-Type: application/json" \
  -d '{"title": "Integrate Prisma ORM"}' | jq

{
  "error": {
    "code": "CONFLICT",
    "message": "Task with title 'Integrate Prisma ORM' already exists"
  }
}

14. Architecture Overview

Here's how all the layers work together after the database integration:

Layer	Responsibility	What Changed
Middleware	Parse body, validate, log, handle errors	Added Prisma error handling
Controller	HTTP ↔ service translation	Nothing
Service	Business rules, orchestration	Nothing
Repository	Data access abstraction	`Map` → Prisma queries
Prisma Client	Query building, connection pooling	New layer
PostgreSQL	Data storage, constraints, indexes	New

Summary

Topic	What We Did
Prisma Setup	Schema-first design with enums, relations, indexes
Migrations	Generate, apply, and manage database changes with version control
Repository Swap	Replaced in-memory Map with Prisma queries — zero changes to service/controller
Transactions	Atomic operations with `$transaction` for bulk updates
Connection Pooling	Singleton pattern, pool configuration, serverless considerations
Seeding	Reproducible development data with `prisma db seed`
Testing	Integration tests with real database, proper setup/teardown
Error Handling	Prisma error codes mapped to HTTP responses

Key takeaways:

Layered architecture pays off — swapping the data layer required changes to exactly one file
Prisma types flow through your codebase — schema changes trigger compile-time errors everywhere
Migrations are version-controlled — your database schema evolves with your code
Test with real databases — mocking ORM calls hides real bugs
Connection pooling is critical — especially in serverless environments

This completes the TypeScript Full-Stack Development series. You now have a production-grade TypeScript backend with:

Professional project setup with ESLint, Prettier, and fast builds
Type-safe REST API with Zod validation and OpenAPI docs
Real database integration with Prisma, migrations, and tested repository layer
A monorepo structure to tie frontend and backend together

Series: TypeScript Full-Stack Development
Previous: TypeScript: Build REST API with Express.js