How to Avoid: N+1 Query in a Loop

To prevent N+1 queries in AI-generated code, replace database calls inside loops with batch operations: use IN clauses, JOIN statements, or ORM eager loading (include, with, prefetch_related). AI frequently generates code that queries the database on each iteration—logically correct but catastrophically slow at scale. The fix is straightforward: fetch all related data in one or two queries upfront, then process in memory.

This is one of the most common performance bugs in AI-generated code, and it's invisible in development. Your test suite passes, your local environment runs fine, and then production falls over when real users hit real data volumes.

What Is an N+1 Query Problem?

The N+1 query problem occurs when code executes one query to fetch a list of N records, then executes N additional queries to fetch related data for each record. Instead of 2 database round trips, you get N+1.

Here's the classic pattern AI generates:

// AI-generated code: looks clean, performs terribly
async function getOrdersWithCustomers(orderIds) {
  const orders = await db.query('SELECT * FROM orders WHERE id IN (?)', [orderIds]);
  
  for (const order of orders) {
    // This runs once PER ORDER — N additional queries
    order.customer = await db.query('SELECT * FROM customers WHERE id = ?', [order.customer_id]);
  }
  
  return orders;
}

With 10 orders, this executes 11 queries. With 1,000 orders, it executes 1,001 queries. Each query has network latency, connection overhead, and database processing time. What takes 50ms in development takes 5 seconds in production.

Why AI Produces This Pattern

AI code generation optimizes for what it can verify: syntactic correctness, logical clarity, and adherence to the prompt. Performance characteristics require understanding production context that isn't in the prompt.

When you ask an AI to "fetch orders with their customers," it generates code that correctly fetches orders, then correctly fetches each customer. The relationship between the two operations is expressed as iteration—the most readable approach for a human reviewer glancing at the code.

AI models don't simulate production load. They don't know your orders table has 50,000 rows. They don't know your database connection pool maxes out at 10 connections. They produce code that works, and "works" in training data usually means "executes without errors," not "handles scale."

This makes N+1 queries a systematic failure mode, not an occasional mistake. Fairy's code reviews flag this pattern specifically because it appears so consistently in AI-generated database code.

The Correct Patterns

Pattern 1: Batch with IN Clause

Collect all IDs upfront, fetch all related records in one query, then map them in memory:

// Correct: 2 queries regardless of order count
async function getOrdersWithCustomers(orderIds) {
  const orders = await db.query('SELECT * FROM orders WHERE id IN (?)', [orderIds]);
  
  // Extract all customer IDs
  const customerIds = [...new Set(orders.map(o => o.customer_id))];
  
  // Single query for ALL customers
  const customers = await db.query('SELECT * FROM customers WHERE id IN (?)', [customerIds]);
  
  // Build lookup map
  const customerMap = new Map(customers.map(c => [c.id, c]));
  
  // Attach in memory — no DB calls
  for (const order of orders) {
    order.customer = customerMap.get(order.customer_id);
  }
  
  return orders;
}

This executes exactly 2 queries whether you have 10 orders or 10,000.

Pattern 2: JOIN in SQL

For simple relationships, join at the database level:

// Correct: 1 query, database does the join
async function getOrdersWithCustomers(orderIds) {
  return db.query(`
    SELECT 
      o.*,
      c.name as customer_name,
      c.email as customer_email
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    WHERE o.id IN (?)
  `, [orderIds]);
}

This is the most efficient approach when you need specific fields from the related table.

Pattern 3: ORM Eager Loading

Every major ORM has eager loading syntax—use it:

// Prisma
const orders = await prisma.order.findMany({
  where: { id: { in: orderIds } },
  include: { customer: true }  // Eager load in single query
});

// Sequelize
const orders = await Order.findAll({
  where: { id: orderIds },
  include: [Customer]
});

// TypeORM
const orders = await orderRepository.find({
  where: { id: In(orderIds) },
  relations: ['customer']
});

# Django
orders = Order.objects.filter(id__in=order_ids).select_related('customer')

# SQLAlchemy
orders = session.query(Order).options(joinedload(Order.customer)).filter(Order.id.in_(order_ids)).all()

ORMs generate optimized queries—usually a JOIN or a second query with an IN clause—depending on the relationship type.

How to Detect N+1 Queries in AI-Generated Code

Code Review Signals

Look for these patterns in any AI-generated database code:

1. Loop + await + query: Any for, map, or forEach containing await and database keywords (query, find, select, get)

2. Nested async calls: Functions that accept a single ID and are called inside iteration

3. Missing include/with/join: ORM queries that fetch a parent entity without specifying relationships

// Red flag: await inside map
const results = await Promise.all(items.map(async (item) => {
  return await db.query('SELECT * FROM details WHERE item_id = ?', [item.id]);
}));

Promise.all doesn't fix N+1—it just executes N queries concurrently instead of sequentially. You still hammer the database with N connections.

Automated Detection

Add query logging to your test environment:

// Development middleware to catch N+1
let queryCount = 0;
db.on('query', () => queryCount++);

afterEach(() => {
  if (queryCount > 10) {
    console.warn(`Warning: ${queryCount} queries executed in single request`);
  }
  queryCount = 0;
});

Any endpoint executing more queries than expected rows fetched is suspect.

Real-World Impact

N+1 queries are particularly dangerous because they're invisible until they're catastrophic:

• Development: 10 test records, 11 queries, 50ms total. Looks fine.
• Staging: 100 records, 101 queries, 500ms. Slower but passes.
• Production: 10,000 records, 10,001 queries, 50 seconds. Timeouts. Connection pool exhaustion. Cascading failures.

The failure mode is non-linear. Performance degrades linearly with data growth until you hit a threshold (connection pool limit, query timeout, memory ceiling), then it collapses entirely.

This is why Fairy's code verification specifically flags loop-based database access patterns. The bug is systematic enough to detect automatically but subtle enough to slip past human review when the code "looks clean."

Preventing N+1 in AI Workflows

Prompt Engineering

When requesting database code from AI, specify batch requirements:

"Fetch orders with their customers. Use eager loading or batch queries—no queries inside loops."

This often produces correct code, but doesn't guarantee it. AI may still generate N+1 patterns for complex nested relationships.

Review Checklist

Before merging AI-generated database code:

1. Search for await inside for/map/forEach
2. Check that ORM queries include eager loading (include, with, select_related)
3. Verify JOIN or IN clause usage for related data
4. Run the code path against realistic data volumes
5. Add query count assertions to tests

Continuous Verification

N+1 queries can be introduced by seemingly unrelated changes. A refactor that moves a query into a helper function, a new feature that adds a relationship—these create N+1 patterns that weren't there before.

Fairy Intelligence can answer questions about your codebase's database access patterns, helping identify potential N+1 queries before they reach production. For new code, Fairy Scout provides free AI PR reviews that flag performance patterns including loop-based database calls.

Beyond N+1: Related Database Performance Issues

N+1 is the most common AI-generated database performance bug, but it's not the only one. Watch for:

• Missing indexes: AI generates queries but doesn't suggest index creation
• SELECT * everywhere: Fetching all columns when only a few are needed
• No pagination: Fetching unbounded result sets
• String interpolation in SQL: Beyond performance, this creates injection vulnerabilities that Fairy flags as critical security issues

Each of these patterns follows the same dynamic: AI generates code that works correctly but doesn't account for production constraints.

Conclusion

N+1 queries are a systematic failure mode in AI-generated code. AI optimizes for correctness and clarity, producing loop-based database access that works in development and collapses in production. The fix is consistent: batch with IN clauses, JOIN at the database level, or use ORM eager loading.

Detection is mechanical: any await inside a loop touching the database is suspect. Add query count logging in development, flag these patterns in code review, and verify against realistic data volumes before deployment.

AI does the work. Making that work reliable at scale requires verification—and N+1 queries are exactly the kind of invisible performance bug that verification catches before your users do.

Ready to catch N+1 queries and other AI code issues automatically? Get started with Fairy.