Prisma Client: Choosing Between include and select for Efficient Relational Queries

Problem: Over‑fetching and performance pain points

In many applications, a developer wants to read a user and all of their posts. A naïve query that pulls every field of every related record can quickly become expensive: large payloads, more database joins, and higher latency. The core decision is whether to pull every scalar field of the relation or only the handful of fields you actually need.

Thesis: Use include for full objects, select for targeted fields

Prisma’s Fluent API gives two complementary ways to shape relational data:

• include – additive, brings in all scalar fields of the related model plus the specified relations.
• select – exclusive, forces you to list every field you want; any omitted scalar field is gone.

Mixing them at the same query level is a compile‑time error, which protects you from accidental over‑fetching.

1. Understanding Prisma’s Fluent API

The API is built on top of a generated PrismaClient that maps your Prisma schema to type‑safe JavaScript/TypeScript calls. Every query looks like:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  // include or select goes here
});

Both include and select are objects whose keys correspond to model fields. The difference is in how Prisma interprets the object.

2. include vs select – semantics & constraints

| Feature | include | select | |---------|---------|--------| | Scalar fields | All automatically added | Only listed fields | | Relations | Can be nested, additive | Must be explicitly listed | | Mixing at same level | ❌ error | ❌ error | | Query generation | Can use JOINs or separate queries (auto‑optimized) | Always explicit fields, no implicit JOINs |

Because select is exclusive, if you use it you must enumerate every scalar you want. That means you can’t accidentally pull in a large binary column or a computed field you didn’t intend to fetch.

3. Nested queries – depth vs cost

Prisma supports deeply nested relationships:

prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      include: {
        comments: true
      }
    }
  }
});

Each level adds another JOIN or separate query. If you include a relation that has many rows (e.g., a user with thousands of posts), the generated SQL can become heavy. In such cases, select lets you fetch only the fields you need and can be combined with take or pagination to keep the result set small.

4. Practical example – User and Post

Assume the following Prisma schema:

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

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

Generate the client with npx prisma generate.

Using include – full objects

const result = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true }
});
console.log(JSON.stringify(result, null, 2));

Output will contain the user’s id, name, email, plus an array of posts, each with id, title, content, authorId, and the nested author object (which again includes all scalar fields). This is convenient but can be wasteful if you only need the post titles.

Using select – targeted fields

const result = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    name: true,
    posts: {
      select: {
        title: true
      }
    }
  }
});
console.log(JSON.stringify(result, null, 2));

Here the user object contains only id and name, and each post contains only title. No email, content, or author data is fetched, which reduces payload size and database work.

Attempting to mix – compile‑time guard

// ❌ This will error at compile time
const result = await prisma.user.findUnique({
  where: { id: 1 },
  select: { id: true, name: true, posts: true },
  include: { posts: true }
});

Prisma’s type‑checker will reject this, reminding you to choose one style per level.

Trade‑off: Over‑fetching vs complexity

Using include is simple and expressive, but be mindful of:

• Large models can bring in dozens of columns you never use.
• Deep nesting can produce wide JOINs that hit the database’s query planner.
• When you need only a few fields, select gives you fine‑grained control and can improve both network and DB load.

Conversely, select requires more upfront work: you must list every field and relation explicitly. In a rapidly evolving schema, forgetting to add a new field can lead to subtle bugs where data appears missing.

Actionable closing

1. Start with include for quick prototypes and when you truly need all data. 2. Switch to select when you notice:

• Large payloads in logs or on the network.
• Slow query times reported by your profiler.
• Unnecessary columns in the result (e.g., binary blobs).

3. Use nested select for deep relations. Combine with pagination (take, skip) to avoid exploding result sets. 4. Leverage Prisma’s query plan debugging. Run prisma studio or prisma db pull to inspect generated SQL and verify that the joins match your expectations.

By consciously choosing between include and select, you keep your API calls lean, your database load manageable, and your application’s performance predictable.