What is the N+1 query problem and how do I fix it?

The N+1 problem occurs when you fetch a list of N records and then execute one additional query per record to fetch related data — resulting in N+1 total queries. For example, fetching 20 posts and then querying the author for each post individually runs 21 queries. Fix it by eager-loading relations: in Prisma use include, in Mongoose use populate, in raw SQL use a JOIN. The result is always a single query (or at most two with Prisma's query splitting strategy).

When should I embed documents vs use references in MongoDB?

Embed when the nested data is always read together with the parent (e.g., a user's address), the nested data is owned exclusively by the parent, and the nested data is bounded in size (won't grow indefinitely). Reference when the nested data is shared between multiple parents (e.g., tags on posts), when the nested data is large, or when you need to query the nested data independently. The MongoDB rule of thumb: if you always access it together, embed it. If you access it separately, reference it.

What is a junction table and when do I need one?

A junction table (also called a join table or pivot table) is an intermediary table that represents a many-to-many relationship between two tables. For example, a Post can have many Tags and a Tag can belong to many Posts — the junction table PostTag stores pairs of (postId, tagId). Prisma generates and manages this table automatically using the @relation directive with two sides of the relation. You need a junction table any time two entities have a many-to-many relationship.

What is the difference between INNER JOIN, LEFT JOIN, and LEFT JOIN with null check?

INNER JOIN returns only rows where both tables have a match — useful when you only want records that have related data. LEFT JOIN returns all rows from the left table plus matching rows from the right — useful when related data is optional (e.g., users without posts). LEFT JOIN with IS NULL in the WHERE clause finds rows in the left table that have no match in the right table — useful for finding orphaned records or users who have never posted.

How does Prisma handle many-to-many relations?

Prisma supports two kinds of many-to-many. Implicit many-to-many: add two @relation arrays and Prisma creates the hidden junction table automatically. Explicit many-to-many: create the junction model yourself so you can add extra fields to the relationship (e.g., a role field on a User-Team junction). Use implicit when the relationship has no additional data, and explicit when it does.

← Back to Node.js Full‑Stack Course

Database Relationships & Joins in Node.js

Every non-trivial application has related data. Users write posts. Posts have comments. Products belong to categories. Orders contain line items. How you model and query those relationships determines whether your application stays fast and correct as data grows.

This module covers relationships in both MongoDB (Mongoose) and PostgreSQL (Prisma), because most Node.js developers work with both at some point. The patterns are different but the underlying concepts — one-to-one, one-to-many, many-to-many — are universal.

This is Module 16 of the Node.js Full‑Stack Developer course.

The Three Relationship Types

Type	Example	SQL mechanism	MongoDB mechanism
One-to-one	User ↔ Profile	Foreign key + UNIQUE	Embedded doc or `$ref`
One-to-many	User → Posts	Foreign key	Array of refs or embedded
Many-to-many	Posts ↔ Tags	Junction table	Array of refs on both sides

Relationships in PostgreSQL with Prisma

One-to-One

A user has exactly one profile. The profile cannot exist without a user.

prisma

model User {
  id      String   @id @default(cuid())
  email   String   @unique
  profile Profile?
}

model Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?

  userId String @unique          // UNIQUE ensures one-to-one
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

// Create user with profile
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    profile: {
      create: { bio: 'Full-stack developer' },
    },
  },
  include: { profile: true },
});

// Fetch user with profile
const user = await prisma.user.findUnique({
  where: { id },
  include: { profile: true },
});

// Update profile through user
await prisma.user.update({
  where: { id },
  data: {
    profile: {
      upsert: {
        create: { bio: 'New bio' },
        update: { bio: 'New bio' },
      },
    },
  },
});

One-to-Many

A user writes many posts. A post belongs to exactly one user.

prisma

model User {
  id    String @id @default(cuid())
  email String @unique
  posts Post[]
}

model Post {
  id       String @id @default(cuid())
  title    String
  authorId String
  author   User   @relation(fields: [authorId], references: [id])

  @@index([authorId])
}

// Create a post for an existing user
const post = await prisma.post.create({
  data: {
    title: 'Hello World',
    body: '...',
    authorId: userId,
  },
});

// Fetch user with their posts (eager load)
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 10,
    },
  },
});

// Fetch posts with their author
const posts = await prisma.post.findMany({
  where: { published: true },
  include: {
    author: { select: { id: true, name: true } },
  },
});

Many-to-Many (Implicit)

A post has many tags. A tag belongs to many posts. No extra data on the relationship.

prisma

model Post {
  id   String @id @default(cuid())
  tags Tag[]  @relation("PostTags")
}

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

Prisma creates the hidden _PostTags junction table automatically.

// Create post with tags (connect or create)
const post = await prisma.post.create({
  data: {
    title: 'My Post',
    body: '...',
    authorId: userId,
    tags: {
      connectOrCreate: [
        { where: { name: 'nodejs' }, create: { name: 'nodejs' } },
        { where: { name: 'express' }, create: { name: 'express' } },
      ],
    },
  },
  include: { tags: true },
});

// Add a tag to an existing post
await prisma.post.update({
  where: { id: postId },
  data: { tags: { connect: { name: 'javascript' } } },
});

// Remove a tag from a post
await prisma.post.update({
  where: { id: postId },
  data: { tags: { disconnect: { name: 'javascript' } } },
});

// Find all posts with a given tag
const posts = await prisma.post.findMany({
  where: { tags: { some: { name: 'nodejs' } } },
  include: { tags: true },
});

Many-to-Many (Explicit — with extra data)

When the relationship itself carries data (e.g., a user is a member of a team with a specific role):

prisma

model User {
  id          String       @id @default(cuid())
  memberships Membership[]
}

model Team {
  id          String       @id @default(cuid())
  name        String
  memberships Membership[]
}

model Membership {
  id        String   @id @default(cuid())
  role      String   @default("MEMBER")
  joinedAt  DateTime @default(now())

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

  teamId String
  team   Team   @relation(fields: [teamId], references: [id], onDelete: Cascade)

  @@unique([userId, teamId])   // a user can only be in a team once
  @@index([teamId])
}

// Add a user to a team as admin
await prisma.membership.create({
  data: { userId, teamId, role: 'ADMIN' },
});

// Get all teams a user belongs to
const memberships = await prisma.membership.findMany({
  where: { userId },
  include: {
    team: { select: { id: true, name: true } },
  },
});

// Get all members of a team
const members = await prisma.membership.findMany({
  where: { teamId },
  include: {
    user: { select: { id: true, name: true, email: true } },
  },
});

Raw SQL Joins with Prisma

For complex queries, use $queryRaw with explicit JOINs:

// Get posts with author name and tag count
const posts = await prisma.$queryRaw`
  SELECT
    p.id,
    p.title,
    p."createdAt",
    u.name AS "authorName",
    COUNT(pt."B") AS "tagCount"
  FROM "Post" p
  INNER JOIN "User" u ON u.id = p."authorId"
  LEFT JOIN "_PostTags" pt ON pt."A" = p.id
  WHERE p.published = true
  GROUP BY p.id, u.name
  ORDER BY p."createdAt" DESC
  LIMIT ${take} OFFSET ${skip}
`;

JOIN Types

sql

-- INNER JOIN — only posts that have an author (should always match due to FK)
SELECT p.title, u.name
FROM "Post" p
INNER JOIN "User" u ON u.id = p."authorId";

-- LEFT JOIN — all users, even those with no posts
SELECT u.name, COUNT(p.id) AS post_count
FROM "User" u
LEFT JOIN "Post" p ON p."authorId" = u.id
GROUP BY u.id, u.name;

-- Users who have never written a post
SELECT u.name
FROM "User" u
LEFT JOIN "Post" p ON p."authorId" = u.id
WHERE p.id IS NULL;

Relationships in MongoDB with Mongoose

MongoDB is schema-less at the database level, so you choose your relationship strategy in application code.

Embedding (Denormalization)

Embed when the data is always read together and owned exclusively by the parent:

const orderSchema = new mongoose.Schema({
  userId: { type: mongoose.Schema.Types.ObjectId, ref: 'User', required: true },
  status: { type: String, enum: ['pending', 'paid', 'shipped'], default: 'pending' },
  // Embed line items — they belong entirely to this order
  items: [
    {
      productId: mongoose.Schema.Types.ObjectId,
      name: String,       // denormalized for read speed
      price: Number,      // snapshot at time of order
      quantity: Number,
    },
  ],
  total: Number,
}, { timestamps: true });

The name and price are intentionally duplicated from the Product document. This is a snapshot — if a product's price changes tomorrow, this order still reflects what the customer paid.

Referencing (Normalization)

Reference when data is shared across documents or queried independently:

const postSchema = new mongoose.Schema({
  title: String,
  body: String,
  author: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true,
  },
  tags: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Tag' }],
}, { timestamps: true });

Populate (Eager Loading References)

// Basic populate
const posts = await Post
  .find({ published: true })
  .populate('author', 'name email')      // select specific fields
  .populate('tags', 'name')
  .lean();

// Nested populate
const post = await Post.findById(id)
  .populate({
    path: 'comments',
    populate: {
      path: 'author',
      select: 'name avatar',
    },
    options: { sort: { createdAt: -1 }, limit: 10 },
  })
  .lean();

// Conditional populate
const user = await User.findById(id)
  .populate({
    path: 'posts',
    match: { published: true },    // only populate published posts
    select: 'title createdAt',
    options: { limit: 5 },
  })
  .lean();

MongoDB Aggregation — $lookup (JOIN equivalent)

For complex joins in MongoDB, use the aggregation pipeline's $lookup:

// Posts with author details (equivalent to SQL JOIN)
const posts = await Post.aggregate([
  { $match: { published: true } },
  {
    $lookup: {
      from: 'users',          // collection name (lowercase plural)
      localField: 'author',   // field in Post
      foreignField: '_id',    // field in User
      as: 'authorDetails',
      pipeline: [
        { $project: { name: 1, email: 1, _id: 1 } },
      ],
    },
  },
  { $unwind: '$authorDetails' },   // flatten array to object
  {
    $project: {
      title: 1,
      createdAt: 1,
      'authorDetails.name': 1,
      'authorDetails.email': 1,
    },
  },
  { $sort: { createdAt: -1 } },
  { $skip: skip },
  { $limit: limit },
]);

The N+1 Query Problem

The most common performance mistake with relationships:

// ❌ N+1 — fetches posts then makes one DB call per post for the author
const posts = await Post.find();
for (const post of posts) {
  post.author = await User.findById(post.author);  // N extra queries!
}

// ✅ One query — populate fetches all authors in a single additional query
const posts = await Post.find().populate('author', 'name email').lean();

// ❌ N+1 in Prisma — manually fetching related data in a loop
const posts = await prisma.post.findMany();
for (const post of posts) {
  post.author = await prisma.user.findUnique({ where: { id: post.authorId } });
}

// ✅ Single query — Prisma's include resolves all relations efficiently
const posts = await prisma.post.findMany({
  include: { author: { select: { id: true, name: true } } },
});

Detecting N+1 in Development

Enable Prisma query logging to see what SQL is actually running:

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

For Mongoose, enable debug mode:

mongoose.set('debug', true);

If you see the same query repeated N times with different IDs, you have an N+1 problem.

Selecting Only What You Need

Always project to the minimum set of fields required. Fetching unused data wastes bandwidth and memory:

// Prisma — select only needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    // NOT: password, role, createdAt, updatedAt
  },
});

// Mongoose — project fields
const users = await User.find().select('name email').lean();

For deeply nested includes, always add select at every level:

const post = await prisma.post.findUnique({
  where: { id },
  include: {
    author: {
      select: { id: true, name: true },     // ← don't load password, role, etc.
    },
    tags: {
      select: { name: true },               // ← only the tag name
    },
    _count: {
      select: { comments: true },           // ← just the count, not the data
    },
  },
});

Cascading Deletes

When a parent is deleted, related records should be handled explicitly:

prisma

// Prisma — cascade delete profile when user is deleted
model Profile {
  userId String @unique
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

// Restrict — prevent deleting a user who has posts
model Post {
  authorId String
  author   User   @relation(fields: [authorId], references: [id], onDelete: Restrict)
}

// SetNull — set authorId to null when user is deleted
model Post {
  authorId String?
  author   User?  @relation(fields: [authorId], references: [id], onDelete: SetNull)
}

In Mongoose, cascade deletes must be handled manually with middleware:

// Delete all posts when a user is deleted
userSchema.pre('findOneAndDelete', async function (next) {
  const userId = this.getQuery()['_id'];
  await Post.deleteMany({ author: userId });
  next();
});

Node.js Full‑Stack Course — Module 16 of 32

You now understand how to model and query relationships in both SQL and MongoDB. Continue to Module 17 to add Redis caching and dramatically improve API performance.

Summary

Relationships are the backbone of any data model:

One-to-one: unique foreign key; in Prisma add @unique on the FK field; in Mongoose embed or reference
One-to-many: foreign key on the "many" side; always index it; use include or populate to avoid N+1
Many-to-many: junction table in SQL (implicit or explicit in Prisma); array of refs in MongoDB
The N+1 problem is always caused by lazy-loading inside a loop — fix it with include (Prisma), populate (Mongoose), or a JOIN
Always select only the fields you need — never load the full document when a projection suffices
Decide embed vs reference in MongoDB based on access pattern: always-together → embed, independently-queried → reference
Cascading deletes must be defined explicitly — both Prisma (onDelete) and Mongoose (pre-hooks) require deliberate configuration

Continue to Module 17: Caching with Redis →