Sequelize 实战项目 - 构建完整的博客系统

发布时间：2024-04-15
作者：一介布衣
标签：Sequelize, 实战项目, 博客系统, 全栈开发

前言

今天咱们来做一个完整的实战项目 - 用 Sequelize 构建一个功能完整的博客系统。说实话，通过实际项目来学习技术是最有效的方法，能让你把之前学到的知识点串联起来。

我记得刚开始学 Sequelize 的时候，看了很多教程，但总感觉缺少实战经验。后来自己动手做了几个项目，才真正理解了 ORM 在实际开发中的应用。

今天我就带大家从零开始，构建一个包含用户管理、文章发布、评论系统、标签分类等功能的完整博客系统。

项目需求分析

功能需求

用户模块：

用户注册、登录、登出
用户资料管理
密码修改
头像上传

文章模块：

文章发布、编辑、删除
文章分类管理
文章标签系统
文章状态管理（草稿、发布、归档）
文章搜索

评论模块：

文章评论
评论回复
评论管理

管理模块：

用户管理
文章管理
评论管理
数据统计

技术栈

后端：Node.js + Express + Sequelize
数据库：MySQL
认证：JWT
文件上传：Multer
API文档：Swagger
测试：Jest

数据库设计

1. 用户表（Users）

javascript

// models/User.js
const { DataTypes } = require('sequelize');
const bcrypt = require('bcryptjs');

module.exports = (sequelize) => {
  const User = sequelize.define('User', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    username: {
      type: DataTypes.STRING(50),
      allowNull: false,
      unique: true,
      validate: {
        len: [3, 50],
        isAlphanumeric: true
      }
    },
    email: {
      type: DataTypes.STRING(100),
      allowNull: false,
      unique: true,
      validate: {
        isEmail: true
      }
    },
    password: {
      type: DataTypes.STRING(255),
      allowNull: false,
      validate: {
        len: [6, 255]
      }
    },
    nickname: {
      type: DataTypes.STRING(50),
      allowNull: true
    },
    avatar: {
      type: DataTypes.STRING(500),
      allowNull: true
    },
    bio: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    role: {
      type: DataTypes.ENUM('user', 'admin'),
      defaultValue: 'user'
    },
    status: {
      type: DataTypes.ENUM('active', 'inactive', 'banned'),
      defaultValue: 'active'
    },
    lastLoginAt: {
      type: DataTypes.DATE,
      allowNull: true
    }
  }, {
    hooks: {
      beforeCreate: async (user) => {
        if (user.password) {
          user.password = await bcrypt.hash(user.password, 10);
        }
      },
      beforeUpdate: async (user) => {
        if (user.changed('password')) {
          user.password = await bcrypt.hash(user.password, 10);
        }
      }
    }
  });

  // 实例方法
  User.prototype.validatePassword = async function(password) {
    return await bcrypt.compare(password, this.password);
  };

  User.prototype.toSafeObject = function() {
    const { password, ...safeUser } = this.toJSON();
    return safeUser;
  };

  // 关联关系
  User.associate = (models) => {
    User.hasMany(models.Post, { foreignKey: 'authorId', as: 'posts' });
    User.hasMany(models.Comment, { foreignKey: 'authorId', as: 'comments' });
  };

  return User;
};

2. 分类表（Categories）

javascript

// models/Category.js
module.exports = (sequelize, DataTypes) => {
  const Category = sequelize.define('Category', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING(50),
      allowNull: false,
      unique: true
    },
    slug: {
      type: DataTypes.STRING(50),
      allowNull: false,
      unique: true
    },
    description: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    color: {
      type: DataTypes.STRING(7),
      defaultValue: '#007bff'
    },
    sortOrder: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    }
  });

  Category.associate = (models) => {
    Category.hasMany(models.Post, { foreignKey: 'categoryId', as: 'posts' });
  };

  return Category;
};

3. 标签表（Tags）

javascript

// models/Tag.js
module.exports = (sequelize, DataTypes) => {
  const Tag = sequelize.define('Tag', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING(30),
      allowNull: false,
      unique: true
    },
    slug: {
      type: DataTypes.STRING(30),
      allowNull: false,
      unique: true
    },
    color: {
      type: DataTypes.STRING(7),
      defaultValue: '#6c757d'
    }
  });

  Tag.associate = (models) => {
    Tag.belongsToMany(models.Post, {
      through: 'PostTags',
      foreignKey: 'tagId',
      otherKey: 'postId',
      as: 'posts'
    });
  };

  return Tag;
};

4. 文章表（Posts）

javascript

// models/Post.js
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define('Post', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    title: {
      type: DataTypes.STRING(200),
      allowNull: false,
      validate: {
        len: [5, 200]
      }
    },
    slug: {
      type: DataTypes.STRING(200),
      allowNull: false,
      unique: true
    },
    excerpt: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    content: {
      type: DataTypes.TEXT('long'),
      allowNull: false
    },
    featuredImage: {
      type: DataTypes.STRING(500),
      allowNull: true
    },
    status: {
      type: DataTypes.ENUM('draft', 'published', 'archived'),
      defaultValue: 'draft'
    },
    publishedAt: {
      type: DataTypes.DATE,
      allowNull: true
    },
    viewCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    likeCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    commentCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    authorId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Users',
        key: 'id'
      }
    },
    categoryId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Categories',
        key: 'id'
      }
    }
  }, {
    hooks: {
      beforeCreate: (post) => {
        if (post.status === 'published' && !post.publishedAt) {
          post.publishedAt = new Date();
        }
      },
      beforeUpdate: (post) => {
        if (post.changed('status') && post.status === 'published' && !post.publishedAt) {
          post.publishedAt = new Date();
        }
      }
    }
  });

  // 实例方法
  Post.prototype.incrementViewCount = async function() {
    await this.increment('viewCount');
  };

  Post.prototype.isPublished = function() {
    return this.status === 'published';
  };

  // 关联关系
  Post.associate = (models) => {
    Post.belongsTo(models.User, { foreignKey: 'authorId', as: 'author' });
    Post.belongsTo(models.Category, { foreignKey: 'categoryId', as: 'category' });
    Post.belongsToMany(models.Tag, {
      through: 'PostTags',
      foreignKey: 'postId',
      otherKey: 'tagId',
      as: 'tags'
    });
    Post.hasMany(models.Comment, { foreignKey: 'postId', as: 'comments' });
  };

  return Post;
};

5. 评论表（Comments）

javascript

// models/Comment.js
module.exports = (sequelize, DataTypes) => {
  const Comment = sequelize.define('Comment', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    content: {
      type: DataTypes.TEXT,
      allowNull: false,
      validate: {
        len: [1, 1000]
      }
    },
    status: {
      type: DataTypes.ENUM('pending', 'approved', 'rejected'),
      defaultValue: 'pending'
    },
    authorId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Users',
        key: 'id'
      }
    },
    postId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Posts',
        key: 'id'
      }
    },
    parentId: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'Comments',
        key: 'id'
      }
    }
  });

  Comment.associate = (models) => {
    Comment.belongsTo(models.User, { foreignKey: 'authorId', as: 'author' });
    Comment.belongsTo(models.Post, { foreignKey: 'postId', as: 'post' });
    Comment.belongsTo(models.Comment, { foreignKey: 'parentId', as: 'parent' });
    Comment.hasMany(models.Comment, { foreignKey: 'parentId', as: 'replies' });
  };

  return Comment;
};

核心服务实现

1. 用户服务

javascript

// services/UserService.js
const { User } = require('../models');
const jwt = require('jsonwebtoken');
const { ValidationError, NotFoundError } = require('../utils/errors');

class UserService {
  static async register(userData) {
    const { username, email, password, nickname } = userData;

    // 检查用户名和邮箱是否已存在
    const existingUser = await User.findOne({
      where: {
        [Op.or]: [{ username }, { email }]
      }
    });

    if (existingUser) {
      if (existingUser.username === username) {
        throw new ValidationError('用户名已存在');
      }
      if (existingUser.email === email) {
        throw new ValidationError('邮箱已被注册');
      }
    }

    const user = await User.create({
      username,
      email,
      password,
      nickname: nickname || username
    });

    return {
      user: user.toSafeObject(),
      token: this.generateToken(user.id)
    };
  }

  static async login(email, password) {
    const user = await User.findOne({ where: { email } });

    if (!user) {
      throw new NotFoundError('用户不存在');
    }

    if (user.status !== 'active') {
      throw new ValidationError('账户已被禁用');
    }

    const isValidPassword = await user.validatePassword(password);
    if (!isValidPassword) {
      throw new ValidationError('密码错误');
    }

    // 更新最后登录时间
    await user.update({ lastLoginAt: new Date() });

    return {
      user: user.toSafeObject(),
      token: this.generateToken(user.id)
    };
  }

  static async getUserProfile(userId) {
    const user = await User.findByPk(userId, {
      attributes: { exclude: ['password'] },
      include: [
        {
          model: Post,
          as: 'posts',
          where: { status: 'published' },
          required: false,
          attributes: ['id', 'title', 'slug', 'publishedAt'],
          limit: 5,
          order: [['publishedAt', 'DESC']]
        }
      ]
    });

    if (!user) {
      throw new NotFoundError('用户不存在');
    }

    return user;
  }

  static async updateProfile(userId, updateData) {
    const user = await User.findByPk(userId);
    if (!user) {
      throw new NotFoundError('用户不存在');
    }

    const allowedFields = ['nickname', 'bio', 'avatar'];
    const filteredData = {};
    
    allowedFields.forEach(field => {
      if (updateData[field] !== undefined) {
        filteredData[field] = updateData[field];
      }
    });

    await user.update(filteredData);
    return user.toSafeObject();
  }

  static generateToken(userId) {
    return jwt.sign(
      { userId },
      process.env.JWT_SECRET,
      { expiresIn: '7d' }
    );
  }

  static verifyToken(token) {
    try {
      return jwt.verify(token, process.env.JWT_SECRET);
    } catch (error) {
      throw new ValidationError('无效的令牌');
    }
  }
}

module.exports = UserService;

2. 文章服务

javascript

// services/PostService.js
const { Post, User, Category, Tag, Comment } = require('../models');
const { Op } = require('sequelize');
const slugify = require('slugify');

class PostService {
  static async createPost(authorId, postData) {
    const { title, content, excerpt, categoryId, tags, status = 'draft' } = postData;

    // 生成 slug
    const slug = await this.generateUniqueSlug(title);

    const post = await Post.create({
      title,
      slug,
      content,
      excerpt: excerpt || this.generateExcerpt(content),
      categoryId,
      authorId,
      status
    });

    // 处理标签
    if (tags && tags.length > 0) {
      await this.attachTags(post.id, tags);
    }

    return await this.getPostById(post.id);
  }

  static async updatePost(postId, authorId, updateData) {
    const post = await Post.findOne({
      where: { id: postId, authorId }
    });

    if (!post) {
      throw new NotFoundError('文章不存在或无权限');
    }

    const { title, content, excerpt, categoryId, tags, status } = updateData;
    const updateFields = {};

    if (title && title !== post.title) {
      updateFields.title = title;
      updateFields.slug = await this.generateUniqueSlug(title, postId);
    }

    if (content) updateFields.content = content;
    if (excerpt) updateFields.excerpt = excerpt;
    if (categoryId) updateFields.categoryId = categoryId;
    if (status) updateFields.status = status;

    await post.update(updateFields);

    // 更新标签
    if (tags) {
      await this.updateTags(postId, tags);
    }

    return await this.getPostById(postId);
  }

  static async getPostById(postId, includePrivate = false) {
    const whereClause = { id: postId };
    if (!includePrivate) {
      whereClause.status = 'published';
    }

    const post = await Post.findOne({
      where: whereClause,
      include: [
        {
          model: User,
          as: 'author',
          attributes: ['id', 'username', 'nickname', 'avatar']
        },
        {
          model: Category,
          as: 'category',
          attributes: ['id', 'name', 'slug', 'color']
        },
        {
          model: Tag,
          as: 'tags',
          attributes: ['id', 'name', 'slug', 'color'],
          through: { attributes: [] }
        }
      ]
    });

    if (!post) {
      throw new NotFoundError('文章不存在');
    }

    // 增加浏览量（只对已发布的文章）
    if (post.status === 'published') {
      await post.incrementViewCount();
    }

    return post;
  }

  static async getPostsList(options = {}) {
    const {
      page = 1,
      limit = 10,
      categoryId,
      tagId,
      authorId,
      search,
      status = 'published'
    } = options;

    const offset = (page - 1) * limit;
    const whereClause = { status };

    // 分类筛选
    if (categoryId) {
      whereClause.categoryId = categoryId;
    }

    // 作者筛选
    if (authorId) {
      whereClause.authorId = authorId;
    }

    // 搜索
    if (search) {
      whereClause[Op.or] = [
        { title: { [Op.like]: `%\${search}%` } },
        { content: { [Op.like]: `%\${search}%` } }
      ];
    }

    const includeClause = [
      {
        model: User,
        as: 'author',
        attributes: ['id', 'username', 'nickname', 'avatar']
      },
      {
        model: Category,
        as: 'category',
        attributes: ['id', 'name', 'slug', 'color']
      },
      {
        model: Tag,
        as: 'tags',
        attributes: ['id', 'name', 'slug', 'color'],
        through: { attributes: [] }
      }
    ];

    // 标签筛选
    if (tagId) {
      includeClause[2].where = { id: tagId };
      includeClause[2].required = true;
    }

    const { count, rows } = await Post.findAndCountAll({
      where: whereClause,
      include: includeClause,
      order: [['publishedAt', 'DESC']],
      limit,
      offset,
      distinct: true
    });

    return {
      posts: rows,
      pagination: {
        total: count,
        page,
        limit,
        pages: Math.ceil(count / limit)
      }
    };
  }

  static async deletePost(postId, authorId) {
    const post = await Post.findOne({
      where: { id: postId, authorId }
    });

    if (!post) {
      throw new NotFoundError('文章不存在或无权限');
    }

    await post.destroy();
    return true;
  }

  // 辅助方法
  static async generateUniqueSlug(title, excludeId = null) {
    let baseSlug = slugify(title, { lower: true, strict: true });
    let slug = baseSlug;
    let counter = 1;

    while (true) {
      const whereClause = { slug };
      if (excludeId) {
        whereClause.id = { [Op.ne]: excludeId };
      }

      const existingPost = await Post.findOne({ where: whereClause });
      if (!existingPost) {
        break;
      }

      slug = `\${baseSlug}-\${counter}`;
      counter++;
    }

    return slug;
  }

  static generateExcerpt(content, length = 200) {
    // 移除 HTML 标签
    const plainText = content.replace(/&lt;[^&gt;]*>/g, '');
    return plainText.length > length 
      ? plainText.substring(0, length) + '...'
      : plainText;
  }

  static async attachTags(postId, tagNames) {
    const tags = await Promise.all(
      tagNames.map(async (name) => {
        const [tag] = await Tag.findOrCreate({
          where: { name },
          defaults: {
            name,
            slug: slugify(name, { lower: true, strict: true })
          }
        });
        return tag;
      })
    );

    const post = await Post.findByPk(postId);
    await post.setTags(tags);
  }

  static async updateTags(postId, tagNames) {
    await this.attachTags(postId, tagNames);
  }
}

module.exports = PostService;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248

3. 评论服务

javascript

// services/CommentService.js
const { Comment, User, Post } = require('../models');
const { NotFoundError, ValidationError } = require('../utils/errors');

class CommentService {
  static async createComment(authorId, commentData) {
    const { content, postId, parentId } = commentData;

    // 验证文章是否存在
    const post = await Post.findByPk(postId);
    if (!post || post.status !== 'published') {
      throw new NotFoundError('文章不存在或未发布');
    }

    // 验证父评论是否存在
    if (parentId) {
      const parentComment = await Comment.findByPk(parentId);
      if (!parentComment || parentComment.postId !== postId) {
        throw new ValidationError('父评论不存在');
      }
    }

    const comment = await Comment.create({
      content,
      postId,
      parentId,
      authorId,
      status: 'approved' // 可以根据需要设置为 pending
    });

    // 更新文章评论数
    await post.increment('commentCount');

    return await this.getCommentById(comment.id);
  }

  static async getCommentById(commentId) {
    const comment = await Comment.findByPk(commentId, {
      include: [
        {
          model: User,
          as: 'author',
          attributes: ['id', 'username', 'nickname', 'avatar']
        }
      ]
    });

    if (!comment) {
      throw new NotFoundError('评论不存在');
    }

    return comment;
  }

  static async getPostComments(postId, options = {}) {
    const { page = 1, limit = 20 } = options;
    const offset = (page - 1) * limit;

    // 获取顶级评论
    const { count, rows } = await Comment.findAndCountAll({
      where: {
        postId,
        parentId: null,
        status: 'approved'
      },
      include: [
        {
          model: User,
          as: 'author',
          attributes: ['id', 'username', 'nickname', 'avatar']
        },
        {
          model: Comment,
          as: 'replies',
          include: [
            {
              model: User,
              as: 'author',
              attributes: ['id', 'username', 'nickname', 'avatar']
            }
          ],
          where: { status: 'approved' },
          required: false,
          order: [['createdAt', 'ASC']]
        }
      ],
      order: [['createdAt', 'DESC']],
      limit,
      offset
    });

    return {
      comments: rows,
      pagination: {
        total: count,
        page,
        limit,
        pages: Math.ceil(count / limit)
      }
    };
  }

  static async updateComment(commentId, authorId, updateData) {
    const comment = await Comment.findOne({
      where: { id: commentId, authorId }
    });

    if (!comment) {
      throw new NotFoundError('评论不存在或无权限');
    }

    await comment.update(updateData);
    return await this.getCommentById(commentId);
  }

  static async deleteComment(commentId, authorId) {
    const comment = await Comment.findOne({
      where: { id: commentId, authorId }
    });

    if (!comment) {
      throw new NotFoundError('评论不存在或无权限');
    }

    // 删除评论及其回复
    await Comment.destroy({
      where: {
        [Op.or]: [
          { id: commentId },
          { parentId: commentId }
        ]
      }
    });

    // 更新文章评论数
    const post = await Post.findByPk(comment.postId);
    if (post) {
      const commentCount = await Comment.count({
        where: { postId: comment.postId, status: 'approved' }
      });
      await post.update({ commentCount });
    }

    return true;
  }
}

module.exports = CommentService;

API 路由实现

1. 认证中间件

javascript

// middleware/auth.js
const jwt = require('jsonwebtoken');
const { User } = require('../models');

const authenticateToken = async (req, res, next) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];

    if (!token) {
      return res.status(401).json({
        success: false,
        message: '访问令牌缺失'
      });
    }

    const decoded = jwt.verify(token, process.env.JWT_SECRET);
    const user = await User.findByPk(decoded.userId);

    if (!user || user.status !== 'active') {
      return res.status(401).json({
        success: false,
        message: '无效的访问令牌'
      });
    }

    req.user = user;
    next();
  } catch (error) {
    return res.status(401).json({
      success: false,
      message: '无效的访问令牌'
    });
  }
};

const requireAdmin = (req, res, next) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({
      success: false,
      message: '需要管理员权限'
    });
  }
  next();
};

module.exports = {
  authenticateToken,
  requireAdmin
};

2. 用户路由

javascript

// routes/auth.js
const express = require('express');
const router = express.Router();
const UserService = require('../services/UserService');
const { authenticateToken } = require('../middleware/auth');
const { validateRequest } = require('../middleware/validation');
const { body } = require('express-validator');

// 注册
router.post('/register', [
  body('username').isLength({ min: 3, max: 50 }).isAlphanumeric(),
  body('email').isEmail(),
  body('password').isLength({ min: 6 }),
  validateRequest
], async (req, res) => {
  try {
    const result = await UserService.register(req.body);
    res.status(201).json({
      success: true,
      data: result
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      message: error.message
    });
  }
});

// 登录
router.post('/login', [
  body('email').isEmail(),
  body('password').notEmpty(),
  validateRequest
], async (req, res) => {
  try {
    const { email, password } = req.body;
    const result = await UserService.login(email, password);
    res.json({
      success: true,
      data: result
    });
  } catch (error) {
    res.status(401).json({
      success: false,
      message: error.message
    });
  }
});

// 获取用户资料
router.get('/profile', authenticateToken, async (req, res) => {
  try {
    const user = await UserService.getUserProfile(req.user.id);
    res.json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(404).json({
      success: false,
      message: error.message
    });
  }
});

// 更新用户资料
router.put('/profile', authenticateToken, [
  body('nickname').optional().isLength({ min: 1, max: 50 }),
  body('bio').optional().isLength({ max: 500 }),
  validateRequest
], async (req, res) => {
  try {
    const user = await UserService.updateProfile(req.user.id, req.body);
    res.json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      message: error.message
    });
  }
});

module.exports = router;

3. 文章路由

javascript

// routes/posts.js
const express = require('express');
const router = express.Router();
const PostService = require('../services/PostService');
const { authenticateToken } = require('../middleware/auth');
const { validateRequest } = require('../middleware/validation');
const { body, query } = require('express-validator');

// 获取文章列表
router.get('/', [
  query('page').optional().isInt({ min: 1 }),
  query('limit').optional().isInt({ min: 1, max: 50 }),
  query('categoryId').optional().isInt(),
  query('tagId').optional().isInt(),
  query('search').optional().isLength({ max: 100 }),
  validateRequest
], async (req, res) => {
  try {
    const result = await PostService.getPostsList(req.query);
    res.json({
      success: true,
      data: result
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// 获取单篇文章
router.get('/:id', async (req, res) => {
  try {
    const post = await PostService.getPostById(req.params.id);
    res.json({
      success: true,
      data: post
    });
  } catch (error) {
    res.status(404).json({
      success: false,
      message: error.message
    });
  }
});

// 创建文章
router.post('/', authenticateToken, [
  body('title').isLength({ min: 5, max: 200 }),
  body('content').notEmpty(),
  body('categoryId').isInt(),
  body('tags').optional().isArray(),
  body('status').optional().isIn(['draft', 'published']),
  validateRequest
], async (req, res) => {
  try {
    const post = await PostService.createPost(req.user.id, req.body);
    res.status(201).json({
      success: true,
      data: post
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      message: error.message
    });
  }
});

// 更新文章
router.put('/:id', authenticateToken, [
  body('title').optional().isLength({ min: 5, max: 200 }),
  body('content').optional().notEmpty(),
  body('categoryId').optional().isInt(),
  body('tags').optional().isArray(),
  body('status').optional().isIn(['draft', 'published', 'archived']),
  validateRequest
], async (req, res) => {
  try {
    const post = await PostService.updatePost(req.params.id, req.user.id, req.body);
    res.json({
      success: true,
      data: post
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      message: error.message
    });
  }
});

// 删除文章
router.delete('/:id', authenticateToken, async (req, res) => {
  try {
    await PostService.deletePost(req.params.id, req.user.id);
    res.json({
      success: true,
      message: '文章删除成功'
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      message: error.message
    });
  }
});

module.exports = router;

总结

今天我们构建了一个完整的博客系统，涵盖了：

✅ 完整的数据库设计和模型关联
✅ 用户认证和权限管理
✅ 文章发布和管理系统
✅ 评论系统实现
✅ RESTful API 设计
✅ 服务层架构设计

通过这个实战项目，你可以学到：

如何设计复杂的数据库关联关系
如何构建可维护的服务层架构
如何实现完整的用户认证系统
如何处理复杂的业务逻辑
如何设计 RESTful API

这个博客系统包含了大部分 Web 应用的核心功能，是学习 Sequelize 和 Node.js 开发的绝佳实践项目！

相关文章推荐：

有问题欢迎留言讨论，我会及时回复大家！

🧭 快速导航

Sequelize 实战项目 - 构建完整的博客系统 ​

前言 ​

项目需求分析 ​

功能需求 ​

技术栈 ​

数据库设计 ​

1. 用户表（Users） ​

2. 分类表（Categories） ​

3. 标签表（Tags） ​

4. 文章表（Posts） ​

5. 评论表（Comments） ​

核心服务实现 ​

1. 用户服务 ​

2. 文章服务 ​

3. 评论服务 ​

API 路由实现 ​

1. 认证中间件 ​

2. 用户路由 ​

3. 文章路由 ​

总结 ​

Sequelize 实战项目 - 构建完整的博客系统

前言

项目需求分析

功能需求

技术栈

数据库设计

1. 用户表（Users）

2. 分类表（Categories）

3. 标签表（Tags）

4. 文章表（Posts）

5. 评论表（Comments）

核心服务实现

1. 用户服务

2. 文章服务

3. 评论服务

API 路由实现

1. 认证中间件

2. 用户路由

3. 文章路由

总结