ORM框架Sequelize

什么是ORM

ORM是关系型映射的翻译，英文全称为Object Relational Mapping，它是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。效果上表现为在我们操作数据库的时候不需要去写一行行sql语句，而是将关系型数据库映射到一个对象里，在代码中只需要调用这些方法既可以了。

在传统的企业应用设计系统中，通常采用MVC的架构方式，其中Model层通常用于处理数据的逻辑关系，这样别会出现数据库操作与业务相关的数据操作强耦合的现象。几十上百行的sql语句会夹杂在业务中，而ORM的出现使对数据库的操作变为对普通对象的操作，对其模型的抽离大大简化了代码。

各语言都有各自不同的ORM框架，本文主要介绍nodejs的ORM框架Sequelize,并且只针对到MySQL数据源的介绍

使用

安装

npm install --save sequelize
npm install --save mysql2

建立连接

//方法1:单独传递参数
const sequelize = new Sequelize('test', 'root', 'Aa04153071.', {
    host: '127.0.0.1',
    port: 3306,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,//抛出错误前保持多少毫秒继续尝试连接
        idle: 10000,//在空闲时间保持多少毫秒释放连接
    }
});
/*
// 方法2: 传递连接 URI
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');*/

其他配置参数可参考官方文档--构造函数

连接测试

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

模型建立

此处进介绍一种方式

const User = sequelize.define('user_test', {
    //可设置字段类型
    //可设置是否为空
    //可定义主键
    //可设置自增
    //可设置自定义列名称
    //可设置注释
    id: {
        type: DataType.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true,
        comment: '这是一个注释'
    },
    //可设置默认值
    //可设置唯一索引，当具有相同值的两记录将抛出错误
    //多字段设置同一唯一索引将组成联合索引
    uid: {
        type: DataType.INTEGER,
        defaultValue: 0,
        unique: 'index'
    },
    hk_state: {
        type: DataType.INTEGER,
        unique: 'index'
    },
    us_state: {
        type: DataType.INTEGER,
        unique: true
    },
    as_state: {
        type: DataType.INTEGER,
        unique: true
    },
    created_at: {
        type: DataType.INTEGER
    },
    updated_at: {
        type: DataType.INTEGER,
        //属性验证器
        validate:{
            not: ["[a-z]",'i'],       // 不允许字母
        }
    }
}, {
    //禁用默认时间戳
    timestamp: false,
    // 不删除数据库条目,但将新添加的属性deletedAt设置为当前日期(删除完成时). 
    // paranoid 只有在启用时间戳时才能工作
    paranoid:false,
    //是否启用下划线命名
    underscored: true,
    //是否禁止修改表名
    freezeTableName: true,
    // 定义表的名称
    tableName: 'my_very_custom_table_name',
    // 启用乐观锁定. 启用时,sequelize将向模型添加版本计数属性,
    // 并在保存过时的实例时引发OptimisticLockingError错误.
    // 设置为true或具有要用于启用的属性名称的字符串.
    version: true,   
})

属性验证器信息

{
      is: ["^[a-z]+$",'i'],     // 只允许字母
      is: /^[a-z]+$/i,          // 与上一个示例相同,使用了真正的正则表达式
      not: ["[a-z]",'i'],       // 不允许字母
      isEmail: true,            // 检查邮件格式 (foo@bar.com)
      isUrl: true,              // 检查连接格式 (http://foo.com)
      isIP: true,               // 检查 IPv4 (129.89.23.1) 或 IPv6 格式
      isIPv4: true,             // 检查 IPv4 (129.89.23.1) 格式
      isIPv6: true,             // 检查 IPv6 格式
      isAlpha: true,            // 只允许字母
      isAlphanumeric: true,     // 只允许使用字母数字
      isNumeric: true,          // 只允许数字
      isInt: true,              // 检查是否为有效整数
      isFloat: true,            // 检查是否为有效浮点数
      isDecimal: true,          // 检查是否为任意数字
      isLowercase: true,        // 检查是否为小写
      isUppercase: true,        // 检查是否为大写
      notNull: true,            // 不允许为空
      isNull: true,             // 只允许为空
      notEmpty: true,           // 不允许空字符串
      equals: 'specific value', // 只允许一个特定值
      contains: 'foo',          // 检查是否包含特定的子字符串
      notIn: [['foo', 'bar']],  // 检查是否值不是其中之一
      isIn: [['foo', 'bar']],   // 检查是否值是其中之一
      notContains: 'bar',       // 不允许包含特定的子字符串
      len: [2,10],              // 只允许长度在2到10之间的值
      isUUID: 4,                // 只允许uuids
      isDate: true,             // 只允许日期字符串
      isAfter: "2011-11-05",    // 只允许在特定日期之后的日期字符串
      isBefore: "2011-11-05",   // 只允许在特定日期之前的日期字符串
      max: 23,                  // 只允许值 <= 23
      min: 23,                  // 只允许值 >= 23
      isCreditCard: true,       // 检查有效的信用卡号码

      // 自定义验证器的示例:
      isEven(value) {
        if (parseInt(value) % 2 !== 0) {
          throw new Error('Only even values are allowed!');
        }
      }
      isGreaterThanOtherField(value) {
        if (parseInt(value) <= parseInt(this.otherField)) {
          throw new Error('Bar must be greater than otherField.');
        }
      }
    }
  }

数据类型

Char

Sequelize.STRING                      // VARCHAR(255)
Sequelize.STRING(1234)                // VARCHAR(1234)
Sequelize.STRING.BINARY               // VARCHAR BINARY
Sequelize.TEXT                        // TEXT
Sequelize.TEXT('tiny')                // TINYTEXT

Number

Sequelize.INTEGER                     // INTEGER
Sequelize.BIGINT                      // BIGINT
Sequelize.BIGINT(11)                  // BIGINT(11)

Sequelize.FLOAT                       // FLOAT
Sequelize.FLOAT(11)                   // FLOAT(11)
Sequelize.FLOAT(11, 10)               // FLOAT(11,10)

Sequelize.DOUBLE                      // DOUBLE
Sequelize.DOUBLE(11)                  // DOUBLE(11)
Sequelize.DOUBLE(11, 10)              // DOUBLE(11,10)

Sequelize.DECIMAL                     // DECIMAL
Sequelize.DECIMAL(10, 2)              // DECIMAL(10,2)

Time

Sequelize.DATE                        // mysql / sqlite 为 DATETIME, postgres 为带时区的 
Sequelize.DATE(6)                     // DATETIME(6) 适用 mysql 5.6.4+. 小数秒支持最多6位精度
Sequelize.DATEONLY                    // DATE 不带时间.

Boolean

Sequelize.BOOLEAN                     // TINYINT(1)

ENUM

Sequelize.ENUM('value 1', 'value 2')  // 一个允许值为'value 1'和'value 2'的ENUM

常见操作

属性

Model.findAll({
  attributes: ['foo', 'bar']
});
// SELECT foo, bar ...

使用嵌套数组重命名

Model.findAll({
  attributes: ['foo', ['bar', 'baz']]
});

// SELECT foo, bar AS baz ...

使用sequelize.fn聚合

Model.findAll({
  attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});
// SELECT COUNT(hats) AS no_hats ...

使用include额外包括

Model.findAll({
  attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
});

// SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...

使用exclude排除某些表字段

Model.findAll({
  attributes: { exclude: ['baz'] }
});

where

const Op = Sequelize.Op;

Post.findAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2

Post.findAll({
  where: {
    authorId: 12,
    status: 'active'
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

Post.findAll({
  where: {
    [Op.or]: [{authorId: 12}, {authorId: 13}]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 13]
    }
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;

操作符

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.startsWith]: 'hat'     // 类似 'hat%'
[Op.endsWith]: 'hat'       // 类似 '%hat'
[Op.substring]: 'hat'      // 类似 '%hat%'
[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

运算符别名

const operatorsAliases = {
  $eq: Op.eq,
  $ne: Op.ne,
  $gte: Op.gte,
  $gt: Op.gt,
  $lte: Op.lte,
  $lt: Op.lt,
  $not: Op.not,
  $in: Op.in,
  $notIn: Op.notIn,
  $is: Op.is,
  $like: Op.like,
  $notLike: Op.notLike,
  $iLike: Op.iLike,
  $notILike: Op.notILike,
  $regexp: Op.regexp,
  $notRegexp: Op.notRegexp,
  $iRegexp: Op.iRegexp,
  $notIRegexp: Op.notIRegexp,
  $between: Op.between,
  $notBetween: Op.notBetween,
  $overlap: Op.overlap,
  $contains: Op.contains,
  $contained: Op.contained,
  $adjacent: Op.adjacent,
  $strictLeft: Op.strictLeft,
  $strictRight: Op.strictRight,
  $noExtendRight: Op.noExtendRight,
  $noExtendLeft: Op.noExtendLeft,
  $and: Op.and,
  $or: Op.or,
  $any: Op.any,
  $all: Op.all,
  $values: Op.values,
  $col: Op.col
};

limit, offset(分页, 限制)

// 获取10个实例/行
Project.findAll({ limit: 10 })

// 跳过8个实例/行
Project.findAll({ offset: 8 })

// 跳过5个实例,然后取5个
Project.findAll({ offset: 5, limit: 5 })

常用查询方法

findAll

Model.findAll({
  where: {
    attr1: 42,
    attr2: 'cake'
  }
})

findOne

Model.findOne({
  where: {
    attr1: 42,
    attr2: 'cake'
  }
})

count

user_test.count({
    where: {
        id: {
            [Op.or]: [1,2]
        }
    }
});

aggregate聚合查询

//params 字段名，聚合函数名
user_test.aggregate('id', 'sum',{
    where:{
        id:{
            [Op.or]:[1,2]
        }
    },
});

findAndCountAll分页

const ret = user_test.findAndCountAll({
    limit:2,
    offset:1,
});
ret.then((val) => {
    console.log(val.count);
    console.log(val.rows);
})

findOrCreate

const ret = user_test.findOrCreate({
    where:{
        uid:10000,
        newUser:1
    },
    defaults:{
        uid:10000,
        newUser:1,
        oldUser:0,
        hk_task:0,
        us_task:0,
        as_task:0,
        free_task:0,
        add_at:123123123,
        update_at:123123123

    }
});
ret[1]===false->find
ret[1]===true->create

max

const ret = await user_test.max('add_at');

min

sum

create

const ret = await user_test.create({
        uid:100002,
        newUser:1,
        oldUser:0,
        hk_task:0,
        us_task:0,
        as_task:0,
        free_task:0,
        add_at:123123123,
        update_at:123123123
    });

bulkCreate

//返回插入结果的数组
const ret = await user_test.bulkCreate([
        {
            uid: 100059,
            newUser: 1,
            oldUser: 1,
            hk_task: 2,
            us_task: 4,
            as_task: 5,
            free_task: 0,
            add_at: 123123123,
            update_at: 123123123
        },
        {
            uid: 100060,
            newUser: 1,
            oldUser: 1,
            hk_task: 2,
            us_task: 4,
            as_task: 5,
            free_task: 0,
            add_at: 123123123,
            update_at: 123123123
        }
    ]);

upsert

//创建或更新一行。如果匹配到主键或唯一约束键时会进行更新。
//更新是返回false，创建时返回true
const ret = await user_test.upsert({
        uid:100002,
        newUser:1,
        oldUser:0,
        hk_task:0,
        us_task:0,
        as_task:0,
        free_task:0,
        add_at:123123123,
        update_at:123123123
    });

update

//批量更新是只需控制where条件就行
//返回数组，只有一个元素，标识更新的数量
const ret = await user_test.update(
        {
            newUser: 0,
            oldUser: 0,
            hk_task: 0,
            us_task: 0,
        },{
            where:{}
        }
    );

describe

//查询表信息
const ret = await user_test.describe();

destory

//返回删除的记录数
const ret = await user_test.destroy({
        where:{
            uid:{
                [Op.or]:[100059,100060,100001]
            }
        }
    });