sequelize 连接sql server服务的简单封装

司空鸿熙
2023-12-01

最近的项目,用nodejs做的webapi,连接sql server数据库用的sequelize框架,现在把我自己简单封装的sequelize服务记录下来,以备参考:

sql.js

const Sequelize = require('sequelize')
const fs = require('fs')
const path = require('path')

const basePath = path.resolve('./')

const db = {
  database: 'TestMall',
  username: 'sa',
  password: 'whb',

  host: '192.168.1.101',
  port: 1433,
  
  dialect: 'mssql',
  // close log
  logging: false,
  // "timestamps: false" fixed Unknown column 'createdAt' in 'field list'
  timestamps: false,
  dialectOptions: {
    multipleStatements: true
  }
}
//const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');
const sequelize = new Sequelize(db)

sequelize.sync()

sequelize.authenticate().then(() => {
    console.log('Connection has been established successfully.')
}).catch(err => {
    console.error('Unable to connect to the database:', err)
})

//sequelize.close()

// 统一事件调用方法,避免业务逻辑代码里大量重复的then()cathc()代码
Sequelize.Model.excute = function (res, action, param, cb) {
  this[action](param[0], (param.length > 1 ? param[1] : null)).then((re) => {
    if (cb) {
      cb(re)
    } else {
      res.json({
        code: 200,
        data: re
      })
    }
  }).catch(err => {
    console.log(err)
    // 错误日志
    fs.writeFile(`${basePath}/logs/sqlerr.log`, err, () => {});
    res.json({
      code: 500,
      data: err
    })
  })
}

// 统一存储过程、批处理调用方法,避免业务逻辑代码里大量重复的then()cathc()代码
Sequelize.Model.exec_proc = function (res, sql, param, cb) {
  sequelize.query(sql, {
    replacements: param
  }).then(re => {
    if (re[0][0].msg) {
      // 错误日志
      fs.writeFile(`${basePath}/logs/sqlerr.log`, re[0][0].msg, () => {});
      res.json({
        code: 500,
        data: re[0][0].msg
      })
    } else {
      if (cb) {
        cb(re[0])
      } else {
        res.json({
          code: 200,
          data: re[0]
        })
      }
    }
  }).catch(err => {
    // 错误日志
    fs.writeFile(`${basePath}/logs/sqlerr.log`, err, () => { });
    res.json({
      code: 500,
      data: err
    })
  })
}
module.exports = sequelize

考虑到新手不容易理解和接受,现在添加几张数据表映射模型定义和几个简单调用例子:

model.js

const Sequelize = require('sequelize')
const sequelize = require('./sql')
const Model = Sequelize.Model

//#region 数据模型申明
class user extends Model {}
class goods extends Model {}
class files extends Model {}
class orders extends Model {}
//#endregion

//#region 数据模型定义
/**
 * 用户账号信息表
 */
user.init({
  id: {
    primaryKey: true,
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    allowNull: false,
    field: 'xGUID'
  },
  account: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'Account'
  },
  name: {
    type: Sequelize.STRING,
    field: 'Name'
  },
  password: {
    type: Sequelize.BOOLEAN,
    allowNull: false,
    field: 'Password'
  }
}, {
  sequelize,
  timestamps: false,
  tableName: 'Person'
})

/**
 * 商品表
 */
goods.init({
  id: {
    primaryKey: true,
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    allowNull: false,
    field: 'xGUID'
  },
  /**
   * book's name
   */
  name: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'Name'
  },
  /**
   * price
   * 定价
   */
  price: {
    type: Sequelize.REAL,
    allowNull: false,
    field: 'DJ'
  },
}, {
  sequelize,
  timestamps: false,
  tableName: 'Goods'
})

/**
 * 订单表
 */
orders.init({
  id: {
    primaryKey: true,
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    allowNull: false,
    field: 'xGUID'
  },
  quantity: {
    type: Sequelize.INTEGER,
    allowNull: false,
    field: 'GoodsID'
  },
  amount: {
    type: Sequelize.REAL,
    allowNull: false,
    field: 'Amount'
  },
  /**
   * 外键, 关联商品表主键
   */
  goodsID: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'GoodsID'
  },
  /**
   * 外键, 关联用户表主键
   */
  personID: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'Person_ID'
  }
}, {
  sequelize,
  timestamps: false,
  tableName: 'Orders'
})

/**
 * 资源文件表
 */
files.init({
  id: {
    primaryKey: true,
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    allowNull: false,
    field: 'xGUID'
  },
  /**
   * 外键, 关联商品表主键
   */
  goodsID: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'LYBMID'
  },
  path: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'Path'
  }
}, {  
  sequelize,
  timestamps: false,
  tableName: 'File'
})
//#endregion

//#region 表关联
goods.hasMany(files, {
  as: 'images',
  foreignKey: 'goodsID',
  through: null
});
orders.belongsTo(user, {
  as: 'buyer',
  foreignKey: 'userID',
  through: null
});
orders.belongsTo(goods, {
  as: 'goods',
  foreignKey: 'goodsID',
  through: null
});
files.belongsTo(orders, {
  as: 'order',
  sourceKey: 'goodsID',
  foreignKey: 'userID',
  through: null
});
//#endregion

module.exports = {
  user,
  goods,
  orders,
  files
}

接下来是业务逻辑层的调用例子:

order.js

const express = require('express')
const Sequelize = require('sequelize')
const Op = Sequelize.Op

const {
  user,
  goods,
  files,
  orders
} = require('../models')


Router.post('/add', (req, res) => {
  payInfo.exec_proc(res,"exec proc_addOrder :personID,:addressID,:amount,:goodsIDs,:nums", {
    personID: req.body.personID,
    goodsID: req.body.goodsID,
    quantity: req.body.quantity,
    amount: req.body.amount
  },(re)=>{
    if(re...){
      //处理库存锁定等业务。。。
      //存储过程其实已经做了, 这里就是表示下这个位置的用法。。。
    }
  })
})

Router.post('/list', (req, res) => {
  orders.excute(res, 'findAndCountAll', [{
    attributes: ['id',  'goodsID', 'buyerID', quantity, amount],
    where: {
      buyerID: req.body.userID,
      status: {
        [Op.ne]: '已取消'
      }
    },
    include: [{
        as: 'goods',
        model: goods,
        attributes: ['id', 'name', 'price'],
        required: false
      }, {
        as: 'images',
        model: files,
        where: {
          status: 0
        },
        attributes: ['id', 'name', 'path'],
        required: false
    }],
    order: [
      ['createTime', 'DESC'],
    ],
    offset: req.body.pageIndex * req.body.pageSize,
    limit: req.body.pageSize
  }])
})

module.exports = Router

最后是路由设定了:

route.js

const express = require('express')
const bodyParser = require('body-parser')
const cookieParser = require('cookie-parser')
const session = require('express-session')
const fs = require('fs')
const path = require('path')

//导入接口文件
const order = require('./controller/order')

// 默认页,首页
const pagePath = path.resolve(__dirname, 'public/index.html')
const page = fs.readFileSync(pagePath, 'utf-8', (err, data) => {
  if (err) {
    return err
  }
  return data
})

//创建express应用实例
const app = express()

//启用session、cookie等
app.use(cookieParser())
app.use(
  session({
    secret: 'keyboard cat',
    // cookie: {
    //   domain: '192.168.2.7',
    //   maxAge: 60 * 60 * 1000,
    //   secure: false
    // },
    resave: false,
    saveUninitialized: true
  })
)

app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: false
  })
)

//设置跨域等头部参数
app.all('*', function (req, res, next) {
  res.header('Access-Control-Allow-Origin', '*')
  //res.header('Access-Control-Allow-Origin', 'http://www.uni-engine.cn:8060')
  res.header('Access-Control-Allow-Headers', 'Content-Type')
  res.header('Access-Control-Allow-Methods', 'PUT,POST,GET,DELETE,OPTIONS')
  res.header('Access-Control-Allow-Credentials', true);
  res.header('X-Powered-By', ' 3.2.1')
  res.header('Content-Type', 'application/json;charset=utf-8')
  res.cookie('test', 'sadasdsad')
  next()
})

//指定路由
app.use('/api/v1/order', order)

//允许访问静态页
app.use('/apidoc', (req, res) => {
  res.set('Content-Type', 'text/html')
  res.send(page)
})

//允许访问指定的静态资源
app.use('/public', express.static(path.join(__dirname, 'public')))

//端口监听
app.listen('9000', () => {
  console.log('open Browser on http://127.0.0.1:9000')
})

这样,一个相对完整的webapi程序的代码就全有了。。。

下面再贴几个常用写法:

关联查询加分页:

Router.post('/list', (req, res) => {
  orders.findAndCountAll({
    attributes: ['id', 'code', 'amount', 'status', 'createTime'],
    where: {
      personID: req.body.personID,
    },
    distinct: true, //避免因连接查询导致数据重复,统计条数错误
    include: [{
      as: 'address',
      model: receiveAddress,
      attributes: ['id', 'area', 'address'],
      required: false
    }, {
      as: 'details',
      model: orderDetail,
      attributes: ['id', 'status', 'quantity'],
      include: [{
        as: 'goods',
        model: goods,
        raw: true, //合并字段
        attributes: ['desc', 'name', 'price', 'salePrice', 'stock'],
        required: false //避免关联表数据为空是不返回主表数据
      }, {
        as: 'images',
        model: files,
        where: {
          scenes: '图书封面'
        },
        //through: false //如果多对多关系,中间有关联表,这个属性表示是否留存关联表结构
        raw: true, //合并字段
        attributes: ['path'],
        required: false //避免关联表数据为空是不返回主表数据
      }]
    }],
    order: [
      ['createTime', 'DESC'],
    ],
    offset: req.body.pageIndex * req.body.pageSize,
    limit: req.body.pageSize
  }).then((re)={
    res.Json({
      code: '200',
      data: re
    })
  }).catch((err)=>{
    res.Json({
      code: '500',
      data: err
    })
  })
})

通过调用存储过程添加订单:

Router.post('/add', (req, res) => {
  payInfo.sequelize.query("exec proc_addOrder :personID,:addressID,:amount,:goodsIDs,:nums", {
    personID: req.body.personID,
    addressID: req.body.addressID,
    amount: req.body.amount,
    goodsIDs: req.body.goodsID,
    nums: req.body.quantity
  }).then((re) => {
    res.json({
      code: 200,
      data: re[0]
    })
  }).catch(err => {
    res.json({
      code: 500,
      data: err
    })
  })
})

 

 类似资料: