N+1 Problems - 🌲 Sonamu

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("r").table("users").select("id", "name"); // 100 users

  // 2. Fetch orders for each user (N queries)
  for (const user of users) {
    const orders = await OrderModel.getPuri("r")
      .table("orders")
      .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("r").table("users").select("id", "name");

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

  return users;
}

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

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

  // 2. Fetch all orders at once
  const orders = await OrderModel.getPuri("r")
    .table("orders")
    .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 BaseModelClass {
  // Orders loader by user
  private userOrdersLoader = new DataLoader(async (userIds: number[]) => {
    const orders = await this.getPuri("r").table("orders").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 BaseModelClass {
  // N+1 occurring
  @api({ httpMethod: "GET" })
  async listPostsSlow() {
    const posts = await this.getPuri("r").table("posts").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("r")
        .table("comments")
        .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("r").table("users").select("id", "name");

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

      const totalSpent = await OrderModel.getPuri("r")
        .table("orders")
        .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("r")
      .table("users")
      .select({
        id: "users.id",
        name: "users.name",
        order_count: Puri.count("orders.id"),
        total_spent: Puri.rawNumber("COALESCE(SUM(orders.total), 0)"),
      })
      .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.getDB("r");
  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("r").table("posts").select("*");
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

​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