N+1 Query Problem

// Fetching users and their posts - N+1 problem
async function getUsersWithPosts() {
  const users = await User.findAll();  // 1 query

  for (const user of users) {
    const posts = await user.getPosts();  // N queries (one per user)
    user.posts = posts;
  }

  return users;
}

// Results in: 1 + N queries
// With 100 users: 101 queries! 🔴

// Using eager loading with include
async function getUsersWithPosts() {
  const users = await User.findAll({
    include: [{ model: Post }]  // Eager load posts with JOIN
  });

  return users;
}

// Results in: 1 or 2 queries (depending on ORM strategy)
// Generated SQL: SELECT users.*, posts.* FROM users LEFT JOIN posts...
// 100 users: Still just 1-2 queries! ✅

// GraphQL resolver without batching
const resolvers = {
  User: {
    posts: async (user) => {
      // This runs once per user - N+1 problem!
      return await Post.findAll({ where: { userId: user.id } });
    }
  }
};

// Query for 10 users results in 11 database queries

import DataLoader from 'dataloader';

// Batch function fetches posts for multiple users at once
const batchLoadPosts = async (userIds) => {
  const posts = await Post.findAll({
    where: { userId: { [Op.in]: userIds } }
  });

  // Group by userId for DataLoader
  return userIds.map(id => posts.filter(p => p.userId === id));
};

const postLoader = new DataLoader(batchLoadPosts);

const resolvers = {
  User: {
    posts: (user) => postLoader.load(user.id)  // Batched!
  }
};

// Query for 10 users results in just 2 queries (users + batched posts)

// Fetch users
const users = await db.query('SELECT * FROM users');

// Then fetch posts for each user
for (const user of users) {
  const posts = await db.query(
    'SELECT * FROM posts WHERE user_id = ?',
    [user.id]
  );
  user.posts = posts;
}

// N+1 queries executed

// Fetch users and posts with JOIN
const results = await db.query(`
  SELECT
    users.*,
    posts.id as post_id,
    posts.title,
    posts.content
  FROM users
  LEFT JOIN posts ON users.id = posts.user_id
`);

// Group results in application code
const users = {};
for (const row of results) {
  if (!users[row.id]) {
    users[row.id] = { ...row, posts: [] };
  }
  if (row.post_id) {
    users[row.id].posts.push({
      id: row.post_id,
      title: row.title,
      content: row.content
    });
  }
}

// Single query for all data!

// Fetching users then posts separately
const users = await prisma.user.findMany();

// N+1 problem - query for each user
for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id }
  });
  user.posts = posts;
}

// Result: 1 + N queries 🔴

// Prisma automatically optimizes with include
const users = await prisma.user.findMany({
  include: {
    posts: true,  // Fetch posts with users
    profile: true // Can include multiple relations
  }
});

// Prisma generates optimized query
// Result: 1-2 queries total ✅

// For complex filtering:
const users = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5  // Only latest 5 posts per user
    }
  }
});

// TypeORM entity with lazy relations
@Entity()
class User {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToMany(() => Post, post => post.author)
  posts: Promise<Post[]>;  // Lazy loaded!
}

// Using lazy relations triggers N+1
const users = await userRepository.find();

for (const user of users) {
  const posts = await user.posts;  // New query each time 🔴
  console.log(posts.length);
}

// Load relations eagerly with find options
const users = await userRepository.find({
  relations: ['posts', 'profile'],  // Load all at once
});

// Or use QueryBuilder for more control
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'posts')
  .leftJoinAndSelect('user.profile', 'profile')
  .where('user.active = :active', { active: true })
  .getMany();

// Single optimized query with JOINs ✅

// For nested relations:
const users = await userRepository.find({
  relations: ['posts', 'posts.comments', 'profile']
});

// Frontend fetching users then posts separately
async function loadUserData() {
  // 1 API call
  const users = await fetch('/api/users').then(r => r.json());

  // N API calls - one per user!
  for (const user of users) {
    const posts = await fetch(`/api/users/${user.id}/posts`)
      .then(r => r.json());
    user.posts = posts;
  }

  return users;
}

// 100 users = 101 HTTP requests 🔴
// Each request has network latency (50-200ms)
// Total time: 5-20 seconds!

// API endpoint with include parameter
async function loadUserData() {
  // Single API call with include
  const users = await fetch('/api/users?include=posts,profile')
    .then(r => r.json());

  return users; // Already has posts and profile
}

// Backend implementation:
app.get('/api/users', async (req, res) => {
  const includes = req.query.include?.split(',') || [];

  const query = {
    include: {}
  };

  if (includes.includes('posts')) {
    query.include.posts = true;
  }
  if (includes.includes('profile')) {
    query.include.profile = true;
  }

  const users = await prisma.user.findMany(query);
  res.json(users);
});

// Alternative: Use GraphQL
// Client specifies exactly what it needs
const query = `
  query {
    users {
      id
      name
      posts { id title }
      profile { bio }
    }
  }
`;