nodejs-store

Avoiding N+1 reads

The problem

An endpoint lists a page of parents (orders) and then, for each parent, goes back to the database for its children (order items). The first version is one query for the list plus one query per parent:

// N+1: 1 query for the list + 1 query per parent
const orders = await store.query(
  'Order($condition:@c0,$sort:@s0,$limit:@l){ _id, code }',
  { c0: { status: 'open' }, s0: { createdAt: -1 }, l: 20 },
);

for (const order of orders) {
  order.items = await store.query(
    'OrderItem($condition:@c0,$sort:@s0,$limit:@l){ sku, qty }',
    { c0: { orderId: order._id }, s0: { qty: -1 }, l: 5 },
  );
}

A list of 20 parents costs 21 round trips, and the cost grows with the size of the list. Each trip also re-parses the same shape and re-runs the same permission checks, and nothing keeps the parent list and its children consistent with each other — a concurrent write between the two loops is invisible to the result.

Why nodejs-store

A relation is declared once in the schema, not spelled out per read:

relations: {
  items: { model: 'OrderItem', type: 'many', localField: '_id', foreignField: 'orderId' },
}

Referencing the relation name inside the GQL selection set resolves it as part of the same read:

You do not hand-write the child query, so it cannot drift from the parent query: the same $condition / $sort / $skip / $limit semantics apply at both levels, and permissions and computed columns are resolved against the schema for each level.

Walkthrough

const { store } = require('nodejs-store');

store.register({
  name: 'Order',
  collection: 'orders',
  idPrefix: 'OD',
  timestamps: true,
  fields: {
    code: { type: 'string', default: '' },
    status: { type: 'string', default: 'draft' },
  },
  relations: {
    items: { model: 'OrderItem', type: 'many', localField: '_id', foreignField: 'orderId' },
  },
  indexes: [{ keys: { status: 1, createdAt: -1 } }],
});

store.register({
  name: 'OrderItem',
  collection: 'order_items',
  idPrefix: 'OI',
  timestamps: true,
  fields: {
    orderId: { type: 'string' },
    sku: { type: 'string', default: '' },
    qty: { type: 'int', default: 1 },
  },
});

// One read: the parents plus a per-parent window of their items.
const orders = await store.query(
  'Order($condition:@c0,$sort:@s0){ _id, code, status, items($sort:@s1,$limit:@l1){ sku, qty } }',
  {
    c0: { status: 'open' },
    s0: { createdAt: -1 },
    s1: { qty: -1 },
    l1: 5,
  },
);
// orders = [{ _id, code, status, items: [{ sku, qty }, ...] }, ...]

That read compiles to one native execution per backend:

// MongoDB — one aggregation pipeline for the whole list
[
  { $match: { status: 'open' } },
  { $sort: { createdAt: -1 } },
  {
    $lookup: {
      from: 'order_items',
      let: { rel__id: { $ifNull: ['$_id', null] } },
      pipeline: [
        { $match: { $expr: { $eq: ['$orderId', '$$rel__id'] } } },
        { $sort: { qty: -1 } },
        { $limit: 5 },
        { $project: { _id: 1, sku: 1, qty: 1 } },
      ],
      as: 'items',
    },
  },
]

-- MySQL / PostgreSQL / SQLite — one statement for the whole list (shape; identifiers are
-- back-quoted on MySQL and double-quoted elsewhere, PostgreSQL binds $n instead of ?)
SELECT t."_id", t."code", t."status",
       r0."sku" AS "items_0_sku", r0."qty" AS "items_0_qty"
FROM "orders" t
LEFT JOIN (
  SELECT * FROM (
    SELECT c.*, ROW_NUMBER() OVER (PARTITION BY c."orderId" ORDER BY c."qty" DESC) AS "__rn"
    FROM "order_items" c
  ) w WHERE w."__rn" <= 5
) r0 ON r0."orderId" = t."_id"
WHERE t."status" = ?
ORDER BY t."createdAt" DESC;

The child rows come back flat and are rehydrated into the nested items array per parent, so the application sees the same document shape it would have assembled by hand.

Paging the parents

Adding a parent window to the same read is where the shape changes. With a root $skip / $limit, the planner switches to a two-phase shape: phase one selects the page’s _ids, phase two matches those _ids and resolves the relations.

const orders = await store.query(
  'Order($condition:@c0,$sort:@s0,$skip:@sk,$limit:@l0){ _id, code, items($sort:@s1,$limit:@l1){ sku, qty } }',
  { c0: { status: 'open' }, s0: { createdAt: -1 }, sk: 20, l0: 10, s1: { qty: -1 }, l1: 5 },
);

// Phase 1 — page the parent ids
[{ $match: { status: 'open' } }, { $sort: { createdAt: -1 } }, { $skip: 20 }, { $limit: 10 },
 { $project: { _id: 1 } }]

// Phase 2 — resolve the relations for exactly those ids
[{ $match: { _id: { $in: ['...ids from phase 1...'] } } },
 { $lookup: { /* items, as above */ } },
 { $project: { _id: 1, code: 1, items: 1 } }]

That is two native executions (two aggregations on MongoDB; two SQL statements on the other backends), not one, and the ordering is restored from the phase-one _id order. It is still not N+1: the number of executions is fixed, independent of how many parents the page contains. The two-phase shape is used when the read has a relation and a root $skip / $limit, and the root $sort does not reference a relation field.

What is paged

Pitfalls

See also