Select fields
By default, when a query returns records (as opposed to a count), the result includes the default selection set:
- All scalar fields defined in the Prisma schema (including enums)
- None of the relations
To customize the result:
- Use
select
to return specific fields - you can also use a nestedselect
to include relation fields - Use
include
to explicitly include relations
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 examples are based on the following schema:
Expand for sample schema
- Relational databases
- MongoDB
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model ExtendedProfile {
id Int @id @default(autoincrement())
biography String
user User @relation(fields: [userId], references: [id])
userId Int @unique
}
model User {
id Int @id @default(autoincrement())
name String?
email String @unique
profileViews Int @default(0)
role Role @default(USER)
coinflips Boolean[]
posts Post[]
profile ExtendedProfile?
}
model Post {
id Int @id @default(autoincrement())
title String
published Boolean @default(true)
author User @relation(fields: [authorId], references: [id])
authorId Int
comments Json?
views Int @default(0)
likes Int @default(0)
categories Category[]
}
model Category {
id Int @id @default(autoincrement())
name String @unique
posts Post[]
}
enum Role {
USER
ADMIN
}
datasource db {
provider = "mongodb"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model ExtendedProfile {
id String @id @default(auto()) @map("_id") @db.ObjectId
biography String
user User @relation(fields: [userId], references: [id])
userId String @unique @db.ObjectId
}
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
name String?
email String @unique
profileViews Int @default(0)
role Role @default(USER)
coinflips Boolean[]
posts Post[]
profile ExtendedProfile?
}
model Post {
id String @id @default(auto()) @map("_id") @db.ObjectId
title String
published Boolean @default(true)
author User @relation(fields: [authorId], references: [id])
authorId String @db.ObjectId
comments Json?
views Int @default(0)
likes Int @default(0)
categories Category[]
}
model Category {
id String @id @default(auto()) @map("_id") @db.ObjectId
name String @unique
posts Post[]
}
enum Role {
USER
ADMIN
}
For relational databases, use db push
command to push the example schema to your own database
npx prisma db push
For MongoDB, ensure your data is in a uniform shape and matches the model defined in the Prisma schema.
Return the default selection set
The following query returns the default selection set (all scalar fields, no relations):
// Query returns User or null
const getUser: User | null = await prisma.user.findUnique({
where: {
id: 22,
},
})
{
id: 22,
name: "Alice",
email: "alice@prisma.io",
profileViews: 0,
role: "ADMIN",
coinflips: [true, false],
}
Select specific fields
Use select
to return a limited subset of fields instead of all fields. The following example returns the email
and name
fields only:
// Returns an object or null
const getUser: object | null = await prisma.user.findUnique({
where: {
id: 22,
},
select: {
email: true,
name: true,
},
})
{
name: "Alice",
email: "alice@prisma.io",
}
Include relations and select relation fields
To return specific relation fields, you can:
- Use a nested
select
- Use a
select
within aninclude
To return all relation fields, use
include
only - for example,{ include: { posts: true } }
.
The following query uses a nested select
to select each user's name
and the title
of each related post:
const users = await prisma.user.findMany({
select: {
name: true,
posts: {
select: {
title: true,
},
},
},
})
{
"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 users = await prisma.user.findMany({
// Returns all user fields
include: {
posts: {
select: {
title: true,
},
},
},
})
{
"id": 9
"name": "Sabelle",
"email": "sabelle@prisma.io",
"profileViews": 90,
"role": "USER",
"profile": null,
"coinflips": [],
"posts":[
{
"title":"Getting started with Azure Functions"
},
{
"title":"All about databases"
}
]
}
For more information about querying relations, refer to the following documentation:
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.