NestJS Prisma ORM - Using 'select' versus 'include' when fetching data records?

Question

I'm trying to fetch data records from a Postgres database in NestJS (Node.JS environment).

I'm using Prisma as my Object Relational Mapper (ORM) in TypeScript.

I'm having trouble choosing which query to use when fetching 'ADMIN' user records.

Someone please explain the difference between using 'select' versus using 'include' when fetching data records (I'm a Prisma beginner - please keep it simple).

Thanks in advance!

The code looks like below:

Using include:

const users = await prisma.user.findMany({
  where: {
    role: 'ADMIN',
  },
  include: {
    posts: true,
  },
})

Using select:

const users = await prisma.user.findMany({
  where: {
    role: 'ADMIN',
  },
  select: {
    posts: true,
  },
})

Answer 1

It's explained in the documentations of Select fields and Relation Queries That include and select have different usage:

By default, when a query returns records [..], the result includes the default selection set:

All scalar fields defined in the Prisma schema [..and] None of the relations

To change this behavior, you can use:

(1) Select:

allows you to either return a limited subset of fields instead of all fields:

const getUser: object | null = await prisma.user.findUnique({
  where: {
    id: 22,
  },
  select: {
    email: true,
    name: true,
  },
})

// Result
{
  name: "Alice",
  email: "[email protected]",
}

or include relations and select relation fields (nested usage):

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

(2) Include:

allows you to return some or all relation fields (which are not returned by default as mentioned before):

const getPosts = await prisma.post.findMany({
  where: {
    title: {
      contains: 'cookies',
    },
  },
  include: {
    author: true, // Return all fields
  },
})

// Result:
;[
  {
    id: 17,
    title: 'How to make cookies',
    published: true,
    authorId: 16,
    comments: null,
    views: 0,
    likes: 0,
    author: {
      id: 16,
      name: null,
      email: '[email protected]',
      profileViews: 0,
      role: 'USER',
      coinflips: [],
    },
  },
  {
    id: 21,
    title: 'How to make cookies',
    published: true,
    authorId: 19,
    comments: null,
    views: 0,
    likes: 0,
    author: {
      id: 19,
      name: null,
      email: '[email protected]',
      profileViews: 0,
      role: 'USER',
      coinflips: [],
    },
  },
]

(3) Select within Include:

Finally, you can use Select within Include, to return a subset of the relation fields.

const users = await prisma.user.findMany({
  // Returns all user fields
  include: {
    posts: {
      select: {
        title: true,
      },
    },
  },
})