Part 1 — Understanding Relationships in the Schema
Before you can query, you need to understand what the schema is actually saying. Prisma uses a specific syntax to define how models relate to each other.
One-to-Many
One user has many posts. One post belongs to one user.
model User {
id String @id @default(cuid())
name String
posts Post[] // <-- User has many posts (array = many)
}
model Post {
id String @id @default(cuid())
content String
author User @relation(fields: [authorId], references: [id]) // <-- Post belongs to one User
authorId String // <-- This is the foreign key stored in the DB
}
What to understand here:
Post stores the foreign key (authorId) — that's always where @relation lives, on the model that "owns" the foreign key. User just has posts Post[] as a reference back, it stores nothing extra in the database for that.
When you write @relation(fields: [authorId], references: [id]) you're saying: the authorId column in this table points to the id column in the User table.
Many-to-Many (via a junction table)
One user can be a member of many projects. One project can have many members. You need a table in between.
model User {
id String @id @default(cuid())
memberships ProjectMember[] // User has many memberships
}
model Project {
id String @id @default(cuid())
members ProjectMember[] // Project has many memberships
}
model ProjectMember {
id String @id @default(cuid())
role String @default("MEMBER")
user User @relation(fields: [userId], references: [id])
userId String
project Project @relation(fields: [projectId], references: [id])
projectId String
@@unique([userId, projectId]) // A user can only appear once per project
}
ProjectMember is the junction table. It holds foreign keys to both sides and can carry its own data (like role). @@unique([userId, projectId]) is a composite unique constraint — it means the combination of both columns must be unique. A user can appear multiple times in the table, but never twice for the same project.
Self-Referential (a model relating to itself)
This is what Follow does in a social media platform — a User follows another User.
model User {
id String @id @default(cuid())
followers Follow[] @relation("Following") // People who follow this user
following Follow[] @relation("Follower") // People this user follows
}
model Follow {
id String @id @default(cuid())
follower User @relation("Follower", fields: [followerId], references: [id])
followerId String
following User @relation("Following", fields: [followingId], references: [id])
followingId String
@@unique([followerId, followingId])
}
Because both sides point to User, Prisma can't figure out which is which without named relations. "Follower" and "Following" are just labels that let Prisma match up which field on User maps to which side of Follow. The names themselves don't matter, they just have to match between the model and the junction table.
Named Relations (Two Relations to the Same Model)
Same problem occurs in a Task model — a task has an assignedTo user and a createdBy user. Both point to User, so both need names:
model Task {
assignedTo User? @relation("AssignedTo", fields: [assignedToId], references: [id])
assignedToId String?
createdBy User @relation("CreatedBy", fields: [createdById], references: [id])
createdById String
}
model User {
assignedTasks Task[] @relation("AssignedTo")
createdTasks Task[] @relation("CreatedBy")
}
The question mark in the assignedTo user is to show that it's optional.
Rule: any time two fields in the same model point to the same other model, both need a name. Prisma will throw an error at migration time if you forget.
Part 2 — Reading Data (Queries)
Every read in Prisma starts with one of these:
| Method | When to use it |
|---|---|
findMany |
Get a list of records |
findUnique |
Get exactly one record by a unique field |
findFirst |
Get the first record matching a filter |
findUnique
Used when you're looking up by a field marked @id or @unique in the schema.
// Get user by id
const user = await prisma.user.findUnique({
where: { id: 'some-id' }
})
// Get user by email (also @unique)
const user = await prisma.user.findUnique({
where: { email: 'jeffrey@velto.com' }
})
// Composite unique — ProjectMember has @@unique([userId, projectId])
const membership = await prisma.projectMember.findUnique({
where: {
userId_projectId: { // <-- Prisma generates this name from the field names
userId: 'abc',
projectId: 'xyz'
}
}
})
The composite unique key name is generated by joining the field names with underscores. @@unique([userId, projectId]) becomes userId_projectId. @@unique([postId, userId]) on Like becomes postId_userId.
findUnique returns null if nothing is found, it doesn't throw.
findMany
Gets a list. By default it returns everything. You narrow it with filters.
// All projects
const projects = await prisma.project.findMany()
// All projects ordered by name
const projects = await prisma.project.findMany({
orderBy: { name: 'asc' } // 'asc' or 'desc'
})
// Latest 20 posts
const posts = await prisma.post.findMany({
orderBy: { createdAt: 'desc' },
take: 20
})
// Posts 21-40 (pagination)
const posts = await prisma.post.findMany({
orderBy: { createdAt: 'desc' },
take: 20,
skip: 20
})
findFirst
Like findMany but returns only the first match. Useful when you need one record but can't use findUnique because you're not filtering by a unique field.
const task = await prisma.task.findFirst({
where: { title: 'Fix the login bug' }
})
Part 3 — where
where is how you filter. It maps 1:1 to SQL's WHERE clause.
Basic equality
const users = await prisma.user.findMany({
where: { role: 'ADMIN' }
})
this returns all records with role ADMIN
Operators
Prisma wraps operators in objects:
where: {
price: { gt: 50 } // greater than
price: { gte: 50 } // greater than or equal
price: { lt: 100 } // less than
price: { lte: 100 } // less than or equal
price: { not: 50 } // not equal
name: { contains: 'shoe' } // SQL LIKE '%shoe%'
name: { contains: 'shoe', mode: 'insensitive' } // case-insensitive
name: { startsWith: 'Nike' }
name: { endsWith: 'Pro' }
id: { in: ['id1', 'id2', 'id3'] } // SQL IN
id: { notIn: ['id1', 'id2'] }
}
AND, OR, NOT
// Both conditions must be true (AND is the default when you pass multiple fields)
where: {
category: 'Electronics',
price: { lte: 100 }
}
// Explicit OR — either condition matches
where: {
OR: [
{ name: { contains: 'shoe', mode: 'insensitive' } },
{ description: { contains: 'shoe', mode: 'insensitive' } }
]
}
// NOT
where: {
NOT: { role: 'ADMIN' }
}
// Combining them
where: {
AND: [
{ category: 'Electronics' },
{
OR: [
{ name: { contains: 'phone' } },
{ name: { contains: 'laptop' } }
]
}
]
}
Relation Filters — some, every, none
These are used inside where when you want to filter by properties of a related model.
// Get all projects where the current user is a member
const projects = await prisma.project.findMany({
where: {
members: {
some: { userId: req.user.id }
}
}
})
some means "at least one related record matches this condition." This is the most common one.
// some — at least one member has role OWNER
where: { members: { some: { role: 'OWNER' } } }
// every — ALL members have role OWNER (rarely needed)
where: { members: { every: { role: 'OWNER' } } }
// none — NO members have role OWNER
where: { members: { none: { role: 'OWNER' } } }
The feed query in a social media platform uses a nested some:
// Posts from users the logged-in user follows
where: {
author: {
followers: {
some: { followerId: req.user.id }
}
}
}
Breaking this down: you're filtering Post where the author (a User) has followers (a list of Follow records) where at least one has followerId matching the current user. That's a three-level deep filter in one where.
Conditional where (dynamic filters)
This pattern shows up in the products search route:
const { category, search } = req.query
const products = await prisma.product.findMany({
where: {
...(category && { category }), // Only add if category is provided
...(search && {
OR: [
{ name: { contains: search, mode: 'insensitive' } },
{ description: { contains: search, mode: 'insensitive' } }
]
})
}
})
The spread ...(condition && { key: value }) is a JavaScript pattern — if category is undefined or empty, the spread adds nothing. If it has a value, it spreads { category: 'Electronics' } into the where object.
Part 4 — select
select lets you choose exactly which fields to return. Everything not listed is excluded.
// Only get id, name, email — not password, not createdAt
const user = await prisma.user.findUnique({
where: { id: userId },
select: {
id: true,
name: true,
email: true
}
})
You cannot use select and include at the same level at the same time. You pick one.
But you CAN use select inside an include:
const tasks = await prisma.task.findMany({
include: {
assignedTo: {
select: { id: true, name: true } // Only get id and name of the user, not their password etc.
}
}
})
This is the correct pattern for pulling in related data without exposing sensitive fields.
Part 5 — include
include tells Prisma to JOIN related data and return it nested in the result. Without it, relations just don't come back.
// Without include — you just get the task fields
const task = await prisma.task.findUnique({ where: { id } })
// Result: { id, title, description, boardId, assignedToId, createdById, ... }
// No user data, no board data, no comments
// With include
const task = await prisma.task.findUnique({
where: { id },
include: {
assignedTo: true, // Include the full User object
board: true, // Include the full Board object
comments: true // Include all Comment objects in an array
}
})
// Result: { id, title, ..., assignedTo: { id, name, email, ... }, board: { ... }, comments: [...] }
Nested include
You can include inside an include:
const project = await prisma.project.findUnique({
where: { id },
include: {
boards: {
orderBy: { order: 'asc' },
include: {
tasks: {
include: {
assignedTo: {
select: { id: true, name: true }
}
}
}
}
}
}
})
This gives you: project → boards (sorted) → tasks → assignedTo (just id and name). Three levels deep in one query.
Filtering inside include
You can put a where inside an include to filter the related records:
// Only include memberships where the user is an OWNER
include: {
members: {
where: { role: 'OWNER' }
}
}
// The isFollowing trick from a social media platform — only include the Follow record if it belongs to the current user
include: {
likes: {
where: { userId: req.user.id }
}
}
// If the current user liked this post, likes array has 1 item. If not, it's empty [].
// So isLiked = likes.length > 0
Part 6 — _count
_count gives you the count of related records without fetching them all. It's like doing a COUNT(*) in SQL without loading the actual rows.
const projects = await prisma.project.findMany({
include: {
_count: {
select: {
members: true, // Count how many members
boards: true // Count how many boards
}
}
}
})
// Result: { id, name, ..., _count: { members: 4, boards: 3 } }
You can combine _count with other includes:
include: {
_count: { select: { likes: true, comments: true } },
assignedTo: { select: { id: true, name: true } },
comments: true
}
Part 7 — Writing Data
create
const user = await prisma.user.create({
data: {
name: 'Jeffrey',
email: 'jeffrey@velto.com',
password: hashedPassword
}
})
You can create related records inline (nested writes):
// Create a project and immediately create its boards in one call
const project = await prisma.project.create({
data: {
name: 'Velto Website Redesign',
boards: {
create: [
{ name: 'To Do', order: 0 },
{ name: 'In Progress', order: 1 },
{ name: 'Done', order: 2 }
]
}
}
})
createMany
Creates multiple records in one database call:
await prisma.board.createMany({
data: [
{ name: 'To Do', order: 0, projectId: project.id },
{ name: 'In Progress', order: 1, projectId: project.id },
{ name: 'Done', order: 2, projectId: project.id }
]
})
createMany doesn't support nested creates or return the created records by default. Use create with nested writes if you need that.
update
// Update a single record by unique field
await prisma.task.update({
where: { id: taskId },
data: { title: 'New title' }
})
Partial updates — only update what you send:
// Only update fields the client sent, ignore undefined ones
const { title, description, priority, boardId } = req.body
await prisma.task.update({
where: { id },
data: {
...(title !== undefined && { title }),
...(description !== undefined && { description }),
...(priority !== undefined && { priority }),
...(boardId !== undefined && { boardId })
}
})
Atomic operations (increment/decrement without a read-then-write):
await prisma.product.update({
where: { id: productId },
data: { stock: { decrement: quantity } }
})
// Also available: increment, multiply, divide, set
updateMany
Updates all records matching a filter:
// Mark all notifications as read for this user
await prisma.notification.updateMany({
where: { userId: req.user.id, read: false },
data: { read: true }
})
updateMany returns { count: N } — the number of records updated.
upsert
Either creates or updates depending on whether the record exists. Takes three fields: where, update, create.
// Add to cart — if the user already has this product, increment quantity
const cartItem = await prisma.cartItem.upsert({
where: {
userId_productId: { // Composite unique key
userId: req.user.id,
productId
}
},
update: {
quantity: { increment: quantity } // Record exists — just increment
},
create: {
userId: req.user.id,
productId,
quantity // Record doesn't exist — create it
}
})
delete
await prisma.task.delete({ where: { id: taskId } })
deleteMany
// Clear the entire cart after placing an order
await prisma.cartItem.deleteMany({ where: { userId: req.user.id } })
Part 8 — Transactions ($transaction)
A transaction groups multiple operations so they either all succeed or all fail together. This is critical any time you're doing multiple writes that depend on each other.
const result = await prisma.$transaction(async (tx) => {
// Use 'tx' instead of 'prisma' inside here
const order = await tx.order.create({ data: { ... } })
await tx.cartItem.deleteMany({ where: { userId } })
for (const item of cartItems) {
await tx.product.update({
where: { id: item.productId },
data: { stock: { decrement: item.quantity } }
})
}
return order
})
If any operation inside throws an error, Prisma rolls back everything — none of it hits the database. This is how you protect against partial failures (order created but cart not cleared, or stock decremented but order creation failed).
To trigger a rollback manually, just throw inside the transaction:
const result = await prisma.$transaction(async (tx) => {
if (cartItems.length === 0) throw new Error('Cart is empty')
// ... rest of the logic
})
Part 9 — orderBy, take, skip
These work together for sorting and pagination.
// Sort by one field
orderBy: { createdAt: 'desc' }
// Sort by multiple fields
orderBy: [
{ priority: 'asc' },
{ createdAt: 'desc' }
]
// Limit results
take: 20
// Skip N records (for pagination)
// Page 1: skip 0, take 20
// Page 2: skip 20, take 20
// Page 3: skip 40, take 20
skip: (page - 1) * limit
// Sort related records inside include
include: {
boards: {
orderBy: { order: 'asc' }
}
}
Part 10 — Putting It All Together
Here's a realistic "get project with everything" query that combines most of what's above:
const project = await prisma.project.findUnique({
where: { id: projectId },
include: {
// All members with just the fields we need from User
members: {
include: {
user: {
select: { id: true, name: true, email: true }
}
}
},
// All boards sorted by order
boards: {
orderBy: { order: 'asc' },
include: {
tasks: {
include: {
// Assignee — only need id and name
assignedTo: {
select: { id: true, name: true }
},
// Comment count without loading the actual comments
_count: {
select: { comments: true }
}
}
}
}
}
}
})
// Check the user is actually a member before returning
const isMember = project.members.some(m => m.userId === req.user.id)
if (!isMember) return res.status(403).json({ message: 'Not a member' })
Quick Reference
| What you want | How to do it |
|---|---|
| Filter records | where: { field: value } |
| Filter with comparison | where: { price: { gt: 50 } } |
| Text search | where: { name: { contains: q, mode: 'insensitive' } } |
| Either/or filter | where: { OR: [...] } |
| Filter by related record | where: { relation: { some: { field: value } } } |
| Include related data | include: { relation: true } |
| Include and filter related | include: { relation: { where: { ... } } } |
| Pick specific fields | select: { id: true, name: true } |
| Pick fields on included relation | include: { relation: { select: { ... } } } |
| Count related records | include: { _count: { select: { relation: true } } } |
| Sort results | orderBy: { field: 'asc' } |
| Limit results | take: N |
| Paginate | take: N, skip: N |
| Create with related records | create: { data: { relation: { create: [...] } } } |
| Add or increment (upsert) | upsert: { where, update, create } |
| Increment a number atomically | update: { data: { stock: { decrement: 5 } } } |
| Update many at once | updateMany: { where, data } |
| Delete many at once | deleteMany: { where } |
| Group multiple writes safely | $transaction(async (tx) => { ... }) |
