ORMs — Prisma & Mongoose

Prisma Setup and Schema Definition

Installation

npm install prisma @prisma/client
npx prisma init

The prisma init command creates a prisma/schema.prisma file and a .env file with a DATABASE_URL placeholder. Prisma uses its own schema language (not TypeScript or JSON) to define your data model, and it generates a fully typed client from that schema.

Schema Definition

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  password  String
  role      Role     @default(USER)
  credits   Int      @default(0)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  posts     Post[]
  comments  Comment[]
  profile   Profile?

  @@map("users")
}

model Post {
  id          String    @id @default(uuid())
  title       String
  content     String
  slug        String    @unique
  viewCount   Int       @default(0) @map("view_count")
  published   Boolean   @default(false)
  publishedAt DateTime? @map("published_at")
  authorId    String    @map("author_id")
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")

  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags        Tag[]
  comments    Comment[]

  @@index([authorId])
  @@index([slug])
  @@index([published, createdAt(sort: Desc)])
  @@map("posts")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]

  @@map("tags")
}

model Comment {
  id        String   @id @default(uuid())
  content   String
  postId    String   @map("post_id")
  authorId  String?  @map("author_id")
  parentId  String?  @map("parent_id")
  createdAt DateTime @default(now()) @map("created_at")

  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  author    User?    @relation(fields: [authorId], references: [id], onDelete: SetNull)
  parent    Comment? @relation("CommentThread", fields: [parentId], references: [id])
  replies   Comment[] @relation("CommentThread")

  @@index([postId])
  @@map("comments")
}

model Profile {
  id     String @id @default(uuid())
  bio    String?
  avatar String?
  userId String @unique @map("user_id")

  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Key Schema Concepts

• @id and @default: Define primary keys and default values. UUIDs are recommended for distributed systems.
• @unique: Enforces uniqueness constraints at the database level, not just application level.
• @map: Maps Prisma field names (camelCase) to database column names (snake_case).
• @@map: Maps model names to different table names in the database.
• @relation: Defines foreign key relationships between models.
• @@index: Creates database indexes for frequently queried columns.
• onDelete: Specifies cascade behavior when a referenced record is deleted.

Running Migrations

npx prisma migrate dev --name init
npx prisma generate

The migrate dev command creates a SQL migration file, applies it to your database, and regenerates the Prisma Client. The generate command alone regenerates the client without applying migrations — useful after schema changes in development.

CRUD Queries and Filtering

Type-Safe Client

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

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

// CREATE — nested creation with relations
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    password: hashedPassword,
    profile: { create: { bio: 'Full-stack developer' } },
    posts: {
      create: [
        { title: 'First Post', content: 'Hello World', slug: 'first-post' },
        { title: 'Second Post', content: 'More content', slug: 'second-post' },
      ],
    },
  },
  include: { posts: true, profile: true },
});

// READ — complex filtering with relations
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: { role: 'ADMIN' },
    tags: { some: { name: 'react' } },
    createdAt: { gte: new Date('2025-01-01') },
  },
  include: {
    author: { select: { id: true, name: true, email: true } },
    tags: true,
    _count: { select: { comments: true } },
  },
  orderBy: { createdAt: 'desc' },
  take: 20,
  skip: 0,
});

// UPDATE — conditional updates
const updated = await prisma.post.update({
  where: { id: postId },
  data: {
    title: 'Updated Title',
    published: true,
    publishedAt: new Date(),
    viewCount: { increment: 1 },
  },
});

// UPSERT — insert or update atomically
const profile = await prisma.profile.upsert({
  where: { userId },
  update: { bio: 'Updated bio' },
  create: { userId, bio: 'New bio' },
});

// DELETE
await prisma.post.delete({ where: { id: postId } });
await prisma.post.deleteMany({ where: { published: false, viewCount: 0 } });

Advanced Filtering Patterns

// Build dynamic where clauses
async function searchPosts(filters: {
  query?: string;
  authorId?: string;
  tag?: string;
  from?: Date;
  to?: Date;
}) {
  const where: Prisma.PostWhereInput = { published: true };

  if (filters.query) {
    where.OR = [
      { title: { contains: filters.query, mode: 'insensitive' } },
      { content: { contains: filters.query, mode: 'insensitive' } },
    ];
  }

  if (filters.authorId) where.authorId = filters.authorId;
  if (filters.tag) where.tags = { some: { name: filters.tag } };
  if (filters.from || filters.to) {
    where.createdAt = {
      ...(filters.from && { gte: filters.from }),
      ...(filters.to && { lte: filters.to }),
    };
  }

  return prisma.post.findMany({ where, include: { author: true, tags: true } });
}

// Pagination with cursor-based approach
async function getPostsAfterCursor(cursor?: string) {
  return prisma.post.findMany({
    take: 20,
    ...(cursor && { cursor: { id: cursor }, skip: 1 }),
    orderBy: { id: 'asc' },
    include: { author: { select: { name: true } } },
  });
}

// Aggregation with _sum, _count, _avg
const stats = await prisma.post.aggregate({
  _sum: { viewCount: true },
  _count: true,
  _avg: { viewCount: true },
  where: { published: true },
});

Group By

const postsByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { id: true },
  _sum: { viewCount: true },
  where: { published: true },
  orderBy: { _count: { id: 'desc' } },
  having: { id: { _count: { gte: 5 } } },
});

Transactions and Batch Operations

Interactive Transactions

Interactive transactions let you perform multiple operations atomically. If any operation fails, all changes are rolled back.

async function transferCredits(fromId: string, toId: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    // Read both users atomically
    const [sender, receiver] = await Promise.all([
      tx.user.findUniqueOrThrow({ where: { id: fromId } }),
      tx.user.findUniqueOrThrow({ where: { id: toId } }),
    ]);

    if (sender.credits < amount) {
      throw new Error('Insufficient credits');
    }

    // Perform the transfer
    await tx.user.update({
      where: { id: fromId },
      data: { credits: { decrement: amount } },
    });

    await tx.user.update({
      where: { id: toId },
      data: { credits: { increment: amount } },
    });

    // Record the transaction
    await tx.transactionRecord.create({
      data: { fromId, toId, amount, type: 'TRANSFER', createdAt: new Date() },
    });

    return { success: true, newBalance: sender.credits - amount };
  }, {
    maxWait: 5000,  // max time to wait for a transaction slot
    timeout: 10000, // max time the transaction can run
  });
}

Batch Operations

// Bulk insert with deduplication
await prisma.post.createMany({
  data: posts.map(p => ({
    title: p.title,
    content: p.content,
    slug: p.slug,
    authorId: p.authorId,
  })),
  skipDuplicates: true,
});

// Atomic increments — no race conditions
await prisma.post.update({
  where: { id: postId },
  data: { viewCount: { increment: 1 } },
});

// Update many with conditions
await prisma.post.updateMany({
  where: { published: false, createdAt: { lt: new Date('2024-01-01') } },
  data: { publishedAt: new Date() },
});

Raw SQL Queries

// Complex analytics that Prisma can't express
const dailyStats = await prisma.$queryRaw`
  SELECT
    DATE_TRUNC('day', created_at) as day,
    COUNT(*) as post_count,
    SUM(view_count) as total_views,
    AVG(view_count) as avg_views
  FROM posts
  WHERE created_at > NOW() - INTERVAL '30 days'
    AND published = true
  GROUP BY DATE_TRUNC('day', created_at)
  ORDER BY day DESC
`;

// Parameterized raw query for safety
const users = await prisma.$queryRaw`
  SELECT id, name, email
  FROM users
  WHERE role = ${role}
  AND created_at > ${since}
  LIMIT ${limit}
`;

// Execute raw DML statements
await prisma.$executeRaw`
  UPDATE posts SET view_count = view_count + 1
  WHERE id = ${postId}
`;

// Use within a transaction for atomicity
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: userId } });
  await tx.$executeRaw`
    UPDATE users SET credits = credits - ${amount} WHERE id = ${userId} AND credits >= ${amount}
  `;
  return user;
});

Migrations and Schema Management

Migration Workflow

Prisma migrations are SQL files that track every change to your database schema. This gives you version control for your database structure.

# Create a new migration after schema changes
npx prisma migrate dev --name add-user-credits

# Apply pending migrations in production
npx prisma migrate deploy

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

# Generate Prisma Client after schema changes
npx prisma generate

# View migration history
npx prisma migrate status

Migration Files

Each migration creates a numbered directory under prisma/migrations/:

prisma/
  migrations/
    20250101120000_init/
      migration.sql
    20250102140000_add-user-credits/
      migration.sql
  schema.prisma

Example migration SQL:

-- 20250102140000_add-user-credits/migration.sql
ALTER TABLE "users" ADD COLUMN "credits" INTEGER NOT NULL DEFAULT 0;

CREATE INDEX "posts_published_created_at_idx" ON "posts"("published", "created_at" DESC);

Schema Best Practices

// Use enums for fixed sets of values
enum Status {
  DRAFT
  PUBLISHED
  ARCHIVED
}

// Composite unique constraints
model UserSubscription {
  id           String   @id @default(uuid())
  userId       String
  subscription String
  periodStart  DateTime
  periodEnd    DateTime

  user         User     @relation(fields: [userId], references: [id])

  @@unique([userId, subscription, periodStart])
  @@map("user_subscriptions")
}

// Soft deletes with @@ignore for filtered queries
model Article {
  id        String    @id @default(uuid())
  title     String
  deletedAt DateTime? @map("deleted_at")
  isActive  Boolean   @default(true) @map("is_active")

  @@index([deletedAt])
}

Schema Review Checklist

1. Every table has a primary key — prefer UUIDs for distributed systems
2. Foreign keys have indexes — Prisma @relation fields should have @@index
3. Unique constraints are explicit — use @unique and @@unique for business rules
4. Column names map to conventions — use @map for snake_case in DB, camelCase in code
5. Sensitive fields are handled — never store plain-text passwords; use @db.Text for large strings
6. Soft deletes are considered — deletedAt column instead of hard deletes for audit trails

Relations and Advanced Patterns

Relation Types

// One-to-One: User has one Profile
model User {
  id      String   @id @default(uuid())
  profile Profile?
}

model Profile {
  id     String @id @default(uuid())
  userId String @unique
  user   User   @relation(fields: [userId], references: [id])
}

// One-to-Many: User has many Posts
model Post {
  id       String @id @default(uuid())
  authorId String
  author   User   @relation(fields: [authorId], references: [id])
}

// Many-to-Many: Posts have Tags (implicit join table)
model Post {
  id   String @id @default(uuid())
  tags Tag[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

// Many-to-Many with extra fields (explicit join table)
model Post {
  id       String        @id @default(uuid())
  categories PostCategory[]
}

model Category {
  id   String         @id @default(uuid())
  name String         @unique
  posts PostCategory[]
}

model PostCategory {
  postId     String
  categoryId String
  assignedAt DateTime @default(now())
  assignedBy String?

  post     Post     @relation(fields: [postId], references: [id])
  category Category @relation(fields: [categoryId], references: [id])

  @@id([postId, categoryId])
}

Self-Referencing Relations

// Tree structure: comments can have parent comments
model Comment {
  id        String    @id @default(uuid())
  content   String
  parentId  String?   @map("parent_id")
  authorId  String    @map("author_id")

  parent    Comment?  @relation("CommentThread", fields: [parentId], references: [id])
  replies   Comment[] @relation("CommentThread")
  author    User      @relation(fields: [authorId], references: [id])

  @@index([parentId])
  @@map("comments")
}

Querying Relations Efficiently

// Selective includes — only fetch what you need
const post = await prisma.post.findUnique({
  where: { id: postId },
  include: {
    author: { select: { name: true, email: true } },
    tags: true,
    _count: { select: { comments: true } },
  },
});

// Nested includes with filtering
const posts = await prisma.post.findMany({
  where: { published: true },
  include: {
    author: {
      include: { profile: true },
    },
    tags: { select: { name: true } },
    comments: {
      where: { content: { not: null } },
      take: 5,
      orderBy: { createdAt: 'desc' },
      include: { author: { select: { name: true } } },
    },
  },
});

// Avoid N+1: use include instead of manual loops
// ❌ Bad: N+1 problem
const users = await prisma.user.findMany();
for (const user of users) {
  user.posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// ✅ Good: single query with include
const usersWithPosts = await prisma.user.findMany({
  include: { posts: true },
});

Relation Filters

// Find users who have at least one published post
const activeAuthors = await prisma.user.findMany({
  where: { posts: { some: { published: true } } },
});

// Find posts with no comments
const uncommentedPosts = await prisma.post.findMany({
  where: { comments: { none: {} } },
});

// Find users with exactly 3 posts
const prolificAuthors = await prisma.user.findMany({
  where: { posts: { _count: { equals: 3 } } },
});

ORM vs Raw SQL Tradeoffs

When to Use Prisma ORM

// ✅ Good for: CRUD operations with type safety
const users = await prisma.user.findMany({
  where: { role: 'ADMIN' },
  include: { posts: true },
});
// Autocomplete, type checking, compile-time validation

// ✅ Good for: rapid prototyping and iteration
const newPost = await prisma.post.create({
  data: { title, content, slug, authorId },
});
// No need to write SQL strings, no SQL injection risk

// ✅ Good for: schema migrations with version control
npx prisma migrate dev --name add-credits-column
// Automatic SQL generation from schema diff

When to Use Raw SQL

// ✅ Good for: complex analytics and window functions
const rankings = await prisma.$queryRaw`
  SELECT
    u.name,
    COUNT(p.id) as post_count,
    SUM(p.view_count) as total_views,
    RANK() OVER (ORDER BY SUM(p.view_count) DESC) as rank
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  WHERE p.published = true
  GROUP BY u.id, u.name
  HAVING COUNT(p.id) > 0
  ORDER BY total_views DESC
  LIMIT 10
`;

// ✅ Good for: database-specific features
const searchResults = await prisma.$queryRaw`
  SELECT *,
    ts_rank(
      to_tsvector('english', title || ' ' || content),
      plainto_tsquery('english', ${searchQuery})
    ) as relevance
  FROM posts
  WHERE to_tsvector('english', title || ' ' || content)
    @@ plainto_tsquery('english', ${searchQuery})
  ORDER BY relevance DESC
  LIMIT 20;
`;

// ✅ Good for: bulk operations with complex logic
await prisma.$executeRaw`
  UPDATE posts SET
    status = CASE
      WHEN view_count > 1000 THEN 'popular'
      WHEN view_count > 100 THEN 'trending'
      ELSE 'normal'
    END
  WHERE published = true
`;

Comparison Table

Hybrid Approach in Practice

export class PostRepository {
  // Standard CRUD with Prisma
  async findById(id: string) {
    return prisma.post.findUnique({
      where: { id },
      include: { author: true, tags: true },
    });
  }

  async create(data: CreatePostInput) {
    return prisma.post.create({ data });
  }

  // Complex analytics with raw SQL
  async getDashboardStats(): Promise<DashboardStats> {
    return prisma.$queryRaw<DashboardStats[]>`
      SELECT
        COUNT(*) as total_posts,
        COUNT(CASE WHEN published THEN 1 END) as published_count,
        SUM(view_count) as total_views
      FROM posts
      WHERE created_at > NOW() - INTERVAL '30 days'
    `;
  }

  // Full-text search with raw SQL
  async search(query: string) {
    return prisma.$queryRaw`
      SELECT id, title, ts_headline('english', content, plainto_tsquery('english', ${query})) as snippet
      FROM posts
      WHERE to_tsvector('english', title || ' ' || content) @@ plainto_tsquery('english', ${query})
      ORDER BY ts_rank(to_tsvector('english', title || ' ' || content), plainto_tsquery('english', ${query})) DESC
      LIMIT 20
    `;
  }
}

Decision Framework

• Use Prisma when: building CRUD APIs, need type safety, rapid development, team has mixed SQL experience
• Use Raw SQL when: complex analytics, database-specific features, performance-critical hot paths, advanced full-text search
• Use Both when: application has both standard CRUD and analytical/reporting features