NestJS — Database & API

This lecture replaces the in-memory arrays from lecture 70 with Prisma ORM and PostgreSQL. We also add Swagger API documentation and Docker for local development. By the end, the task-api-nest project has persistent storage, typed database queries, and interactive API docs.

Lecture roadmap:

Prisma setup — why Prisma, installation, schema definition
Models & migrations — User, Task, Category, RefreshToken entities
Database integration — PrismaService, refactored CRUD services
Relations & filtering — includes, pagination, query parameters
Swagger — automatic API documentation
Docker — PostgreSQL for local development

Prisma Setup

Why Prisma

In lecture 67, you wrote raw SQL with better-sqlite3 — parameterized queries, manual type mapping, hand-written migrations. Prisma automates all of that:

Approach	Query style	Type safety	Migrations	Used in
Raw SQL (`better-sqlite3`)	Write SQL strings	None — you cast results	Hand-written `.sql` files	Lecture 67
TypeORM	Active Record or Query Builder	Partial — decorators on classes	Auto-generated from entities	Alternative for A6
Prisma	Typed client methods	Full — generated from schema	Auto-generated from schema changes	This lecture

Prisma's approach: define your data model in a schema file, Prisma generates a fully typed TypeScript client. You never write SQL for CRUD — Prisma generates it. You still can write raw SQL for complex queries.

info

The assignment (A6) allows either Prisma or TypeORM. This lecture uses Prisma because it has the strongest TypeScript integration and the schema-first approach is easier to reason about. If you prefer TypeORM (decorator-based, similar to Java/C# ORMs), the NestJS documentation covers it well.

Installation

npm install @prisma/client
npm install -D prisma

Initialize Prisma — this creates a prisma/ directory with a schema file:

npx prisma init

This generates:

prisma/
└── schema.prisma    # Data model definition
.env                 # DATABASE_URL added automatically

Schema & Models

The schema file defines your database structure. Prisma uses its own DSL (not TypeScript), but the syntax is straightforward:

// 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 User {
  id           String   @id @default(uuid())
  email        String   @unique
  passwordHash String   @map("password_hash")
  firstName    String   @map("first_name")
  lastName     String   @map("last_name")
  createdAt    DateTime @default(now()) @map("created_at")

  tasks         Task[]
  categories    Category[]
  refreshTokens RefreshToken[]

  @@map("users")
}

model Task {
  id          String       @id @default(uuid())
  title       String
  description String       @default("")
  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")

  userId     String   @map("user_id")
  user       User     @relation(fields: [userId], references: [id])
  categoryId String?  @map("category_id")
  category   Category? @relation(fields: [categoryId], references: [id])

  @@map("tasks")
}

model Category {
  id        String   @id @default(uuid())
  name      String
  createdAt DateTime @default(now()) @map("created_at")

  userId String @map("user_id")
  user   User   @relation(fields: [userId], references: [id])
  tasks  Task[]

  @@map("categories")
}

model RefreshToken {
  id        String   @id @default(uuid())
  token     String   @unique
  expiresAt DateTime @map("expires_at")
  createdAt DateTime @default(now()) @map("created_at")

  userId String @map("user_id")
  user   User   @relation(fields: [userId], references: [id])

  @@map("refresh_tokens")
}

Key Prisma conventions:

@id @default(uuid()) — UUID primary keys (same as lecture 67)
@map("snake_case") — Prisma uses camelCase in TypeScript, snake_case in the database
@@map("table_name") — explicit table names
Relations — @relation(fields: [userId], references: [id]) defines foreign keys
@updatedAt — Prisma automatically sets this on update
Enums — Prisma creates PostgreSQL enums; values match our existing types from lecture 70

Common mistake

Forgetting @map on fields. Without it, Prisma uses camelCase column names (passwordHash, firstName). This works, but breaks conventions if you switch ORMs later or query the database directly. Always use @map for snake_case columns and @@map for table names.

Checkpoint 1 — Prisma schema

The schema defines all four models with relations. No database yet — the next section creates one and runs migrations.

Migrations & Database

PostgreSQL with Docker

You need a running PostgreSQL instance. The simplest way for development:

# docker-compose.yml — development database only
services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: taskuser
      POSTGRES_PASSWORD: taskpass
      POSTGRES_DB: taskdb
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U taskuser -d taskdb"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  pgdata:

docker compose up -d

Set the connection string in .env:

# .env
DATABASE_URL="postgresql://taskuser:taskpass@localhost:5432/taskdb"
PORT=3000
CORS_ORIGIN=http://localhost:5173,http://localhost:3001

info

Lecture 67 used SQLite for development (zero setup, single file). With NestJS + Prisma, we move directly to PostgreSQL — the Docker container takes 5 seconds to start and gives you the same database in dev and production. If you cannot run Docker, Prisma also supports SQLite — change provider = "postgresql" to provider = "sqlite" and set DATABASE_URL = "file:./dev.db".

Running migrations

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

This does three things:

Compares your schema to the current database state
Generates a SQL migration file in prisma/migrations/
Applies the migration and regenerates the Prisma Client

# View the generated SQL
cat prisma/migrations/*/migration.sql

The generated migration creates all tables, columns, indexes, and enums. Compare this to the hand-written migrations in lecture 67 — Prisma generates them from your schema changes.

# Apply migrations in production/CI (non-interactive)
npx prisma migrate deploy

# Reset the database (drops all data — development only)
npx prisma migrate reset

Prisma Client

After running migrate dev, the Prisma Client is generated automatically. It provides fully typed methods for every model:

// Auto-generated — this is what you get for free
prisma.task.findMany()       // returns Promise<Task[]>
prisma.task.findUnique()     // returns Promise<Task | null>
prisma.task.create()         // returns Promise<Task>
prisma.task.update()         // returns Promise<Task>
prisma.task.delete()         // returns Promise<Task>

Every method is typed based on your schema — TypeScript will autocomplete field names, reject invalid values, and type-check relations.

Prisma Studio

A visual database browser:

npx prisma studio

Opens http://localhost:5555 — view and edit data directly. Useful for debugging.

PrismaService

Create a NestJS service that wraps the Prisma Client with proper lifecycle management:

// src/prisma/prisma.service.ts
import { Injectable, OnModuleInit, OnModuleDestroy } from "@nestjs/common";
import { PrismaClient } from "@prisma/client";

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}

// src/prisma/prisma.module.ts
import { Global, Module } from "@nestjs/common";
import { PrismaService } from "./prisma.service";

@Global() // available in all modules without importing
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

Import in the root module:

// src/app.module.ts
import { Module } from "@nestjs/common";
import { ConfigModule } from "@nestjs/config";
import { PrismaModule } from "./prisma/prisma.module";
import { TasksModule } from "./tasks/tasks.module";
import { CategoriesModule } from "./categories/categories.module";

@Module({
  imports: [
    ConfigModule.forRoot({ isGlobal: true }),
    PrismaModule,
    TasksModule,
    CategoriesModule,
  ],
})
export class AppModule {}

Why a dedicated service instead of importing PrismaClient directly?

Lifecycle management — onModuleInit connects when the app starts, onModuleDestroy disconnects gracefully on shutdown
Single connection pool — one PrismaClient instance shared across all services
Testability — you can mock PrismaService in unit tests (lecture 72)

Repository Layer

Tasks service with Prisma

Replace the in-memory array from lecture 70 with Prisma queries:

// src/tasks/tasks.service.ts
import { Injectable, NotFoundException } from "@nestjs/common";
import { PrismaService } from "../prisma/prisma.service";
import { CreateTaskDto } from "./dto/create-task.dto";
import { UpdateTaskDto } from "./dto/update-task.dto";
import { PatchTaskDto } from "./dto/patch-task.dto";
import { Task, TaskStatus, TaskPriority } from "@prisma/client";

@Injectable()
export class TasksService {
  constructor(private readonly prisma: PrismaService) {}

  async findAll(userId: string, status?: string, priority?: string): Promise<Task[]> {
    return this.prisma.task.findMany({
      where: {
        userId,
        ...(status && Object.values(TaskStatus).includes(status as TaskStatus) && { status: status as TaskStatus }),
        ...(priority && Object.values(TaskPriority).includes(priority as TaskPriority) && { priority: priority as TaskPriority }),
      },
      include: { category: true },
      orderBy: { createdAt: "desc" },
    });
  }

  async findOne(id: string, userId: string): Promise<Task> {
    const task = await this.prisma.task.findUnique({
      where: { id },
      include: { category: true },
    });

    if (!task || task.userId !== userId) {
      throw new NotFoundException("Task not found");
    }

    return task;
  }

  async create(dto: CreateTaskDto, userId: string): Promise<Task> {
    return this.prisma.task.create({
      data: {
        title: dto.title,
        description: dto.description ?? "",
        status: dto.status ?? TaskStatus.todo,
        priority: dto.priority ?? TaskPriority.medium,
        dueDate: dto.dueDate ? new Date(dto.dueDate) : null,
        categoryId: dto.categoryId ?? null,
        userId,
      },
      include: { category: true },
    });
  }

  async update(id: string, dto: UpdateTaskDto, userId: string): Promise<Task> {
    // Verify ownership
    const existing = await this.prisma.task.findUnique({ where: { id } });
    if (!existing || existing.userId !== userId) {
      throw new NotFoundException("Task not found");
    }

    // Verify category ownership if changing category
    if (dto.categoryId) {
      const category = await this.prisma.category.findUnique({
        where: { id: dto.categoryId },
      });
      if (!category || category.userId !== userId) {
        throw new NotFoundException("Category not found");
      }
    }

    return this.prisma.task.update({
      where: { id },
      data: {
        title: dto.title,
        description: dto.description,
        status: dto.status,
        priority: dto.priority,
        dueDate: dto.dueDate ? new Date(dto.dueDate) : null,
        categoryId: dto.categoryId,
      },
      include: { category: true },
    });
  }

  async partialUpdate(id: string, dto: PatchTaskDto, userId: string): Promise<Task> {
    const existing = await this.prisma.task.findUnique({ where: { id } });
    if (!existing || existing.userId !== userId) {
      throw new NotFoundException("Task not found");
    }

    if (dto.categoryId) {
      const category = await this.prisma.category.findUnique({
        where: { id: dto.categoryId },
      });
      if (!category || category.userId !== userId) {
        throw new NotFoundException("Category not found");
      }
    }

    return this.prisma.task.update({
      where: { id },
      data: {
        ...(dto.title !== undefined && { title: dto.title }),
        ...(dto.description !== undefined && { description: dto.description }),
        ...(dto.status !== undefined && { status: dto.status }),
        ...(dto.priority !== undefined && { priority: dto.priority }),
        ...(dto.dueDate !== undefined && { dueDate: dto.dueDate ? new Date(dto.dueDate) : null }),
        ...(dto.categoryId !== undefined && { categoryId: dto.categoryId }),
      },
      include: { category: true },
    });
  }

  async remove(id: string, userId: string): Promise<void> {
    const existing = await this.prisma.task.findUnique({ where: { id } });
    if (!existing || existing.userId !== userId) {
      throw new NotFoundException("Task not found");
    }

    await this.prisma.task.delete({ where: { id } });
  }
}

PatchTaskDto follows the same pattern as lecture 70, but imports enums from @prisma/client instead of from ./create-task.dto.

Key changes from the in-memory version:

All methods are now async — database operations return promises
User scoping built-in — every query filters by userId (same principle as lecture 67, but Prisma makes it cleaner)
include: { category: true } eagerly loads the related category
No manual ID generation — Prisma's @default(uuid()) handles it

Common mistake

The Prisma schema has a foreign key from Task.userId to User.id. Unlike the in-memory arrays in lecture 70, PostgreSQL will reject create() calls if the referenced user does not exist. You need a real user row before you can create tasks or categories. Use Prisma's seed script to insert a dev user:

Create a seed file:

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  await prisma.user.upsert({
    where: { email: "dev@test.com" },
    update: {},
    create: {
      id: "dev-user-id",
      email: "dev@test.com",
      passwordHash: "not-a-real-hash", // replaced with bcrypt hash after auth (lecture 72)
      firstName: "Dev",
      lastName: "User",
    },
  });
  console.log("Seed user created: dev@test.com (id: dev-user-id)");
}

main()
  .finally(() => prisma.$disconnect());

Add the seed command to package.json:

{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

npx prisma db seed

Update the controller to pass the seed user's ID. Lecture 72 replaces this with the authenticated user:

// src/tasks/tasks.controller.ts
import {
  Controller, Get, Post, Put, Patch, Delete,
  Param, Query, Body, HttpCode, HttpStatus,
} from "@nestjs/common";
import { TasksService } from "./tasks.service";
import { CreateTaskDto } from "./dto/create-task.dto";
import { UpdateTaskDto } from "./dto/update-task.dto";
import { PatchTaskDto } from "./dto/patch-task.dto";

@Controller("api/v1/tasks")
export class TasksController {
  constructor(private readonly tasksService: TasksService) {}

  @Get()
  findAll(@Query("status") status?: string, @Query("priority") priority?: string) {
    // "dev-user-id" replaced with @CurrentUser in lecture 72
    return this.tasksService.findAll("dev-user-id", status, priority);
  }

  @Get(":id")
  findOne(@Param("id") id: string) {
    return this.tasksService.findOne(id, "dev-user-id");
  }

  @Post()
  @HttpCode(HttpStatus.CREATED)
  create(@Body() dto: CreateTaskDto) {
    return this.tasksService.create(dto, "dev-user-id");
  }

  @Put(":id")
  update(@Param("id") id: string, @Body() dto: UpdateTaskDto) {
    return this.tasksService.update(id, dto, "dev-user-id");
  }

  @Patch(":id")
  partialUpdate(@Param("id") id: string, @Body() dto: PatchTaskDto) {
    return this.tasksService.partialUpdate(id, dto, "dev-user-id");
  }

  @Delete(":id")
  @HttpCode(HttpStatus.NO_CONTENT)
  remove(@Param("id") id: string) {
    return this.tasksService.remove(id, "dev-user-id");
  }
}

Categories service with Prisma

Same pattern. Note the UpdateCategoryDto — categories only have a name field, but using a separate DTO keeps the pattern consistent and allows adding fields later:

// src/categories/dto/update-category.dto.ts
import { IsString, MinLength } from "class-validator";
import { ApiProperty } from "@nestjs/swagger";

export class UpdateCategoryDto {
  @ApiProperty({ description: "Category name", example: "Work" })
  @IsString()
  @MinLength(1)
  name: string;
}

// src/categories/categories.service.ts
import { Injectable, NotFoundException } from "@nestjs/common";
import { PrismaService } from "../prisma/prisma.service";
import { CreateCategoryDto } from "./dto/create-category.dto";
import { UpdateCategoryDto } from "./dto/update-category.dto";
import { Category } from "@prisma/client";

@Injectable()
export class CategoriesService {
  constructor(private readonly prisma: PrismaService) {}

  async findAll(userId: string): Promise<Category[]> {
    return this.prisma.category.findMany({
      where: { userId },
      orderBy: { createdAt: "desc" },
    });
  }

  async findOne(id: string, userId: string): Promise<Category> {
    const cat = await this.prisma.category.findUnique({ where: { id } });
    if (!cat || cat.userId !== userId) {
      throw new NotFoundException("Category not found");
    }
    return cat;
  }

  async create(dto: CreateCategoryDto, userId: string): Promise<Category> {
    return this.prisma.category.create({
      data: { name: dto.name.trim(), userId },
    });
  }

  async update(id: string, dto: UpdateCategoryDto, userId: string): Promise<Category> {
    const existing = await this.prisma.category.findUnique({ where: { id } });
    if (!existing || existing.userId !== userId) {
      throw new NotFoundException("Category not found");
    }
    return this.prisma.category.update({
      where: { id },
      data: { name: dto.name.trim() },
    });
  }

  async remove(id: string, userId: string): Promise<void> {
    const existing = await this.prisma.category.findUnique({ where: { id } });
    if (!existing || existing.userId !== userId) {
      throw new NotFoundException("Category not found");
    }
    await this.prisma.category.delete({ where: { id } });
  }
}

Pagination (optional enhancement)

For large datasets, add pagination with skip and take. This changes the GET /tasks response from Task[] to { data: Task[], total: number } — update your frontend accordingly if you add this:

warning

Adding pagination changes the API contract. The examples in this lecture and lecture 72 use the simpler Task[] return. If you add pagination for your assignment, update your frontend to handle { data, total } instead of a plain array.

// src/tasks/tasks.service.ts — optional pagination variant
async findAllPaginated(
  userId: string,
  status?: string,
  priority?: string,
  page: number = 1,
  limit: number = 20,
): Promise<{ data: Task[]; total: number }> {
  const where = {
    userId,
    ...(status && Object.values(TaskStatus).includes(status as TaskStatus) && { status: status as TaskStatus }),
    ...(priority && Object.values(TaskPriority).includes(priority as TaskPriority) && { priority: priority as TaskPriority }),
  };

  const [data, total] = await Promise.all([
    this.prisma.task.findMany({
      where,
      include: { category: true },
      orderBy: { createdAt: "desc" },
      skip: (page - 1) * limit,
      take: limit,
    }),
    this.prisma.task.count({ where }),
  ]);

  return { data, total };
}

// Controller — if using pagination
@Get()
findAll(
  @Query("status") status?: string,
  @Query("priority") priority?: string,
  @Query("page") page?: string,
  @Query("limit") limit?: string,
) {
  return this.tasksService.findAllPaginated(
    "dev-user-id",
    status,
    priority,
    page ? parseInt(page, 10) : 1,
    limit ? parseInt(limit, 10) : 20,
  );
}

Checkpoint 2 — database CRUD

All task and category endpoints are backed by PostgreSQL. Data persists across restarts. Test with curl and verify with Prisma Studio (npx prisma studio).

Relations & Includes

Eager loading

Prisma's include fetches related records in a single query:

// Include the category for each task
const tasks = await this.prisma.task.findMany({
  where: { userId },
  include: { category: true }, // adds category object to each task
});

// Include tasks count on categories
const categories = await this.prisma.category.findMany({
  where: { userId },
  include: { _count: { select: { tasks: true } } },
});

Use select instead of include when you only need specific fields:

// Only fetch id, title, and category name
const tasks = await this.prisma.task.findMany({
  where: { userId },
  select: {
    id: true,
    title: true,
    category: { select: { name: true } },
  },
});

Relation validation

When creating or updating a task with a categoryId, verify the category belongs to the current user. This check must happen in both create() and update() / partialUpdate():

// src/tasks/tasks.service.ts — add to create and update methods
private async verifyCategoryOwnership(categoryId: string, userId: string): Promise<void> {
  const category = await this.prisma.category.findUnique({
    where: { id: categoryId },
  });
  if (!category || category.userId !== userId) {
    throw new NotFoundException("Category not found");
  }
}

async create(dto: CreateTaskDto, userId: string): Promise<Task> {
  if (dto.categoryId) {
    await this.verifyCategoryOwnership(dto.categoryId, userId);
  }

  return this.prisma.task.create({
    data: { /* ... same as before */ },
    include: { category: true },
  });
}

The update() and partialUpdate() methods in the service listing above already include this check. Extract a shared helper like verifyCategoryOwnership to avoid repeating the logic.

Common mistake

Not validating cross-entity ownership on updates, not just creates. Without the check, user A could assign user B's category to their own task by guessing the category UUID. This is an IDOR (Insecure Direct Object Reference) vulnerability. The check must run on every operation that accepts a categoryId — create, update, and partial update.

API Documentation with Swagger

Swagger generates interactive API documentation from your code. Required for A6.

Setup

npm install @nestjs/swagger

Configure Swagger in main.ts:

// src/main.ts — add after app creation, before listen
import { SwaggerModule, DocumentBuilder } from "@nestjs/swagger";

// inside bootstrap():
const config = new DocumentBuilder()
  .setTitle("Task API")
  .setDescription("NestJS Task API with JWT authentication")
  .setVersion("1.0")
  .addBearerAuth()  // adds JWT auth to Swagger UI
  .build();

const document = SwaggerModule.createDocument(app, config);
SwaggerModule.setup("api", app, document);

Visit http://localhost:3000/api to see the interactive documentation.

DTO decorators

Add @ApiProperty decorators to DTOs for Swagger to display field descriptions:

// src/tasks/dto/create-task.dto.ts
import { IsString, IsEnum, IsOptional, IsDateString, MinLength } from "class-validator";
import { ApiProperty, ApiPropertyOptional } from "@nestjs/swagger";
import { TaskStatus, TaskPriority } from "@prisma/client";

export class CreateTaskDto {
  @ApiProperty({ description: "Task title", example: "Learn NestJS" })
  @IsString()
  @MinLength(1)
  title: string;

  @ApiPropertyOptional({ description: "Task description", example: "Complete the NestJS tutorial" })
  @IsOptional()
  @IsString()
  description?: string;

  @ApiPropertyOptional({ enum: TaskStatus, default: TaskStatus.todo })
  @IsOptional()
  @IsEnum(TaskStatus)
  status?: TaskStatus;

  @ApiPropertyOptional({ enum: TaskPriority, default: TaskPriority.medium })
  @IsOptional()
  @IsEnum(TaskPriority)
  priority?: TaskPriority;

  @ApiPropertyOptional({ description: "ISO date string or null", example: "2025-12-31T00:00:00.000Z" })
  @IsOptional()
  @IsDateString()
  dueDate?: string | null;

  @ApiPropertyOptional({ description: "Category UUID or null" })
  @IsOptional()
  @IsString()
  categoryId?: string | null;
}

Controller decorators

Add Swagger metadata to controllers:

// src/tasks/tasks.controller.ts — add decorators
import { ApiTags, ApiOperation, ApiResponse } from "@nestjs/swagger";

@ApiTags("tasks")
@Controller("api/v1/tasks")
export class TasksController {
  // ...

  @Get()
  @ApiOperation({ summary: "List all tasks for the current user" })
  @ApiResponse({ status: 200, description: "List of tasks" })
  findAll(/* ... */) { /* ... */ }

  @Post()
  @HttpCode(HttpStatus.CREATED)
  @ApiOperation({ summary: "Create a new task" })
  @ApiResponse({ status: 201, description: "Task created" })
  @ApiResponse({ status: 400, description: "Validation failed" })
  create(@Body() dto: CreateTaskDto) { /* ... */ }
}

info

The @nestjs/swagger CLI plugin can auto-generate @ApiProperty decorators from your TypeScript types. Add to nest-cli.json:

{
  "compilerOptions": {
    "plugins": ["@nestjs/swagger"]
  }
}

With the plugin, Swagger reads your DTO type annotations directly — you only need explicit @ApiProperty for custom descriptions or examples.

Checkpoint 3 — documented API

Swagger UI at /api shows all endpoints with request/response schemas. You can test endpoints directly from the browser. The "Authorize" button accepts a JWT token (used after auth in lecture 72).

Docker for Development

Full docker-compose.yml

For running the entire stack locally with Docker:

# docker-compose.yml
services:
  app:
    build: .
    ports:
      - "3000:3000"
    environment:
      - DATABASE_URL=postgresql://taskuser:taskpass@db:5432/taskdb
      - PORT=3000
      - CORS_ORIGIN=http://localhost:5173
    depends_on:
      db:
        condition: service_healthy

  db:
    image: postgres:16-alpine
    environment:
      - POSTGRES_USER=taskuser
      - POSTGRES_PASSWORD=taskpass
      - POSTGRES_DB=taskdb
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U taskuser -d taskdb"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  pgdata:

Dockerfile

Multi-stage build — same pattern as lecture 67, adapted for NestJS + Prisma:

# Dockerfile
FROM node:22-alpine AS builder
WORKDIR /app
COPY package*.json ./
COPY prisma ./prisma/
RUN npm ci
COPY . .
RUN npx prisma generate
RUN npm run build

FROM node:22-alpine AS runner
WORKDIR /app
COPY package*.json ./
COPY prisma ./prisma/
RUN npm ci --omit=dev
COPY --from=builder /app/dist ./dist
COPY --from=builder /app/node_modules/.prisma ./node_modules/.prisma
COPY --from=builder /app/node_modules/@prisma ./node_modules/@prisma
COPY --from=builder /app/node_modules/prisma ./node_modules/prisma

EXPOSE 3000
# Run migrations then start the app
CMD ["sh", "-c", "npx prisma migrate deploy && node dist/main.js"]

Key differences from the Express Dockerfile in lecture 67:

COPY prisma ./prisma/ — the Prisma schema and migrations must be available at both build and runtime
npx prisma generate — generates the Prisma Client during build
npx prisma migrate deploy — applies pending migrations on container start
node_modules/.prisma, node_modules/@prisma, node_modules/prisma — the generated client and the Prisma CLI must be copied to the runtime stage (the CLI is a devDependency, so --omit=dev removes it)

warning

The CMD runs migrations on every container start. This is safe — prisma migrate deploy is idempotent (it only applies pending migrations). For production with multiple replicas, run migrations in a separate init container or CI step to avoid race conditions.

Endpoint Reference

All endpoints at this stage (auth added in lecture 72):

Method	Path	Auth	Request Body	Success Response	Status
GET	`/api/v1/tasks`	No*	—	`Task[]`	200
GET	`/api/v1/tasks/:id`	No*	—	`Task`	200 / 404
POST	`/api/v1/tasks`	No*	`CreateTaskDto`	`Task`	201
PUT	`/api/v1/tasks/:id`	No*	`UpdateTaskDto` (all fields)	`Task`	200 / 404
PATCH	`/api/v1/tasks/:id`	No*	partial `UpdateTaskDto`	`Task`	200 / 404
DELETE	`/api/v1/tasks/:id`	No*	—	—	204 / 404
GET	`/api/v1/categories`	No*	—	`Category[]`	200
GET	`/api/v1/categories/:id`	No*	—	`Category`	200 / 404
POST	`/api/v1/categories`	No*	`CreateCategoryDto`	`Category`	201
PUT	`/api/v1/categories/:id`	No*	`UpdateCategoryDto`	`Category`	200 / 404
DELETE	`/api/v1/categories/:id`	No*	—	—	204 / 404

*Auth is added in lecture 72 — all task and category endpoints will require a JWT token.

Defence Preparation

Be prepared to explain topics like these:

What is an ORM and how does Prisma differ from writing raw SQL? — An ORM (Object-Relational Mapping) maps between database tables and programming language objects. Prisma takes a schema-first approach: you define models in schema.prisma, and Prisma generates a fully typed TypeScript client. Every query is type-checked at compile time — prisma.task.findMany({ where: { status: "invalid" } }) is a TypeScript error. With raw SQL (lecture 67), you write SQL strings and manually cast the results — no compile-time safety, and typos in column names only fail at runtime.
What is a database migration and why not just edit the database directly? — A migration is a versioned, reversible change to the database schema. When you modify schema.prisma and run prisma migrate dev, Prisma generates a SQL file that transforms the database from the old schema to the new one. These files are committed to git, so every developer and every deployment gets the same database structure. Editing the database directly (with pgAdmin or SQL) causes drift — your local database differs from production, and there is no record of what changed or when.
What is the difference between Prisma's include and select? — include fetches the full model plus related records: include: { category: true } returns all task fields plus the full category object. select picks specific fields: select: { id: true, title: true } returns only those two fields. Use select to reduce data transfer when you do not need every column. They cannot be combined at the same level — use one or the other. include generates SQL JOINs; select generates targeted SELECT columns.
Why use a PrismaService wrapper instead of importing PrismaClient directly? — Three reasons: (1) Lifecycle management — onModuleInit connects to the database when the app starts, onModuleDestroy disconnects gracefully on shutdown. Without this, connections may leak. (2) Single connection pool — NestJS creates one PrismaService instance (singleton) shared across all services. Importing new PrismaClient() in each service would create multiple connection pools. (3) Testability — in unit tests, you provide a mock PrismaService that returns fake data instead of hitting the real database.
What does Swagger/OpenAPI provide and why is it required for A6? — Swagger generates interactive API documentation from your code. The /api page shows every endpoint with its request body schema, query parameters, and response format. Developers can test endpoints directly from the browser without curl or Postman. For A6, it serves as a contract document between your backend and frontend — the frontend developer (or your future self) can see exactly what the API expects and returns. Swagger can also generate client code (TypeScript interfaces, API functions) from the OpenAPI spec.
How does Prisma handle the N+1 query problem? — The N+1 problem occurs when you fetch a list of N records, then make one additional query per record to fetch related data (N+1 total queries). Prisma avoids this by default because it does not use lazy loading — relations are only fetched when you explicitly request them with include or select. When you write findMany({ include: { category: true } }), Prisma generates a single SQL query with a JOIN (or two queries batched together), not N separate queries. You must opt-in to fetching relations, which forces you to think about what data you need.
What is the difference between prisma migrate dev and prisma migrate deploy? — prisma migrate dev is for development: it compares your schema to the database, generates a new migration file, applies it, and regenerates the Prisma Client. It is interactive — it may ask you to confirm destructive changes (dropping columns, renaming tables). prisma migrate deploy is for production and CI: it applies all pending migration files without generating new ones, never prompts for confirmation, and does not regenerate the client. Use dev on your machine, deploy in Dockerfiles and CI pipelines.

Prisma Setup​

Why Prisma​

Installation​

Schema & Models​

Migrations & Database​

PostgreSQL with Docker​

Running migrations​

Prisma Client​

Prisma Studio​

PrismaService​

Repository Layer​

Tasks service with Prisma​

Categories service with Prisma​

Pagination (optional enhancement)​

Relations & Includes​

Eager loading​

Relation validation​

API Documentation with Swagger​

Setup​

DTO decorators​

Controller decorators​

Docker for Development​

Full docker-compose.yml​

Dockerfile​

Endpoint Reference​

Defence Preparation​

Prisma Setup

Why Prisma

Installation

Schema & Models

Migrations & Database

PostgreSQL with Docker

Running migrations

Prisma Client

Prisma Studio

PrismaService

Repository Layer

Tasks service with Prisma

Categories service with Prisma

Pagination (optional enhancement)

Relations & Includes

Eager loading

Relation validation

API Documentation with Swagger

Setup

DTO decorators

Controller decorators

Docker for Development

Full docker-compose.yml

Dockerfile

Endpoint Reference

Defence Preparation