Sequelize 使用

Sequelize 是一款基于 Nodejs 功能强大的异步 ORM 框架。
同时支持 PostgreSQL, MySQL, SQLite and MSSQL 多种数据库，很适合作为 Nodejs 后端数据库的存储接口，为快速开发 Nodejs 应用奠定扎实、安全的基础。
下面基于 koa2 使用 Sequelize
github传送门

1.入门

1.1 Sequelize 安装

squelize 可以通过 npm 命令获取，除安装 sequelize 模块外还要安装所使用数据的驱动模块：

$ npm install --save sequelize

# 还需要安装以下之一：
$ npm install --save pg pg-hstore  // postgreSql
$ npm install --save mysql // mysql 或 mariadb
$ npm install --save sqlite3  
$ npm install --save tedious // MSSQL

1.2 Sequelize 建立连接

Sequelize 会在初始化时设置一个连接池，这样你应该为每个数据库创建一个实例：

./config/db.js

const sequelize = new Sequelize('database', 'username', 'password', {
    host: 'IP',
    dialect: 'mysql',
    operatorsAliases: false,
    dialectOptions: {
        // 字符集
        charset: "utf8mb4",
        collate: "utf8mb4_unicode_ci",
        supportBigNumbers: true,
        bigNumberStrings: true
    },

    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    },
    timezone: '+08:00' //东八时区
});

1.3 定义数据库表

这里只给出一张表的例子，其余表的构建基本相同

./table/team.js

const moment = require('moment'); // 用于格式化时间
const Sequelize = require('sequelize');

module.exports = function (sequelize, DataTypes){
    return sequelize.define('team',{
        team_id:{ // 自增主键
            type:DataTypes.INTEGER,
            primaryKey:true,
            allowNull:false,
            autoIncrement:true
        },
        team_name:{
            type:DataTypes.CHAR(45),
            allowNull:false
        },
        leader:{
            type:DataTypes.CHAR(20),
            allowNull:false,
            references: {  // 外键
                model: 'user',
                key: 'username',
                deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE
            }
        },
        limit:{
            type:DataTypes.INTEGER,
            allowNull:false,
            defaultValue: 0
        },
        createdAt: {
            type: DataTypes.DATE,
            get() {
                return moment(this.getDataValue('createdAt')).format('YYYY-MM-DD HH:mm:ss');
            }
        },
        // 更新时间
        updatedAt: {
            type: DataTypes.DATE,
            get() {
                return moment(this.getDataValue('updatedAt')).format('YYYY-MM-DD HH:mm:ss');
            }
        }
    }, {
        // 如果为 true 则表的名称和 model 相同，即 team
        // 为 false MySQL创建的表名称会是复数 teams
        // 如果指定的表名称本就是复数形式则不变
        freezeTableName: true
    })
};

1.4 定义 Module

这里只给出一些基础的操作，数据库增删改查

./modules/teamModel.js

const db = require('../config/db');
const Sequelize = require('sequelize');
const sequelize = db.sequelize;
const Team = sequelize.import('../table/team');

Team.sync({force: false}); // 如果force为true，则在尝试创建表之前，都将删除原有的表。如果force为false，原本不存在表示会创建表。

数据库增加操作

• create：建立新实例

static async createTeam(data) {
    return await Team.create({
        team_name : data.team_name,
        leader : data.leader,
        limit : data.limit,
    })
}

数据库删除操作:

• destory：删除实例
• where：判断属性相等

static async deleteTeam(team_id) {
    return await Team.destroy({
        where: {
            team_id : team_id
        }
    })
}

数据库修改操作

• update：更改实例

static async updateTeamDescription(new_data) {
    await Team.update({
        team_name: new_data.team_name,
        limit: new_data.limit
    }, {
        where: {
            team_id : new_data.team_id
        }
    })
}

数据库查询操作

• findOne：查询单个实例
• findAll：查询多个实例，返回数组
• attributes：规定返回的属性，不加 attributes 默认返回全属性

static async getTeamByTeamId(team_id, type) {
    return await Team.findOne({
        attributes: ['team_id', 'team_name', 'leader', 'limit'],
        where: {
            team_id : team_id,
        }
    })
}

1.5 定义 Controller

这里只给出一个例子，其余调用基本相同

./controller/teamController.js

const TeamModel = require('../modules/teamModel');

调用数据库查询操作

static async getGroupByGroupId(team_id) {
    let result = null;
    try {
        let data = await TeamModel.getTeamByTeamId(team_id);
        if (data.length === 0) {
            result = {
                code: 213,
                msg: '查询成功,没有该小组',
                data: null
            };
        } else {
            result = {
                code: 200,
                msg: '查询成功',
                data: data
            };
        }
    } catch (err) {
        result = {
            code: 412,
            msg: '查询失败',
            data: err.message
        };
    }
    return result;
}

1.6 路由

./routes/router.js

const team_api = require('./team_api.js');
module.exports = {
    team_api: team_api,
};

app.js

app.use(routers.team_api.routes(), routers.team_api.allowedMethods());

./routes/team_api.js
路由包含：get、post、del、put、patch，这里只使用了一个get作为例子

const router = require('koa-router')();
const TeamController = require('../controller/teamController');

// 配置路由，全局配置，此router的所有路径都会自动被添加该前缀。
router.prefix('/api/team');

router.get('/Id/', async (ctx) => {
    let query_params = ctx.query;
    let result = null;
    if (query_params.team_id) {
        result = await TeamController.getGroupByGroupId(query_params.team_id)
    } else {
        result = {
            code: 400,
            msg: 'Wrong query param.',
            data: null
        }
    }
    response(ctx, result);
});

let response = (ctx, result) => {
    ctx.response.status = result.code;
    ctx.body = {
        code: result.code,
        msg: result.msg,
        data: result.data
    };
    return ctx;
};
module.exports = router;

配置完毕运行 koa2，就可以通过 ip:port/api/team/Id?team_id=[team_id] 访问API了
在浏览器只能测试 get 和 del，想要测试其他可以使用 Postman

2. 升级使用

2.1 表的联立查询

./modules/teamModel.js

static async getTeamByTeamId(team_id) {
	// 外键，一对多
    Team.hasMany(Teamlabel, {foreignKey : 'team_id'});
    Teamlabel.belongsTo(Team, {foreignKey : 'team_id'});
    Team.hasMany(Members, {foreignKey : 'team_id'});
    Members.belongsTo(Team, {foreignKey : 'team_id'});
    return await Team.findOne({
        attributes: ['team_id', 'team_name', 'leader', 'limit'],
        where: {
            team_id : team_id,
        },
        include: [{
            attributes: ['team_id', 'label'],
            model: Teamlabel,
        },{
            attributes: ['team_id', 'member_username'],
            model: Members,
        }],
    })
}

2.2 查询操作符

const Op = Sequelize.Op

[Op.and]: {a: 5}           // 且 (a = 5)
[Op.or]: [{a: 5}, {a: 6}]  // (a = 5 或 a = 6)
[Op.gt]: 6,                // id > 6
[Op.gte]: 6,               // id >= 6
[Op.lt]: 10,               // id < 10
[Op.lte]: 10,              // id <= 10
[Op.ne]: 20,               // id != 20
[Op.eq]: 3,                // = 3
[Op.not]: true,            // 不是 TRUE
[Op.between]: [6, 10],     // 在 6 和 10 之间
[Op.notBetween]: [11, 15], // 不在 11 和 15 之间
[Op.in]: [1, 2],           // 在 [1, 2] 之中
[Op.notIn]: [1, 2],        // 不在 [1, 2] 之中
[Op.like]: '%hat',         // 包含 '%hat'
[Op.notLike]: '%hat'       // 不包含 '%hat'
[Op.iLike]: '%hat'         // 包含 '%hat' (不区分大小写)  (仅限 PG)
[Op.notILike]: '%hat'      // 不包含 '%hat'  (仅限 PG)
[Op.regexp]: '^[h|a|t]'    // 匹配正则表达式/~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.notRegexp]: '^[h|a|t]' // 不匹配正则表达式/!~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (仅限 PG)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (仅限 PG)
[Op.like]: { [Op.any]: ['cat', 'hat']} // 包含任何数组['cat', 'hat'] - 同样适用于 iLike 和 notLike
[Op.overlap]: [1, 2]       // && [1, 2] (PG数组重叠运算符)
[Op.contains]: [1, 2]      // @> [1, 2] (PG数组包含运算符)
[Op.contained]: [1, 2]     // <@ [1, 2] (PG数组包含于运算符)
[Op.any]: [2,3]            // 任何数组[2, 3]::INTEGER (仅限PG)
[Op.col]: 'user.organization_id' // = 'user'.'organization_id', 使用数据库语言特定的列标识符, 本例使用 PG

使用样例

static async getTeamByName(team_name) {
    return await Team.findAll({
        attributes: ['team_id', 'team_name', 'leader', 'limit'],
        where: {
            team_name: {
                [Op.like]: '%'+team_name+'%',
            }
        }
    })
}