Prisma/TypeORM

ORMs (Prisma, TypeORM) let you write JS/TS code instead of raw SQL, but merely "knowing how to use" them without understanding the underlying generated SQL easily leads to poor performance optimization decisions (placing indexes in the wrong spots, misidentifying N+1 issues, or not knowing when pagination is required). This section walks through a concrete example: using Prisma's include to fetch relational data (e.g. fetching an Order along with its related Customer)—seemingly straightforward, yet Prisma's underlying execution contains several easily misunderstood details.

1. include does not always generate a single JOIN

You can inspect the exact raw SQL Prisma generates via query logging:

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

With findMany({ where: {...}, include: { customer: true } }), Prisma typically splits the operation into 2 fixed queries rather than a single JOIN:

SELECT * FROM "Order" WHERE status = 'pending';
SELECT * FROM "Customer" WHERE id IN (1, 5, 9, ...);

• Not an N+1 problem: the query count is fixed (2 queries) and does not grow with the number of rows returned from the Order table. Deeply nested includes (e.g. include: { customer: { include: { address: true } } }) increase query count relative to the depth of the relations, not the number of rows.
• True N+1 risks still occur if you manually write loops executing individual findUnique/findMany calls per row, which is unrelated to include.

2. Knowing the underlying SQL ensures correct index placement

• In the example above: Order.status requires an index (used in the WHERE clause of query 1). Customer.id is typically already indexed by default as the primary key, requiring no extra indexes for query 2 (WHERE id IN (...)).
• Failing to understand how Prisma breaks down queries easily leads to placing indexes on the wrong columns or overlooking the columns that actually need optimization.

3. Avoid unbounded findMany() on large tables

• db.order.findMany({ where: { status: 'pending' } }) without take/limit: if 500,000 records match the condition, Node must hold all 500,000 objects in RAM simultaneously.
• Consequence matches the large-file fs.readFile() issue covered in Phase 1: risk of heap out-of-memory errors, server slowdowns, and network bottlenecks caused by transmitting massive JSON payloads to clients.
• Mitigate using pagination:

const orders = await db.order.findMany({
  where: { status: 'pending' },
  take: 20,
  skip: (page - 1) * 20,
});

4. Offset-based vs. cursor-based pagination

• Offset-based (skip/take): the database must still scan through all skipped rows before fetching the requested slice. As page numbers increase (higher skip values), queries become progressively slower, with complexity increasing almost linearly with the page depth.
• Cursor-based: uses a value from the last row of the previous page (typically id) as a reference marker:

const orders = await db.order.findMany({
  where: { status: 'pending', id: { gt: lastSeenId } },
  take: 20,
  orderBy: { id: 'asc' },
});

Because id is indexed, WHERE id > lastSeenId leverages the index to jump directly to the exact position, keeping latency constant regardless of how deep the pagination goes (there is no strict concept of page numbers, only "continue from the last cursor"). Trade-off: cannot jump directly to an arbitrary page number; can only paginate forward or backward sequentially.