如何在NodeJS的Sequelize模型中设置默认值（角色USER、is_active=1）

问题背景 #

已创建向数据库添加数据的控制器，需要在Sequelize中设置默认值，确保新创建的用户默认角色为USER且is_active = 1，现有代码如下：

User模型 #

module.exports = (sequelize, Sequelize) => {
  const User = sequelize.define("user", {
    user_id: {
      type: Sequelize.STRING,
      autoIncrement: true,
      primaryKey: true,
    },
    username: {
      type: Sequelize.STRING,
    },
    password: {
      type: Sequelize.STRING,
    },
    full_name: {
      type: Sequelize.STRING,
    },
    email: {
      type: Sequelize.STRING,
    },
    role_id: {
      type: Sequelize.STRING,
    },
    is_active: {
      type: Sequelize.BOOLEAN,
    },
    created_date: {
      type: Sequelize.DATE,
    },
    created_by: {
      type: Sequelize.STRING,
    },
    updated_date: {
      type: Sequelize.DATE,
    },
    updated_by: {
      type: Sequelize.STRING,
    },
  });

  return User;
};

Role模型 #

module.exports = (sequelize, Sequelize) => {
  const Role = sequelize.define("role", {
    role_id: {
      type: Sequelize.STRING,
      autoIncrement: true,
      primaryKey: true,
    },
    role_name: {
      type: Sequelize.STRING,
    },
    is_active: {
      type: Sequelize.BOOLEAN,
    },
    created_date: {
      type: Sequelize.DATE,
    },
    created_by: {
      type: Sequelize.STRING,
    },
    updated_date: {
      type: Sequelize.DATE,
    },
    updated_by: {
      type: Sequelize.STRING,
    },
  });

  return Role;
};

User控制器 #

const db = require("../models");
const User = db.users;

exports.create = (req, res) => {
    // Validate request
    if (!req.body.username) {
      res.status(400).send({
        message: "Content can not be empty!"
      });
      return;
    }
  
    // Create a Tutorial
    const user = {
        username: req.body.username,
        password: req.body.password,
        full_name: req.body.full_name,
        email: req.body.email,
        role_id: req.body.role_id,
        is_active: req.body.is_active,
        created_date: req.body.created_date,
        created_by: req.body.created_by,
        updated_date: req.body.updated_date,
        updated_by: req.body.updated_by,
    };
  
    // Save Tutorial in the database
    User.create(user)
      .then(data => {
        res.send(data);
      })
      .catch(err => {
        res.status(500).send({
          message:
            err.message || "Some error occurred while creating new User."
        });
      });
  };

解决方案 #

1. 修改User模型设置默认值 #

在Sequelize模型中，通过defaultValue属性为字段设置默认值，同时添加allowNull: false确保字段不为空：

module.exports = (sequelize, Sequelize) => {
  const User = sequelize.define("user", {
    user_id: {
      type: Sequelize.STRING,
      autoIncrement: true,
      primaryKey: true,
    },
    username: {
      type: Sequelize.STRING,
      allowNull: false // 建议添加，确保用户名必填
    },
    password: {
      type: Sequelize.STRING,
      allowNull: false // 建议添加，确保密码必填
    },
    full_name: {
      type: Sequelize.STRING,
    },
    email: {
      type: Sequelize.STRING,
    },
    role_id: {
      type: Sequelize.STRING,
      allowNull: false,
      defaultValue: 'USER' // 假设Role表中USER角色的role_id为'USER'，如果是自增ID则替换为对应数值（比如1）
    },
    is_active: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: true // 布尔值true对应数据库中的1
    },
    created_date: {
      type: Sequelize.DATE,
      defaultValue: Sequelize.NOW // 可选：自动设置创建时间为当前时间
    },
    created_by: {
      type: Sequelize.STRING,
    },
    updated_date: {
      type: Sequelize.DATE,
      defaultValue: Sequelize.NOW,
      onUpdate: Sequelize.NOW // 可选：更新时自动刷新时间
    },
    updated_by: {
      type: Sequelize.STRING,
    },
  });

  return User;
};

注意：如果Role表中的USER角色是自增ID（比如role_id为1），则将defaultValue改为对应的数值，同时需要确保Role表中已预先添加USER角色数据。

2. 优化User控制器 #

模型设置默认值后，创建用户时无需手动传入role_id和is_active，控制器可简化为：

const db = require("../models");
const User = db.users;

exports.create = (req, res) => {
    // Validate request
    if (!req.body.username || !req.body.password) { // 建议同时校验密码
      res.status(400).send({
        message: "用户名和密码不能为空!"
      });
      return;
    }
  
    // 创建用户对象，模型会自动填充默认值
    const user = {
        username: req.body.username,
        password: req.body.password,
        full_name: req.body.full_name,
        email: req.body.email,
        created_by: req.body.created_by,
        updated_by: req.body.updated_by,
    };
  
    // 保存用户到数据库
    User.create(user)
      .then(data => {
        res.send(data);
      })
      .catch(err => {
        res.status(500).send({
          message:
            err.message || "创建用户时发生错误。"
        });
      });
  };

如果需要强制用户角色只能是USER（即使前端传入其他值也覆盖），可在控制器中手动固定值：

const user = {
    // ...其他字段
    role_id: 'USER', // 强制设置为USER角色
    is_active: true,
};

内容的提问来源于stack exchange，提问作者KAITO_NGUYEN 2KXXX