Prisma makes database relationships intuitive. Here's how to model and query them effectively.
One-to-Many Relationships#
1// schema.prisma
2model User {
3 id String @id @default(cuid())
4 email String @unique
5 name String?
6 posts Post[] // One user has many posts
7 profile Profile? // One user has one profile
8}
9
10model Post {
11 id String @id @default(cuid())
12 title String
13 content String?
14 published Boolean @default(false)
15 author User @relation(fields: [authorId], references: [id])
16 authorId String
17 createdAt DateTime @default(now())
18}
19
20model Profile {
21 id String @id @default(cuid())
22 bio String?
23 user User @relation(fields: [userId], references: [id])
24 userId String @unique // Unique makes it one-to-one
25}1// Create with relation
2const user = await prisma.user.create({
3 data: {
4 email: 'john@example.com',
5 name: 'John',
6 posts: {
7 create: [
8 { title: 'First Post', content: 'Hello world' },
9 { title: 'Second Post', content: 'More content' },
10 ],
11 },
12 profile: {
13 create: {
14 bio: 'Software developer',
15 },
16 },
17 },
18 include: {
19 posts: true,
20 profile: true,
21 },
22});
23
24// Query with relations
25const userWithPosts = await prisma.user.findUnique({
26 where: { id: userId },
27 include: {
28 posts: {
29 where: { published: true },
30 orderBy: { createdAt: 'desc' },
31 take: 10,
32 },
33 profile: true,
34 },
35});
36
37// Select specific fields
38const userWithPostTitles = await prisma.user.findUnique({
39 where: { id: userId },
40 select: {
41 name: true,
42 posts: {
43 select: {
44 title: true,
45 createdAt: true,
46 },
47 },
48 },
49});Many-to-Many Relationships#
1// Implicit many-to-many
2model Post {
3 id String @id @default(cuid())
4 title String
5 categories Category[]
6}
7
8model Category {
9 id String @id @default(cuid())
10 name String @unique
11 posts Post[]
12}
13
14// Explicit many-to-many (with extra fields)
15model Post {
16 id String @id @default(cuid())
17 title String
18 tags PostTag[]
19}
20
21model Tag {
22 id String @id @default(cuid())
23 name String @unique
24 posts PostTag[]
25}
26
27model PostTag {
28 post Post @relation(fields: [postId], references: [id])
29 postId String
30 tag Tag @relation(fields: [tagId], references: [id])
31 tagId String
32 assignedAt DateTime @default(now())
33 assignedBy String?
34
35 @@id([postId, tagId])
36}1// Implicit many-to-many
2const post = await prisma.post.create({
3 data: {
4 title: 'New Post',
5 categories: {
6 connect: [{ id: 'cat1' }, { id: 'cat2' }],
7 // Or create new categories
8 create: [{ name: 'Technology' }],
9 // Or connect or create
10 connectOrCreate: [
11 {
12 where: { name: 'Programming' },
13 create: { name: 'Programming' },
14 },
15 ],
16 },
17 },
18 include: { categories: true },
19});
20
21// Update many-to-many
22const updatedPost = await prisma.post.update({
23 where: { id: postId },
24 data: {
25 categories: {
26 set: [{ id: 'cat3' }], // Replace all
27 disconnect: [{ id: 'cat1' }], // Remove specific
28 connect: [{ id: 'cat4' }], // Add new
29 },
30 },
31});
32
33// Explicit many-to-many
34const postWithTags = await prisma.post.create({
35 data: {
36 title: 'Tagged Post',
37 tags: {
38 create: [
39 {
40 assignedBy: 'admin',
41 tag: {
42 connectOrCreate: {
43 where: { name: 'javascript' },
44 create: { name: 'javascript' },
45 },
46 },
47 },
48 ],
49 },
50 },
51 include: {
52 tags: {
53 include: { tag: true },
54 },
55 },
56});Self-Relations#
1// User following users
2model User {
3 id String @id @default(cuid())
4 name String
5 followers Follow[] @relation("followers")
6 following Follow[] @relation("following")
7}
8
9model Follow {
10 follower User @relation("following", fields: [followerId], references: [id])
11 followerId String
12 following User @relation("followers", fields: [followingId], references: [id])
13 followingId String
14 createdAt DateTime @default(now())
15
16 @@id([followerId, followingId])
17}
18
19// Hierarchical data (tree structure)
20model Category {
21 id String @id @default(cuid())
22 name String
23 parent Category? @relation("CategoryTree", fields: [parentId], references: [id])
24 parentId String?
25 children Category[] @relation("CategoryTree")
26}
27
28// Comments with replies
29model Comment {
30 id String @id @default(cuid())
31 content String
32 parent Comment? @relation("CommentReplies", fields: [parentId], references: [id])
33 parentId String?
34 replies Comment[] @relation("CommentReplies")
35 post Post @relation(fields: [postId], references: [id])
36 postId String
37}1// Follow a user
2await prisma.follow.create({
3 data: {
4 followerId: currentUserId,
5 followingId: targetUserId,
6 },
7});
8
9// Get followers
10const userWithFollowers = await prisma.user.findUnique({
11 where: { id: userId },
12 include: {
13 followers: {
14 include: { follower: true },
15 },
16 following: {
17 include: { following: true },
18 },
19 },
20});
21
22// Get category tree
23const categories = await prisma.category.findMany({
24 where: { parentId: null }, // Root categories
25 include: {
26 children: {
27 include: {
28 children: true, // Two levels deep
29 },
30 },
31 },
32});
33
34// Get comments with replies
35const comments = await prisma.comment.findMany({
36 where: {
37 postId: postId,
38 parentId: null, // Top-level comments only
39 },
40 include: {
41 replies: {
42 include: {
43 replies: true, // Nested replies
44 },
45 },
46 },
47});Nested Writes#
1// Create deeply nested data
2const order = await prisma.order.create({
3 data: {
4 customer: {
5 connectOrCreate: {
6 where: { email: 'customer@example.com' },
7 create: {
8 email: 'customer@example.com',
9 name: 'John Doe',
10 address: {
11 create: {
12 street: '123 Main St',
13 city: 'New York',
14 country: 'USA',
15 },
16 },
17 },
18 },
19 },
20 items: {
21 create: [
22 {
23 quantity: 2,
24 product: {
25 connect: { id: productId },
26 },
27 },
28 ],
29 },
30 payment: {
31 create: {
32 method: 'credit_card',
33 amount: 99.99,
34 },
35 },
36 },
37 include: {
38 customer: {
39 include: { address: true },
40 },
41 items: {
42 include: { product: true },
43 },
44 payment: true,
45 },
46});
47
48// Nested update
49const updatedOrder = await prisma.order.update({
50 where: { id: orderId },
51 data: {
52 status: 'shipped',
53 items: {
54 updateMany: {
55 where: { shipped: false },
56 data: { shipped: true },
57 },
58 },
59 shipment: {
60 upsert: {
61 create: {
62 trackingNumber: 'TRK123',
63 carrier: 'UPS',
64 },
65 update: {
66 trackingNumber: 'TRK123-UPDATED',
67 },
68 },
69 },
70 },
71});Filtering by Relations#
1// Find posts where author name contains "John"
2const posts = await prisma.post.findMany({
3 where: {
4 author: {
5 name: { contains: 'John' },
6 },
7 },
8});
9
10// Find users who have at least one published post
11const activeAuthors = await prisma.user.findMany({
12 where: {
13 posts: {
14 some: { published: true },
15 },
16 },
17});
18
19// Find users who have no posts
20const usersWithoutPosts = await prisma.user.findMany({
21 where: {
22 posts: {
23 none: {},
24 },
25 },
26});
27
28// Find users where ALL posts are published
29const usersAllPublished = await prisma.user.findMany({
30 where: {
31 posts: {
32 every: { published: true },
33 },
34 },
35});
36
37// Complex relation filters
38const users = await prisma.user.findMany({
39 where: {
40 AND: [
41 {
42 posts: {
43 some: {
44 published: true,
45 categories: {
46 some: { name: 'Technology' },
47 },
48 },
49 },
50 },
51 {
52 profile: {
53 isNot: null,
54 },
55 },
56 ],
57 },
58});Aggregations with Relations#
1// Count related records
2const usersWithPostCount = await prisma.user.findMany({
3 include: {
4 _count: {
5 select: { posts: true },
6 },
7 },
8});
9
10// Filter by count
11const usersWithPostCount = await prisma.user.findMany({
12 include: {
13 _count: {
14 select: {
15 posts: {
16 where: { published: true },
17 },
18 },
19 },
20 },
21});
22
23// Order by relation count
24const popularAuthors = await prisma.user.findMany({
25 orderBy: {
26 posts: {
27 _count: 'desc',
28 },
29 },
30 take: 10,
31});
32
33// Group with relation aggregation
34const categoriesWithStats = await prisma.category.findMany({
35 include: {
36 _count: { select: { posts: true } },
37 posts: {
38 select: {
39 _count: { select: { comments: true } },
40 },
41 },
42 },
43});Transactions#
1// Sequential transaction
2const [user, post] = await prisma.$transaction([
3 prisma.user.create({
4 data: { email: 'new@example.com', name: 'New User' },
5 }),
6 prisma.post.create({
7 data: {
8 title: 'First Post',
9 authorId: 'will-be-replaced', // Placeholder
10 },
11 }),
12]);
13
14// Interactive transaction
15const transfer = await prisma.$transaction(async (tx) => {
16 const sender = await tx.account.update({
17 where: { id: senderId },
18 data: { balance: { decrement: amount } },
19 });
20
21 if (sender.balance < 0) {
22 throw new Error('Insufficient funds');
23 }
24
25 const recipient = await tx.account.update({
26 where: { id: recipientId },
27 data: { balance: { increment: amount } },
28 });
29
30 return { sender, recipient };
31});Best Practices#
Schema Design:
✓ Use appropriate relation types
✓ Name relations clearly
✓ Consider cascade deletes
✓ Add indexes for foreign keys
Queries:
✓ Use include/select wisely
✓ Avoid N+1 with includes
✓ Filter relations in query
✓ Use transactions for consistency
Performance:
✓ Limit nested includes depth
✓ Paginate relation results
✓ Use count instead of fetching
✓ Index frequently filtered fields
Conclusion#
Prisma makes working with relationships intuitive through its schema definition and query API. Use implicit many-to-many for simple cases and explicit for additional fields. Filter and aggregate through relations efficiently, and use transactions for data consistency across multiple operations.