Database Anti-Patterns Detection

Database Anti-Patterns Detection

Detection rules and grep patterns for identifying database performance anti-patterns in code.

When to Use This Skill

• Scanning codebase for performance issues
• Code review for database patterns
• Used by db-performance-agent for automated detection

Anti-Pattern Categories

1. N+1 Query Patterns (CRITICAL)

Queries executed inside loops - causes O(n) database calls.

Detection Patterns:

// Pattern 1: await inside for loop
for (const item of items) {
  await supabase.from('table')...  // N+1!
}

// Pattern 2: await inside forEach
items.forEach(async (item) => {
  await supabase.from('table')...  // N+1!
});

// Pattern 3: await inside map
await Promise.all(items.map(async (item) => {
  await supabase.from('table')...  // N+1 even with Promise.all!
}));

// Pattern 4: count queries in loop
for (const item of items) {
  const { count } = await supabase.from('x').select('id', { count: 'exact' })...
}

Grep Patterns:

# For loops with await supabase
grep -Pzo 'for\s*\([^)]+\)\s*\{[^}]*await[^}]*\.from\(' --include="*.ts"

# forEach with async
grep -n 'forEach\s*\(\s*async' --include="*.ts"

# map with nested await from()
grep -n '\.map\s*\(\s*async.*await.*\.from\(' --include="*.ts"

Severity: CRITICAL Impact: 50-500+ queries per request Auto-fixable: Yes - batch fetch + Map lookup

---

2. Sequential Independent Queries (HIGH)

Multiple await statements that could run in parallel.

Detection Pattern:

// Sequential (BAD)
const a = await supabase.from('table_a').select()...;
const b = await supabase.from('table_b').select()...;
const c = await supabase.from('table_c').select()...;

// Should be parallel (GOOD)
const [a, b, c] = await Promise.all([
  supabase.from('table_a').select()...,
  supabase.from('table_b').select()...,
  supabase.from('table_c').select()...,
]);

Detection Heuristic:

Look for 2+ consecutive lines matching:

• const/​let X = await supabase.from(...)
• With no data dependency between them

Severity: HIGH Impact: 2-5x latency increase Auto-fixable: Yes - wrap in Promise.all()

---

3. Unbounded Data Fetches (MEDIUM)

Queries without .limit() on tables that can grow large.

Large Tables to Check:

const LARGE_TABLES = [
  'events',
  'cast_assignments',
  'invoices',
  'invoice_line_items',
  'notifications',
  'notification_deliveries',
  'airtable_sync_changes',
  'audit_logs',
  'feedback_requests',
  'reimbursements',
  'reimbursement_line_items',
];

Detection Pattern:

// Missing limit (BAD for large tables)
await supabase.from('events').select('*')

// Should have limit or be filtered (GOOD)
await supabase.from('events').select('*').limit(100)
await supabase.from('events').select('*').eq('user_id', userId)

Grep Pattern:

# Selects on large tables without limit
grep -n "\.from\(['\"]events['\"]\)" --include="*.ts" | grep -v "\.limit\|\.eq\|\.in\|\.single"

Severity: MEDIUM Impact: Memory exhaustion, slow queries Auto-fixable: Partial - add .limit(), may need review

---

4. Individual Insert/​Update in Loops (HIGH)

Single-row operations that should be batched.

Detection Pattern:

// Individual inserts (BAD)
for (const item of items) {
  await supabase.from('table').insert({ ...item });
}

// Individual updates (BAD)
for (const id of ids) {
  await supabase.from('table').update({ status: 'done' }).eq('id', id);
}

// Batch operations (GOOD)
await supabase.from('table').insert(items);
await supabase.from('table').update({ status: 'done' }).in('id', ids);

Grep Pattern:

# Insert in loop
grep -Pzo 'for\s*\([^)]+\)\s*\{[^}]*\.insert\(' --include="*.ts"

# Update in loop
grep -Pzo 'for\s*\([^)]+\)\s*\{[^}]*\.update\(' --include="*.ts"

Severity: HIGH Impact: N database round-trips Auto-fixable: Yes - batch operations

---

5. Count Queries Instead of Aggregation (MEDIUM)

Using multiple COUNT queries instead of fetching once and aggregating.

Detection Pattern:

// Multiple count queries (BAD)
const { count: countA } = await supabase.from('x').select('id', { count: 'exact' }).eq('status', 'a');
const { count: countB } = await supabase.from('x').select('id', { count: 'exact' }).eq('status', 'b');
const { count: countC } = await supabase.from('x').select('id', { count: 'exact' }).eq('status', 'c');

// Single fetch + aggregate (GOOD)
const { data } = await supabase.from('x').select('status').in('status', ['a', 'b', 'c']);
const counts = { a: 0, b: 0, c: 0 };
for (const item of data) counts[item.status]++;

Grep Pattern:

# Multiple count queries (look for pattern of consecutive count selects)
grep -n "count: 'exact'" --include="*.ts"

Severity: MEDIUM Impact: 2-10x more queries than necessary Auto-fixable: Yes - single query + in-memory aggregation

---

6. Missing Promise.all for Related Lookups (MEDIUM)

Fetching the same related entity type multiple times sequentially.

Detection Pattern:

// Sequential related lookups (BAD)
const user1 = await getUser(id1);
const user2 = await getUser(id2);
const user3 = await getUser(id3);

// Batch lookup (GOOD)
const users = await getUsers([id1, id2, id3]);

Severity: MEDIUM Impact: Increased latency Auto-fixable: Sometimes - depends on function signature

---

Quick Reference: Detection Commands

# Find all N+1 patterns (for loops with await from)
grep -rn "for.*{" apps/​web --include="*.ts" | xargs -I {} sh -c 'grep -l "await.*\.from\(" {}'

# Find sequential queries (consecutive await from lines)
grep -n "await.*\.from\(" apps/​web --include="*.ts" | sort | uniq -c | sort -rn

# Find unbounded selects on large tables
for table in events cast_assignments invoices notifications; do
  grep -rn "\.from(['\"]$table['\"])" apps/​web --include="*.ts" | grep -v "\.limit\|\.single\|\.eq\|\.in"
done

# Find insert/​update in loops
grep -rn "for\s*(" apps/​web --include="*.ts" -A 5 | grep -E "\.(insert|update)\("

# Find multiple count queries in same file
grep -l "count: 'exact'" apps/​web --include="*.ts" -r | xargs -I {} grep -c "count: 'exact'" {} | grep -v ":1$"

Severity Levels

Files Commonly Affected

High-risk file patterns to prioritize scanning:

1. Cron jobs: app/​api/​cron/​*/​route.ts
2. Services with batch ops: **/​services/​*.service.ts
3. Admin list pages: app/​admin/​**/​actions.ts
4. Report generators: **/​reporting*.ts

Related Skills

• db-performance-patterns - Correct patterns and fixes
• db-performance-agent - Automated scanning and fixing