web 全栈开发-实战项目- 连接mysql数据库–Sequelize
Sequelize 是一个基于 promise 的 Node.js ORM, 目前支持 Postgres, MySQL, MariaDB, SQLite 以及 Microsoft SQL Server. 它具有强大的事务支持, 关联关系, 预读和延迟加载,读取复制等功能。
Sequelize官网地址:https://sequelize.org/
Sequelize中文文档地址:https://www.sequelize.com.cn/
安装Sequelize
npm install sequelize --save
安装mysql2
npm install mysql2 --save
配置文件
在component文件中 新建mysql文件,在mysql文件内新建mysql-config.js文件,用来配置数据库信息。再创建mysqlDB文件,用来对sequelize进行简单封装,和数据表的引入,mysqlDB文件内创建db.js,再创建Notices.js文件
mysql-config.js
var config = {
dbname: 'express', // 数据库名称
uname: 'root', // 数据库登录名
upwd: 'root', // 数据库登录密码
host: '127.0.0.1',// 数据库主机
port: 3306, // 数据库端口,mysql默认是3306
dialect: 'mysql', // 数据库类型,这里是mysql
pool: { // 连接池配置
max: 5,
min: 0,
idle: 10000
}
};
module.exports = config;
mysqlDB–db.js
// 引入模块
const Sequelize = require('sequelize');
// 读取配置
const mysqlConfig = require('../mysql-config');
// 根据配置实例化seq
var seq = new Sequelize(mysqlConfig.dbname, mysqlConfig.uname, mysqlConfig.upwd, {
host: mysqlConfig.host,
dialect: mysqlConfig.dialect,
pool: mysqlConfig.pool
});
/**
* 定义数据模型
*
* @param {any} name 模型名称【数据库表名】
* @param {any} attributes 数据字段集合
* @returns 数据模型对象
*/
function defineModel (name, attributes) {
var attrs = {};
for (let key in attributes) {
let value = attributes[key];
if (typeof value === 'object' && value['type']) {
value.allowNull = value.allowNull || false;
attrs[key] = value;
} else {
attrs[key] = {
type: value,
allowNull: false
};
}
}
attrs.createAt = {
type: Sequelize.BIGINT,
allowNull: false
};
attrs.updateAt = {
type: Sequelize.BIGINT,
allowNull: false
};
attrs.version = {
type: Sequelize.BIGINT,
allowNull: false
};
// 状态:0表示有效,1表示无效,2表示已删除,默认为0.
attrs.status = {
type: Sequelize.INTEGER,
allowNull: false
};
// 调用seq的方法定义模型并返回
return seq.define(name, attrs, {
tableName: name,
timestamps: false,
hooks: {
beforeValidate: function (obj) {
let now = Date.now();
if (obj.isNewRecord) {
obj.createAt = now;
obj.updateAt = now;
obj.version = 0;
} else {
obj.updateAt = now;
++obj.version;
}
}
}
});
}
exports.defineModel = defineModel;
mysqlDB–Notices.js
var db = require('./db');
var seq = require('sequelize');
var Model = db.defineModel('notices', {
content: seq.TEXT,
title: seq.STRING(30),
startDate: seq.BIGINT,
expireDate: seq.BIGINT,
gmId: seq.INTEGER(10),
});
// 进行数据结构的同步
// Model.sync();
// 导出模型对象
module.exports = Model;
到这里,封装完成,
引用notices模型
const Notices = require('../../component/mysql/mysqlDB/Notices');
操作数据
添加
NoticesAdd: async (ctx, next) => {
try{
var data = await Notices.create({
content: '内容。',
title: '标题',
gmId: '10000',
status: 0,
expireDate: 1527396599123,
startDate: Date.now()
})
exitParame.successCTX(ctx,200,data)
}catch{
exitParame.errorCTX(ctx,200,data)
}
},
修改
NoticesUpdate: async (ctx, next) => {
try{
var data = await Notices.update({
status: 2
},{
where: {id: 1}
})
exitParame.successCTX(ctx,200,data)
}catch{
exitParame.errorCTX(ctx,200,data)
}
},
删除
NoticesDestroy: async (ctx, next) => {
try{
var data = await Notices.destroy({
where: {id: 1}
})
exitParame.successCTX(ctx,200,data)
}catch{
exitParame.errorCTX(ctx,200,data)
}
},
查找
NoticesFind: async (ctx, next) => {
try{
var data = await Notices.findAll();
exitParame.successCTX(ctx,200,data)
}catch{
exitParame.errorCTX(ctx,200,data)
}
},
配置路由
{ httpType:'POST', addr:"/customer/admin/NoticesAdd", fun:admin.NoticesAdd },
{ httpType:'POST', addr:"/customer/admin/NoticesUpdate", fun:admin.NoticesUpdate },
{ httpType:'GET', addr:"/customer/admin/NoticesDestroy", fun:admin.NoticesDestroy },
{ httpType:'GET', addr:"/customer/admin/NoticesFind", fun:admin.NoticesFind },