JS后台开发之Sequelize和Koa2使用总结

使用Sequelize进行左连接查询

const Sequelize = require('sequelize');
const SequelizeInstance = require('../config/sequelizeBase');
const activityModel = require('../models/activity')(SequelizeInstance, Sequelize);
const Obj = ctx.query
const teaID = jwt.decode(ctx.request.header['access-token']);
const word = Obj.word || '';
const activities = await activityModel.findAll({
  where:{
    '$or':[
      {ID:{'$like':'%'+word+'%'}},
      {info:{'$like':'%'+word+'%'}},
      {name:{'$like':'%'+word+'%'}},
    ],
    teaID:teaID,
	availCollege:{'$like':'%'+stuCollege+'%'},
	enrollStart:{'$lt':new Date()},
	T_end:{'$gt':new Date()}
  },
  include:[{
      attributes:[['name', 'clubName']],
      association: activityModel.hasOne(clubModel, {foreignKey:'ID', sourceKey:'clubID', required: true})
    },{
      attributes:[[Sequelize.fn('count', Sequelize.col('enrolls.ID')), 'enrollNum']],
      association: activityModel.hasMany(enrollModel, {foreignKey:'actID', sourceKey:'ID', required: false}),
      required: false
    },{
      association: activityModel.hasMany(scoreModel, {foreignKey:'actID', sourceKey:'ID', required: false}),
      required: false
    },{
      association: activityModel.hasMany(achievementModel, {foreignKey:'actID', sourceKey:'ID', required: false}),
      required: false
    },{
      association: activityModel.hasMany(complaintModel, {foreignKey:'actID', sourceKey:'ID', required: false}),
      required: false
    }
  ],
  order: [[ Sequelize.col('auditStatus')], [ Sequelize.col('enrollStart'), 'DESC' ]],
  raw: true
});

在查询的时候附加关联条件association，并指明关系hasOne还是hasMany，例如从另一张表clubModel中的ID字段找到主表activityModel中clubID字段相等的值，最后设置raw: true返回原始查询结果。使用hasMany时，enrollModel关联查询返回的结果名为enrolls，就是数据表名称后面加上s。required: false指的是左连接，required: true就是右连接。

使用Sequelize自动生成数据模型

把文件命名为autosql.js，然运行node autosql.js，或者在package.json中的scripts加入"models": "node autosql.js"。运行前需要安装yarn add sequelize-auto mysql2。

const { host, user, password, database, dialect, port } = {
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : '数据库名称',
    port: 3306,
    dialect: 'mysql'
}
const SequelizeAuto = require('sequelize-auto')
const options = {
  host,
  dialect,
  directory: 'models',  // 指定输出 models 文件的目录
  port,
  additional: {
    timestamps: false
  }
}
const auto = new SequelizeAuto(database, user, password, options)
auto.run(err => {
    console.log(err)
})

生成的模板如下，文件名和数据表名称一致：

const Sequelize = require('sequelize');
module.exports = function(sequelize, DataTypes) {
  return sequelize.define('click', {
    ID: {
      type: DataTypes.BIGINT,
      allowNull: false,
      primaryKey: true
    },
    stuID: {
      type: DataTypes.BIGINT,
      allowNull: true
    },
    actID: {
      type: DataTypes.BIGINT,
      allowNull: true
    }
  }, {
    sequelize,
    tableName: 'click',
    timestamps: false,
    indexes: [
      {
        name: "PRIMARY",
        unique: true,
        using: "BTREE",
        fields: [
          { name: "ID" },
        ]
      },
    ]
  });
};

生成后在controllers导入数据库的方法：

const Sequelize = require('sequelize');
const SequelizeInstance = require('../config/sequelizeBase');
const activityModel = require('../models/activity')(SequelizeInstance, Sequelize);

koa2的文件上传与下载

在app.js中加入

// 使用文件上传中间件
app.use(KoaBody({
  multipart: true,
  formidable: {
    maxFileSize: 5000*1024*1024,
    multipart: true
  }}));
// 访问静态资源文件
app.use(require('koa-static')(__dirname + '/public'));

后台接收文件，并返回访问链接

function upload (file, filePath) {
	try{
        // 如果本地已经有同名文件，需要先删除
        if (fs.existsSync(filePath)){
            fs.unlinkSync(filePath)
        }
        // 创建可读流
        const reader = fs.createReadStream(file.path);
        // 创建可写流
        const upStream = fs.createWriteStream(filePath);
        // 可读流通过管道写入可写流
        reader.pipe(upStream);
    }
    catch(e){
        console.log(e);
        // 防止文件占用
        reader.close()
        upStream.close()
    }
}
exports.uploadFile = async (ctx)=>{
  try{
    const Obj = JSON.parse(ctx.request.body.info)
    const file = ctx.request.files.file; // 获取上传文件
    last = file.name.split(".")
    last = last[last.length-1] //文件名后缀
    fname = Obj.actID.toString()+"."+last
    url = path.join('acievements', fname)
    let filePath = path.join(__dirname, '..', 'public/xxxfiles', fname);
    upload(file, filePath)
    ctx.body = {
      code:1,
      message:'已成功新增',
      url: url
    }
  }
  catch(e){
    console.log(e)
    ctx.body = {
      code:10000,
      message:'网络出错'+e
    }
  }
}
router.post('/api/club/addAchievement', uploadFile);

前台上传文件代码

<template>
  <a-form @submit="handleSubmit" :form="form">
    <a-form-item
      label="上传文件"
      :labelCol="labelCol"
      :wrapperCol="wrapperCol"
      hasFeedback
    >
      <a-upload v-decorator="['file', {rules:[{required: true, message: '请上传文件'}]}]" name="file" :beforeUpload="beforeUpload" :showUploadList="false">
        <a-button icon="upload">选择图片</a-button>
      </a-upload>
    </a-form-item>
  </a-form>
</template>
<script>
function addFile (parameter) {
  console.log(parameter)
  return request({
    url: 'http://localhost:3000/api/club/addAchievement',
    method: 'post',
    data: parameter,
    processData: false,
    contentType: false
  })
}
methods: {
    onOk () {
      console.log('监听了 modal ok 事件', this.value)
      const { form: { validateFields } } = this
      var err = null
      var value = null
      validateFields((errors, values) => {
        err = errors
        value = values
      })
      if (!err) {
        const formData = new FormData()
        formData.append('file', value.file.file.originFileObj, value.file.file.name)
        formData.append('info', JSON.stringify(value))
        addFile(formData) // 上传
        return new Promise(resolve => {
          resolve(true)
        })
      }
    }
  }