Module 3: Database Design & Management

Master advanced PostgreSQL, Prisma ORM, and database design patterns for production applications

📚 Understanding Database Design

Database design is the foundation of any scalable application. A well-designed database ensures data integrity, optimal performance, and maintainability as your application grows.

Why Prisma ORM?

Prisma is a next-generation ORM (Object-Relational Mapping) that makes database access easy with an auto-generated and type-safe query builder. It replaces traditional ORMs and raw SQL queries with a more intuitive and safer approach.

Type Safety

Auto-generated TypeScript types from your database schema. No more runtime errors from typos!

Developer Experience

Intuitive API with autocomplete, inline documentation, and helpful error messages.

Migrations

Version control for your database schema with automatic migration generation.

🎯 What We'll Build

Upgrade our Task Manager with advanced database features: user authentication, task categories, tags, comments, and proper relationships. Learn to design complex schemas and optimize queries.

New Features:

User authentication with hashed passwords
Task categories (one-to-many relationship)
Tags with many-to-many relationships
Comments and activity history
Advanced queries with filtering and pagination

What You'll Learn:

• Prisma schema design

• Database relationships

• Migrations and seeding

• Query optimization

• Transactions

• Database indexing

• Data validation

• Type-safe queries

Lesson 1: Setting Up Prisma ORM

📖 What is an ORM?

ORM (Object-Relational Mapping) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm. Instead of writing SQL, you work with objects and methods in your programming language.

Example: Instead of SELECT * FROM users WHERE id = 1, you write prisma.user.findUnique({ where: { id: 1 } })

Step 1: Install Prisma

In your backend project, install Prisma CLI and Client:

cd task-manager-backend

# Install Prisma as dev dependency
npm install -D prisma

# Install Prisma Client
npm install @prisma/client

# Initialize Prisma
npx prisma init

💡 This creates a prisma folder with a schema.prisma file and adds DATABASE_URL to your .env file.

Step 2: Configure Database Connection

Update your .env file:

DATABASE_URL="postgresql://localhost:5432/task_manager?schema=public"

Step 3: Understand Prisma Schema

The prisma/schema.prisma file is where you define your database structure:

// This is your Prisma schema file

generator client {
  provider = "prisma-client-js"
}

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

// We'll add models here in the next lesson

📚 Schema Components:

generator - Specifies what Prisma should generate (TypeScript client)

datasource - Database connection configuration

model - Represents a table in your database (we'll add these next)

Lesson 2: Designing Database Schema

📖 Database Relationships

Relationships connect tables together. There are three main types:

One-to-Many

One user has many tasks. Each task belongs to one user.

Many-to-Many

Tasks can have many tags, and tags can be on many tasks.

One-to-One

One user has one profile. One profile belongs to one user.

Step 1: Define User Model

Update prisma/schema.prisma:

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  password  String   // Will be hashed
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relations
  tasks     Task[]
  comments  Comment[]
  
  @@map("users")
}

Step 2: Define Task Model with Relations

model Task {
  id          String   @id @default(uuid())
  title       String
  description String?
  completed   Boolean  @default(false)
  priority    Priority @default(MEDIUM)
  dueDate     DateTime?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  // Foreign keys
  userId      String
  categoryId  String?
  
  // Relations
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  category    Category? @relation(fields: [categoryId], references: [id])
  tags        TaskTag[]
  comments    Comment[]
  
  @@map("tasks")
  @@index([userId])
  @@index([completed])
}

enum Priority {
  LOW
  MEDIUM
  HIGH
  URGENT
}

Step 3: Add Category and Tag Models

model Category {
  id        String   @id @default(uuid())
  name      String   @unique
  color     String   // Hex color code
  createdAt DateTime @default(now())
  
  tasks     Task[]
  
  @@map("categories")
}

model Tag {
  id        String   @id @default(uuid())
  name      String   @unique
  createdAt DateTime @default(now())
  
  tasks     TaskTag[]
  
  @@map("tags")
}

// Junction table for many-to-many relationship
model TaskTag {
  taskId    String
  tagId     String
  createdAt DateTime @default(now())
  
  task      Task @relation(fields: [taskId], references: [id], onDelete: Cascade)
  tag       Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)
  
  @@id([taskId, tagId])
  @@map("task_tags")
}

Step 4: Add Comment Model

model Comment {
  id        String   @id @default(uuid())
  content   String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  userId    String
  taskId    String
  
  user      User @relation(fields: [userId], references: [id], onDelete: Cascade)
  task      Task @relation(fields: [taskId], references: [id], onDelete: Cascade)
  
  @@map("comments")
  @@index([taskId])
}

💡 Schema Annotations Explained:

@id - Primary key

@default(uuid()) - Auto-generate UUID

@unique - Enforce uniqueness

@updatedAt - Auto-update on changes

@relation - Define foreign key relationships

@@index - Create database index for faster queries

@@map - Custom table name in database

onDelete: Cascade - Delete related records when parent is deleted

Lesson 3: Database Migrations

📖 What are Migrations?

Migrations are version control for your database. They track changes to your schema over time, allowing you to apply, rollback, and share database changes with your team.

Step 1: Create Initial Migration

Generate a migration from your Prisma schema:

npx prisma migrate dev --name init

# This will:
# 1. Create SQL migration files
# 2. Apply migration to database
# 3. Generate Prisma Client

💡 Prisma creates a prisma/migrations folder with SQL files that can be version controlled with Git.

Step 2: Generate Prisma Client

The Prisma Client is auto-generated based on your schema:

npx prisma generate

# This creates TypeScript types and query methods
# You'll get autocomplete for all your models!

Step 3: Create Prisma Client Instance

Create src/lib/prisma.ts:

import { PrismaClient } from '@prisma/client';

// Prevent multiple instances in development
const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: ['query', 'error', 'warn'],
  });

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

export default prisma;

Step 4: Seed Database with Sample Data

Create prisma/seed.ts:

import { PrismaClient } from '@prisma/client';
import bcrypt from 'bcrypt';

const prisma = new PrismaClient();

async function main() {
  // Create sample user
  const hashedPassword = await bcrypt.hash('password123', 10);
  
  const user = await prisma.user.create({
    data: {
      email: 'demo@example.com',
      name: 'Demo User',
      password: hashedPassword,
    },
  });

  // Create categories
  const workCategory = await prisma.category.create({
    data: { name: 'Work', color: '#3B82F6' },
  });

  const personalCategory = await prisma.category.create({
    data: { name: 'Personal', color: '#10B981' },
  });

  // Create tags
  const urgentTag = await prisma.tag.create({
    data: { name: 'Urgent' },
  });

  const importantTag = await prisma.tag.create({
    data: { name: 'Important' },
  });

  // Create sample tasks
  await prisma.task.create({
    data: {
      title: 'Complete project proposal',
      description: 'Write and submit Q4 project proposal',
      userId: user.id,
      categoryId: workCategory.id,
      priority: 'HIGH',
      tags: {
        create: [
          { tagId: urgentTag.id },
          { tagId: importantTag.id },
        ],
      },
    },
  });

  console.log('✅ Database seeded successfully');
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Add seed script to package.json:

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

Run the seed:

npm install bcrypt @types/bcrypt
npx prisma db seed

🎯 What You Learned:

✓ Creating and applying migrations
✓ Generating Prisma Client
✓ Setting up database connection
✓ Seeding database with sample data

Lesson 4: CRUD Operations with Prisma

📖 Prisma Query API

Prisma provides a type-safe and intuitive API for database operations. All queries are auto-completed in your IDE and type-checked at compile time.

Step 1: Update Task Routes with Prisma

Replace your src/routes/tasks.ts with Prisma queries:

import { Router, Request, Response } from 'express';
import prisma from '../lib/prisma';

const router = Router();

// GET /api/tasks - Get all tasks with relations
router.get('/', async (req: Request, res: Response) => {
  try {
    const tasks = await prisma.task.findMany({
      include: {
        user: {
          select: { id: true, name: true, email: true },
        },
        category: true,
        tags: {
          include: { tag: true },
        },
        comments: {
          include: { user: { select: { name: true } } },
          orderBy: { createdAt: 'desc' },
        },
      },
      orderBy: { createdAt: 'desc' },
    });
    
    res.json(tasks);
  } catch (error) {
    console.error('Error fetching tasks:', error);
    res.status(500).json({ error: 'Failed to fetch tasks' });
  }
});

// GET /api/tasks/:id - Get single task
router.get('/:id', async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    
    const task = await prisma.task.findUnique({
      where: { id },
      include: {
        user: { select: { id: true, name: true, email: true } },
        category: true,
        tags: { include: { tag: true } },
        comments: {
          include: { user: { select: { name: true } } },
          orderBy: { createdAt: 'desc' },
        },
      },
    });
    
    if (!task) {
      return res.status(404).json({ error: 'Task not found' });
    }
    
    res.json(task);
  } catch (error) {
    console.error('Error fetching task:', error);
    res.status(500).json({ error: 'Failed to fetch task' });
  }
});

// POST /api/tasks - Create new task
router.post('/', async (req: Request, res: Response) => {
  try {
    const { title, description, userId, categoryId, tagIds, priority, dueDate } = req.body;
    
    if (!title || !userId) {
      return res.status(400).json({ error: 'Title and userId are required' });
    }
    
    const task = await prisma.task.create({
      data: {
        title,
        description,
        userId,
        categoryId,
        priority: priority || 'MEDIUM',
        dueDate: dueDate ? new Date(dueDate) : null,
        tags: tagIds ? {
          create: tagIds.map((tagId: string) => ({ tagId })),
        } : undefined,
      },
      include: {
        user: { select: { name: true } },
        category: true,
        tags: { include: { tag: true } },
      },
    });
    
    res.status(201).json(task);
  } catch (error) {
    console.error('Error creating task:', error);
    res.status(500).json({ error: 'Failed to create task' });
  }
});

// PATCH /api/tasks/:id - Update task
router.patch('/:id', async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { title, description, completed, priority, categoryId, tagIds } = req.body;
    
    // If updating tags, we need to replace them
    const updateData: any = {
      title,
      description,
      completed,
      priority,
      categoryId,
    };
    
    if (tagIds) {
      // Delete existing tags and create new ones
      await prisma.taskTag.deleteMany({ where: { taskId: id } });
      updateData.tags = {
        create: tagIds.map((tagId: string) => ({ tagId })),
      };
    }
    
    const task = await prisma.task.update({
      where: { id },
      data: updateData,
      include: {
        category: true,
        tags: { include: { tag: true } },
      },
    });
    
    res.json(task);
  } catch (error) {
    console.error('Error updating task:', error);
    res.status(500).json({ error: 'Failed to update task' });
  }
});

// DELETE /api/tasks/:id - Delete task
router.delete('/:id', async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    
    await prisma.task.delete({ where: { id } });
    
    res.json({ message: 'Task deleted successfully' });
  } catch (error) {
    console.error('Error deleting task:', error);
    res.status(500).json({ error: 'Failed to delete task' });
  }
});

export default router;

💡 Prisma Query Methods:

findMany() - Get multiple records

findUnique() - Get one record by unique field

create() - Insert new record

update() - Modify existing record

delete() - Remove record

include - Load related data (like SQL JOIN)

select - Choose specific fields to return

where - Filter records

orderBy - Sort results

Step 2: Add Comment Routes

Create src/routes/comments.ts:

import { Router, Request, Response } from 'express';
import prisma from '../lib/prisma';

const router = Router();

// POST /api/tasks/:taskId/comments - Add comment
router.post('/:taskId/comments', async (req: Request, res: Response) => {
  try {
    const { taskId } = req.params;
    const { content, userId } = req.body;
    
    if (!content || !userId) {
      return res.status(400).json({ error: 'Content and userId required' });
    }
    
    const comment = await prisma.comment.create({
      data: {
        content,
        userId,
        taskId,
      },
      include: {
        user: { select: { name: true, email: true } },
      },
    });
    
    res.status(201).json(comment);
  } catch (error) {
    console.error('Error creating comment:', error);
    res.status(500).json({ error: 'Failed to create comment' });
  }
});

// GET /api/tasks/:taskId/comments - Get task comments
router.get('/:taskId/comments', async (req: Request, res: Response) => {
  try {
    const { taskId } = req.params;
    
    const comments = await prisma.comment.findMany({
      where: { taskId },
      include: {
        user: { select: { name: true, email: true } },
      },
      orderBy: { createdAt: 'desc' },
    });
    
    res.json(comments);
  } catch (error) {
    console.error('Error fetching comments:', error);
    res.status(500).json({ error: 'Failed to fetch comments' });
  }
});

export default router;

🎯 What You Learned:

✓ Type-safe database queries with Prisma
✓ Loading related data with include
✓ Creating records with relationships
✓ Filtering and sorting results

Lesson 5: Advanced Queries & Filtering

📖 Complex Queries

Real applications need advanced filtering, pagination, and search. Prisma makes these operations type-safe and intuitive.

Step 1: Add Filtering and Pagination

Update your GET /api/tasks route with query parameters:

router.get('/', async (req: Request, res: Response) => {
  try {
    const {
      page = '1',
      limit = '10',
      completed,
      priority,
      categoryId,
      search,
      sortBy = 'createdAt',
      sortOrder = 'desc',
    } = req.query;
    
    const skip = (parseInt(page as string) - 1) * parseInt(limit as string);
    const take = parseInt(limit as string);
    
    // Build where clause
    const where: any = {};
    
    if (completed !== undefined) {
      where.completed = completed === 'true';
    }
    
    if (priority) {
      where.priority = priority;
    }
    
    if (categoryId) {
      where.categoryId = categoryId;
    }
    
    if (search) {
      where.OR = [
        { title: { contains: search as string, mode: 'insensitive' } },
        { description: { contains: search as string, mode: 'insensitive' } },
      ];
    }
    
    // Get total count for pagination
    const total = await prisma.task.count({ where });
    
    // Get tasks with filters
    const tasks = await prisma.task.findMany({
      where,
      include: {
        user: { select: { name: true } },
        category: true,
        tags: { include: { tag: true } },
        _count: { select: { comments: true } },
      },
      orderBy: { [sortBy as string]: sortOrder },
      skip,
      take,
    });
    
    res.json({
      tasks,
      pagination: {
        page: parseInt(page as string),
        limit: take,
        total,
        totalPages: Math.ceil(total / take),
      },
    });
  } catch (error) {
    console.error('Error fetching tasks:', error);
    res.status(500).json({ error: 'Failed to fetch tasks' });
  }
});

Step 2: Aggregations and Statistics

Create src/routes/stats.ts:

import { Router, Request, Response } from 'express';
import prisma from '../lib/prisma';

const router = Router();

// GET /api/stats - Get task statistics
router.get('/', async (req: Request, res: Response) => {
  try {
    const { userId } = req.query;
    
    const where = userId ? { userId: userId as string } : {};
    
    // Count tasks by status
    const totalTasks = await prisma.task.count({ where });
    const completedTasks = await prisma.task.count({
      where: { ...where, completed: true },
    });
    const activeTasks = totalTasks - completedTasks;
    
    // Count by priority
    const tasksByPriority = await prisma.task.groupBy({
      by: ['priority'],
      where,
      _count: true,
    });
    
    // Count by category
    const tasksByCategory = await prisma.task.groupBy({
      by: ['categoryId'],
      where,
      _count: true,
    });
    
    // Get category details
    const categories = await prisma.category.findMany({
      where: {
        id: { in: tasksByCategory.map(t => t.categoryId).filter(Boolean) as string[] },
      },
    });
    
    const categoryStats = tasksByCategory.map(stat => ({
      category: categories.find(c => c.id === stat.categoryId),
      count: stat._count,
    }));
    
    res.json({
      total: totalTasks,
      completed: completedTasks,
      active: activeTasks,
      completionRate: totalTasks > 0 ? (completedTasks / totalTasks) * 100 : 0,
      byPriority: tasksByPriority,
      byCategory: categoryStats,
    });
  } catch (error) {
    console.error('Error fetching stats:', error);
    res.status(500).json({ error: 'Failed to fetch statistics' });
  }
});

export default router;

💡 Advanced Query Features:

contains - Text search (case-insensitive with mode)

OR / AND - Combine multiple conditions

count() - Get total number of records

groupBy() - Aggregate data by field

_count - Count related records

skip / take - Implement pagination

Lesson 6: Transactions & Data Integrity

📖 Database Transactions

Transactions ensure that multiple database operations either all succeed or all fail together. This maintains data consistency and prevents partial updates.

Example: When transferring a task to another user, you want to update the task AND create an activity log. If one fails, both should be rolled back.

Step 1: Using Transactions

// Transfer task to another user with activity log
router.post('/:id/transfer', async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { newUserId, currentUserId } = req.body;
    
    // Use transaction to ensure both operations succeed
    const result = await prisma.$transaction(async (tx) => {
      // Update task owner
      const task = await tx.task.update({
        where: { id },
        data: { userId: newUserId },
      });
      
      // Create activity log
      await tx.comment.create({
        data: {
          content: `Task transferred from user ${currentUserId} to ${newUserId}`,
          userId: currentUserId,
          taskId: id,
        },
      });
      
      return task;
    });
    
    res.json(result);
  } catch (error) {
    console.error('Error transferring task:', error);
    res.status(500).json({ error: 'Failed to transfer task' });
  }
});

Step 2: Batch Operations

// Mark multiple tasks as completed
router.post('/bulk-complete', async (req: Request, res: Response) => {
  try {
    const { taskIds } = req.body;
    
    const result = await prisma.task.updateMany({
      where: {
        id: { in: taskIds },
      },
      data: {
        completed: true,
      },
    });
    
    res.json({
      message: `${result.count} tasks marked as completed`,
      count: result.count,
    });
  } catch (error) {
    console.error('Error bulk updating tasks:', error);
    res.status(500).json({ error: 'Failed to update tasks' });
  }
});

// Delete completed tasks older than 30 days
router.delete('/cleanup', async (req: Request, res: Response) => {
  try {
    const thirtyDaysAgo = new Date();
    thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
    
    const result = await prisma.task.deleteMany({
      where: {
        completed: true,
        updatedAt: { lt: thirtyDaysAgo },
      },
    });
    
    res.json({
      message: `Cleaned up ${result.count} old tasks`,
      count: result.count,
    });
  } catch (error) {
    console.error('Error cleaning up tasks:', error);
    res.status(500).json({ error: 'Failed to cleanup tasks' });
  }
});

🎉 Congratulations!

You've mastered Prisma ORM and advanced database management! Your Task Manager now has a production-ready database layer with relationships, transactions, and optimized queries.

What You've Learned:

✓ Prisma schema design

✓ Database relationships

✓ Migrations and seeding

✓ Type-safe queries

✓ Advanced filtering

✓ Pagination

✓ Transactions

✓ Batch operations

🚀 Next Steps

→Add full-text search with PostgreSQL
→Implement soft deletes
→Add database indexes for performance
→Set up database backups

Continue to Module 4: Cloud & Deployment →