Query Select Optimization

Optimize Prisma 6 queries through selective field loading and relation batching to prevent N+1 problems and reduce data transfer.

---

<role> Optimize Prisma 6 queries by selecting required fields only, properly loading relations to prevent N+1 problems while minimizing data transfer and memory usage. </role> <when-to-activate> - Writing user-facing data queries - Loading models with relations - Building API endpoints or GraphQL resolvers - Optimizing slow queries; reducing database load - Working with large result sets </when-to-activate> <workflow> ## Optimization Workflow

1. Identify: Determine required fields, relations to load, relation count needs, full vs. specific fields
2. Choose: include (prototyping, most fields) vs. select (production, API responses, performance-critical)
3. Implement: Use select for precise control, nest relations with select, use _count instead of loading all records, limit relation results with take
4. Index: Fields in where clauses, orderBy fields, composite indexes for filtered relations
5. Validate: Enable query logging for single-query verification, test with realistic data volumes, measure payload size and query duration </workflow>

<core-principles> ## Core Principles

1. Select Only Required Fields

Problem: Fetching entire models wastes bandwidth and memory

const users = await prisma.user.findMany()

Solution: Use select to fetch only needed fields

const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
  },
})

Performance Impact:

• Reduces data transfer by 60-90% for models with many fields
• Faster JSON serialization
• Lower memory usage
• Excludes sensitive fields by default

2. Include vs Select

Include: Adds relations to full model

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
    profile: true,
  },
})

Select: Precise control over all fields

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    email: true,
    posts: {
      select: {
        id: true,
        title: true,
        published: true,
      },
    },
    profile: {
      select: {
        bio: true,
        avatar: true,
      },
    },
  },
})

When to Use:

• include: Quick prototyping, need most fields
• select: Production code, API responses, performance-critical paths

3. Preventing N+1 Queries

N+1 Problem: Separate query for each relation

const posts = await prisma.post.findMany()

for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  })
}

Solution: Use include or select with relations

const posts = await prisma.post.findMany({
  include: {
    author: true,
  },
})

Better: Select only needed author fields

const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    content: true,
    author: {
      select: {
        id: true,
        name: true,
        email: true,
      },
    },
  },
})

4. Relation Counting

Problem: Loading all relations just to count them

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
  },
})

const postCount = user.posts.length

Solution: Use _count for efficient aggregation

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    name: true,
    _count: {
      select: {
        posts: true,
        comments: true,
      },
    },
  },
})

Result:

{
  id: 1,
  name: "Alice",
  _count: {
    posts: 42,
    comments: 128
  }
}

</core-principles> <quick-reference> ## Quick Reference

Optimized Query Pattern

const optimized = await prisma.model.findMany({
  where: {},
  select: {
    field1: true,
    field2: true,
    relation: {
      select: {
        field: true,
      },
      take: 10,
    },
    _count: {
      select: {
        relation: true,
      },
    },
  },
  orderBy: { field: 'desc' },
  take: 20,
  skip: 0,
})

Key Takeaways

• Default to select for all production queries
• Use include only for prototyping
• Always use _count for counting relations
• Combine selection with filtering and pagination
• Prevent N+1 by loading relations upfront
• Select minimal fields for list views, more for detail views </quick-reference>

<constraints> ## Constraints and Guidelines

MUST:

• Use select for all API responses
• Load relations in same query (prevent N+1)
• Use _count for relation counts
• Add indexes for filtered/ordered fields
• Test with realistic data volumes

SHOULD:

• Limit relation results with take
• Create reusable selection objects
• Enable query logging during development
• Measure performance improvements
• Document selection patterns

NEVER:

• Use include in production without field selection
• Load relations in loops (N+1)
• Fetch full models when only counts needed
• Over-fetch nested relations
• Skip indexes on commonly queried fields </constraints>

---

References

For detailed patterns and examples, see:

• Nested Selection Patterns - Deep relation hierarchies and complex selections
• API Optimization Patterns - List vs detail views, pagination with select
• N+1 Prevention Guide - Detailed anti-patterns and solutions
• Type Safety Guide - TypeScript types and reusable selection objects
• Performance Verification - Testing and validation techniques