Part 3.3 — Prisma Queries, Relations & Best Practices

A backend becomes serious when data becomes relational.
Prisma gives you type-safe access to PostgreSQL, but you still need to understand how queries behave, how relations load, how pagination works, and how to avoid N+1 traps that slow real apps.

This post makes you comfortable with Prisma’s relational querying model and how to use it effectively inside NestJS services.

1. Your Current Schema (from Part 3.1)

You already modeled:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  body      String
  createdAt DateTime @default(now())
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
}

This schema will guide our relational queries.

2. Fetching Relations Using include

To fetch the author of a post:

const post = await this.prisma.post.findUnique({
  where: { id },
  include: { author: true },
});

To fetch a user and all posts:

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

Use include when you want entire nested objects.

3. Using select for Precise Field Control

select is better when you want partial fields:

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

Why it matters:

• Smaller DB result → less network overhead

• Faster serialization

• Predictable structure for frontend DTOs

4. Avoiding the N+1 Problem (Critical!)

This common trap:

• Fetch all posts

• For each post, fetch its author

• → 1 query for list + N queries for authors

Bad:

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

Avoid it by using relations:

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

One query. No N+1.

5. Pagination: take, skip, cursor

For lists:

const posts = await this.prisma.post.findMany({
  take: 10,
  skip: 20,
  orderBy: { createdAt: 'desc' },
});

Cursor pagination (recommended for large datasets):

const posts = await this.prisma.post.findMany({
  take: 10,
  skip: 1,
  cursor: { id: lastSeenId },
});

This avoids pagination drift on frequently updated tables.

6. Filtering: where Queries

Examples:

// Filter by author
where: { authorId: 1 }

// Search by title
where: { title: { contains: 'react', mode: 'insensitive' } }

// Range queries
where: { createdAt: { gte: someDate } }

Combine them:

where: {
  authorId: 1,
  title: { contains: 'guide', mode: 'insensitive' },
}

PostgreSQL will generate the appropriate query plan.

7. Sorting: orderBy

orderBy: { createdAt: 'desc' }

Multiple fields:

orderBy: [
  { createdAt: 'desc' },
  { id: 'asc' },
]

Sorting matters for pagination correctness.

8. Counting Rows Efficiently

Counting with filters:

const count = await this.prisma.post.count({
  where: { authorId: 1 },
});

Avoid counting inside loops — it’s expensive.

9. Transactions with $transaction

Group related operations atomically:

await this.prisma.$transaction([
  this.prisma.post.create({
    data: { title, body, authorId },
  }),
  this.prisma.user.update({
    where: { id: authorId },
    data: { /* ... */ },
  }),
]);

This ensures consistency, especially when multiple modifications depend on one another.

10. Using connect and disconnect for Relations

Create a post for an existing user:

this.prisma.post.create({
  data: {
    title,
    body,
    author: { connect: { id: authorId } },
  },
});

Switch relation:

this.prisma.post.update({
  where: { id: postId },
  data: {
    author: { connect: { id: newAuthorId } },
  },
});

Remove relation:

disconnect: true

11. Selecting Nested Lists

Fetch a user with 3 latest posts:

include: {
  posts: {
    take: 3,
    orderBy: { createdAt: 'desc' },
  },
}

This avoids pulling entire datasets into memory.

12. Use Indexes to Improve Query Speed (PostgreSQL Tip)

Prisma does not auto-index fields unless declared:

@@index([email])
@@index([createdAt])

For full-text searching:

@@index([title, body], type: Gin)

Indexes matter when:

• Filtering

• Sorting

• Searching

• Joining

• Paginating

We’ll explore deeper query performance in Part 13.

13. Efficient Query Patterns for Production

• Always page large queries

• Avoid returning whole rows when unnecessary

• Use select for predictable frontend contracts

• Avoid nested loops

• Preload relations intentionally

• Use transactions for multi-step writes

• Consider EXPLAIN ANALYZE for query diagnostics

14. Example Service Using Best Practices

findRecentByAuthor(authorId: number, limit = 10) {
  return this.prisma.post.findMany({
    where: { authorId },
    take: limit,
    orderBy: { createdAt: 'desc' },
    select: {
      id: true,
      title: true,
      createdAt: true,
    },
  });
}

This is fast, predictable, and optimized.

15. Summary

You now know how to:

• Use Prisma includes, selects, relations

• Avoid N+1 queries

• Page data efficiently

• Filter, sort, count, and join models

• Write performance-conscious PostgreSQL queries

• Design services that scale with real traffic

In Part 3.4, you’ll bring structure to error handling — global filters, pipes, interceptors, logging — everything needed for a production-grade NestJS backend.