Query Options

Options for controlling query behavior.

select

Choose specific fields to return:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    name: true,
    email: true,
    // password: false (excluded by not including)
  }
})
// Returns: { id: 1, name: 'Alice', email: 'alice@prisma.io' }

Select relations

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    name: true,
    posts: {
      select: {
        title: true,
        published: true
      }
    }
  }
})

Select with include inside

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

Select relation count

const users = await prisma.user.findMany({
  select: {
    name: true,
    _count: {
      select: { posts: true }
    }
  }
})
// Returns: { name: 'Alice', _count: { posts: 5 } }

include

Include related records:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
    profile: true
  }
})

Filtered include

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5
    }
  }
})

Nested include

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      include: {
        comments: {
          include: {
            author: true
          }
        }
      }
    }
  }
})

Include relation count

const users = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true, followers: true }
    }
  }
})

omit

Exclude specific fields:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  omit: {
    password: true
  }
})
// Returns all fields except password

Omit in relations

const users = await prisma.user.findMany({
  omit: { password: true },
  include: {
    posts: {
      omit: { content: true }
    }
  }
})

Note: Cannot use select and omit together.

where

Filter records:

const users = await prisma.user.findMany({
  where: {
    email: { contains: '@prisma.io' },
    role: 'ADMIN'
  }
})

See filters.md for detailed filter operators.

orderBy

Sort results:

// Single field
const users = await prisma.user.findMany({
  orderBy: { name: 'asc' }
})

// Multiple fields
const users = await prisma.user.findMany({
  orderBy: [
    { role: 'desc' },
    { name: 'asc' }
  ]
})

Order by relation

const users = await prisma.user.findMany({
  orderBy: {
    posts: { _count: 'desc' }
  }
})

Null handling

const users = await prisma.user.findMany({
  orderBy: {
    name: { sort: 'asc', nulls: 'last' }
  }
})

take & skip

Pagination:

// First page
const users = await prisma.user.findMany({
  take: 10,
  skip: 0
})

// Second page
const users = await prisma.user.findMany({
  take: 10,
  skip: 10
})

Negative take (reverse)

const lastUsers = await prisma.user.findMany({
  take: -10,
  orderBy: { id: 'asc' }
})
// Returns last 10 users

cursor

Cursor-based pagination:

// First page
const firstPage = await prisma.user.findMany({
  take: 10,
  orderBy: { id: 'asc' }
})

// Next page using cursor
const nextPage = await prisma.user.findMany({
  take: 10,
  skip: 1,  // Skip the cursor record
  cursor: { id: firstPage[firstPage.length - 1].id },
  orderBy: { id: 'asc' }
})

distinct

Return unique values:

const cities = await prisma.user.findMany({
  distinct: ['city'],
  select: { city: true }
})

Multiple distinct fields

const locations = await prisma.user.findMany({
  distinct: ['city', 'country']
})