N+1 Problems

This guide covers understanding N+1 query problems and how to solve them using Sonamu’s Subset and Relation features.

What is the N+1 Problem?

The N+1 problem is a performance issue where 1 main query triggers N additional queries.

Problem Example

// N+1 problem occurring
async function getUsersWithOrders() {
  // 1. Fetch users (1 query)
  const users = await UserModel.getPuri().select("id", "name");  // 100 users

  // 2. Fetch orders for each user (N queries)
  for (const user of users) {
    const orders = await OrderModel.getPuri()
      .select("*")
      .where({ user_id: user.id });

    user.orders = orders;  // 100 queries!
  }

  return users;
}

// Total queries: 1 + 100 = 101

Performance Impact:

100 users = 101 queries
1000 users = 1001 queries
Accumulated network round-trip time
Increased database load

Sonamu’s Solution

Sonamu automatically solves the N+1 problem through Subsets.

1. Define Subset

Define a Subset in the Entity:

// user.entity.json
{
  "name": "User",
  "properties": [
    {
      "name": "id",
      "type": "int",
      "isPrimary": true
    },
    {
      "name": "name",
      "type": "varchar"
    }
  ],
  "subsets": {
    "A": {
      "fields": ["id", "name"]
    },
    "WithOrders": {
      "fields": ["id", "name"],
      "relations": [
        {
          "name": "orders",
          "subset": "A"
        }
      ]
    }
  }
}

2. Use Subset

// Solving N+1 with Subset
async function getUsersWithOrders() {
  // Get query builder with getSubsetQueries
  const { qb } = UserModel.getSubsetQueries("WithOrders");

  // Execute automatically optimized query
  const result = await UserModel.executeSubsetQuery({
    subset: "WithOrders",
    qb,
    params: { num: 100, page: 1 }
  });

  // result.rows[0].orders is already loaded
  // No additional queries!

  return result.rows;
}

// Total queries: 2 (users 1 + orders 1)

Internal Operation:

Fetch users (1 query)
Fetch all orders with user IDs in batch (1 query)
Join in memory

Subset Usage Patterns

Single Relation

// post.entity.json
{
  "name": "Post",
  "subsets": {
    "WithAuthor": {
      "fields": ["id", "title", "content"],
      "relations": [
        {
          "name": "author",
          "subset": "A"
        }
      ]
    }
  }
}

// Fetch posts with authors
const { qb } = PostModel.getSubsetQueries("WithAuthor");
const result = await PostModel.executeSubsetQuery({
  subset: "WithAuthor",
  qb,
  params: { num: 20, page: 1 }
});

result.rows.forEach(post => {
  console.log(`${post.title} by ${post.author.name}`);
});

Multiple Relations

// order.entity.json
{
  "name": "Order",
  "subsets": {
    "Detail": {
      "fields": ["id", "total", "status"],
      "relations": [
        {
          "name": "user",
          "subset": "A"
        },
        {
          "name": "items",
          "subset": "A"
        },
        {
          "name": "payment",
          "subset": "A"
        }
      ]
    }
  }
}

// Order + user + items + payment info
const { qb } = OrderModel.getSubsetQueries("Detail");
const result = await OrderModel.executeSubsetQuery({
  subset: "Detail",
  qb: qb.where({ status: "completed" }),
  params: { num: 20, page: 1 }
});

// All relations are already loaded
result.rows.forEach(order => {
  console.log(`Order ${order.id}`);
  console.log(`User: ${order.user.name}`);
  console.log(`Items: ${order.items.length}`);
  console.log(`Payment: ${order.payment.method}`);
});

Nested Relations

// comment.entity.json
{
  "name": "Comment",
  "subsets": {
    "WithAll": {
      "fields": ["id", "content", "created_at"],
      "relations": [
        {
          "name": "author",
          "subset": "A"
        },
        {
          "name": "post",
          "subset": "WithAuthor"  // Nested: post + post author
        }
      ]
    }
  }
}

// Comment + comment author + post + post author
const { qb } = CommentModel.getSubsetQueries("WithAll");
const result = await CommentModel.executeSubsetQuery({
  subset: "WithAll",
  qb,
  params: { num: 20, page: 1 }
});

result.rows.forEach(comment => {
  console.log(`${comment.author.name}: ${comment.content}`);
  console.log(`  on "${comment.post.title}" by ${comment.post.author.name}`);
});

// Only 4 queries executed:
// 1. comments
// 2. comment authors
// 3. posts
// 4. post authors

Manual Solution with whereIn

When Subsets are not available, you can optimize with whereIn:

// N+1 problem
async function getUsersWithOrders() {
  const users = await UserModel.getPuri().select("id", "name");

  for (const user of users) {
    user.orders = await OrderModel.getPuri()
      .select("*")
      .where({ user_id: user.id });
  }

  return users;
}

// Optimized with whereIn
async function getUsersWithOrders() {
  // 1. Fetch users
  const users = await UserModel.getPuri().select("id", "name");

  const userIds = users.map(u => u.id);

  // 2. Fetch all orders at once
  const orders = await OrderModel.getPuri()
    .select("*")
    .whereIn("user_id", userIds);

  // 3. Map in memory
  const ordersByUserId = orders.reduce((acc, order) => {
    if (!acc[order.user_id]) acc[order.user_id] = [];
    acc[order.user_id].push(order);
    return acc;
  }, {} as Record<number, Order[]>);

  users.forEach(user => {
    user.orders = ordersByUserId[user.id] || [];
  });

  return users;
}

// 2 queries: users + orders

DataLoader Pattern

Use the DataLoader pattern when repeated queries are needed:

class OrderModelClass extends BaseModel {
  // Orders loader by user
  private userOrdersLoader = new DataLoader(async (userIds: number[]) => {
    const orders = await this.getPuri()
      .select("*")
      .whereIn("user_id", userIds);

    // Group by user_id
    const grouped = userIds.map(userId =>
      orders.filter(o => o.user_id === userId)
    );

    return grouped;
  });

  async getOrdersByUserId(userId: number): Promise<Order[]> {
    return this.userOrdersLoader.load(userId);
  }
}

// Usage
async function processUsers(users: User[]) {
  for (const user of users) {
    // DataLoader automatically batches
    const orders = await OrderModel.getOrdersByUserId(user.id);
    console.log(`${user.name}: ${orders.length} orders`);
  }
}

// Internally queries all at once with whereIn

Practical Examples

API Endpoint Optimization

class PostModelClass extends BaseModel {
  // N+1 occurring
  @api({ httpMethod: "GET" })
  async listPostsSlow() {
    const posts = await this.getPuri().select("id", "title", "content");

    // N+1 occurring!
    for (const post of posts) {
      post.author = await UserModel.findById(post.author_id);
      post.comments = await CommentModel.getPuri().where({ post_id: post.id });
    }

    return posts;
  }

  // Optimized with Subset
  @api({ httpMethod: "GET" })
  async listPostsFast() {
    const { qb } = this.getSubsetQueries("WithAuthorAndComments");

    return this.executeSubsetQuery({
      subset: "WithAuthorAndComments",
      qb: qb.orderBy("created_at", "desc"),
      params: { num: 20, page: 1 }
    });
  }
}

Dashboard Data

class DashboardService {
  // N+1 occurring
  async getDashboardSlow() {
    const users = await UserModel.getPuri().select("id", "name");

    const stats = [];
    for (const user of users) {
      const orderCount = await OrderModel.getPuri()
        .select({ count: Puri.count() })
        .where({ user_id: user.id });

      const totalSpent = await OrderModel.getPuri()
        .select({ total: Puri.sum("total") })
        .where({ user_id: user.id });

      stats.push({
        user: user.name,
        orderCount: orderCount[0].count,
        totalSpent: totalSpent[0].total
      });
    }

    return stats;
  }

  // Optimized with JOIN
  async getDashboardFast() {
    return UserModel.getPuri()
      .select(
        "users.id",
        "users.name",
        knex.raw("COUNT(orders.id) as order_count"),
        knex.raw("COALESCE(SUM(orders.total), 0) as total_spent")
      )
      .leftJoin("orders", "orders.user_id", "users.id")
      .groupBy("users.id");
  }
}

Detecting N+1

Query Logging

// sonamu.config.ts
export default {
  database: {
    defaultOptions: {
      debug: true  // Output all queries
    }
  }
} satisfies SonamuConfig;

Check the console after execution:

SELECT * FROM users;
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
...

Repetitive similar queries indicate an N+1 problem!

Detection with Tests

test("N+1 problem check", async () => {
  // Query counter
  let queryCount = 0;

  const db = UserModel.getPuri().getDB();
  db.on("query", () => queryCount++);

  // Test
  const { qb } = UserModel.getSubsetQueries("WithOrders");
  const result = await UserModel.executeSubsetQuery({
    subset: "WithOrders",
    qb: qb.limit(10),
    params: { num: 10, page: 1 }
  });

  // Verify query count
  expect(queryCount).toBeLessThan(5);  // Would be 11+ with N+1
  expect(result.rows).toHaveLength(10);
});

Performance Comparison

With N+1 Problem

Querying 100 users:
- Queries: 101
- Time: ~2000ms
- DB connections: 101 acquire/release

Optimized with Subset

Querying 100 users:
- Queries: 2
- Time: ~50ms
- DB connections: 2 acquire/release

Over 40x performance improvement!

Best Practices

1. Always Consider Subset First

// Using Subset
const { qb } = PostModel.getSubsetQueries("WithAuthor");
const result = await PostModel.executeSubsetQuery({
  subset: "WithAuthor",
  qb,
  params: { num: 20, page: 1 }
});

// Manual loading - avoid
const posts = await PostModel.getPuri();
for (const post of posts) {
  post.author = await UserModel.findById(post.author_id);
}

2. Define Necessary Subsets for API Responses

{
  "name": "User",
  "subsets": {
    "ForAPI": {
      "fields": ["id", "name", "email"],
      "relations": [
        {
          "name": "profile",
          "subset": "Public"
        }
      ]
    }
  }
}

3. Consider JOIN for Complex Queries

// Cases where JOIN is better than Subset:
// - Aggregation functions needed
// - Complex filtering
// - Large datasets

4. Monitor Queries During Development

// Query logging only in development
export default {
  database: {
    defaultOptions: {
      debug: process.env.NODE_ENV === "development"
    }
  }
} satisfies SonamuConfig;

Checklist

To prevent N+1 problems:

Avoid DB queries inside loops
Pre-load relation data with Subset
Use whereIn for bulk queries
Use JOIN for aggregations
Enable query logging during development
Verify query count with tests
Monitor API response times

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

What is the N+1 Problem?

Problem Example

Sonamu’s Solution

1. Define Subset

2. Use Subset

Subset Usage Patterns

Single Relation

Multiple Relations

Nested Relations

Manual Solution with whereIn

DataLoader Pattern

Practical Examples

API Endpoint Optimization

Dashboard Data

Detecting N+1

Query Logging

Detection with Tests

Performance Comparison

With N+1 Problem

Optimized with Subset

Best Practices

1. Always Consider Subset First

2. Define Necessary Subsets for API Responses

3. Consider JOIN for Complex Queries

4. Monitor Queries During Development

Checklist

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​What is the N+1 Problem?

​Problem Example

​Sonamu’s Solution

​1. Define Subset

​2. Use Subset

​Subset Usage Patterns

​Single Relation

​Multiple Relations

​Nested Relations

​Manual Solution with whereIn

​DataLoader Pattern

​Practical Examples

​API Endpoint Optimization

​Dashboard Data

​Detecting N+1

​Query Logging

​Detection with Tests

​Performance Comparison

​With N+1 Problem

​Optimized with Subset

​Best Practices

​1. Always Consider Subset First

​2. Define Necessary Subsets for API Responses

​3. Consider JOIN for Complex Queries

​4. Monitor Queries During Development

​Checklist

​Related Documentation

What is the N+1 Problem?

Problem Example

Sonamu’s Solution

1. Define Subset

2. Use Subset

Subset Usage Patterns

Single Relation

Multiple Relations

Nested Relations

Manual Solution with whereIn

DataLoader Pattern

Practical Examples

API Endpoint Optimization

Dashboard Data

Detecting N+1

Query Logging

Detection with Tests

Performance Comparison

With N+1 Problem

Optimized with Subset

Best Practices

1. Always Consider Subset First

2. Define Necessary Subsets for API Responses

3. Consider JOIN for Complex Queries

4. Monitor Queries During Development

Checklist

Related Documentation