Choosing Between Prisma's Fluent API and Declarative Include/Select for Type‑Safe Data Fetching

Problem statement

When building a REST or GraphQL endpoint with Prisma, you often need to load a record together with its related data. You want the result to be fully typed in TypeScript, but you also want to avoid unnecessary database round‑trips or huge payloads. The choice between Prisma’s Fluent API and the classic include/select options directly impacts both readability and performance.

Thesis

The Fluent API lets you chain relation queries in a way that feels like ordinary JavaScript code, making dynamic query building easy. However, unless you rely on Prisma’s internal batching, each chain step can trigger a separate SQL query, leading to the classic N+1 problem. The declarative include and select arguments fetch everything in a single round‑trip and give you fine‑grained control over which columns are retrieved, preserving type safety while keeping the payload predictable.

Understanding the two approaches

Prisma Client exposes two main ways to shape a query:

• Declarative include and select – you pass an object that tells Prisma which relations to join and which scalar fields to return. The generated SQL contains the necessary JOINs (or separate queries that Prisma batches internally) and the result is shaped exactly as you described.
• Fluent API – you start with a base query (e.g., prisma.user.findUnique) and then call relation‑specific methods like posts() or comments() on the returned promise‑like object. Each call returns a new query builder that you can further modify before awaiting the final result.

Both paths produce the same TypeScript types because Prisma generates the client from your schema; the difference lies in how the underlying SQL is emitted.

Worked example: fetching a user with posts and comments

Assume the following simplified Prisma schema (version 5.x):

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

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

model Comment {
  id        Int      @id @default(autoincrement())
  body      String
  postId    Int
  post      Post     @relation(fields: [postId], references: [id])
}

Goal: retrieve a single user, all of their posts, and each post’s comments, while only pulling the name field from the user, title from posts, and body from comments.

Using select (declarative)

// run in a Node.js script or route handler
const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    name: true,
    posts: {
      select: {
        title: true,
        comments: {
          select: { body: true }
        }
      }
    }
  }
});

Prisma will emit a set of SQL statements that fetch the user, then the posts for that user, then the comments for those posts. Depending on the version and the batchSize setting, the posts and comments queries may be combined into two round‑trips (one for posts, one for all comments). The returned user object is fully typed: user.posts[0].comments[0].body is known to be a string.

Using the Fluent API

// same environment as above
const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: { name: true } // we only need the name from the user level
});

if (user) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id },
    select: { title: true }
  });

  // fetch comments for each post – note the loop
  const postsWithComments = await Promise.all(
    posts.map(async p => {
      const comments = await prisma.comment.findMany({
        where: { postId: p.id },
        select: { body: true }
      });
      return { ...p, comments };
    })
  );

  user.posts = postsWithComments;
}

Each findMany call issues its own SQL statement. If a user has *N* posts, you will see 1 (user) + N (posts) + N (comments per post) queries unless you manually batch them. The TypeScript result is still correctly typed because the shape you assign matches the Prisma-generated types.

Trade‑offs and practical considerations

• Performance – The declarative approach usually results in fewer database round‑trips. The Fluent API can lead to the N+1 problem when used inside loops, as shown in the example. If you notice latency spikes under load, check your database logs or enable Prisma’s debug flag to see the query count.
• Payload control – select lets you omit large columns (e.g., a TEXT field that stores markdown). The Fluent API does not prevent you from accidentally fetching those columns unless you explicitly select them at each level.
• Dynamic queries – When the set of relations to fetch depends on runtime conditions (e.g., feature flags or query‑string parameters), the Fluent API shines because you can conditionally chain posts(), comments(), etc., without building a massive nested select object.
• Type safety – Both paths rely on the same generated Prisma Client types, so IntelliSense and compile‑time checks work identically. No extra type definitions are needed.
• Version notes – Starting with Prisma 4.6, the client attempts to batch relation queries when possible, but the behavior is not guaranteed for all Fluent API patterns. Always verify the actual SQL emitted for your specific version.

When to choose which approach

Use the declarative include/select when:

• You know the exact shape of the data you need at compile time.
• You want to minimize round‑trips and keep payload size predictable.
• You are dealing with large binary or text columns that you wish to exclude.

Consider the Fluent API when:

• The query structure varies based on runtime input (e.g., optional expansions requested by an API consumer).
• You are building a helper function that composes queries incrementally.
• You have verified that the number of related records is small enough that the extra queries do not hurt performance, or you have implemented your own batching layer.

Regardless of the choice, you can validate your decision by:

1. Running npx prisma generate to update the client after any schema change.
2. Starting your application with the environment variable PRISMA_CLIENT_ENGINE_TYPE=binary (default) and enabling debug: true in the Prisma Client constructor to see the generated SQL in stdout.
3. Checking the network tab (if you expose a GraphQL endpoint) or your database’s query log to confirm the number of statements executed for a typical request.
4. Ensuring the returned TypeScript types match expectations by hovering over the result in your IDE or adding a type‑only test like type UserWithPosts = Prisma.PromiseReturnType<typeof prisma.user.findUnique>.

By measuring the actual query count and inspecting the generated types, you can confidently pick the strategy that gives you the best balance of readability, safety, and efficiency for your use case.