Week 5: Relations, the N+1 Problem & Transactions

1. One-to-Many Relations

We'll extend last week's blog schema. A User can write many Posts, and each Post belongs to exactly one User — a classic one-to-many relation. In Prisma, you declare the "many" side with a foreign key scalar field plus a @relation field, and the "one" side with a plain array field:

model User {
  id       Int       @id @default(autoincrement())
  email    String    @unique
  name     String
  posts    Post[]
  comments Comment[]
}

model Post {
  id            Int       @id @default(autoincrement())
  title         String
  content       String
  published     Boolean   @default(false)
  featured      Boolean   @default(false)
  commentCount  Int       @default(0)
  authorId      Int
  author        User      @relation(fields: [authorId], references: [id])
  comments      Comment[]
  tags          Tag[]     @relation("PostTags")
  createdAt     DateTime  @default(now())
}

model Comment {
  id        Int      @id @default(autoincrement())
  body      String
  postId    Int
  post      Post     @relation(fields: [postId], references: [id])
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}

authorId is a real column in the Post table — that's the foreign key. author is not a column at all; it's a virtual field that tells Prisma's client how to join back to User when you ask for it. After running prisma migrate dev --name add-post-author, Prisma generates a fully-typed client where author and posts exist as optional relation fields on their respective types — TypeScript will refuse to let you access post.author.name unless you actually asked Prisma to fetch it.

import { Router } from "express";
import { prisma } from "../lib/prisma.js";

export const postsRouter = Router();

postsRouter.get("/:id", async (req, res, next) => {
  try {
    const post = await prisma.post.findUnique({
      where: { id: Number(req.params.id) },
      include: { author: true }, // fetch the related User in the same query
    });

    if (!post) {
      return res.status(404).json({ error: "Post not found" });
    }

    res.json(post);
  } catch (err) {
    next(err);
  }
});

2. Many-to-Many Relations

A Post can have many Tags, and a Tag ("node", "typescript") can apply to many Posts — many-to-many. Prisma supports two ways to model this. The simpler, implicit form just declares array fields on both sides with a matching relation name; Prisma creates and manages the join table for you behind the scenes:

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

// Post.tags from Section 1 is the other half of this relation

postsRouter.post("/:id/tags", async (req, res, next) => {
  try {
    const post = await prisma.post.update({
      where: { id: Number(req.params.id) },
      data: {
        tags: {
          connect: [{ name: "typescript" }, { name: "prisma" }],
        },
      },
      include: { tags: true },
    });
    res.json(post);
  } catch (err) {
    next(err);
  }
});

connect attaches existing rows to the relation without creating new ones — it's the equivalent of an INSERT into the hidden join table. Reach for this implicit form whenever the relation itself carries no extra data. The moment you need to store something about the relationship — when a student enrolled in a course, what grade they got — you need an explicit join model instead, because the implicit join table has no room for extra columns:

model Student {
  id          Int          @id @default(autoincrement())
  name        String
  enrollments Enrollment[]
}

model Course {
  id          Int          @id @default(autoincrement())
  title       String
  enrollments Enrollment[]
}

model Enrollment {
  studentId  Int
  courseId   Int
  enrolledAt DateTime @default(now())
  grade      String?

  student Student @relation(fields: [studentId], references: [id])
  course  Course  @relation(fields: [courseId], references: [id])

  @@id([studentId, courseId]) // composite primary key -- one row per enrollment
}

3. The N+1 Problem

The N+1 problem is what happens when you fetch a list of N rows, then loop over that list and issue one additional query per row to fetch each row's related data — N+1 total queries where one would've done. It's easy to write by accident, and it's the single most common Prisma performance bug in real apps:

postsRouter.get("/", async (req, res, next) => {
  try {
    const posts = await prisma.post.findMany(); // 1 query

    const withAuthors = await Promise.all(
      posts.map(async (post) => {
        // one extra query PER post -- this is the "+N"
        const author = await prisma.user.findUnique({ where: { id: post.authorId } });
        return { ...post, author };
      }),
    );

    res.json(withAuthors); // 21 total queries for a page of 20 posts
  } catch (err) {
    next(err);
  }
});

Promise.all here makes the extra queries run concurrently rather than sequentially, which softens the latency hit but doesn't remove the underlying problem: you're still round-tripping to the database 21 times for one page of results, and connection-pool pressure scales with that count, not with wall-clock time. The fix is to tell Prisma up front which relations you need, so it generates a single query — a JOIN under the hood — instead of one query per row:

postsRouter.get("/", async (req, res, next) => {
  try {
    const posts = await prisma.post.findMany({
      include: {
        author: { select: { id: true, name: true } }, // only the fields the UI needs
        tags: true,
      },
      orderBy: { createdAt: "desc" },
      take: 20,
    });

    res.json(posts); // 1 query, full result set
  } catch (err) {
    next(err);
  }
});

include pulls in an entire related model (or a nested select within it, as shown for author here to trim unused columns). select on the top-level query does the same trimming for the root model — use it when you only need a handful of fields from Post itself and want to avoid pulling content across the wire for a list view that doesn't render it.

4. Multi-Step Writes with $transaction

Some operations touch more than one row and need to succeed or fail together. Say only one post on the whole blog can be featured at a time: featuring a new post means un-featuring whichever post currently holds that spot and featuring the new one. Do that as two separate, unguarded update calls and a second request arriving between them can leave you with two featured posts, or zero:

// Request A and Request B, both firing at nearly the same time,
// can interleave their two updates and leave the data inconsistent.
await prisma.post.updateMany({ where: { featured: true }, data: { featured: false } });
await prisma.post.update({ where: { id: newPostId }, data: { featured: true } });

prisma.$transaction fixes this by running a group of writes inside a single database transaction: either every write in the group commits, or (on error) it rolls back as if none of them ran. Prisma offers two forms. The array form is for a fixed batch of independent writes:

await prisma.$transaction([
  prisma.post.updateMany({ where: { featured: true }, data: { featured: false } }),
  prisma.post.update({ where: { id: newPostId }, data: { featured: true } }),
]);

This is already correct for the featuring example, since both writes are unconditional — neither reads a value the other produced. The interactive form is for when a later step needs the result of an earlier one, such as checking a value and then acting on it within the same atomic unit:

postsRouter.post("/:id/comments", async (req, res, next) => {
  try {
    const postId = Number(req.params.id);

    const comment = await prisma.$transaction(async (tx) => {
      const post = await tx.post.findUnique({ where: { id: postId } });
      if (!post) {
        throw new Error("POST_NOT_FOUND"); // throwing inside rolls back everything above
      }

      const created = await tx.comment.create({
        data: { body: req.body.body, postId, authorId: req.user!.id },
      });

      await tx.post.update({
        where: { id: postId },
        data: { commentCount: { increment: 1 } }, // atomic increment, safe under concurrency
      });

      return created;
    });

    res.status(201).json(comment);
  } catch (err) {
    if (err instanceof Error && err.message === "POST_NOT_FOUND") {
      return res.status(404).json({ error: "Post not found" });
    }
    next(err);
  }
});

Every query inside the callback uses tx, the transaction-scoped client Prisma passes in — not the top-level prisma object — so all three statements run on the same database connection inside the same transaction. Throwing anywhere in the callback rolls back every write that happened earlier in that same callback, which is exactly what you want if the post lookup fails partway through: you never want an orphaned comment with an incremented count on a post that doesn't exist. Note also { increment: 1 } instead of reading commentCount and writing back + 1 yourself — that read-then-write pattern has its own race condition even inside a transaction block if done as two separate statements; Prisma's atomic operators push the increment down to the database itself.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.