1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

// Eager loading examples across different ORMs
 
// TypeORM - using 'relations' option
const order = await orderRepository.findOne({
  where: { id: orderId },
  relations: ['items', 'items.product', 'customer']
});
// Single query (or optimized batch queries)
// order.items and order.customer are fully loaded
 
// Prisma - using 'include'
const order = await prisma.order.findUnique({
  where: { id: orderId },
  include: {
    items: {
      include: { product: true }
    },
    customer: true
  }
});
// All related data loaded immediately
 
// Sequelize - using 'include' in query options
const order = await Order.findByPk(orderId, {
  include: [
    { model: OrderItem, as: 'items', include: [{ model: Product }] },
    { model: Customer, as: 'customer' }
  ]
});
 
// Entity Framework (C#) - using 'Include'
var order = await context.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .Include(o => o.Customer)
    .FirstOrDefaultAsync(o => o.Id == orderId);
 
// Hibernate (Java) - using JOIN FETCH in JPQL
var order = entityManager.createQuery(
    "SELECT o FROM Order o " +
    "JOIN FETCH o.items i " +
    "JOIN FETCH i.product " +
    "JOIN FETCH o.customer " +
    "WHERE o.id = :id", Order.class)
    .setParameter("id", orderId)
    .getSingleResult();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Pattern 1: JOIN-based (single query, possibly large result)
SELECT 
    o.id, o.order_date, o.status,
    c.id as customer_id, c.name as customer_name, c.email,
    i.id as item_id, i.quantity, i.unit_price,
    p.id as product_id, p.name as product_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items i ON o.id = i.order_id
LEFT JOIN products p ON i.product_id = p.id
WHERE o.id = ?;
 
-- Note: If Order has 10 items, this returns 10 rows
-- The ORM de-duplicates to create one Order with 10 Items
 
-- Pattern 2: Batch queries (multiple queries, cleaner results)
-- Query 1: Get the order
SELECT * FROM orders WHERE id = ?;
 
-- Query 2: Get items for this order
SELECT * FROM order_items WHERE order_id = ?;
 
-- Query 3: Get products for these items
SELECT * FROM products WHERE id IN (?, ?, ?, ...);
 
-- Query 4: Get customer
SELECT * FROM customers WHERE id = ?;
 
-- Total: 4 queries instead of potentially N+1
-- Each query returns clean, non-duplicated rows

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

// Lazy loading behavior (conceptual illustration)
 
class Order {
  private id: string;
  private customerProxy: LazyProxy<Customer>;
  private itemsProxy: LazyProxy<OrderItem[]>;
  
  // Called by ORM during hydration
  _setCustomerProxy(proxy: LazyProxy<Customer>): void {
    this.customerProxy = proxy;
  }
  
  // Access triggers lazy load
  getCustomer(): Customer {
    if (!this.customerProxy.isLoaded()) {
      // This triggers: SELECT * FROM customers WHERE id = ?
      this.customerProxy.load();
    }
    return this.customerProxy.getValue();
  }
  
  getItems(): OrderItem[] {
    if (!this.itemsProxy.isLoaded()) {
      // This triggers: SELECT * FROM order_items WHERE order_id = ?
      this.itemsProxy.load();
    }
    return this.itemsProxy.getValue();
  }
}
 
// Usage feels natural
const order = await orderRepo.findById(orderId);
// Only order data loaded
 
const customer = order.getCustomer(); 
// NOW customer is loaded (triggers query)
 
const items = order.getItems();
// NOW items are loaded (triggers another query)
 
// Problem: In a loop, this is disaster
const orders = await orderRepo.findAll(); // 1 query
for (const order of orders) {
  console.log(order.getCustomer().name); // 1 query per order!
  // For 100 orders = 100 additional queries
}
// Total: 101 queries when 2 would suffice

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

// THE N+1 PROBLEM IN ACTION
 
// ❌ PROBLEMATIC: N+1 queries hidden in innocent-looking code
async function getOrderSummaries(): Promise<OrderSummary[]> {
  // Query 1: Get all orders
  const orders = await orderRepo.findAll(); // SELECT * FROM orders
  
  const summaries: OrderSummary[] = [];
  
  for (const order of orders) {
    // Each iteration triggers lazy loads:
    // Query 2, 3, 4, ...: Get customer for each order
    const customer = order.getCustomer(); // SELECT * FROM customers WHERE id = ?
    
    // Query N+1, N+2, ...: Get items for each order  
    const items = order.getItems(); // SELECT * FROM order_items WHERE order_id = ?
    
    summaries.push({
      orderId: order.id,
      customerName: customer.name,
      itemCount: items.length,
      total: items.reduce((sum, i) => sum + i.subtotal, 0)
    });
  }
  
  return summaries;
}
 
// For 100 orders:
// - 1 query for orders
// - 100 queries for customers
// - 100 queries for items
// Total: 201 queries!
 
// ✅ SOLUTION 1: Eager loading
async function getOrderSummariesOptimized(): Promise<OrderSummary[]> {
  // Single query (or small number of batch queries)
  const orders = await orderRepo.findAll({
    relations: ['customer', 'items']
  });
  
  return orders.map(order => ({
    orderId: order.id,
    customerName: order.customer.name,
    itemCount: order.items.length,
    total: order.items.reduce((sum, i) => sum + i.subtotal, 0)
  }));
}
// Total: 1-3 queries regardless of order count
 
// ✅ SOLUTION 2: Batch loading
async function getOrderSummariesBatch(): Promise<OrderSummary[]> {
  // Query 1: Get all orders
  const orders = await orderRepo.findAll();
  
  // Query 2: Batch load all needed customers
  const customerIds = [...new Set(orders.map(o => o.customerId))];
  const customers = await customerRepo.findByIds(customerIds);
  const customerMap = new Map(customers.map(c => [c.id, c]));
  
  // Query 3: Batch load all needed items
  const orderIds = orders.map(o => o.id);
  const allItems = await orderItemRepo.findByOrderIds(orderIds);
  const itemsByOrder = groupBy(allItems, 'orderId');
  
  return orders.map(order => ({
    orderId: order.id,
    customerName: customerMap.get(order.customerId)!.name,
    itemCount: itemsByOrder.get(order.id)?.length ?? 0,
    total: (itemsByOrder.get(order.id) ?? [])
      .reduce((sum, i) => sum + i.subtotal, 0)
  }));
}
// Total: 3 queries regardless of order count

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

// Explicit loading pattern
 
class Order {
  private id: string;
  private customerId: string;
  private customer: Customer | null = null;
  private items: OrderItem[] | null = null;
  
  // ID is always available (stored on entity)
  getCustomerId(): string {
    return this.customerId;
  }
  
  // Full entity might not be loaded
  getCustomer(): Customer {
    if (this.customer === null) {
      throw new Error("Customer not loaded. Call loadCustomer() first.");
    }
    return this.customer;
  }
  
  isCustomerLoaded(): boolean {
    return this.customer !== null;
  }
  
  // Explicit load method - clear about what it does
  async loadCustomer(): Promise<void> {
    this.customer = await this.customerRepository.findById(this.customerId);
  }
  
  getItems(): ReadonlyArray<OrderItem> {
    if (this.items === null) {
      throw new Error("Items not loaded. Call loadItems() first.");
    }
    return this.items;
  }
  
  async loadItems(): Promise<void> {
    this.items = await this.orderItemRepository.findByOrderId(this.id);
  }
}
 
// Usage is explicit about database calls
const order = await orderRepo.findById(orderId);
 
// This is clear that database calls happen
await order.loadCustomer();
await order.loadItems();
 
// Now safe to access
const customer = order.getCustomer();
const items = order.getItems();
 
// Batch loading pattern with explicit loading
async function processOrders(orders: Order[]): Promise<void> {
  // Batch load all customers at once
  const customerIds = orders.map(o => o.getCustomerId());
  const customers = await customerRepo.findByIds(customerIds);
  const customerMap = new Map(customers.map(c => [c.id, c]));
  
  // Assign loaded customers to orders
  for (const order of orders) {
    order._setCustomer(customerMap.get(order.getCustomerId())!);
  }
  
  // Batch load all items at once
  const orderIds = orders.map(o => o.id);
  const allItems = await itemRepo.findByOrderIds(orderIds);
  const itemsByOrder = groupBy(allItems, 'orderId');
  
  for (const order of orders) {
    order._setItems(itemsByOrder.get(order.id) ?? []);
  }
  
  // Now process with everything loaded
  for (const order of orders) {
    // No database calls happen here
    processOrder(order, order.getCustomer(), order.getItems());
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

// Strategy selection in practice
 
class OrderRepository {
  // List view: minimal data, optimized for table display
  async findAllForListView(pagination: Pagination): Promise<OrderListItem[]> {
    return this.prisma.order.findMany({
      select: {
        id: true,
        orderDate: true,
        status: true,
        // Include just what the list needs
        customer: { select: { id: true, name: true } },
        _count: { select: { items: true } }, // Count, not full items
        total: true
      },
      ...pagination
    });
  }
  
  // Detail view: full data for single order display
  async findByIdWithDetails(id: string): Promise<Order> {
    return this.prisma.order.findUnique({
      where: { id },
      include: {
        customer: true,
        items: {
          include: { product: true },
          orderBy: { createdAt: 'asc' }
        },
        shippingAddress: true,
        payments: true
      }
    });
  }
  
  // Bulk processing: batch load for efficiency
  async findAllForProcessing(ids: string[]): Promise<Order[]> {
    const orders = await this.prisma.order.findMany({
      where: { id: { in: ids } }
    });
    
    // Batch load items for all orders at once
    const items = await this.prisma.orderItem.findMany({
      where: { orderId: { in: ids } },
      include: { product: true }
    });
    
    // Attach items to orders
    const itemsByOrderId = groupBy(items, 'orderId');
    for (const order of orders) {
      order.items = itemsByOrderId.get(order.id) ?? [];
    }
    
    return orders;
  }
  
  // Reports: raw projection, no entity overhead
  async getRevenueByCustomer(): Promise<RevenueReport[]> {
    return this.prisma.$queryRaw`
      SELECT 
        c.id as customer_id,
        c.name as customer_name,
        COUNT(o.id) as order_count,
        SUM(o.total) as total_revenue
      FROM customers c
      LEFT JOIN orders o ON c.id = o.customer_id
      WHERE o.status = 'COMPLETED'
      GROUP BY c.id, c.name
      ORDER BY total_revenue DESC
    `;
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

// DataLoader batches multiple load requests into single query
 
import DataLoader from 'dataloader';
 
// Create loader (typically per-request in web apps)
const customerLoader = new DataLoader<string, Customer>(async (ids) => {
  // ids might be [id1, id3, id7] - accumulated from multiple resolve calls
  const customers = await customerRepo.findByIds(ids as string[]);
  
  // Must return in same order as input ids
  const customerMap = new Map(customers.map(c => [c.id, c]));
  return ids.map(id => customerMap.get(id)!);
});
 
// Usage in resolvers (GraphQL example)
const resolvers = {
  Order: {
    customer: (order: Order) => {
      // This just queues the load
      return customerLoader.load(order.customerId);
    }
  }
};
 
// When resolving many orders, DataLoader:
// 1. Collects all customerLoader.load(id) calls
// 2. Deduplicates IDs
// 3. Makes ONE batch query
// 4. Returns individual results to each caller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

// Instead of loading full Customer entity just for name
const order = await orderRepo.findById(orderId, {
  include: { customer: true } // Loads all customer fields
});
const name = order.customer.name;
 
// Load only what you need
const order = await orderRepo.findById(orderId, {
  include: {
    customer: {
      select: { id: true, name: true } // Only id and name
    }
  }
});
// customer object has only id and name - smaller memory, faster query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

// Instead of one giant JOIN
const order = await orderRepo.findWithEverything(orderId);
// SELECT * FROM orders 
// JOIN customers ...
// JOIN order_items ...
// JOIN products ...
// JOIN shipping ...
// JOIN payments ...
// Result: massive row duplication
 
// Split into parallel queries
async function loadOrderWithDetails(orderId: string): Promise<Order> {
  const [order, items, shipping, payments] = await Promise.all([
    orderRepo.findById(orderId, { include: { customer: true } }),
    orderItemRepo.findByOrderId(orderId, { include: { product: true } }),
    shippingRepo.findByOrderId(orderId),
    paymentRepo.findByOrderId(orderId)
  ]);
  
  // Assemble in memory
  order.items = items;
  order.shipping = shipping;
  order.payments = payments;
  
  return order;
}
// 4 parallel queries, no row duplication, often faster overall

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

// NEVER do this for potentially large collections
const allItems = await order.getItems(); // Could be millions
 
// Instead, always paginate
interface PaginatedResult<T> {
  items: T[];
  cursor: string | null;
  hasMore: boolean;
}
 
class Order {
  async getItemsPage(cursor?: string, limit: number = 20): Promise<PaginatedResult<OrderItem>> {
    const items = await this.orderItemRepo.findByOrderId(this.id, {
      cursor,
      take: limit + 1 // Fetch one extra to check hasMore
    });
    
    const hasMore = items.length > limit;
    const resultItems = hasMore ? items.slice(0, -1) : items;
    const nextCursor = hasMore ? resultItems[resultItems.length - 1].id : null;
    
    return { items: resultItems, cursor: nextCursor, hasMore };
  }
}
 
// Usage
let cursor: string | undefined;
do {
  const page = await order.getItemsPage(cursor, 100);
  for (const item of page.items) {
    processItem(item);
  }
  cursor = page.cursor ?? undefined;
} while (cursor);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

// TypeORM: Configuring lazy loading with eager fallback
@Entity()
class Order {
  @ManyToOne(() => Customer)
  customer: Customer; // Not lazy - loads when order loads if you use relations
  
  @OneToMany(() => OrderItem, item => item.order, { lazy: true })
  items: Promise<OrderItem[]>; // Lazy - returns Promise
}
 
// Prisma: Explicit includes at query time
// No decorator configuration - always specify at query
const order = await prisma.order.findUnique({
  where: { id },
  include: { items: true } // Must explicitly request
});
 
// Sequelize: Scopes for common loading patterns
@Scopes(() => ({
  withCustomer: {
    include: [{ model: Customer, as: 'customer' }]
  },
  withItems: {
    include: [{ model: OrderItem, as: 'items', include: [Product] }]
  },
  full: {
    include: [
      { model: Customer, as: 'customer' },
      { model: OrderItem, as: 'items', include: [Product] }
    ]
  }
}))
@Table
class Order extends Model {}
 
// Usage with scopes
const order = await Order.scope('full').findByPk(id);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

// Query counting in tests
 
describe('OrderService', () => {
  it('should load orders efficiently', async () => {
    // Create test data: 100 orders with items and customers
    await createTestOrders(100);
    
    // Reset and capture query count
    const queryLogger = new QueryLogger();
    
    // Execute the method under test
    const summaries = await orderService.getOrderSummaries();
    
    // Assert on query count
    const queryCount = queryLogger.getCount();
    expect(queryCount).toBeLessThanOrEqual(5); // Should be ~3, not 201
    
    // Alternatively: expect specific queries
    expect(queryLogger.getQueries()).toEqual([
      expect.stringContaining('SELECT * FROM orders'),
      expect.stringContaining('SELECT * FROM customers WHERE id IN'),
      expect.stringContaining('SELECT * FROM order_items WHERE order_id IN')
    ]);
  });
});
 
// Middleware for request-level query counting
function queryCountMiddleware(req: Request, res: Response, next: NextFunction) {
  const startCount = getQueryCount();
  
  res.on('finish', () => {
    const endCount = getQueryCount();
    const queryCount = endCount - startCount;
    
    if (queryCount > 50) {
      console.warn(`High query count: ${queryCount} queries for ${req.path}`);
    }
    
    res.setHeader('X-Query-Count', queryCount.toString());
  });
  
  next();
}