sequelize (二)

  最近做的一个node项目，设计后端API应用中，对数据库的操作应用到 sequelize。对 sequelize 的技术应用做个随笔记录。

Sequelize简介：

  Sequelize 提供对 MySQL，MariaDB，SQLite 和 PostgreSQL 数据库的简单访问，通过映射数据库条目到对象，或者对象到数据库条目。简而言之，就是 ORM（Object-Relational-Mapper）。Sequelize.js 完全是使用 JavaScript 编写，适用于 Node.js 的环境。

申明model

<span style="font-family:Microsoft YaHei;font-size:18px;">export default (sequelize, DataTypes) => {
  return sequelize.define('Commodity', {
    id: {type: DataTypes.STRING, primaryKey: true, field: 'f_id'},
    name: {type: DataTypes.STRING, field: 'f_name'},
    description: {type: DataTypes.STRING, field: 'f_description'},
    price: {type: DataTypes.FLOAT, field: 'f_price'},
    kind_id: {type: DataTypes.STRING, field: 'f_kind_name'},
    brand_id: {type: DataTypes.STRING, field: 'f_brands'},
    area: {type: DataTypes.STRING, field: 'f_area'},
    is_show_on_mainpage: {type: DataTypes.STRING, field: 'F_IS_SHOW_ON_MAINPAGE'}
  }, {
    timestamps: false,
    tableName: 'ERP_COMMODITY_INFO', //数据库表名
    classMethods: {
      associate: ({Commodity, CommodityPicture, CommodityDetailPicutre}) => {
        Commodity.belongsToMany(CommodityPicture, {//一对多
          foreignKey: 'F_COMMODITY_NUM', //中间表的关联外键
          through: 'CommodityPictures' //中间表的model
        }),
        Commodity.belongsToMany(CommodityDetailPicutre, {
          foreignKey: 'F_COMMODITY_NUM',
          through: 'CommodityDetailPictures'
        })
      }
    }
  })
}

//申明中间表model
export default (sequelize, DataTypes) => {
  return sequelize.define('CommodityPictures', {}, {
    timestamps: false,
    tableName: 'ERP_COMMODITY_INFO_PICTURE'
  })
}

//申明关联表model
export default (sequelize, DataTypes) => {
  return sequelize.define('CommodityPicture', {
    id: {type: DataTypes.STRING, field: 'f_id', primaryKey: true},
    path: {type: DataTypes.STRING, field: 'f_path'},
    filename: {type: DataTypes.STRING, field: 'f_filename'},
    contentType: {type: DataTypes.STRING, field: 'f_content_type'}
  }, {
    timestamps: false,
    tableName: 'CDE_ATTACHMENT',
    classMethods: {
      associate: ({CommodityPicture, Commodity}) => {
        CommodityPicture.belongsToMany(Commodity, {
          foreignKey: 'F_ATTACHMENT_ID',
          through: 'CommodityPictures'
        })
      }
    }
  })
}

</span>

操作model

<span style="font-family:Microsoft YaHei;font-size:18px;">const getCommodities = async (params) => {
  let kindList = []
  const  where = {}, offset =  parseInt(params.offset) || 0, limit = parseInt(params.limit) || config.get('page:limit')
  const commodiKinds = await getCommoditySecondKinds(params)

  commodiKinds.map(kind => kindList.push(kind.id))

  if (params.brand) {
    where['brand_id'] = params.brand
  }
  if (params.price_range) {
    where['price'] = {$between: priceMap[params.price_range]}
  }
  if(params.kind){
    where['kind_id'] = {$in: kindList}
  }

  return await sequelize.models.Commodity.findAll({
    where,
    limit,
    offset,
    order: 'price',
    include: [{model: sequelize.models.CommodityPicture}],
    attributes: ['id', 'name', 'description', 'price', 'kind_id', 'brand_id', 'area']
  })
}</span>