ZEROMAKE | keep codeing and thinking!
2019-09-19 | orm

sequelize一些陷阱

前言

  • 最近接手一些公司的其它 node 项目,orm 框架从 knex + bookshelf 切换到了 sequelize 了。
  • 在试着像 knex + bookshelf 一样来使用 sequelize 发现了一些问题,记录下来。

模型定义

model/book.js

1
module.exports = (sequelize, DataTypes) => {
2
const Books = sequelize.define('books', {
3
name: DataTypes.STRING
4
});
5
Books.associate = function(models) {
6
const { Books, Tags } = models;
7
Books.hasMany(Tags, {
8
sourceKey: 'id',
9
foreignKey: 'book_id',
10
});
11
};
12
return Books;
13
};

model/tag.js

1
module.exports = (sequelize, DataTypes) => {
2
const Tags = sequelize.define('tags', {
3
name: DataTypes.STRING,
4
book_id: DataTypes.INTEGER
5
});
6
Tags.associate = function(models) {
7
};
8
return Tags;
9
};

model/index.js

1
const fs = require('fs');
2
const path = require('path');
3
const Sequelize = require('sequelize');
4
const basename = path.basename(__filename);
5
const env = process.env.NODE_ENV || 'development';
6
const config = require('../config/config.js')[env];
7
const db = {};
8
const sequelize = new Sequelize(config);
9
10
fs
11
.readdirSync(__dirname)
12
.filter(file => {
13
return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
14
})
15
.forEach(file => {
16
var model = sequelize['import'](path.join(__dirname, file));
17
const name = model.name;
18
db[name[0].toUpperCase() + name.substring(1)] = model;
19
});
20
21
Object.keys(db).forEach(modelName => {
22
if (db[modelName].associate) {
23
db[modelName].associate(db);
24
}
25
});
26
27
db.sequelize = sequelize;
28
db.Sequelize = Sequelize;
29
db.dialect = config.dialect;
30
31
module.exports = db;

config/config.js

1
const defaultConfig = {
2
storage: "./test.db",
3
dialect: "sqlite",
4
define: {
5
// 关闭更新和创建时间
6
timestamps: false,
7
// 字段名格式使用 underscored
8
underscored: true,
9
// 表名与 model 定义一致
10
freezeTableName: true,
11
}
12
};
13
module.exports = {
14
development: defaultConfig,
15
test: defaultConfig,
16
production: defaultConfig,
17
};

seeders/20190920072110-Init.js

1
module.exports = {
2
/**
3
*
4
* @param {import('sequelize').QueryInterface} queryInterface
5
* @param {import('sequelize')} Sequelize
6
*/
7
async up(queryInterface, Sequelize) {
8
await queryInterface.bulkInsert('books', [{
9
name: 'Test',
10
}], {});
11
const [book] = await queryInterface.select(null, 'books', {
12
attributes: ['id'],
13
limit: 1,
14
order: [
15
['id', 'DESC']
16
]
17
});
18
const id = book.id;
19
await queryInterface.bulkInsert('tags', [
20
{
21
name: 'test',
22
book_id: id,
23
},
24
{
25
name: 'tag',
26
book_id: id,
27
}
28
]);
29
},
30
31
/**
32
*
33
* @param {import('sequelize').QueryInterface} queryInterface
34
* @param {import('sequelize')} Sequelize
35
*/
36
async down(queryInterface, Sequelize) {
37
await queryInterface.bulkDelete('tags', null, {});
38
await queryInterface.bulkDelete('books', null, {});
39
}
40
};

一、表关联 + 分页的奇怪子查询嵌套

例如以上的 model 关联情况下,我希望通过 Tag 的字段来筛选出 Book 的列表。

1
const db = require('../models');
2
const { Books, Tags } = db;
3
const Op = db.Sequelize.Op;
4
5
async function main() {
6
const books = await Books.findAll({
7
include: [
8
{
9
model: Tags,
10
required: true,
11
}
12
],
13
limit: 20
14
});
15
console.log(books);
16
}

最终输出的 sql 为一个两层子查询嵌套的语句:

SELECT `books`.*, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`book_id` AS `tags.book_id` FROM (SELECT `books`.`id`, `books`.`name` FROM `books` AS `books` WHERE ( SELECT `book_id` FROM `tags` AS `tags` WHERE (`tags`.`book_id` = `books`.`id`) LIMIT 1 ) IS NOT NULL LIMIT 20) AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id`;

这明显对于我想要的效果不太符合,其中有两次的 tags.id 的过滤,明显是多余的。 通过查找资料发现这个是 sequelize 自带的优化效果,需要设置 subQuery 选项为 false

1
const db = require('../models');
2
const { Books, Tags } = db;
3
const Op = db.Sequelize.Op;
4
5
async function main() {
6
const books = await Books.findAll({
7
subQuery: false,
8
include: [
9
{
10
model: Tags,
11
required: true,
12
}
13
],
14
limit: 20
15
});
16
console.log(books);
17
}

这样手动关闭自动的子查询优化就得到正常的连接查询了:

SELECT `books`.`id`, `books`.`name`, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`book_id` AS `tags.book_id` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id` LIMIT 20;

以上对于单条数据是没有问题,但是如果我使用多个 Tags.id 来查询 Book 的话就有可能出现 Book 列表的数据重复。 当然 sequelize 是已经处理了这个重复的问题了,但是还有很多东西没有处理,例如 count 还有分页使用关联数据进行排序。 还有一对多关联的查询问题。

二、表关联后的总数问题

1
const rawCount = await Books.count();
2
const count = await Books.count({
3
include: [
4
{
5
model: Tags,
6
required: true,
7
}
8
],
9
});
10
console.log(rawCount, count);
11
// 1, 2
SELECT count(`books`.`id`) AS `count` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id`;

使用默认的 count 去查询会出现一对多重复,然后造成 count 的值不正确。

1
const rawCount = await Books.count();
2
const count = await Books.count({
3
subQuery: false,
4
include: [
5
{
6
model: Tags,
7
required: true,
8
}
9
],
10
});
11
console.log(rawCount, count);
12
// 1, 2
SELECT count(`books`.`id`) AS `count` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id`;

就算使用了 subQuery: false 也不能解决这个问题,和上面的结果一样还是不正确,而且在 count 方法里 subQuery 是无效的。

1
const rawCount = await Books.count();
2
const count = await Books.count({
3
subQuery: false,
4
distinct: true,
5
col: 'id',
6
include: [
7
{
8
model: Tags,
9
required: true,
10
}
11
],
12
});
13
console.log(rawCount, count);
SELECT count(DISTINCT(`books`.`id`)) AS `count` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id`;

这里我们可以使用 distinct + col 来生成 SELECT COUNT(DISTINCT(books.id)) AS count 来解决这个问题。 很明显 sequelize 几乎对于这种连接表的情况根本没有处理,我这边写了一个简单的 options 转换方法,不过是基于 GROUP BY 来做的,也许可以考虑从 bookshelf 抄过来。

1
function countOptions(options) {
2
const countOptions = Object.assign({}, options);
3
4
// group use COUNT(DISTINCT(`group`)) 只对需要 group 的做转换
5
if(countOptions.group) {
6
const group = countOptions.group;
7
delete countOptions.group;
8
countOptions.distinct = true;
9
// 处理 col
10
let col = Array.isArray(group) ? group[0] : group;
11
// col
12
if(col.col) {
13
col = col.col;
14
}
15
16
if(typeof col === 'string') {
17
if(col.includes('.')) {
18
col = col.split('.')[1];
19
}
20
}
21
else {
22
col = null;
23
}
24
if(col) {
25
countOptions.col = col;
26
}
27
}
28
// del page, order, attributes 删除掉不需要的配置
29
delete countOptions.offset;
30
delete countOptions.limit;
31
delete countOptions.order;
32
delete countOptions.attributes;
33
34
return countOptions;
35
};

三、表关联后的关联表字段分页+排序问题

1
function query(offset = 0) {
2
return Books.findAll({
3
include: [
4
{
5
model: Tags,
6
required: true,
7
}
8
],
9
offset,
10
limit: 1,
11
subQuery: false,
12
order: [
13
[Tags, 'id', 'DESC']
14
]
15
});
16
}
17
let books = await query();
18
const id = books[0].get('id');
19
books = await query(1);
20
console.log(id, books[0].get('id'));
21
// 1, 1
1
2
SELECT `books`.`id`, `books`.`name`, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`book_id` AS `tags.book_id` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id` ORDER BY `tags`.`id` DESC LIMIT 0, 1;
3
4
SELECT `books`.`id`, `books`.`name`, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`book_id` AS `tags.book_id` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id` ORDER BY `tags`.`id` DESC LIMIT 1, 1;

以上的明明跳过了一条记录但是取出的 book 还是原来那条,这个就是内连接表出现的 book 如果一条有多个 tag 这个 book 也会补全。 上面初始化了一个 book(1) 对应的 tag(1, 2) 一对多的情况下关闭了 subQuery 会造成分页异常。 解决方法为使用 GROUP BY 来汇总去重。

1
function query(offset = 0) {
2
return Books.findAll({
3
attributes: ['id'],
4
include: [
5
{
6
attributes: [],
7
model: Tags,
8
required: true,
9
}
10
],
11
offset,
12
limit: 1,
13
subQuery: false,
14
group: `${Books.name}.id`,
15
});
16
}
17
const books1 = await query();
18
const books2 = await query(1);
19
console.log(books1.length, books2.length);
20
// 1, 0
1
SELECT `books`.`id` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id` GROUP BY `books`.`id` LIMIT 0, 1;
2
3
SELECT `books`.`id` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id` GROUP BY `books`.`id` LIMIT 1, 1;

四、关闭子查询优化出现的关联数据不全

以上处理完了 count 问题还有一些让人难受的问题,比如关闭了 subQuery 的话,使用 findOne 或者 limit 会出现关联的数据不全。

1
let book = await Books.findOne({
2
include: [
3
{
4
model: Tags,
5
required: true,
6
}
7
]
8
});
9
let tags = book.get('tags');
10
const rawLen = tags.length;
11
// 2
12
13
book = await Books.findOne({
14
subQuery: false,
15
include: [
16
{
17
model: Tags,
18
required: true,
19
}
20
]
21
});
22
tags = book.get('tags');
23
const len = tags.length;
24
// 1
1
-- subQuery is close
2
SELECT `books`.`id`, `books`.`name`, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`book_id` AS `tags.book_id` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id` LIMIT 1;
3
-- subQuery is open
4
SELECT `books`.*, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`book_id` AS `tags.book_id` FROM (SELECT `books`.`id`, `books`.`name` FROM `books` AS `books` WHERE ( SELECT `book_id` FROM `tags` AS `tags` WHERE (`tags`.`book_id` = `books`.`id`) LIMIT 1 ) IS NOT NULL ORDER BY `books`.`id` DESC LIMIT 1) AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id` ORDER BY `books`.`id` DESC;

很明显关闭了 subQuery 虽然得到了更好的 sql,但是也破坏了 sequelize 的表连接使得 tags 无法获得全部。 但是这里有一点如果无需查出关联表数据,或者说我只要关联表的一条数据,是可以使用 subQuery: false 的。

五、对于 subQuery 和分页的终极方案

1
async function findItems(pageOptions, options) {
2
const countOptions = Object.assign({}, options);
3
4
// group use COUNT(DISTINCT(`group`))
5
if(countOptions.group) {
6
const group = countOptions.group;
7
delete countOptions.group;
8
countOptions.distinct = true;
9
let col = Array.isArray(group) ? group[0] : group;
10
// col
11
if(col.col) {
12
col = col.col;
13
}
14
15
if(typeof col === 'string') {
16
if(col.includes('.')) {
17
col = col.split('.')[1];
18
}
19
}
20
else {
21
col = null;
22
}
23
if(col) {
24
countOptions.col = col;
25
}
26
}
27
// del page, order, attributes
28
delete countOptions.offset;
29
delete countOptions.limit;
30
delete countOptions.order;
31
delete countOptions.attributes;
32
33
const total = await this.count(countOptions);
34
// page
35
const pageSize = +pageOptions.page_size || 20;
36
const pageNum = +pageOptions.page_num || 1;
37
options.offset = (pageNum- 1) * pageSize;
38
options.limit = pageSize;
39
40
const result = await this.findAll(options);
41
return [result, total];
42
};
43
44
module.exports = function(Sequelize) {
45
Sequelize.Model.findItems = findItems;
46
};

Model 类扩展一个静态方法 findItems 能够处理上面的大部分分页,总数问题,但是无法解决关联数据缺失。

使用示例:

1
const rawCount = await Books.count();
2
const [_, count] = await Books.findItems({
3
page_num: 1,
4
page_size: 20
5
}, {
6
subQuery: false, // 不强制设置 subQuery
7
include: [
8
{
9
model: Tags,
10
required: true,
11
}
12
],
13
group: `${Books.name}.id`,
14
});
15
console.log(rawCount, count);
16
// 1, 1
1
SELECT count(*) AS `count` FROM `books` AS `books`;
2
SELECT count(DISTINCT(`books`.`id`)) AS `count` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id`;
3
SELECT `books`.`id`, `books`.`name`, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`book_id` AS `tags.book_id` FROM `books` AS `books` INNER JOIN `tags` AS `tags` ON `books`.`id` = `tags`.`book_id` GROUP BY `books`.`id` LIMIT 0, 20;

关联数据缺失得和 bookshelf 一样使用两次查询来解决这个问题。

六、更新的 sql 错误

1
const book = await Books.findOne();
2
book.set({
3
name: undefined,
4
});
5
await book.save();

以上代码会报错在不同的数据库错误不一样但是都会出错,mysql: Query was empty, sqlite: SQLITE_ERROR: near "WHERE": syntax error

这个问题我查了蛮久的,很少资料,然后才发现为什么大家都不会出这个错误,公司项目在连接配置里关闭了 timestamps 选项造成了和正常使用 sequelize 不同的问题

这个实际上应该算 bugModel.__prop__.set 时没有处理值为 undefined 的设置,但是在生成 sql 的时候却过滤了造成 sql 语句错误。

处理方法也有很多种一个是开启 timestamps 这样每次更新都会有更新时间字段就不会出错了,还有就是提交 issuessequelize 修。 还有一个紧急方案,那就是 set 前我把所有的 undefined 的去掉就好了。

1
2
function filterObjectUndefined(obj) {
3
const target = {};
4
for(const key in obj) {
5
if(obj[key] !== undefined) {
6
target[key] = obj[key];
7
}
8
}
9
return target;
10
}
11
12
const book = await Books.findOne();
13
book.set(filterObjectUndefined({
14
name: undefined,
15
}));
16
await book.save();

六、参考