Skip to main content

​
Filtering and Sorting

Prisma Client provides comprehensive filtering and sorting capabilities for precise data queries.

​
Where Clauses

​
Equality Filters

// Exact match
const user = await prisma.user.findFirst({
  where: {
    email: 'alice@example.com'
  }
})

// Explicit equals
const user = await prisma.user.findFirst({
  where: {
    email: { equals: 'alice@example.com' }
  }
})

// Not equal
const users = await prisma.user.findMany({
  where: {
    email: { not: 'spam@example.com' }
  }
})

​
Comparison Filters

​
lt
filter
Less than
const users = await prisma.user.findMany({
  where: {
    age: { lt: 18 }
  }
})
​
lte
filter
Less than or equal to
const users = await prisma.user.findMany({
  where: {
    age: { lte: 18 }
  }
})
​
gt
filter
Greater than
const users = await prisma.user.findMany({
  where: {
    age: { gt: 65 }
  }
})
​
gte
filter
Greater than or equal to
const users = await prisma.user.findMany({
  where: {
    age: { gte: 18 }
  }
})

​
List Filters

​
in
filter
Value is in array
const users = await prisma.user.findMany({
  where: {
    id: { in: ['id1', 'id2', 'id3'] }
  }
})
​
notIn
filter
Value is not in array
const users = await prisma.user.findMany({
  where: {
    role: { notIn: ['ADMIN', 'MODERATOR'] }
  }
})

​
String Filters

​
contains
filter
Contains substring (case-sensitive by default)
const users = await prisma.user.findMany({
  where: {
    email: { contains: '@example.com' }
  }
})

// Case-insensitive (PostgreSQL)
const users = await prisma.user.findMany({
  where: {
    email: {
      contains: '@example.com',
      mode: 'insensitive'
    }
  }
})
​
startsWith
filter
Starts with string
const users = await prisma.user.findMany({
  where: {
    email: { startsWith: 'admin' }
  }
})
​
endsWith
filter
Ends with string
const users = await prisma.user.findMany({
  where: {
    email: { endsWith: '@company.com' }
  }
})
​
mode
'default' | 'insensitive'
Case sensitivity mode (PostgreSQL only)
const users = await prisma.user.findMany({
  where: {
    email: {
      equals: 'alice@example.com',
      mode: 'insensitive'
    }
  }
})

​
Null Filters

// IS NULL
const users = await prisma.user.findMany({
  where: {
    name: null
  }
})

// IS NOT NULL
const users = await prisma.user.findMany({
  where: {
    name: { not: null }
  }
})

// Check if field is set
const users = await prisma.user.findMany({
  where: {
    name: { isSet: true }
  }
})

​
Logical Operators

​
AND

// Implicit AND (all conditions must match)
const users = await prisma.user.findMany({
  where: {
    age: { gte: 18 },
    verified: true,
    email: { contains: '@example.com' }
  }
})

// Explicit AND
const users = await prisma.user.findMany({
  where: {
    AND: [
      { age: { gte: 18 } },
      { verified: true },
      { email: { contains: '@example.com' } }
    ]
  }
})

​
OR

const users = await prisma.user.findMany({
  where: {
    OR: [
      { email: 'alice@example.com' },
      { email: 'bob@example.com' }
    ]
  }
})

// Complex OR conditions
const users = await prisma.user.findMany({
  where: {
    OR: [
      { age: { lt: 18 } },
      { age: { gt: 65 } },
      {
        AND: [
          { verified: false },
          { createdAt: { lt: new Date('2024-01-01') } }
        ]
      }
    ]
  }
})

​
NOT

// Negate condition
const users = await prisma.user.findMany({
  where: {
    NOT: {
      email: { contains: 'spam' }
    }
  }
})

// NOT with multiple conditions
const users = await prisma.user.findMany({
  where: {
    NOT: [
      { age: { lt: 18 } },
      { verified: false }
    ]
  }
})

​
Combining Operators

const users = await prisma.user.findMany({
  where: {
    AND: [
      { verified: true },
      {
        OR: [
          { age: { gte: 18, lte: 25 } },
          { age: { gte: 60 } }
        ]
      },
      {
        NOT: {
          email: { contains: 'test' }
        }
      }
    ]
  }
})

​
Relation Filters

​
some

At least one related record matches: 
// Users with at least one published post
const users = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        published: true
      }
    }
  }
})

// Users with at least one post (any post)
const users = await prisma.user.findMany({
  where: {
    posts: {
      some: {}
    }
  }
})

​
every

All related records match: 
// Users where ALL posts are published
const users = await prisma.user.findMany({
  where: {
    posts: {
      every: {
        published: true
      }
    }
  }
})

// Note: Returns users with NO posts as well
// (vacuous truth - all zero posts are published)

​
none

No related records match: 
// Users with no posts
const users = await prisma.user.findMany({
  where: {
    posts: {
      none: {}
    }
  }
})

// Users with no published posts
const users = await prisma.user.findMany({
  where: {
    posts: {
      none: {
        published: true
      }
    }
  }
})

​
is / isNot

Filter by to-one relations: 
// Posts by specific author
const posts = await prisma.post.findMany({
  where: {
    author: {
      is: {
        email: 'alice@example.com'
      }
    }
  }
})

// Posts NOT by specific author
const posts = await prisma.post.findMany({
  where: {
    author: {
      isNot: {
        email: 'alice@example.com'
      }
    }
  }
})

​
Sorting

​
Basic Sorting

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

// Single field, descending
const users = await prisma.user.findMany({
  orderBy: {
    createdAt: 'desc'
  }
})

​
Multi-Field Sorting

const users = await prisma.user.findMany({
  orderBy: [
    { age: 'desc' },      // Primary sort
    { name: 'asc' },      // Secondary sort
    { email: 'asc' }      // Tertiary sort
  ]
})

​
Null Sorting

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

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

​
Sort by Relation Fields

// Sort posts by author name
const posts = await prisma.post.findMany({
  orderBy: {
    author: {
      name: 'asc'
    }
  }
})

// Sort by nested relation
const comments = await prisma.comment.findMany({
  orderBy: {
    post: {
      author: {
        name: 'asc'
      }
    }
  }
})

​
Sort by Relation Count

// Sort users by post count
const users = await prisma.user.findMany({
  orderBy: {
    posts: {
      _count: 'desc'
    }
  }
})

​
Sort by Aggregations

// Sort users by average post views
const users = await prisma.user.findMany({
  orderBy: {
    posts: {
      _avg: {
        views: 'desc'
      }
    }
  }
})

// Available aggregations in orderBy:
// _count, _avg, _sum, _min, _max

​
Pagination

​
Offset Pagination

​
skip
number
Number of records to skip
const users = await prisma.user.findMany({
  skip: 10,
  take: 10
})
​
take
number
Number of records to return
const users = await prisma.user.findMany({
  take: 10  // Limit to 10 records
})
Pagination helper: 
function paginate(page: number, pageSize: number) {
  return {
    skip: (page - 1) * pageSize,
    take: pageSize
  }
}

const users = await prisma.user.findMany({
  ...paginate(2, 20),  // Page 2, 20 items per page
  orderBy: { createdAt: 'desc' }
})

​
Cursor-Based Pagination

​
cursor
object
Start position based on unique field
const users = await prisma.user.findMany({
  take: 10,
  skip: 1,  // Skip the cursor itself
  cursor: {
    id: 'last-seen-id'
  },
  orderBy: { id: 'asc' }
})
Forward pagination: 
// First page
const firstPage = await prisma.user.findMany({
  take: 10,
  orderBy: { id: 'asc' }
})

// Next page
if (firstPage.length > 0) {
  const lastUser = firstPage[firstPage.length - 1]
  const nextPage = await prisma.user.findMany({
    take: 10,
    skip: 1,
    cursor: { id: lastUser.id },
    orderBy: { id: 'asc' }
  })
}
Backward pagination: 
const prevPage = await prisma.user.findMany({
  take: -10,  // Negative for reverse
  skip: 1,
  cursor: { id: firstUser.id },
  orderBy: { id: 'asc' }
})
Why cursor pagination?
  • Consistent results when data changes
  • Better performance on large datasets
  • No “page drift” when records are added/deleted

​
Get Total Count

const [users, total] = await prisma.$transaction([
  prisma.user.findMany({
    skip: 0,
    take: 20
  }),
  prisma.user.count()
])

const totalPages = Math.ceil(total / 20)

​
Distinct

Return unique records based on one or more fields: 
// Unique emails
const users = await prisma.user.findMany({
  distinct: ['email']
})

// Unique combinations
const users = await prisma.user.findMany({
  distinct: ['email', 'name']
})

// With filtering and sorting
const users = await prisma.user.findMany({
  where: { verified: true },
  distinct: ['email'],
  orderBy: { createdAt: 'desc' }
})

​
Advanced Filtering

​
JSON Field Filters

// Exact match
const users = await prisma.user.findMany({
  where: {
    metadata: {
      equals: { theme: 'dark', language: 'en' }
    }
  }
})

// Path-based access
const users = await prisma.user.findMany({
  where: {
    metadata: {
      path: ['settings', 'notifications'],
      equals: true
    }
  }
})

// String operations on JSON
const users = await prisma.user.findMany({
  where: {
    metadata: {
      path: ['theme'],
      string_contains: 'dark'
    }
  }
})

​
Array Field Filters

(PostgreSQL, CockroachDB, MongoDB) 
// Has specific value
const users = await prisma.user.findMany({
  where: {
    tags: { has: 'premium' }
  }
})

// Has any of these values
const users = await prisma.user.findMany({
  where: {
    tags: { hasSome: ['premium', 'verified'] }
  }
})

// Has all of these values
const users = await prisma.user.findMany({
  where: {
    tags: { hasEvery: ['premium', 'verified'] }
  }
})

// Empty array
const users = await prisma.user.findMany({
  where: {
    tags: { isEmpty: true }
  }
})

​
Full-Text Search

PostgreSQL: 
const posts = await prisma.post.findMany({
  where: {
    content: {
      search: 'cat & dog'  // Both words
    }
  }
})

// OR search
const posts = await prisma.post.findMany({
  where: {
    content: {
      search: 'cat | dog'  // Either word
    }
  }
})
MySQL: 
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { search: 'prisma' } },
      { content: { search: 'prisma' } }
    ]
  }
})

​
Performance Tips

​
Index Your Filters

Add database indexes for frequently filtered fields: 
model User {
  id    String @id
  email String @unique  // Automatic index
  name  String
  age   Int
  
  @@index([age])        // Manual index
  @@index([name, age])  // Composite index
}

​
Avoid N+1 Queries

// ❌ Bad: Multiple queries
const users = await prisma.user.findMany()
for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id }
  })
}

// ✓ Good: Single query with include
const users = await prisma.user.findMany({
  include: { posts: true }
})

​
Use Cursor Pagination for Large Datasets

// Offset pagination gets slower as offset increases
await prisma.user.findMany({
  skip: 100000,  // Slow!
  take: 20
})

// Cursor pagination maintains performance
await prisma.user.findMany({
  take: 20,
  cursor: { id: lastId },
  orderBy: { id: 'asc' }
})

​
Next Steps

Aggregations

Count, sum, and aggregate your data

Transactions

Ensure data consistency with transactions