Query Options
Prisma Client provides a rich set of query options for filtering, sorting, selecting fields, and more.
Selection Options
select
Specify which fields to include in the query result.
select?: {
[field: string]: boolean | NestedSelect
}
const user = await prisma.user.findUnique({
where: { id: 1 },
select: {
id: true,
email: true,
name: true,
posts: {
select: {
title: true,
published: true
}
}
}
})
// Result: { id: 1, email: '...', name: '...', posts: [...] }
When using select, only the specified fields are returned. All other fields are excluded.
include
Include related records (relations) in the query result.
include?: {
[relation: string]: boolean | NestedInclude
}
const user = await prisma.user.findUnique({
where: { id: 1 },
include: {
posts: true,
profile: {
include: {
bio: true
}
}
}
})
// Returns the user with all fields, plus posts and profile relations
select and include are mutually exclusive at the same level, but you can use select within include.
const user = await prisma.user.findUnique({
where: { id: 1 },
include: {
posts: {
select: {
title: true,
published: true
}
}
}
})
omit
Exclude specific fields from the query result.
omit?: {
[field: string]: boolean
}
const user = await prisma.user.findUnique({
where: { id: 1 },
omit: {
password: true,
internalNotes: true
}
})
// Returns all user fields except password and internalNotes
omit can be used with include, but not with select.
Filtering Options
where
Filter records based on field values and conditions.
where?: {
[field: string]: FieldValue | FilterCondition
AND?: WhereInput[]
OR?: WhereInput[]
NOT?: WhereInput | WhereInput[]
}
Basic Filters
Equality:
where: {
email: 'alice@prisma.io',
age: 25
}
where: {
age: { gt: 18 }, // greater than
age: { gte: 18 }, // greater than or equal
age: { lt: 65 }, // less than
age: { lte: 65 }, // less than or equal
age: { not: 30 } // not equal
}
where: {
id: { in: [1, 2, 3, 4, 5] },
role: { notIn: ['ADMIN', 'MODERATOR'] }
}
String Filters
where: {
email: { contains: '@prisma.io' },
name: { startsWith: 'Al' },
name: { endsWith: 'ice' },
email: { equals: 'alice@prisma.io' },
bio: { not: { contains: 'banned' } }
}
where: {
email: {
contains: '@PRISMA.IO',
mode: 'insensitive'
}
}
Logical Operators
AND (implicit):
where: {
age: { gte: 18 },
active: true
}
// Equivalent to: age >= 18 AND active = true
where: {
AND: [
{ age: { gte: 18 } },
{ active: true }
]
}
where: {
OR: [
{ email: { contains: '@prisma.io' } },
{ email: { contains: '@example.com' } }
]
}
where: {
NOT: {
role: 'ADMIN'
}
}
// Multiple NOT conditions
where: {
NOT: [
{ role: 'ADMIN' },
{ role: 'MODERATOR' }
]
}
where: {
AND: [
{ age: { gte: 18 } },
{
OR: [
{ role: 'ADMIN' },
{ verified: true }
]
}
]
}
Null Filters
where: {
bio: null, // bio IS NULL
bio: { not: null }, // bio IS NOT NULL
deletedAt: { equals: null },
updatedAt: { not: { equals: null } }
}
Relation Filters
Filter by related records:
// Users who have at least one published post
const users = await prisma.user.findMany({
where: {
posts: {
some: {
published: true
}
}
}
})
// Users who have ALL posts published
const users = await prisma.user.findMany({
where: {
posts: {
every: {
published: true
}
}
}
})
// Users who have NO published posts
const users = await prisma.user.findMany({
where: {
posts: {
none: {
published: true
}
}
}
})
// Users with no posts at all
const users = await prisma.user.findMany({
where: {
posts: {
none: {}
}
}
})
// Users with a specific profile
const users = await prisma.user.findMany({
where: {
profile: {
is: {
bio: { contains: 'developer' }
}
}
}
})
// Users without a profile
const users = await prisma.user.findMany({
where: {
profile: {
isNot: null
}
}
})
JSON Filters
where: {
metadata: {
path: ['settings', 'theme'],
equals: 'dark'
}
}
// Array contains
where: {
tags: {
array_contains: ['typescript', 'prisma']
}
}
// String contains in JSON field
where: {
data: {
string_contains: 'searchterm'
}
}
Sorting Options
orderBy
Sort query results by one or more fields.
orderBy?: {
[field: string]: 'asc' | 'desc'
} | Array<{
[field: string]: 'asc' | 'desc'
}>
orderBy: { createdAt: 'desc' }
orderBy: [
{ role: 'asc' },
{ createdAt: 'desc' }
]
orderBy: {
posts: {
_count: 'desc'
}
}
orderBy: {
profile: {
bio: 'asc'
}
}
orderBy: {
name: { sort: 'asc', nulls: 'first' }
}
// nulls: 'first' | 'last'
take
Limit the number of records returned.
Example:
const users = await prisma.user.findMany({
take: 10 // Return at most 10 records
})
const users = await prisma.user.findMany({
take: -10, // Return last 10 records
orderBy: { createdAt: 'asc' }
})
skip
Skip a number of records (offset pagination).
Example:
const users = await prisma.user.findMany({
skip: 20, // Skip first 20 records
take: 10 // Then return next 10
})
function paginate(page: number, pageSize: number) {
return {
skip: (page - 1) * pageSize,
take: pageSize
}
}
const users = await prisma.user.findMany({
...paginate(3, 10) // Page 3, 10 items per page
})
cursor
Cursor-based pagination for efficient traversal of large datasets.
cursor?: WhereUniqueInput
// First page
const firstPage = await prisma.user.findMany({
take: 10,
orderBy: { id: 'asc' }
})
// Next page using cursor
const lastUser = firstPage[firstPage.length - 1]
const nextPage = await prisma.user.findMany({
take: 10,
skip: 1, // Skip the cursor itself
cursor: { id: lastUser.id },
orderBy: { id: 'asc' }
})
Cursor-based pagination is more performant than offset pagination for large datasets.
Distinct
distinct
Select distinct records based on one or more fields.
distinct?: FieldName | FieldName[]
const uniqueEmails = await prisma.user.findMany({
distinct: ['email'],
select: { email: true }
})
const uniqueCombinations = await prisma.user.findMany({
distinct: ['role', 'country'],
select: { role: true, country: true }
})
Advanced Options
Prisma.skip
Explicitly skip a field in query arguments (useful with spread operators).
import { Prisma } from '@prisma/client'
const conditionalInclude = includeProfile ? { profile: true } : Prisma.skip
const user = await prisma.user.findUnique({
where: { id: 1 },
include: {
posts: true,
profile: conditionalInclude
}
})
Field References
Compare fields within the same record.
import { Prisma } from '@prisma/client'
const users = await prisma.user.findMany({
where: {
// Find users where updatedAt > createdAt
updatedAt: {
gt: Prisma.DbNull // This is a placeholder; actual syntax varies
}
}
})
Complete Example
Here’s a comprehensive example combining multiple query options:
const result = await prisma.post.findMany({
// Filtering
where: {
AND: [
{ published: true },
{
OR: [
{ title: { contains: 'prisma', mode: 'insensitive' } },
{ content: { contains: 'database' } }
]
},
{
author: {
is: {
role: 'AUTHOR',
active: true
}
}
}
]
},
// Selection
select: {
id: true,
title: true,
createdAt: true,
author: {
select: {
name: true,
email: true
}
},
_count: {
select: {
comments: true
}
}
},
// Sorting
orderBy: [
{ createdAt: 'desc' },
{ title: 'asc' }
],
// Pagination
skip: 10,
take: 20,
// Distinct
distinct: ['authorId']
})
See Also