學會 Sequelize，讓你操作數據更絲滑
Sequelize 是一個基於 promise 的 Node.js ORM, 目前支持 Postgres, MySQL, MariaDB, SQLite 以及 Microsoft SQL Server. 它具有強大的事務支持, 關聯關係, 預讀和延遲加載, 讀取複製等功能。
一、前言
本文希望通過下面的知識講解及一些實戰經驗分享，給予即將入門或正在學習的同學一些幫助。
在之前剛接觸 Sequelize 的時候，遇到挺多問題，比如數據的聚合統計應該怎麼做？複雜的排序規則應該怎麼寫？索引這塊應該如何定義？性能如何衡量？等等這些問題，後來經過自己的琢磨及與後臺人員交流探討，這些問題都得予解決。
我們直接進入主題，學習目錄結構如下：
-
一、前言
-
二、入門
-
- 配置
-
- 創建模型
-
- 增刪改查
-
- 查詢條件
-
-
三、進階
-
- 聯表查詢
-
- 分組
-
- 聚合函數
-
4 索引
-
- 事務
-
-
四、實戰演練
-
五、問題思考
二、入門
1. 配置
module.exports = () => {
const config = (exports = {})
config.sequelize = {
//支持的數據庫類型
dialect: 'mysql',
//連接數據庫的主機
host: 'localhost',
//連接數據庫的端口
port: 3306,
//數據庫名稱
database: 'db_test',
//數據庫用戶名
username: 'root',
//數據庫密碼
password: 'xxxxxx',
//設置標準時區
timezone: '+08:00',
//配置
dialectOptions: {
// 時間格式化，返回字符串
dateStrings: true,
typeCast(field, next) {
if (field.type === 'DATETIME') {
return field.string()
}
return next()
}
}
}
return config
}
注：如果沒有加 dialectOptions 配置上的 typeCast 屬性值爲 true的話，返回的時間是 ISO 標準時間日期字符。（如：'2022-04-16T15:02:08.017Z'）
2. 創建模型
2.1 常用數據類型
像數字、字符類型默認都有一些長度的限定，有時候因爲長度問題導致接口出錯，所以需要根據情況而定。
還有其他類型就不一一列了，需要用到的可以參照文檔看看。Sequelize 數據類型 [1]
2.2 定義模型
module.exports = (app) => {
const { STRING, INTEGER, DATE } = app.Sequelize
const model = app.model.define(
'student',
{
id: {
//類型
type: INTEGER(11),
//是否允許爲空
allowNull: false,
//是否爲主鍵
primaryKey: true,
//自動自增
autoIncrement: true,
//備註
comment: '學生id',
},
name: {
type: STRING(50),
allowNull: false,
validate: {
notEmpty: true,
},
comment: '學生姓名',
},
class_id: {
type: INTEGER(11),
allowNull: false,
validate: {
notEmpty: true,
},
comment: '班級',
},
cid: {
type: STRING(50),
allowNull: false,
unique: 'cid',
validate: {
notEmpty: true,
},
comment: '身份證',
}
},
{
// 啓動時間，設置爲ture會自動生成創建和更新時間，默認字段名稱爲createAt、updateAt。
timestamps: true,
//對應的表名將與model名相同
freezeTableName: true,
//表備註
comment: '表1',
//創建時間字段別名或不展示
createdAt: 'createTime',
//更新時間字段別名或不展示
updatedAt: 'updateTime'
}
)
return model
}
使用 model.sync(options) 可自動執行 SQL 語句建表，但這個不建議用，第一這麼做容易出現問題，第二我們的規範也不允許這麼做。
2.3 表字段規範
這裏講的是我們公司的 SQL 規範，先來看一個案例：
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '學生id',
`name` varchar(50) NOT NULL COMMENT '學生姓名',
`cid` varchar(50) NOT NULL COMMENT '身份證',
`class_id` int NOT NULL COMMENT '班級',
`create_time` datetime NOT NULL COMMENT '',
`update_time` datetime NOT NULL COMMENT '更新時間',
`mobile` varchar(50) DEFAULT NULL COMMENT '手機號',
PRIMARY KEY (`id`),
UNIQUE KEY `cid_unique` (`cid`),
KEY `class_id` (`class_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '學生表';審覈結果：
審覈結果
下面講一下基本的設計規範：
1) 禁用關鍵字
在設計的時候注意不要用到關鍵字，比如 name、type、status 等字段。
常見關鍵字：
關鍵字
2) 字段禁止默認值爲 null
從上面的案例中 mobile 字段使用了允許爲 null 值，審覈平臺這塊沒有強限制，但不建議用 null 爲默認值，很容易出現問題。
3) 需要添加字段註釋和表註釋
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '學生id',
`username` varchar(50) NOT NULL COMMENT '學生姓名',
`cid` varchar(50) NOT NULL COMMENT '身份證',
`class_id` int NOT NULL COMMENT '班級',
`create_time` datetime NOT NULL COMMENT '創建時間',
`update_time` datetime NOT NULL COMMENT '更新時間',
`mobile` varchar(50) DEFAULT NULL COMMENT '手機號',
PRIMARY KEY (`id`),
UNIQUE KEY `cid` (`cid`),
KEY `iclass_id` (`class_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '學生表';4）創建時間和更新時間字段
該表中必須含有 create_time 和 update_time 字段，如果沒有該 2 個字段的話審覈會不通過。
5) 索引命名
唯一索引：必須以 uniq_ 前綴命名。
普通索引：必須以 idx_ 前綴命名。
索引命名
6) 其它
以上是常用的基本規範，更多根據自己公司情況而定。
3. 增刪改查
3.1 create
創建數據
const values = {
username: '張三',
age: 18
}
const result = await this.model.create(values)
console.log(result)
3.2 findAll
查詢數據並返回數組
const filter = {
username: '張三',
age: 18
}
const result = await this.model.findAll({
where: filter
})
console.log(result)
3.3 findAndCountAll
查詢數據並返回總數和數組列表
const filter = {
username: '張三',
age: 18
}
const result = await this.model.findAndCountAll({
where: filter
})
console.log(result)
3.4 findOne
查詢某一條數據返回對象
const filter = {
id: 20
}
const result = await this.model.findOne({
where: filter
})
console.log(result)
3.5 count
返回總條數
const total = await this.model.count()
console.log(total)
3.6 update
更新數據
const value = {
id: 2,
username: '張三',
age: 20
}
const result = await this.model.update(value)
console.log(result)
3.7 destroy
刪除數據
const filter = {
id: 1
}
const result = await this.model.destroy({
where: filter
})
console.log(result)
3.8 其它
還有一些其他的方法，具體可以看看 Sequelize 方法文檔 [2]。
4. 查詢條件
這個具體的查詢用法就不一一說了，根據自己需要的查詢條件看下對應的查詢功能即可。
this.Op = this.app.Sequelize.Op
this.model.findAll({
where: {
[Op.and]: [{ a: 5 }, { b: 6 }], // (a = 5) AND (b = 6)
[Op.or]: [{ a: 5 }, { b: 6 }], // (a = 5) OR (b = 6)
someAttribute: {
// 基本
[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. 聯表查詢
1.1 一對一
比如學生表和身份證表，一名學生只會有一個身份證號碼，一張身份證只會對應一名學生。
這種就是一對一的關係，如圖：
一對一
假如學生表作爲主表，身份證表作爲副表，那邊這張副表的外鍵就是學生表的 id。
在使用 Sequelize 庫中，我們想通過學生表關聯到相應的身份證表信息，可通過 hasOne 方法做關聯：
student.hasOne(card, {foreignKey : 'id'});
如果我們想通過身份證表反向關聯到學生表，這是可以用 belongsTo 方法做關聯：
card.belongsTo(student, {foreignKey : 'cid'});
總結一下 hasOne 和 belongsTo 的區別：
1.2 一對多
比如學生表和班級表，一名學生只有一個班級，一個班級由多名學生組成，這種是一對多（多對一）的關係，如圖：
一對多
在使用 Sequelize 庫中，可通過 hasMany 方法做關聯：
class.hasMany(student, {foreignKey : 'class_id'});
1.3 多對多
比如學生表和課程表，一名學生可以有多門課程，一門課程可以由多名學生參與，這種是多對多的關係，如圖：
多對多
在使用 Sequelize 庫中，可通過 belongsToMany 方法做關聯：
const Student = sequelize.define('Student', { name: DataTypes.STRING });
const Course = sequelize.define('Course', { name: DataTypes.STRING });
const StudentCourse = sequelize.define('StudentCourse', {
studentId: {
type: DataTypes.INTEGER,
references: {
model: Student,
key: 'id'
}
},
courseId: {
type: DataTypes.INTEGER,
references: {
model: Course,
key: 'id'
}
}
});
Student.belongsToMany(Course, { through: StudentCourse });
Course.belongsToMany(Student, { through: StudentCourse });
1.4 聯表查詢優劣
優點：聯表查詢用起來很方便，不需要做太多了處理，尤其在 B 端場景會用的比較多。
缺點：對於 C 端這種流量較大場景，使用聯表查詢效率會很低下，可能會導致服務器崩潰，所以現在大多數在 C 端都不採用 SQL 自帶的聯表查詢功能，一般會通過代碼邏輯來處理，大大減少查詢效率。
有興趣的可以瞭解下 mysql 聯表查詢的步驟是怎樣的，這樣對比起來會更加直觀一點。
2. 分組
舉個例子，你想對訂單各個狀態的數量進行一個統計，你應該會怎麼做？
在之前不知道分組這個功能時，我是這麼做的：
const obj = {}
obj.problemAmoumt = await this.model.count()
obj.resolvedAmoumt = await this.model.count({
where: {
problemStatus:2
}
})
return ctx.returnStatus.SUCCESS(obj)
這似乎看起來很粗暴。
後來發現可通過 SQL 語句中的 group 進行一個分組，group 是指定要進行分組的字段，示例如下：
this.model.count({
attributes: ['order_status'],
group: 'order_status'
})
最終轉換成 SQL 語句如下：
SELECT `order_status`, count(*) AS `count` FROM `order_list` AS `order_list` GROUP BY `order_status`;返回結果
[
{
"order_status": 1,
"count": 4
},
{
"order_status": 2,
"count": 2
},
{
"order_status": 3,
"count": 2
}
]思考：
-
分組在應用時會不會有坑？自己在使用時可以看看。
-
如果想對
order_status進行一個別名bbb字段返回，這時的查詢語句應該如何調整？
3. 聚合函數
3.1 count
統計總數，比如統計各個訂單的數量：
this.model.findAll({
attributes: [
'orderStatus',
[sequelize.fn('count', sequelize.col('order_status')), 'total']
],
group: 'orderStatus'
})
3.2 sum
求和，比如統計全部同學的成績總數：
this.model.findAll({
attributes: [
[sequelize.fn('sum', sequelize.col('score')), 'score']
]
})
3.3 max
查詢最大值，比如找到這個分數最高的同學：
this.model.findAll({
attributes: [
[sequelize.fn('max', sequelize.col('score')), 'score']
]
})
3.4 min
查詢最小值，比如找到這個分數最低的同學：
this.model.findAll({
attributes: [
[sequelize.fn('min', sequelize.col('score')), 'score']
]
})
當然這些方法也可以通過 this.model.方法(字段名稱) 來實現，參數爲指定的字段。
4 索引
索引就是一種將數據庫中的記錄按照特殊形式存儲的數據結構。通過索引，能夠提高數據查詢的效率，從而提升服務器的性能。
4.1 主鍵索引
一個表只能由一個主鍵索引，且不爲空。
可通過 primaryKey 屬性來設置主鍵，通常會把 id 設爲該表的主鍵，當然你也可以指定其它字段來作爲主鍵，該字段值必須是唯一的。
const student = app.model.define(
'student',
{
id: {
type: INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true,
default: 10000,
comment: '學生id',
},
name: {
type: STRING(50),
allowNull: false,
validate: {
notEmpty: true,
},
comment: '學生姓名',
}
}
)
return student
4.2 唯一索引
唯一索引的列的值必須唯一，但是允許出現空值。
可通過 unique 屬性設置爲唯一索引，值爲 true 的話，索引名稱會自動生成，也可以設置爲自定義索引名稱。
const student = app.model.define(
'student',
{
id: {
type: INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true,
default: 10000,
comment: '學生id',
},
name: {
type: STRING(50),
allowNull: false,
validate: {
notEmpty: true,
},
comment: '學生姓名',
},
cid: {
type: STRING(50),
allowNull: false,
unique: 'cid_unique',
validate: {
notEmpty: true,
},
comment: '身份證',
}
}
)
return student
4.3 組合索引
用多個列組合構建的索引，這多個列中的值不允許有空值。
const student = app.model.define(
'student',
{
id: {
type: INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true,
default: 10000,
comment: '學生id',
},
name: {
type: STRING(50),
allowNull: false,
validate: {
notEmpty: true,
},
comment: '學生姓名',
},
class_id: {
type: INTEGER(11),
allowNull: false,
validate: {
notEmpty: true,
},
comment: '班級',
},
cid: {
type: STRING(50),
allowNull: false,
unique: 'cid',
validate: {
notEmpty: true,
},
comment: '身份證',
}
},
{
indexes: [{
name: 'name',
fields: ['name']
}, {
name: 'cid',
fields: ['cid']
}]
}
)
return student
4.4 普通索引
用表中的普通列構建的索引，沒有任何限制。
用法只要在 indexed 屬性裏不設置 unique 字段即可。
const student = app.model.define(
'student',
{
id: {
type: INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true,
default: 10000,
comment: '學生id',
},
name: {
type: STRING(50),
allowNull: false,
validate: {
notEmpty: true,
},
comment: '學生姓名',
},
class_id: {
type: INTEGER(11),
allowNull: false,
validate: {
notEmpty: true,
},
comment: '班級',
},
cid: {
type: STRING(50),
allowNull: false,
unique: 'cid',
validate: {
notEmpty: true,
},
comment: '身份證',
}
},
{
indexes: [{
unique: true,
name: 'name',
fields: ['name']
}, {
unique: true,
name: 'class_id',
fields: ['class_id']
}]
}
)
return student
4.5 加與不加索引的區別？
優點：增加索引會提供查詢效率
缺點：增加內存空間
我們來看一條查詢語句：
explain select * from `experience_problem_list` where `experience_problem_list`.`page_type` = 2;先看看沒加索引的執行結果：
表中總共有 16416 條數據，掃描行數也是 16416 條。
再看下加了索引的執行結果：
我們來看下 rows 字段的對比，加了索引的掃描行數只有 4 條，由此可以看到，加索引的查詢效率大大高於普通查詢。
5. 事務
可以理解爲一個事務對應的是一組完整的業務，並且在這個事務中所作的一切操作要麼全部成功，要麼全部失敗，只要有一個操作沒成功，整個事務都將回滾到事務開始前。
這裏簡單放一個官方使用案例吧：
// 首先,我們開始一個事務並將其保存到變量中
const t = await sequelize.transaction();
try {
// 然後,我們進行一些調用以將此事務作爲參數傳遞:
const user = await User.create({
firstName: 'Bart',
lastName: 'Simpson'
}, { transaction: t });
await user.addSibling({
firstName: 'Lisa',
lastName: 'Simpson'
}, { transaction: t });
// 如果執行到此行,且沒有引發任何錯誤.
// 我們提交事務.
await t.commit();
} catch (error) {
// 如果執行到達此行,則拋出錯誤.
// 我們回滾事務.
await t.rollback();
}
四、實戰演練
1. 查詢數據不想返回內置的數據結構，只想單純返回純數據結構怎麼處理？
來看下默認返回查詢的數據結果：
{
count: 8,
rows: [
student {
dataValues: [Object],
_previousDataValues: [Object],
uniqno: 1,
_changed: Set(0) {},
_options: [Object],
isNewRecord: false
}
]
}這裏 rows 裏面對象返回了很多層，在業務處理時可能取值會不太方便，我們可以在調用方法的參數里加上 raw 等於 true，就返回正常的數據格式。
const result = await this.model.findAll({
where: filter,
raw: true
})
返回：
{
count: 8,
rows: [
{
id: 8,
name: '張三',
cid: 'xxx',
createTime: '2022-04-08 18:06:52',
updateTime: '2022-04-08 18:06:52'
}
]
}2. 如果列表篩選涉及到主副表應該怎麼查詢合適？
有幾種方案：
1）直接用 mysql 語句做表關聯即可，但性能比較低下。
2）用代碼邏輯處理，但寫起來可能有點繞，但效率比直接用 mysql 表關聯效率高。
3）如果主表字段固定且不多的話，可以直接冗餘副表中，但如果後續主表加字段的話，副表更新是個問題。
4）採用 ES，將這 2 張表的數據合併同步到 ES 的一張表裏，但 ES 使用場景一般都是量很大的，加上 nodejs 新增其它數據庫交互，有額外的開銷成本。
綜合以上考慮，如果是內部項目，量也不是很多的話，查詢比較複雜的話可以直接採用第一種方法，如果邏輯相對比較簡單，也可以採用第二種方法。
3. 分組 group 遇到的問題。
group 只能填寫已有的查詢字段。比如你表中有這個字段 aaa，但查詢返回的字段 aaa 被過濾了，這時不能以 aaa 進行分組。
4. 如何獲取今日、最近 7 天、一個月的數據？
可通過字符串函數 DATE_FORMAT 實現：
1）首先通過時間選擇器獲取到今日、最近 7 天、一個月的開始時間和結束時間。
2）然後使用對改模型使用 count 進行查詢，這時獲取到的知識該時期的總條數。
3）對改數據進行分組，使用 group 屬性，字段爲創建的時間，但我們創建的時間是包含時分秒的，如果這麼聚合的話，生成的數據並不是我們想到的，我們需要的是針對年月日，所以需要對這個時間進行一個處理。
[
{
"create_time": "2022-04-12 17:02:36",
"count": 1
},
{
"create_time": "2022-04-14 17:02:38",
"count": 1
},
{
"create_time": "2022-04-14 17:03:38",
"count": 1
},
{
"create_time": "2022-04-14 17:03:58",
"count": 1
}
]這個顯然不是我們想要的一個結果。
4）然後函數 DATE_FORMAT，將時間格式化爲年月日。
總體實現如下：
const countArr = await this.model.count({
where: filter,
attributes: [
[sequelize.fn('DATE_FORMAT', sequelize.col('create_time'), '%Y-%m-%d'), 'createTime'],
], group: 'createTime',
})
假設我想獲取 4 月 10 日 - 4 月 14 日的數據，正常返回結構如下：
[
{
"createTime": "2022-04-12",
"count": 1
},
{
"createTime": "2022-04-14",
"count": 3
}
]5）因爲表中只有 12 號和 14 號的數據，10、11、13 號沒有，這塊需要自己對代碼進行一個處理，把其它日期沒有的數據爲 0 即可。
最終希望達到的效果：
[
{
"time": "2022-04-10",
"count": 0
},
{
"time": "2022-04-11",
"count": 0
},
{
"time": "2022-04-12",
"count": 1
},
{
"time": "2022-04-13",
"count": 0
},
{
"time": "2022-04-14",
"count": 3
}
]五、問題思考
- 如果想要獲取一年中每個月的訂單量，應該怎麼查詢？
答案：關注 " 大轉轉 FE"公衆號，回覆" 統計 "、" 一年 " 其中一個即可
- 如何根據查詢條件的順序，返回相應的數據順序？
答案：關注 " 大轉轉 FE"公衆號，，回覆"filed"、" 排序 " 其中一個即可
- 如何進行動態分表？
答案：關注 " 大轉轉 FE"公衆號，回覆" 分表 " 即可。
注：如果您還有其它問題想交流，歡迎在底部留言。
參考資料
[1] Sequelize 數據類型: https://www.sequelize.com.cn/core-concepts/model-basics
[2] Sequelize 方法文檔: https://www.sequelize.com.cn/core-concepts/model-querying-finders
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/9kTtbzo1UEODIW9avI1l0w