15.3 Prisma Relations — Type-Safe 1:1, 1:N, M:N Queries

Relation Types

One-to-One (1:1)

model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId Int    @unique  // @unique expresses 1:1
}

// Create 1:1
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    profile: {
      create: { bio: 'TypeScript developer' },
    },
  },
  include: { profile: true },
})

// Update profile
const updated = await prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      upsert: {
        create: { bio: 'New profile' },
        update: { bio: 'Updated profile' },
      },
    },
  },
  include: { profile: true },
})

---

One-to-Many (1:N)

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int
}

// Fetch user with posts
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
      select: {
        id: true,
        title: true,
        createdAt: true,
      },
    },
  },
})

// Access author from post
const post = await prisma.post.findUnique({
  where: { id: 1 },
  include: { author: true },
})

// Users with at least one published post
const activeAuthors = await prisma.user.findMany({
  where: {
    posts: {
      some: { published: true },
    },
  },
})

// Users with no posts
const noPostUsers = await prisma.user.findMany({
  where: {
    posts: {
      none: {},
    },
  },
})

// Users where all posts are published
const allPublishedAuthors = await prisma.user.findMany({
  where: {
    posts: {
      every: { published: true },
    },
  },
})

---

Many-to-Many (M:N)

// Implicit M:N (auto-generated join table)
model Post {
  id   Int   @id @default(autoincrement())
  tags Tag[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

// Explicit M:N (manually define join table)
model Post {
  id       Int         @id @default(autoincrement())
  postTags PostTag[]
}

model Tag {
  id       Int         @id @default(autoincrement())
  postTags PostTag[]
}

model PostTag {
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     Int
  createdAt DateTime @default(now())

  @@id([postId, tagId])  // Composite primary key
}

// Implicit M:N — connect tags
const post = await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      connect: [{ id: 1 }, { id: 2 }],      // Connect existing tags
      create: [{ name: 'new-tag' }],          // Create and connect new tag
      disconnect: [{ id: 3 }],              // Disconnect
      set: [{ id: 1 }, { id: 2 }],         // Replace all connections
    },
  },
  include: { tags: true },
})

// Find posts by tag
const typescriptPosts = await prisma.post.findMany({
  where: {
    tags: {
      some: { name: 'TypeScript' },
    },
  },
  include: { tags: true },
})

// Explicit M:N — add data to join table
const postTag = await prisma.postTag.create({
  data: {
    postId: 1,
    tagId: 2,
  },
})

---

Self-Relations

model Category {
  id        Int        @id @default(autoincrement())
  name      String
  parent    Category?  @relation("CategoryTree", fields: [parentId], references: [id])
  parentId  Int?
  children  Category[] @relation("CategoryTree")
}

// Fetch category tree
const rootCategories = await prisma.category.findMany({
  where: { parentId: null },
  include: {
    children: {
      include: {
        children: true,  // Load 2 levels deep
      },
    },
  },
})

---

Relation Filtering

// Nested select for specific fields
const result = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    posts: {
      select: {
        title: true,
        published: true,
      },
      where: { published: true },
    },
    _count: {
      select: { posts: true },  // Include post count
    },
  },
})
// result[0].posts → { title: string; published: boolean }[]
// result[0]._count.posts → number

---

Pro Tips

Fluent API (Chaining Style)

// Fetch posts for a specific user (chain nested relations)
const posts = await prisma.user
  .findUnique({ where: { id: 1 } })
  .posts({ where: { published: true } })

// Access author from post
const author = await prisma.post
  .findUnique({ where: { id: 1 } })
  .author()

// Access user profile
const profile = await prisma.user
  .findUnique({ where: { id: 1 } })
  .profile()