- Equality Filter
- Not Equals Filter
- Contains Filter
- Starts With Filter
- Ends With Filter
- Greater Than / Less Than Filters
- In / Not In Filters
- Logical Filters (AND, OR, NOT)
- List Filters
- Relation Filters
- JSON Filters
- Full-text Search Filter
- Between Filter
- Null Filters
Prisma provides a wide range of powerful filtering options that allow developers to query data based on various conditions. Below are the major filter types along with concrete examples for each one.
Equality Filter
Description: Retrieves records where a field is equal to the specified value.
Example:
const users = await prisma.user.findMany({
where: {
email: {
equals: "user@example.com",
},
},
});
Not Equals Filter
Description: Retrieves records where a field is not equal to the specified value.
Example:
const activeUsers = await prisma.user.findMany({
where: {
status: {
not: "inactive",
},
},
});
Contains Filter
Description: Retrieves records where a string field contains the specified substring. Commonly used for text searching.
Example:
const postsWithPrisma = await prisma.post.findMany({
where: {
title: {
contains: "Prisma",
mode: "insensitive", // optional, case-insensitive search
},
},
});
Starts With Filter
Description: Retrieves records where a string field starts with the specified substring.
Example:
const productsStartingWithPro = await prisma.product.findMany({
where: {
name: {
startsWith: "Pro",
},
},
});
Ends With Filter
Description: Retrieves records where a string field ends with the specified substring.
Example:
const imageFiles = await prisma.file.findMany({
where: {
filename: {
endsWith: ".jpg",
},
},
});
Greater Than / Less Than Filters
Description: Retrieves records where a numerical or date field is greater than, less than, or otherwise compared to a specified value.
Example:
const recentOrders = await prisma.order.findMany({
where: {
createdAt: {
gt: new Date('2024-01-01'),
},
},
});
In / Not In Filters
Description: Retrieves records where a field’s value is (or is not) within a specified list of values.
Example:
const categories = ['Electronics', 'Books', 'Clothing'];
const products = await prisma.product.findMany({
where: {
category: {
in: categories,
},
},
});
Logical Filters (AND, OR, NOT)
Description: Combine multiple filtering conditions using logical operators.
Example (using AND):
const activeAdmins = await prisma.user.findMany({
where: {
AND: [
{ role: "admin" },
{ isActive: true },
],
},
});
Example (using OR):
const adminsOrModerators = await prisma.user.findMany({
where: {
OR: [
{ role: "admin" },
{ role: "moderator" },
],
},
});
Example (using NOT):
const nonAdmins = await prisma.user.findMany({
where: {
NOT: { role: "admin" },
},
});
List Filters
Description: Filters records based on array (list-type) fields.
Example:
const postsWithTagPrisma = await prisma.post.findMany({
where: {
tags: {
has: "prisma",
},
},
});
Relation Filters
Description: Filters records based on fields of related models.
Example:
const postsBySpecificAuthor = await prisma.post.findMany({
where: {
author: {
email: "author@example.com",
},
},
});
JSON Filters
Description: Allows advanced filtering on JSON-type fields.
Example:
const usersWithDarkTheme = await prisma.user.findMany({
where: {
metadata: {
path: ['preferences', 'theme'],
equals: 'dark',
},
},
});
Full-text Search Filter
Description: Performs full-text search on text fields (supported depending on the database).
Example (PostgreSQL):
const searchResults = await prisma.post.findMany({
where: {
searchVector: {
search: "Prisma ORM",
},
},
});
Between Filter
Description: Retrieves records where a field’s value falls within a specified range.
Example:
const ordersInRange = await prisma.order.findMany({
where: {
total: {
gte: 100,
lte: 500,
},
},
});
Null Filters
Description: Filters records based on whether a field is null or not null.
Example:
const usersWithoutProfile = await prisma.user.findMany({
where: {
profile: {
is: null,
},
},
});
These filtering options can be used individually or combined to construct complex and powerful query conditions.