Skip to main content

Select fields

Overview​

By default, when a query returns records (as opposed to a count), the result includes:

  • All scalar fields of a model (including enums)
  • No relations defined on a model

As an example, consider this schema:

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

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

enum Role {
  USER
  ADMIN
}

A query to the User model will include the id, email, name and role fields (because these are scalar fields), but not the posts field (because that's a relation field):

const users = await prisma.user.findFirst()
Show query results
{
  id: 42,
  name: "Sabelle",
  email: "sabelle@prisma.io",
  role: "ADMIN"
}

If you want to customize the result and have a different combination of fields returned, you can:

  • Use select to return specific fields. You can also use a nested select by selecting relation fields.
  • Use omit to exclude specific fields from the result. omit can be seen as the "opposite" to select.
  • Use include to additionally include relations.

In all cases, the query result will be statically typed, ensuring that you don't accidentally access any fields that you did not actually query from the database.

Selecting only the fields and relations that you require rather than relying on the default selection set can reduce the size of the response and improve query speed.

Since version 5.9.0, when doing a relation query with include or by using select on a relation field, you can also specify the relationLoadStrategy to decide whether you want to use a database-level join or perform multiple queries and merge the data on the application level. This feature is currently in Preview, you can learn more about it here.

Example schema​

All following examples on this page are based on the following schema:

model User {
  id           Int       @id 
  name         String?
  email        String    @unique
  password     String
  role         Role      @default(USER)
  coinflips    Boolean[]
  posts        Post[]
  profile      Profile?
}

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

model Profile {
  id        Int    @id 
  biography String
  user      User   @relation(fields: [userId], references: [id])
  userId    Int    @unique
}

enum Role {
  USER
  ADMIN
}

Return the default fields​

The following query returns the default fields (all scalar fields, no relations):

const user = await prisma.user.findFirst()
Show query results
{
  id: 22,
  name: "Alice",
  email: "alice@prisma.io",
  password: "mySecretPassword42"
  role: "ADMIN",
  coinflips: [true, false],
}

Select specific fields​

Use select to return a subset of fields instead of all fields. The following example returns the email and name fields only:

const user = await prisma.user.findFirst({
  select: {
    email: true,
    name: true,
  },
})
Show query results
{
  name: "Alice",
  email: "alice@prisma.io",
}

Return nested objects by selecting relation fields​

You can also return relations by nesting select multiple times on relation fields.

The following query uses a nested select to select each user's name and the title of each related post:

const usersWithPostTitles = await prisma.user.findFirst({
  select: {
    name: true,
    posts: {
      select: { title: true },
    },
  },
})
Show query results
{
  "name":"Sabelle",
  "posts":[
    { "title":"Getting started with Azure Functions" },
    { "title":"All about databases" }
  ]
}

The following query uses select within an include, and returns all user fields and each post's title field:

const usersWithPostTitles = await prisma.user.findFirst({
  include: {
    posts: {
      select: { title: true },
    },
  },
})
Show query results
{
  id: 9
  name: "Sabelle",
  email: "sabelle@prisma.io",
  password: "mySecretPassword42",
  role: "USER",
  coinflips: [],
  posts:[
    { title:"Getting started with Azure Functions" },
    { title:"All about databases" }
  ]
}

You can nest your queries arbitrarily deep. The following query fetches:

  • the title of a Post
  • the name of the related User
  • the biography of the related Profile
const postsWithAuthorsAndProfiles = await prisma.post.findFirst({
  select: {
    title: true,
    author: {
      select: { 
        name: true,
        profile: {
          select: { biography: true }
        }
      },
    },
  },
})
Show query results
{
  id: 9
  title:"All about databases",
  author: {
    name: "Sabelle",.
    profile: {
      biography: "I like turtles"
    }
  }
}
note

Be careful when deeply nesting relations because the underlying database query may become slow due it needing to access a lot of different tables. To ensure your queries always have optimal speed, consider adding a caching layer with Prisma Accelerate or use Prisma Optimize to get query insights and recommendations for performance optimizations.

For more information about querying relations, refer to the following documentation:

Omit specific fields​

There may be situations when you want to return most fields of a model, excluding only a small subset. A common example for this is when you query a User but want to exclude the password field for security reasons.

In these cases, you can use omit, which can be seen as the counterpart to select:

const users = await prisma.user.findFirst({
  omit: {
    password: true
  }
})
Show query results
{
  id: 9
  name: "Sabelle",
  email: "sabelle@prisma.io",
  password: "mySecretPassword4",
  profileViews: 90,
  role: "USER",
  coinflips: [],
}

Notice how the returned object does not contain the password field.

Relation count​

In 3.0.1 and later, you can include or select a count of relations alongside fields. For example, a user's post count.