Part 5: Building a Complete Business Workflow with blog-demo (Advanced Prisma Practices)

In the previous article, we designed a set of relational models for blog-demo: User / Profile / Post / Comment / Category. It's already up and running, but it's still at the "structural level".

In this section, I want to take a different approach:
Instead of explaining by "concepts," I'll walk through a complete "business flow."

Let's assume we have the following requirement:

After a user registers, a profile is automatically generated;
On the first login, guide them to write the first blog post;
Posts can select categories;
Later, on the personal homepage, show: basic info + profile + recent posts + comment count for each post.

Below, we'll use Prisma to implement this requirement step by step.

1. Preparation: What Do We Have?

Let's first review the core data structure of blog-demo (only key fields are kept):

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])
}

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[]
}

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])
}

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])
}

With these tables, we can start organizing code from a "business process" perspective.

All TypeScript code below assumes you already have:

import { PrismaClient } from '../generated/blog'

const prisma = new PrismaClient()

2. User Registration: Write User + Profile in One Go

The first step is registration.
Many projects automatically create a profile record for the user after successful registration, even if the content is initially empty.

This requirement is a perfect fit for Prisma's nested writes: the official documentation emphasizes that nested writes are completed within a single transaction, and if any step fails, the entire operation rolls back.

2.1 The Simplest Implementation

Let's first write a minimal version of the registration function:

async function registerUser(input: {
  email: string
  name: string
}) {
  const user = await prisma.user.create({
    data: {
      email: input.email,
      name: input.name,
      profile: {
        create: {
          bio: '',
          avatarUrl: null,
        },
      },
    },
    include: {
      profile: true,
    },
  })

  return user
}

This code does three things:

1. Insert a new user into the User table.

2. Insert a profile record into the Profile table, with the userId foreign key automatically filled by Prisma.

3. Include the profile field in the return value.

You don't need to manage userId yourself — that's the benefit of nested writes.

2.2 Add a Bit of Robustness: Avoid Duplicate Registration

In a real project, the email is usually required to be unique. We already wrote @unique in the schema, which enforces this constraint at the database level. If you try to insert a duplicate email, Prisma will throw an error (code P2002).

We can wrap it a bit:

async function safeRegisterUser(input: { email: string; name: string }) {
  try {
    return await registerUser(input)
  } catch (e: any) {
    // Simplified: in a real project, use a more detailed error mapping
    if (e.code === 'P2002') {
      throw new Error('邮箱已被注册')
    }
    throw e
  }
}

At this point, we have completed the first chain: "Registration + Auto-created Profile".

3. First Login: Create a Welcome Post + Select Categories

Second step: When the user logs in for the first time, we want to guide them to write their first blog post.

There are generally two ways to implement this flow:

1. The frontend first lets them fill in a form, then sends everything to the backend at once;

2. The backend auto-generates a "welcome post" draft, which the user can edit later.

Here, we'll write the most common case:
"On the first login, the user directly submits a title / content / category selection, and the backend writes the Post + associated Category in one go."

3.1 Prepare Categories: Initial Category Data

Before writing the actual logic, we usually pre-seed some categories, e.g., "Tech" and "Life". This can be done with a script or executed once at startup.

async function seedCategories() {
  await prisma.category.createMany({
    data: [
      { name: '技术', slug: 'tech' },
      { name: '生活', slug: 'life' },
      { name: '随笔', slug: 'essay' },
    ],
    skipDuplicates: true,
  })
}

skipDuplicates: true prevents errors when running repeatedly.

3.2 Write a Post with Categories: Wrap in a Transaction

Now the user submits a form with:

• Title: title

• Content: content

• List of category slugs: ['tech', 'life']

We want:

1. Create a new post;

2. Find the corresponding Categories;

3. Insert association records into PostCategory.

This can be written as a transaction ($transaction): Prisma documentation recommends wrapping multi-step write operations in a transaction to avoid partial success or failure.

async function createFirstPostForUser(params: {
  userId: number
  title: string
  content: string
  categorySlugs: string[]
}) {
  return await prisma.$transaction(async (tx) => {
    // 1. Create the post
    const post = await tx.post.create({
      data: {
        title: params.title,
        content: params.content,
        published: true,
        author: {
          connect: { id: params.userId },
        },
      },
    })

    // 2. Find the categories the user selected
    const categories = await tx.category.findMany({
      where: { slug: { in: params.categorySlugs } },
    })

    // 3. Associate the post with these categories
    if (categories.length > 0) {
      await tx.postCategory.createMany({
        data: categories.map((c) => ({
          postId: post.id,
          categoryId: c.id,
        })),
        skipDuplicates: true,
      })
    }

    // 4. Finally, return the post with categories
    const postWithCategories = await tx.post.findUnique({
      where: { id: post.id },
      include: {
        categories: {
          include: { category: true },
        },
      },
    })

    return postWithCategories
  })
}

There are a few points worth noting here:

• We used prisma.$transaction(async (tx) => { ... }), and all tx.xxx calls within it are submitted as a single transaction;

• postCategory.createMany inherently has transactional semantics (bulk operation), and wrapping it with $transaction ensures overall consistency for the "post + category association";

• Finally, we re-read the post with categories to return.

At this point, the second chain is complete:on the first login, the user can write their first blog post and select categories in one go.

4. Personal Homepage: Retrieve All Key Information in One Query

Third step: Personal homepage.
The requirement is:

Given a user ID, return the following "personal homepage data":

• Basic user info (name / email)

• Profile (bio / avatar)

• Recent N published posts (title + creation time)

• Comment count for each post

This requirement can be approached in two ways:

1. Accomplish with a single Prisma query (nested include + aggregation);

2. Split into multiple queries and assemble in the programming language.

Let's first try the "one-shot" approach, then discuss why sometimes you might need to step back.

4.1 First, Write an Intuitive include

We can start with something like this:

async function getUserHomePage(userId: number, limitPosts = 5) {
  const user = await prisma.user.findUnique({
    where: { id: userId },
    select: {
      id: true,
      name: true,
      email: true,
      profile: {
        select: {
          bio: true,
          avatarUrl: true,
        },
      },
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' },
        take: limitPosts,
        select: {
          id: true,
          title: true,
          createdAt: true,
          _count: {
            select: { comments: true },
          },
        },
      },
    },
  })

  return user
}

Two concepts are used here:

1. Nested select: only fetch the needed fields for profile and posts;

2. _count aggregation: count the comments for each post;

_count.comments is Prisma's built-in support for relation count, referred to as "count relation" in the official documentation.

The resulting structure will look something like:

{
  id: 1,
  name: 'Alice',
  email: 'alice@example.com',
  profile: {
    bio: 'I love Prisma',
    avatarUrl: '...'
  },
  posts: [
    {
      id: 123,
      title: 'Hello Blog',
      createdAt: '...',
      _count: { comments: 3 }
    },
    ...
  ]
}

All the information needed for the personal homepage is there.

4.2 Why Use select Instead of include Here

The official best practices recommend: only fetch necessary fields to reduce data transfer and improve performance.

If you write:

include: { profile: true, posts: true }

Prisma will include all fields of Post and Comment, which is wasteful and may expose "internal implementation details" to the frontend.

select gives you precise control over the return format, even allowing you to craft a dedicated "view model" for the frontend.

5. Comment Feature: Safe Writing Without Redundant Queries

The comment feature seems straightforward: "add a comment under a post."
But if we think more carefully, there are a few issues to consider:

1. Does the post exist?

2. Does the user exist?

3. Should the comment be written inside a transaction (to prevent dirty data under concurrency)?

Here, we'll implement a reasonable and easy-to-understand approach.

5.1 Simple but Not-So-Safe Implementation (Demonstration Only, Not Recommended)

The simplest Prisma approach is:

async function addComment(params: {
  postId: number
  userId: number
  content: string
}) {
  return await prisma.comment.create({
    data: {
      content: params.content,
      post: {
        connect: { id: params.postId },
      },
      author: {
        connect: { id: params.userId },
      },
    },
  })
}

This code has two implicit assumptions:

• The Post definitely exists;

• The User definitely exists.

If one of the IDs is wrong, an error will occur at the database level due to foreign key constraint failure.

In some internal services, this approach is acceptable; but for an external API, you should validate upfront to provide friendlier error messages.

5.2 Use a Transaction to Include Existence Checks

We can use $transaction to write a slightly more rigorous version:

async function safeAddComment(params: {
  postId: number
  userId: number
  content: string
}) {
  return await prisma.$transaction(async (tx) => {
    const [post, user] = await Promise.all([
      tx.post.findUnique({ where: { id: params.postId }, select: { id: true } }),
      tx.user.findUnique({ where: { id: params.userId }, select: { id: true } }),
    ])

    if (!post) {
      throw new Error('文章不存在')
    }
    if (!user) {
      throw new Error('用户不存在')
    }

    return await tx.comment.create({
      data: {
        content: params.content,
        postId: params.postId,
        authorId: params.userId,
      },
    })
  })
}

There's a design choice here:

• Instead of using relational fields with connect, we directly write postId and authorId;

• This is because we already performed existence checks ourselves, and we want to avoid potential foreign key errors from connect.

This is a compromise between "safety and simplicity." You could also rely entirely on foreign key errors and catch them at a higher level to translate into business errors.

6. Some Performance and Practical Tips: Applicable Even from Small Projects

At this point, we've run through a complete path for blog-demo:

• Registration (User + Profile)

• Setting up categories (Category)

• Writing the first post and associating categories (Post + PostCategory)

• Displaying the personal homepage (User + Profile + posts + comment count)

• Comment feature (Comment + safety checks)

Finally, let's take this opportunity to share a few small tips related to Prisma practices that aren't too "theoretical." The official documentation also has a "Best practices" page you can reference.

6.1 Always Select Only the Necessary Fields

select and _count are two very important optimization tools:

• Do not return full-field models directly from external interfaces;

• Define separate query functions for "personal homepage view," "post list view," etc.;

• Each function strictly selects the fields it needs.

This way:

• Data transfer is smaller;

• Types are more precise;

• If the schema changes (fields added or removed), you won't suddenly expose many unintended fields.

6.2 If Nested Writes Can Do It, Don't Break It into Multiple SQL Statements

Prisma's nested writes are essentially a "simplified transaction": the official documentation emphasizes that they ensure multi-table write operations either all succeed or all roll back.

For operations like "create user + profile" or "create post + associate categories":

• If you use multiple independent create calls, you'd need to manually roll back if something goes wrong midway;

• With nested writes, you can accomplish it with a single statement;

• If the nested structure is too deep/complex, then consider using $transaction to break it down step by step.

6.3 One PrismaClient is Enough

The official best practices remind us: the entire application needs only one PrismaClient instance; otherwise, multiple connection pools will be created, potentially exhausting the number of connections.

• In Node/Next.js projects, encapsulate PrismaClient into lib/prisma.ts using a global singleton pattern;

• In serverless environments (Vercel), be careful not to repeatedly create new instances inside handlers.

This is an "architectural hygiene" issue; the earlier you form the habit, the better.

7. Conclusion: From "Writing CRUD" to "Writing Business Logic"

By this point, we have:

• Designed a set of relational structures using blog-demo;

• Used a concrete business flow to tie together nested writes, transactions, relation filters, _count, and select/include.

You'll notice something:

When the Schema is clearly designed, Prisma code increasingly feels like writing business logic rather than wrestling with the database.