Part 4: Practical Prisma Relationship Modeling & Queries (blog-demo Project)

Starting from this article, we move from a single-table world with only User to a multi-table world with real business semantics: blog-demo blog system.

We will use Prisma to build a classic relationship model: User / Profile / Post / Comment / Category, and explain clearly around it:

• One-to-one (one person, one profile)

• One-to-many (one person multiple articles, one article multiple comments)

• Many-to-many (one article multiple categories, one category multiple articles)

• Relation fields vs foreign key fields

• Nested writes and relational queries (include / select / relation filter)

This article assumes you are already familiar with the basic Schema syntax (datasource / generator / scalar types / attributes) covered earlier. If you've forgotten, you can always refer back to Part 3.

1. blog-demo Project and Overall Model Design

We continue with the tech stack mental model from previous articles:

• Prisma 7

• PostgreSQL as the database

• Next.js / Node.js as the runtime environment (the code examples in this article will use Node-style main() functions for demonstration, to focus on relationships)

The target data model for this article is a simplified blog system:

• User: users

• Profile: user profiles (one-to-one)

• Post: articles (User one-to-many Post)

• Comment: comments (Post one-to-many Comment)

• Category: categories (Post many-to-many Category)

It will ultimately form the following relationships:

• User 1 — 1 Profile

• User 1 — n Post

• Post 1 — n Comment

• Post m — n Category

2. blog-demo's schema.prisma: Write it all at once, then break it down section by section

First, let's provide a complete schema version, and then we'll explain each type of relationship writing piece by piece.

// generator & datasource：与前几篇保持一致的风格
generator client {
  provider = "prisma-client"
  output   = "../generated/blog"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// 用户表
model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  // 关系字段
  profile   Profile?
  posts     Post[]
  comments  Comment[]
}

// 用户档案（一对一）
model Profile {
  id        Int     @id @default(autoincrement())
  bio       String?
  avatarUrl String?

  userId    Int     @unique
  user      User    @relation(fields: [userId], references: [id])
}

// 文章（一对多：User -> Post）
model Post {
  id        Int        @id @default(autoincrement())
  title     String
  content   String?
  published Boolean     @default(false)
  createdAt DateTime    @default(now())
  updatedAt DateTime    @updatedAt

  authorId  Int
  author    User        @relation(fields: [authorId], references: [id])

  comments  Comment[]
  categories PostCategory[]
}

// 评论（一对多：Post -> Comment；User -> Comment）
model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  createdAt DateTime @default(now())

  postId    Int
  post      Post     @relation(fields: [postId], references: [id])

  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
}

// 分类（多对多：Post <-> Category）
model Category {
  id        Int            @id @default(autoincrement())
  name      String
  slug      String         @unique

  posts     PostCategory[]
}

// 显式中间表（多对多 + 额外字段的预留空间）
model PostCategory {
  postId     Int
  categoryId Int

  assignedAt DateTime @default(now())

  post       Post     @relation(fields: [postId], references: [id])
  category   Category @relation(fields: [categoryId], references: [id])

  @@id([postId, categoryId])
}

This schema covers:

• One-to-one: User–Profile

• One-to-many: User–Post, Post–Comment, User–Comment

• Many-to-many: Post–Category (explicit intermediate table PostCategory)

Below, we break it down by relationship type.

3. One-to-one Relationship: User – Profile

3.1 One-to-one Concept and Modeling Choices

One-to-one means one user has one profile, and one profile belongs to one user. Common scenario: authentication-related fields are in User, while extended information is in Profile.

In Prisma, the syntax for one-to-one relationships is very similar to one-to-many, except that the relationship field side uses singular + non-array.

3.2 Schema Writing Breakdown

model User {
  // ...
  profile   Profile?
}

model Profile {
  id        Int     @id @default(autoincrement())
  bio       String?
  avatarUrl String?

  userId    Int     @unique
  user      User    @relation(fields: [userId], references: [id])
}

Key points:

1. On Profile, there is a foreign key field userId Int and it is marked with @unique
   This ensures that "one Profile corresponds to one User", preventing multiple profiles from binding to the same user.

2. The relationship field (object type): user User
   Uses @relation(fields: [userId], references: [id]) to specify:

• Uses this table's userId field as the foreign key;

• Corresponds to the User table's id field.

1. The relationship field on User: profile Profile?

   • Profile? indicates it's optional (some users may not have a profile yet);

   • No need to write @relation because Prisma can infer from the reverse side.

3.3 Query and Nested Write Examples

Query a user and their Profile

const userWithProfile = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    profile: true,
  },
})

• include.profile = true tells Prisma to also load the associated Profile;

• The return object will have an additional profile field.

Create a Profile while creating a User (nested write)

const user = await prisma.user.create({
  data: {
    email: 'bob@example.com',
    name: 'Bob',
    profile: {
      create: {
        bio: 'I love Prisma',
        avatarUrl: 'https://example.com/avatar.png',
      },
    },
  },
  include: { profile: true },
})

Points:

• profile: { create: { ... } } means nested creation of the associated record;

• Prisma will automatically fill in Profile.userId foreign key;

• include can directly return the newly created Profile together.

4. One-to-many Relationship: User – Post, Post – Comment

4.1 One-to-many General Pattern

The core of one-to-many is:

• The "many" side has a foreign key field (e.g., authorId, postId);

• The "one" side has an array relationship field (e.g., posts Post[], comments Comment[]);

• Use @relation(fields: [...], references: [...]) to specify the foreign key relationship.

4.2 User – Post (one user has multiple articles)

model User {
  // ...
  posts    Post[]
}

model Post {
  id        Int        @id @default(autoincrement())
  title     String
  content   String?
  published Boolean     @default(false)
  createdAt DateTime    @default(now())
  updatedAt DateTime    @updatedAt

  authorId  Int
  author    User        @relation(fields: [authorId], references: [id])

  comments  Comment[]
  categories PostCategory[]
}

Key points:

• Post has a foreign key field authorId Int;

• author User @relation(fields: [authorId], references: [id]) specifies that "Post.authorId references User.id";

• User's relationship field is posts Post[], array type indicating one user has multiple articles.

Query a user and their articles

const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
  },
})

Query articles and include the author

const posts = await prisma.post.findMany({
  include: {
    author: true,
  },
})

Create a User while creating multiple Posts (nested write)

const user = await prisma.user.create({
  data: {
    email: 'writer@example.com',
    name: 'Writer',
    posts: {
      create: [
        { title: 'First Article', content: 'Hello Prisma' },
        { title: 'Second Article', content: 'Modeling relationships is fun' },
      ],
    },
  },
  include: { posts: true },
})

Add a new article to an existing user (connect to existing relationship)

const post = await prisma.post.create({
  data: {
    title: 'New Article',
    content: 'Content...',
    author: {
      connect: { id: 1 }, // connect to existing user
    },
  },
})

This demonstrates two common operations in Prisma nested writes:

• create: create a new record;

• connect: connect to an existing record.

4.3 Post – Comment (comments on articles)

model Post {
  // ...
  comments  Comment[]
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  createdAt DateTime @default(now())

  postId    Int
  post      Post     @relation(fields: [postId], references: [id])

  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
}

Comment is connected to both Post and User (a comment belongs to an article and also to a user), so it has two foreign keys: postId and authorId.

Create a comment for an article

const comment = await prisma.comment.create({
  data: {
    content: 'Well written!',
    post: {
      connect: { id: 123 }, // connect to the article
    },
    author: {
      connect: { id: 1 },   // connect to the user
    },
  },
})

Query an article and its comments (with authors)

const postWithComments = await prisma.post.findUnique({
  where: { id: 123 },
  include: {
    comments: {
      include: {
        author: true,
      },
    },
  },
})

Here is a nested include:

• include.comments: include the comment list;

• include.comments.include.author: each comment also includes the author.

5. Many-to-many Relationship: Post – Category

5.1 Two Modeling Approaches for Many-to-many

In Prisma, there are two common ways to model many-to-many relationships:

1. Implicit many-to-many
   Only write categories Category[] and posts Post[] on the two models, and Prisma automatically creates an intermediate table for you.

2. Explicit many-to-many
   Manually create an intermediate table model, e.g., PostCategory, suitable for when the intermediate table needs extra fields (like ordering, scoring, timestamps).

For generality, this article uses the explicit intermediate table approach (more flexible).

5.2 Schema Writing: Category + PostCategory

model Category {
  id        Int            @id @default(autoincrement())
  name      String
  slug      String         @unique

  posts     PostCategory[]
}

model PostCategory {
  postId     Int
  categoryId Int

  assignedAt DateTime @default(now())

  post       Post     @relation(fields: [postId], references: [id])
  category   Category @relation(fields: [categoryId], references: [id])

  @@id([postId, categoryId])
}

Key points:

• PostCategory does not have a separate auto-increment id, but uses a composite primary key @@id([postId, categoryId]);

• assignedAt is an extra field on the intermediate table (records when the relationship was created);

• PostCategory.post and PostCategory.category relate to Post and Category through the foreign key fields postId and categoryId respectively;

• Category has posts PostCategory[] as a reverse relationship field, and Post also has categories PostCategory[] earlier.

5.3 Adding Categories to an Article

1) First create categories

const tech = await prisma.category.create({
  data: {
    name: 'Technology',
    slug: 'tech',
  },
})
const life = await prisma.category.create({
  data: {
    name: 'Life',
    slug: 'life',
  },
})

2) Attach an article to multiple categories

const postCategoryLinks = await prisma.postCategory.createMany({
  data: [
    { postId: 123, categoryId: tech.id },
    { postId: 123, categoryId: life.id },
  ],
  skipDuplicates: true,
})

5.4 Query: Article with its Categories / Articles under a Category

Query an article with categories

const post = await prisma.post.findUnique({
  where: { id: 123 },
  include: {
    categories: {
      include: {
        category: true,
      },
    },
  },
})

The returned structure is like:

• post.categories is PostCategory[];

• Each PostCategory also contains a category object (the actual Category information).

In real projects, if you find this too verbose, you can add a mapping layer in the application to transform PostCategory into a simpler { id, name, slug } type for the frontend.

Query all articles under a category

const categoryWithPosts = await prisma.category.findUnique({
  where: { slug: 'tech' },
  include: {
    posts: {
      include: {
        post: true,
      },
    },
  },
})

Similarly, here it's Category.posts (PostCategory[]), and each element contains a post field.

6. Key Concepts in Relational Queries: include / select / relation filter

6.1 include vs select: What data do you want? How many fields to get?

• include: at the relationship dimension, "bring more tables", control "whether to include a certain association";

• select: at the field dimension, "take fewer columns", control "only which fields to get".

Example: when querying articles, only get the author's name, not all fields of the author:

const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    author: {
      select: {
        id: true,
        name: true,
      },
    },
  },
})

Conversely, if you just want to bring in several associations without filtering fields, simply using include: { author: true, comments: true } is sufficient.

6.2 relation filter: Filtering by relationship conditions

Prisma Client supports filtering based on relationship conditions, such as: "find users who have published articles", "find articles with more than 3 comments", etc.

For example: find users who have at least one published article:

const usersWithPublished = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        published: true,
      },
    },
  },
})

Here posts.some is a typical relation filter usage:

• some: at least one associated record satisfies the condition;

• There are also none (no associated record satisfies the condition), every (all associated records satisfy the condition) and other variants.

Another example: find articles with no comments (articles that haven't been discussed):

const postsWithoutComments = await prisma.post.findMany({
  where: {
    comments: {
      none: {}, // as long as there are no comments
    },
  },
})

7. Common Patterns of Nested Writes

In actual code, you rarely manually step through "insert User first, then insert Post, then manually write the foreign key". Instead, you more often use Prisma's nested write capabilities:

Common patterns:

• create: create child records in the parent record;

• connect: connect to existing records;

• connectOrCreate: if it exists, connect; otherwise create;

• update / delete / set / disconnect etc. for relationship updates.

Typical example: register a user while creating a Profile and posting a welcome article.

const user = await prisma.user.create({
  data: {
    email: 'newuser@example.com',
    name: 'Newbie',
    profile: {
      create: {
        bio: 'New here',
      },
    },
    posts: {
      create: {
        title: 'Hello Blog',
        content: 'My first blog post',
        published: true,
      },
    },
  },
  include: {
    profile: true,
    posts: true,
  },
})

One API request, one transaction, multiple tables are written together, and the Client returns complete nested data.

8. After This Article, You Can Build a Decent Relationship Model for Any "Small Business"

By now, you have mastered several key techniques in Prisma relationship modeling:

• One-to-one: expressed through a single-side @unique foreign key + bidirectional relationship fields;

• One-to-many: foreign key on the "many" side, array relationship field on the "one" side;

• Many-to-many: model with an explicit intermediate table, leaving room for "the relationship itself";

• Nested writes: create / connect etc. reduce manual foreign key writing;

• Relational queries: include / select / relation filter (some / none / every).