Joker's Blog HomeArchivesTagsCategoriesResume
从零开始进阶全栈之数据库(二)
|数据库

本篇主要介绍nodejs中的orm框架 —> Sequelize

概述

基于Promise的ORM(Object Relation Mapping)，是一种数据库中间件支持多种数据库、事务、关联等

中间件是介于应用系统和系统软件之间的一类软件，它使用系统软件所提供的基础服务（功
能），衔接网络上应用系统的各个部分或不同的应用，能够达到资源共享、功能共享的目的。目前，它并没有很严格的定义，但是普遍接受IDC的定义：中间件是一种独立的系统软件服务程序，分布式应用软件借助这种软件在不同的技术之间共享资源，中间件位于客户机服务器的操作系统之上，管理计算资源和网络通信。从这个意义上可以用一个等式来表示中间件：中间件=平台+通信，这也就限定了只有用于分布式系统中才能叫中间件，同时也把它与支撑软件和实用软件区分开来。

安装

1
npm i sequelize mysql2 -S

orm和sql的对照关系

sql orm
select findAll(查询多条),findOne(获取第一个条目),findByPk(findById不支持了),findOrCreate(查询,不存在就新建一个),findAndCountAll(分页查询/查询多条并统计数量)
update update
insert create
delete destroy

数据类型

orm sql
Sequelize.STRING VARCHAR(255)
Sequelize.STRING(1234) VARCHAR(1234)
Sequelize.TEXT TEXT
Sequelize.TEXT(‘tiny’) TINYTEXT
Sequelize.CITEXT CITEXT 仅 PostgreSQL 和 SQLite.
Sequelize.TSVECTOR TSVECTOR 仅 PostgreSQL.
Sequelize.BOOLEAN TINYINT(1)
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.REAL REAL 仅 PostgreSQL.
Sequelize.REAL(11) REAL(11) 仅 PostgreSQL.
Sequelize.REAL(11, 12) REAL(11,12) 仅 PostgreSQL.
Sequelize.DOUBLE DOUBLE
Sequelize.DOUBLE(11) DOUBLE(11)
Sequelize.DOUBLE(11, 10) DOUBLE(11,10)
Sequelize.DATE DATETIME 适用于 mysql / sqlite, 带时区的TIMESTAMP 适用于 postgres
Sequelize.DATE(6) DATETIME(6) 适用于 mysql 5.6.4+. 支持6位精度的小数秒
Sequelize.DATEONLY 不带时间的 DATE

前期准备

  1. 建立连接

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    const Sequelize = require('sequelize')
    const sequelize = new Sequelize(db.database, db.user, db.password, { //表名 用户名 密码
        host: db.host, //地址
        port: db.port, //端口
        dialect: 'mysql', //数据库类型：'mysql'|'mariadb'|'sqlite'|'postgres'|'mssql'
        pool: { // 连接池配置
            max: 5,
            min: 0,
            acquire: 30000,
            idle: 10000,
        },
        timezone: '+08:00' //时区转换
    })

  2. 定义模型

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    const User = sequelize.define('user',{
        id: {
            type: Sequelize.STRING(255),
            primaryKey: true, //主键
            autoIncrement: true,  // 自增长
            allowNull:false  
        },
        name: {
            type: Sequelize.STRING,
            defaultValue: 'name1', //设置默认值
            comment: "姓名"  //注释
        }
        role: Sequelize.INTEGER(11),
    })

  3. 同步数据库

    1
    2
    3
    4
    5
    // 严重：force := true 会强制删除表及数据后重建,请一定慎用！！！
    User.sync({ force: false }).then(() => {});

    // 强制同步：创建表之前先删除已存在的表
    User.sync({ force: true }).then(() => {});

  4. 避免自动生成时间戳字段

    1
    2
    3
    const User = sequelize.define('user',{},{
        timestamps:false
    })

  5. 指定表名

  • freezeTableName:true
  • tableName:’xxx’

前者以modelName作为表名，后者则按其值作为表名
蛇形命名 underscored:true
默认驼峰 命名

  1. 实例用法
  • 更新
    1
    2
    3
    4
    5
    6
    const jane = await User.create({ name: "Jane" });
    console.log(jane.name); // "Jane"
    jane.name = "Ada";
    // 数据库中的名称仍然是 "Jane"
    await jane.save();
    // 现在该名称已在数据库中更新为 "Ada"！
  • 删除
    1
    2
    3
    4
    const jane = await User.create({ name: "Jane" });
    console.log(jane.name); // "Jane"
    await jane.destroy();
    // 现在该条目已从数据库中删除
  • 重载
    1
    2
    3
    4
    5
    6
    const jane = await User.create({ name: "Jane" });
    console.log(jane.name); // "Jane"
    jane.name = "Ada";
    // 数据库中的名称依然是 "Jane"
    await jane.reload();
    console.log(jane.name); // "Jane"

    reload 调用生成一个 SELECT 查询,以从数据库中获取最新数据.

  1. UUID

    1
    2
    3
    4
    5
    6
    7
    id: {
        type: Sequelize.UUID,
        primaryKey: true, //主键
        autoIncrement: true,  // 自增长
        allowNull:false,
        defaultValue: Sequelize.UUIDV1,
    }

  2. Getters & Setters

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
// 定义为属性的一部分
name: {
    type: Sequelize.STRING,
    allowNull: false,
    get() { 
        const fname = this.getDataValue("name");
        const price = this.getDataValue("price");
        const stock = this.getDataValue("stock"); 
        return `${fname}(价格：￥${price} 库存：${stock}kg)`;
    }
}
// 定义为模型选项 options中
{
    getterMethods: {
        amount() {
            return this.getDataValue("stock") + "kg";
        }
    }, 
    setterMethods: {
        amount(val) {
            const idx = val.indexOf('kg');
            const v = val.slice(0, idx);
            this.setDataValue('stock', v);
        }
    }
}
// 通过模型实例触发setterMethods
Fruit.findAll().then(fruits => {
    console.log(JSON.stringify(fruits));
     // 修改amount，触发setterMethods
    fruits[0].amount = '150kg'; 
    fruits[0].save(); 
});

基本增删改查

  1. 增

    1
    2
    3
    4
    5
    const res = User.create({
        name:"joker",
        role:1
    })
    consoole.log('create',res)

  2. 改

    1
    2
    3
    4
    5
    const res = User.update(
        {role:2},
        {where:{id:1}}
    )
    consoole.log('update',res)

  3. 查

    • 单条
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      User.findOne({ 
      attributes: ['id', 'name', 'role'], // 指定需要返回的字段
      where: {
          id: id
      }
      }).then(result => {
          console.log(result)
      }).catch(err => {
          console.log(err)
      });
    • 多条
      1
      2
      3
      4
      5
      6
      const Op = Sequelize.Op

      const res = User.findAll(
          {where:{role:{[Op.lt]:3,[Op.gt]:1}}}
      )
      consoole.log('findAll',res)

  4. 删

    • 方式1
      1
      2
      3
      4
      5
      6
      7
      8
      9
      User.destroy({
          where: {
              id: 1
          }
      }).then(result => {
          console.log(result)
      }).catch(err => {
          console.log(err)
      });
    • 方式2
      1
      2
      3
      4
      5
      6
      7
      8
      9
      User.findOne({
          where: {
              id: 1
          }
      }).then(result => {
      result.destroy()
      }).catch(err => {
          console.log(err)
      });

进阶用法

查询特定属性

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

可以使用嵌套数组来重命名属性

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

添加聚合

1
2
3
4
5
6
7
8
// 获取帽子数量
Model.findAll({
  attributes: {
    include: [
      [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats']
    ]
  }
});

排除某些属性

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

分页查询

1
2
3
4
5
Model.findAndCountAll({
    limit:10,//每页10条
    offset:x*10,//第x页*每页个数
    where:{}
});

查询,不存在就新建一个

1
2
3
4
Model.findOrCreate({
    where: {},
    defaults: {}
})

批量新增

1
2
3
4
const data = [{id: 1, name: '张三'}, {id: 1, name: '李四'}]
Model.bulkCreate(data, {
    raw: true,
}).then(res => res)

排序

1
2
3
4
5
Model.findAll({
    order:[
        ['id','DESC']
    ]
})

DESC表示降序,默认ASC升序
更多用法

实用方法

  • count

    1
    2
    3
    4
    5
    6
    7
    8
    const num = await User.count({
      where: {
        age: {
          [Op.gt]: 25
        }
      }
    });
    // 统计年龄大于25的人数

  • max, min

    1
    2
    3
    await User.max('age'); // 最大年龄
    await User.min('age'); // 最小年龄
    await User.min('age', { where: { age: { [Op.gt]: 5 } } }); // 大于五岁的最小年龄

  • sum

    1
    2
    await User.sum('age'); // 年龄总和
    await User.sum('age', { where: { age: { [Op.gt]: 5 } } }); // 大于五岁的年龄总和

关联查询

一对一

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
ModelA.belongsTo(ModelB,{
    // onDelete: 'RESTRICT',  // RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
    // onUpdate: 'RESTRICT', // RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
    // 一对一关联的默认值, ON DELETE 为 SET NULL 而 ON UPDATE 为 CASCADE
    // foreignKey: 'aId',
    // as:"mobelb"
})
ModelB.hasOne(ModelA,{
    // foreignKey: 'aId',
    // as:"mobela"
})

// 使用
ModelA.findAll({
    where:{},
    include:{
        model: ModelB,
        as: 'mobelb'
        where: {}，
        required: false 
    }
})
// 或
ModelA.findAll({
    where:{
        '$mobelb.id$': { // 跨表查询
          [Sequelize.Op.substring]: ''
        }
    },
    include: [
        'mobelb'
    ]
})

一对多

1
2
ModelA.belongsTo(ModelB)
ModelB.hasMany(ModelA)

多对多

1
2
ModelA.belongToMany(ModelB)
ModelB.hasMany(ModelA)

常用符号运算符

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
[Op.eq]: 3,                              // = 3
[Op.ne]: 20,                             // != 20
[Op.is]: null,                           // IS NULL
[Op.not]: true,                          // IS NOT TRUE
[Op.or]: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)
// 使用方言特定的列标识符 (以下示例中使用 PG):
[Op.col]: 'user.organization_id',        // = "user"."organization_id"
// 数字比较
[Op.gt]: 6,                              // > 6
[Op.gte]: 6,                             // >= 6
[Op.lt]: 10,                             // < 10
[Op.lte]: 10,                            // <= 10
[Op.between]: [6, 10],                   // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15],               // NOT BETWEEN 11 AND 15
// 其它操作符
[Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)
[Op.in]: [1, 2],                         // IN [1, 2]
[Op.notIn]: [1, 2],                      // NOT IN [1, 2]
[Op.like]: '%hat',                       // LIKE '%hat'
[Op.notLike]: '%hat',                    // NOT LIKE '%hat'
[Op.startsWith]: 'hat',                  // LIKE 'hat%'
[Op.endsWith]: 'hat',                    // LIKE '%hat'
[Op.substring]: 'hat',                   // LIKE '%hat%'
[Op.iLike]: '%hat',                      // ILIKE '%hat' (不区分大小写) (仅 PG)
[Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (仅 PG)
[Op.regexp]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (仅 MySQL/PG)
[Op.notRegexp]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (仅 MySQL/PG)
[Op.iRegexp]: '^[h|a|t]',                // ~* '^[h|a|t]' (仅 PG)
[Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (仅 PG)
[Op.any]: [2, 3],                        // ANY ARRAY[2, 3]::INTEGER (仅 PG)
[Op.match]: Sequelize.fn('to_tsquery', 'fat & rat') // 匹配文本搜索字符串 'fat' 和 'rat' (仅 PG)
// 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:
[Op.like]: { [Op.any]: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']

一些踩过的坑

在查询结果中添加自定义属性

1
2
3
4
5
6
7
8
9
10
11
12
13
rlt.rows[i] =rlt.rows[i].toJSON()  // 先进行toJSON 操作  然后才能赋值
rlt.rows[i].xxx = 'xxx'

//不这么做的表现，当然 返回给前端的数据中也没有此字段

console.log(rlt.rows[i]) // 读取不到 midWayPortsArr 
console.log(rlt.rows[i].xxx) // 可以获取到
console.log(rlt.rows[i].hasOwnProperty('xxx')) // true


console.log(rlt.rows[i]) // 读取不到 midWayPortsArr 
console.log(rlt.rows[i].midWayPortsArr) // 可以获取到
console.log(rlt.rows[i].hasOwnProperty('midWayPortsArr')) // true
文章作者: Joker
文章链接: https://qytayh.github.io/2021/09/%E4%BB%8E%E9%9B%B6%E5%BC%80%E5%A7%8B%E8%BF%9B%E9%98%B6%E5%85%A8%E6%A0%88%E4%B9%8B%E6%95%B0%E6%8D%AE%E5%BA%93(%E4%BA%8C)/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Joker's Blog！
全栈之路数据库