Feathers.js + Sequelize 基础集成 - 关系型数据库的完美搭档

发布时间：2024-07-20
作者：一介布衣
标签：Feathers.js, Sequelize, ORM, 关系型数据库, 集成

前言

在前面的 Feathers.js 系列文章中，我们学习了如何使用 Knex.js 操作 SQL 数据库。今天咱们来学习另一个强大的选择 - Sequelize ORM。说实话，Sequelize 是 Node.js 生态中最成熟的 ORM 之一，它提供了丰富的功能和优雅的 API。

我记得刚开始用 Sequelize 的时候，被它的功能震撼了：自动的表关联、数据验证、事务支持、迁移系统等等。而且 Feathers.js 对 Sequelize 的支持非常好，两者结合起来开发效率特别高。

今天我就带大家从零开始，学习如何在 Feathers.js 中集成和使用 Sequelize。

环境搭建

1. 安装依赖

# 安装 Feathers.js Sequelize 适配器
npm install @feathersjs/sequelize sequelize

# 安装数据库驱动（选择一个）
npm install pg pg-hstore          # PostgreSQL
npm install mysql2                # MySQL
npm install mariadb               # MariaDB
npm install sqlite3               # SQLite
npm install tedious               # SQL Server

# 安装开发工具
npm install --save-dev sequelize-cli
npm install --save-dev @types/sequelize  # TypeScript 支持

2. 初始化 Sequelize

# 初始化 Sequelize 配置
npx sequelize-cli init

# 这会创建以下目录结构：
# config/config.json    - 数据库配置
# models/               - 模型定义
# migrations/           - 数据库迁移
# seeders/             - 种子数据

3. 数据库配置

// config/config.js
module.exports = {
  development: {
    username: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'feathers_dev',
    host: process.env.DB_HOST || '127.0.0.1',
    port: process.env.DB_PORT || 5432,
    dialect: 'postgres',
    logging: console.log,
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    }
  },
  
  test: {
    username: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'feathers_test',
    host: process.env.DB_HOST || '127.0.0.1',
    port: process.env.DB_PORT || 5432,
    dialect: 'postgres',
    logging: false
  },
  
  production: {
    use_env_variable: 'DATABASE_URL',
    dialect: 'postgres',
    dialectOptions: {
      ssl: {
        require: true,
        rejectUnauthorized: false
      }
    },
    pool: {
      max: 20,
      min: 5,
      acquire: 60000,
      idle: 10000
    },
    logging: false
  }
};

Sequelize 集成配置

1. 创建 Sequelize 实例

// src/sequelize.js
const { Sequelize } = require('sequelize');
const config = require('../config/config');

const env = process.env.NODE_ENV || 'development';
const dbConfig = config[env];

let sequelize;

if (dbConfig.use_env_variable) {
  sequelize = new Sequelize(process.env[dbConfig.use_env_variable], dbConfig);
} else {
  sequelize = new Sequelize(
    dbConfig.database,
    dbConfig.username,
    dbConfig.password,
    dbConfig
  );
}

module.exports = function (app) {
  // 设置 Sequelize 实例
  app.set('sequelizeClient', sequelize);
  
  // 测试连接
  sequelize.authenticate()
    .then(() => {
      console.log('✅ Sequelize 数据库连接成功');
    })
    .catch(err => {
      console.error('❌ Sequelize 数据库连接失败:', err);
      process.exit(1);
    });
  
  // 同步数据库（开发环境）
  if (process.env.NODE_ENV === 'development') {
    sequelize.sync({ alter: true })
      .then(() => {
        console.log('📊 数据库同步完成');
      })
      .catch(err => {
        console.error('❌ 数据库同步失败:', err);
      });
  }
  
  return sequelize;
};

2. 应用配置

// src/app.js
const feathers = require('@feathersjs/feathers');
const express = require('@feathersjs/express');
const socketio = require('@feathersjs/socketio');

const sequelize = require('./sequelize');
const services = require('./services');

const app = express(feathers());

// 配置 Sequelize
app.configure(sequelize);

// 其他配置...
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.configure(express.rest());
app.configure(socketio());

// 配置服务
app.configure(services);

module.exports = app;

模型定义

1. 用户模型

// src/models/users.model.js
const { DataTypes } = require('sequelize');

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  
  const users = sequelizeClient.define('users', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    
    // 基本信息
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        isEmail: true
      }
    },
    
    username: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        len: [3, 30],
        isAlphanumeric: true
      }
    },
    
    password: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        len: [6, 255]
      }
    },
    
    // 个人资料
    firstName: {
      type: DataTypes.STRING,
      field: 'first_name',
      validate: {
        len: [1, 50]
      }
    },
    
    lastName: {
      type: DataTypes.STRING,
      field: 'last_name',
      validate: {
        len: [1, 50]
      }
    },
    
    avatar: {
      type: DataTypes.STRING,
      validate: {
        isUrl: true
      }
    },
    
    bio: {
      type: DataTypes.TEXT
    },
    
    birthDate: {
      type: DataTypes.DATEONLY,
      field: 'birth_date',
      validate: {
        isDate: true,
        isBefore: new Date().toISOString()
      }
    },
    
    // 联系信息
    phone: {
      type: DataTypes.STRING,
      validate: {
        is: /^[\+]?[1-9][\d]{0,15}$/
      }
    },
    
    website: {
      type: DataTypes.STRING,
      validate: {
        isUrl: true
      }
    },
    
    // 地址信息
    country: DataTypes.STRING,
    city: DataTypes.STRING,
    address: DataTypes.TEXT,
    postalCode: {
      type: DataTypes.STRING,
      field: 'postal_code'
    },
    
    // 系统字段
    role: {
      type: DataTypes.ENUM('user', 'moderator', 'admin'),
      defaultValue: 'user'
    },
    
    status: {
      type: DataTypes.ENUM('active', 'inactive', 'suspended'),
      defaultValue: 'active'
    },
    
    emailVerified: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      field: 'email_verified'
    },
    
    emailVerificationToken: {
      type: DataTypes.STRING,
      field: 'email_verification_token'
    },
    
    // 偏好设置（JSON 字段）
    preferences: {
      type: DataTypes.JSON,
      defaultValue: {
        theme: 'light',
        language: 'zh-CN',
        notifications: {
          email: true,
          push: true,
          sms: false
        }
      }
    },
    
    // 统计信息
    postsCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'posts_count'
    },
    
    followersCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'followers_count'
    },
    
    followingCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'following_count'
    },
    
    lastLoginAt: {
      type: DataTypes.DATE,
      field: 'last_login_at'
    }
  }, {
    // 表选项
    tableName: 'users',
    timestamps: true,
    underscored: true,
    
    // 索引
    indexes: [
      {
        unique: true,
        fields: ['email']
      },
      {
        unique: true,
        fields: ['username']
      },
      {
        fields: ['status']
      },
      {
        fields: ['role']
      },
      {
        fields: ['created_at']
      }
    ],
    
    // 作用域
    scopes: {
      active: {
        where: {
          status: 'active'
        }
      },
      
      withoutPassword: {
        attributes: {
          exclude: ['password', 'emailVerificationToken']
        }
      },
      
      publicProfile: {
        attributes: [
          'id', 'username', 'firstName', 'lastName', 
          'avatar', 'bio', 'website', 'createdAt'
        ]
      }
    },
    
    // 钩子
    hooks: {
      beforeCreate: (user, options) => {
        // 在创建前的处理
        if (user.email) {
          user.email = user.email.toLowerCase();
        }
      },
      
      beforeUpdate: (user, options) => {
        // 在更新前的处理
        if (user.changed('email')) {
          user.email = user.email.toLowerCase();
        }
      }
    }
  });

  // 实例方法
  users.prototype.toSafeObject = function() {
    const values = this.get({ plain: true });
    delete values.password;
    delete values.emailVerificationToken;
    return values;
  };

  users.prototype.getFullName = function() {
    return `\${this.firstName || ''} \${this.lastName || ''}`.trim();
  };

  users.prototype.hasRole = function(roles) {
    const roleArray = Array.isArray(roles) ? roles : [roles];
    return roleArray.includes(this.role);
  };

  // 类方法
  users.findByEmail = function(email) {
    return this.findOne({
      where: { email: email.toLowerCase() }
    });
  };

  users.findByUsername = function(username) {
    return this.findOne({
      where: { username }
    });
  };

  // 关联定义（在所有模型加载后）
  users.associate = function(models) {
    // 用户发布的文章
    users.hasMany(models.posts, {
      foreignKey: 'authorId',
      as: 'posts'
    });
    
    // 用户的评论
    users.hasMany(models.comments, {
      foreignKey: 'authorId',
      as: 'comments'
    });
    
    // 用户关注关系
    users.belongsToMany(users, {
      through: 'user_follows',
      as: 'followers',
      foreignKey: 'followingId',
      otherKey: 'followerId'
    });
    
    users.belongsToMany(users, {
      through: 'user_follows',
      as: 'following',
      foreignKey: 'followerId',
      otherKey: 'followingId'
    });
  };

  return users;
};

2. 文章模型

// src/models/posts.model.js
const { DataTypes } = require('sequelize');

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  
  const posts = sequelizeClient.define('posts', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    
    title: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        len: [1, 255]
      }
    },
    
    slug: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        is: /^[a-z0-9-]+$/
      }
    },
    
    excerpt: {
      type: DataTypes.TEXT,
      validate: {
        len: [0, 500]
      }
    },
    
    content: {
      type: DataTypes.TEXT,
      allowNull: false
    },
    
    // 关联字段
    authorId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'author_id',
      references: {
        model: 'users',
        key: 'id'
      }
    },
    
    categoryId: {
      type: DataTypes.UUID,
      field: 'category_id',
      references: {
        model: 'categories',
        key: 'id'
      }
    },
    
    // 状态
    status: {
      type: DataTypes.ENUM('draft', 'published', 'archived'),
      defaultValue: 'draft'
    },
    
    isFeatured: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      field: 'is_featured'
    },
    
    allowComments: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
      field: 'allow_comments'
    },
    
    // 媒体
    featuredImage: {
      type: DataTypes.STRING,
      field: 'featured_image',
      validate: {
        isUrl: true
      }
    },
    
    gallery: {
      type: DataTypes.JSON,
      defaultValue: []
    },
    
    // SEO
    metaTitle: {
      type: DataTypes.STRING,
      field: 'meta_title'
    },
    
    metaDescription: {
      type: DataTypes.TEXT,
      field: 'meta_description'
    },
    
    metaKeywords: {
      type: DataTypes.JSON,
      field: 'meta_keywords',
      defaultValue: []
    },
    
    // 统计
    viewCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'view_count'
    },
    
    likeCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'like_count'
    },
    
    commentCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'comment_count'
    },
    
    shareCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'share_count'
    },
    
    // 时间
    publishedAt: {
      type: DataTypes.DATE,
      field: 'published_at'
    }
  }, {
    tableName: 'posts',
    timestamps: true,
    underscored: true,
    
    indexes: [
      {
        fields: ['author_id']
      },
      {
        fields: ['category_id']
      },
      {
        fields: ['status']
      },
      {
        fields: ['published_at']
      },
      {
        unique: true,
        fields: ['slug']
      },
      {
        fields: ['is_featured']
      }
    ],
    
    scopes: {
      published: {
        where: {
          status: 'published'
        }
      },
      
      featured: {
        where: {
          isFeatured: true
        }
      },
      
      withAuthor: {
        include: [{
          model: sequelizeClient.models.users,
          as: 'author',
          attributes: ['id', 'username', 'firstName', 'lastName', 'avatar']
        }]
      }
    }
  });

  // 实例方法
  posts.prototype.getReadingTime = function() {
    const wordsPerMinute = 200;
    const wordCount = this.content.split(/\s+/).length;
    return Math.ceil(wordCount / wordsPerMinute);
  };

  posts.prototype.isPublished = function() {
    return this.status === 'published' && this.publishedAt;
  };

  // 类方法
  posts.findBySlug = function(slug) {
    return this.findOne({
      where: { slug },
      include: ['author', 'category', 'tags']
    });
  };

  posts.findPublished = function(options = {}) {
    return this.scope('published').findAll({
      order: [['publishedAt', 'DESC']],
      ...options
    });
  };

  // 关联定义
  posts.associate = function(models) {
    // 文章作者
    posts.belongsTo(models.users, {
      foreignKey: 'authorId',
      as: 'author'
    });
    
    // 文章分类
    posts.belongsTo(models.categories, {
      foreignKey: 'categoryId',
      as: 'category'
    });
    
    // 文章标签（多对多）
    posts.belongsToMany(models.tags, {
      through: 'post_tags',
      foreignKey: 'postId',
      otherKey: 'tagId',
      as: 'tags'
    });
    
    // 文章评论
    posts.hasMany(models.comments, {
      foreignKey: 'postId',
      as: 'comments'
    });
  };

  return posts;
};

服务实现

1. 用户服务

// src/services/users/users.class.js
const { Service } = require('feathers-sequelize');

class UsersService extends Service {
  constructor(options, app) {
    super(options, app);
    this.app = app;
  }

  async find(params) {
    const { query = {} } = params;
    
    // 构建查询选项
    const sequelizeOptions = {
      include: [],
      where: {},
      order: [['createdAt', 'DESC']]
    };

    // 搜索功能
    if (query.search) {
      const { Op } = require('sequelize');
      sequelizeOptions.where[Op.or] = [
        { username: { [Op.iLike]: `%\${query.search}%` } },
        { firstName: { [Op.iLike]: `%\${query.search}%` } },
        { lastName: { [Op.iLike]: `%\${query.search}%` } }
      ];
      delete query.search;
    }

    // 角色筛选
    if (query.role) {
      sequelizeOptions.where.role = query.role;
      delete query.role;
    }

    // 状态筛选
    if (query.status) {
      sequelizeOptions.where.status = query.status;
      delete query.status;
    }

    // 关联查询
    if (query.$populate) {
      const populate = Array.isArray(query.$populate) ? query.$populate : [query.$populate];
      
      if (populate.includes('posts')) {
        sequelizeOptions.include.push({
          model: this.app.service('posts').Model,
          as: 'posts',
          where: { status: 'published' },
          required: false
        });
      }
      
      delete query.$populate;
    }

    // 默认排除敏感字段
    if (!query.$select) {
      sequelizeOptions.attributes = {
        exclude: ['password', 'emailVerificationToken']
      };
    }

    // 合并查询参数
    params.sequelize = sequelizeOptions;

    return super.find(params);
  }

  async get(id, params) {
    const { query = {} } = params;
    
    const sequelizeOptions = {
      include: [],
      attributes: {
        exclude: ['password', 'emailVerificationToken']
      }
    };

    // 关联查询
    if (query.$populate) {
      const populate = Array.isArray(query.$populate) ? query.$populate : [query.$populate];
      
      if (populate.includes('posts')) {
        sequelizeOptions.include.push({
          model: this.app.service('posts').Model,
          as: 'posts',
          where: { status: 'published' },
          required: false,
          limit: 10,
          order: [['publishedAt', 'DESC']]
        });
      }
    }

    params.sequelize = sequelizeOptions;
    
    return super.get(id, params);
  }

  async create(data, params) {
    // 数据预处理
    const userData = {
      ...data,
      email: data.email.toLowerCase()
    };

    // 生成用户名（如果没有提供）
    if (!userData.username && userData.email) {
      userData.username = await this.generateUsername(userData.email);
    }

    const result = await super.create(userData, params);
    
    // 创建后处理
    await this.initializeUserData(result.id);
    
    return result;
  }

  async patch(id, data, params) {
    // 邮箱转小写
    if (data.email) {
      data.email = data.email.toLowerCase();
    }

    return super.patch(id, data, params);
  }

  // 生成唯一用户名
  async generateUsername(email) {
    const baseUsername = email.split('@')[0].toLowerCase();
    let username = baseUsername;
    let counter = 1;
    
    while (await this.Model.findByUsername(username)) {
      username = `\${baseUsername}\${counter}`;
      counter++;
    }
    
    return username;
  }

  // 初始化用户数据
  async initializeUserData(userId) {
    // 创建默认分类等初始化操作
    console.log(`初始化用户 \${userId} 的数据`);
  }

  // 获取用户统计
  async getUserStats(userId) {
    const user = await this.Model.findByPk(userId, {
      include: [
        {
          model: this.app.service('posts').Model,
          as: 'posts',
          attributes: []
        }
      ],
      attributes: [
        'id',
        [this.Model.sequelize.fn('COUNT', this.Model.sequelize.col('posts.id')), 'postsCount']
      ],
      group: ['users.id']
    });

    return user;
  }
}

module.exports = UsersService;

2. 服务注册

// src/services/users/users.service.js
const { createModel } = require('../../models/users.model');
const UsersService = require('./users.class');
const hooks = require('./users.hooks');

module.exports = function (app) {
  const options = {
    Model: createModel(app),
    paginate: app.get('paginate')
  };

  // 注册服务
  app.use('/users', new UsersService(options, app));

  // 获取服务实例
  const service = app.service('users');

  service.hooks(hooks);
};

总结

通过这篇文章，我们学习了 Feathers.js 与 Sequelize 的基础集成：

✅ 环境搭建：

• Sequelize 安装和配置
• 数据库连接设置
• 项目结构组织

✅ 模型定义：

• 完整的模型配置
• 数据验证和约束
• 实例和类方法

✅ 服务实现：

• Sequelize 服务类扩展
• 复杂查询处理
• 关联数据加载

✅ 最佳实践：

• 代码组织结构
• 错误处理机制
• 性能优化技巧

掌握了这些基础知识，你就能够在 Feathers.js 中高效地使用 Sequelize ORM 了。

下一篇文章，我们将深入学习模型关联和复杂查询。

---

相关文章推荐：

• Feathers.js + SQL 数据库实战 - 关系型数据库的最佳实践
• Feathers.js + Sequelize 模型设计与关联 - 构建复杂的数据关系

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