Node服务端框架Express-Sequelize-Mysql模型架构设计

Sequelize 是一个基于 promise 的 Node.js ORM, 目前支持 Postgres, MySQL, MariaDB, SQLite 以及 Microsoft SQL Server. 它具有强大的事务支持, 关联关系, 预读和延迟加载,读取复制等功能。

• Express
• Sequelize
• Mysql2
• Helmet-安全中间件

架构目录

├── config							# 配置文件
│   └── sql.config.js				# Mysql配置
├── model							# 模型
│   ├── controller					# 控制器
│   │   ├── user					# 用户资源
│   │   │   └── handel.js			# 用户信息请求
│   │   └── resultMessage.js		# 返回结果消息
│   └── sequelize					# Sequelize配置
│   │   ├── models					# 表模型
│   │   │   └── user.js				# 用户信息模型
│   │   ├── extra-setup.js			# 额外的设置，比如添加关联。
│   │   └── index.js				# 模型处理
├── routes							# 接口路由
│   └── index.js					# 二级入口
└── app.js                    		# 入口文件 初始化等
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

安装依赖

Express框架请自行安装Express官网

# 安装依赖
npm install sequelize
npm install mysql2
npm install helmet

# 启动项目
npm start
1
2
3
4
5
6
7

config / sql.config.js(Mysql配置)

// Mysql配置
var config = {
    host: "",			// 数据库地址
    user: "",			// 用户名
    password: "",		// 密码
    port: "3306",		// 端口默认是3306，请根据实际配置更改
    database: "",		// 库
};

module.exports = config;
1
2
3
4
5
6
7
8
9
10

model / controller / user / handel.js(接口配置)

findOrCreate：
除非找到一个满足查询参数的结果,否则方法 findOrCreate 将在表中创建一个条目. 在这两种情况下,它将返回一个实例(找到的实例或创建的实例)和一个布尔值,指示该实例是已创建还是已经存在

// 这里需要根据项目需求更改，我原先是做的微信小程序登陆(如不理解请留言)
const express = require('express')
const router = express.Router();
const json = require('../resultMessage')
const { models } = require('../../sequelize');

router.post('/login', async (req, res) => {
    try {
        let [user, created] = await models.alluser.findOrCreate({
            where: { openid: req.body.openid },
            defaults: {	// 这里是没有查询到用户情况下需要插入新建的数据(请自行修改)
                openid: req.body.openid,
                nickname: '我叫小明',
                avatar: 'https://c-ssl.dtstatic.com/uploads/item/202005/29/20200529083215_wuahf.thumb.1000_0.jpeg',
                gender: 1,
                createtime: (new Date()).valueOf(),
                updatetime: (new Date()).valueOf(),
            }
        });
        if (created) {
            console.log('新用户注册')
        } else {
            console.log('老用户登陆')
        }
        // 返回结果
        json.success(res, user, '请求成功');
    } catch (error) {
        json.serverError(res);
    }
})

module.exports = router;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

model / controller / resultMessage.js(返回结果格式)

var json = function() {}

json.success = function(res, result, message) {
    return res.status(200).json({
        code: 200,
        message: message,
        data: result
    });
}

json.fail = function(res, message) {
    return res.status(400).json({
        code: 400,
        message: message,
    });
}

json.toLogin = function(res) {
    return res.status(401).json({
        code: 401,
        message: '请登录'
    });
}

json.serverError = function(res) {
    return res.status(500).json({
        code: 500,
        message: '服务器错误',
    });
}

module.exports = json;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

model / sequelize / models / user.js(用户模型)

const Sequelize = require('sequelize');
var DataTypes = Sequelize.DataTypes;

module.exports = function(sequelize) {
    return sequelize.define('alluser', {
        id: {
            type: DataTypes.BIGINT,
            autoIncrement: true,
            primaryKey: true,
            allowNull: false,
            comment: "主键",
        },
        openid: {
            type: DataTypes.STRING,
            allowNull: false,
            comment: "平台ID"
        },
        nickname: {
            type: DataTypes.STRING,
            allowNull: true,
            comment: "昵称"
        },
        avatar: {
            type: DataTypes.STRING,
            allowNull: true,
            comment: "头像"
        },
        gender: {
            type: DataTypes.BOOLEAN(1),
            allowNull: false,
            defaultValue: 0,
            comment: "性别",
        },
        createtime: {
            type: DataTypes.BIGINT(13),
            allowNull: false,
            comment: "注册时间"
        },
        updatetime: {
            type: DataTypes.BIGINT(13),
            allowNull: false,
            comment: "更新时间"
        },
    }, {
        sequelize,
        timestamps: false,
        tableName: 'alluser',
        indexes: [{
            name: "PRIMARY",
            unique: true,
            using: "BTREE",
            fields: [{
                name: "id"
            }]
        }, ]
    });
};
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

model / sequelize / extra-setup.js(额外的设置，比如添加关联)

const { Sequelize } = require('sequelize');
var DataTypes = Sequelize.DataTypes;

function applyExtraSetup(sequelize) {
    const { user } = sequelize.models;

    // 这里添加关联等设置
}

module.exports = { applyExtraSetup };
1
2
3
4
5
6
7
8
9
10

model / sequelize / index.js

const { Sequelize } = require('sequelize');
const { applyExtraSetup } = require('./extra-setup');
const mysqlConfig = require('../../config/sql.config')

// V6最低数据库版本Mysql-5.7
const sequelize = new Sequelize(mysqlConfig.database, mysqlConfig.user, mysqlConfig.password, {
    host: mysqlConfig.host,
    port: mysqlConfig.port,
    dialect: 'mysql',
    logging: false, // 关闭日志
    timestamps: false, // 固定未知列“createdAt”在“字段列表”中
    dialectOptions: {
        multipleStatements: true
    },
    pool: {
        max: 20,
        min: 0,
        acquire: 30000,
        idle: 10000
    },
    timezone: '+08:00' //改为标准时区
});

// 数据模型列表
const modelDefiners = [
    require('./models/user'),
    // 这里添加更多模型
];

// 根据文件定义所有模型。
for (const modelDefiner of modelDefiners) {
    modelDefiner(sequelize);
}

// 定义模型之后执行任何额外的设置，比如添加关联。
applyExtraSetup(sequelize);

sequelize.sync()

console.log('正在连接数据库...')
sequelize.authenticate().then(() => {
    console.log('数据库连接成功')
}).catch(err => {
    console.error('无法连接到数据库:', err)
})

//sequelize.close()

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


module.exports = sequelize
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

routes / index.js

const express = require('express');
const router = express.Router();
const user = require('../model/controller/user/handel')

router.use('/v1/user', user)

/* GET home page. */
router.get('/', function(req, res, next) {
    res.type('html');
    res.render('index', { title: 'Express' });
});

module.exports = router;
1
2
3
4
5
6
7
8
9
10
11
12
13

app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var helmet = require("helmet");

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var app = express();

// 安全中间件
app.use(helmet());

// 允许跨域访问
app.all("*", function(req, res, next) {
    //设置允许跨域的域名，*代表允许任意域名跨域
    res.header("Access-Control-Allow-Origin", "*");
    //允许的header类型
    res.header("Access-Control-Allow-Headers", "Origin,X-Requested-With,Accept,Content-type,Authorization");
    res.header("Access-Control-Allow-Credentials", true);
    //跨域允许的请求方式
    res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
    res.header("Content-Type", "application/json;charset=utf-8")
    if (req.method.toLowerCase() == 'options')
        res.sendStatus(200); //让options尝试请求快速结束
    else
        next();
});

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
    next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
    // set locals, only providing error in development
    res.locals.message = err.message;
    res.locals.error = req.app.get('env') === 'development' ? err : {};

    // render the error page
    // res.status(err.status || 500);
    // res.render('error');

    res.status(500).json({
        code: 500,
        message: err.message,
        tid: (new Date()).valueOf()
    });
});

module.exports = app;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

完结~有问题及时留言
附上整体结构目录打包文件

express-ejs模板

Node服务端框架Express-Sequelize-Mysql模型架构设计封装