Understanding Prisma and Sql — Relationships, Schema, and Querying

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: '[e-mail]' }
})

// 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: '[e-mail]',
    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) => { ... })