1.事务
try {
const transaction = await sequelize.transaction();
const user = await User.findOne(..., { transaction });
await user.update(..., { transaction });
await transaction.commit();
} catch {
await transaction.rollback()
}
A syntax for automatically committing or rolling back based on the promise chain resolution is also supported
try {
await sequelize.transaction(transaction => { // Note that we pass a callback rather than awaiting the call with no arguments
const user = await User.findOne(..., {transaction});
await user.update(..., {transaction});
});
// Committed
} catch(err) {
// Rolled back
console.error(err);
}
2.findOne,Query模式
await ctx.model.query(`select id,mark,UNIX_TIMESTAMP(endTime) as endTime from userPractice where id=${userId}`, { type: 'SELECT' }); // type : select,insert,update,delete
3.findAll，findAndCountAll，findByPk，blukcreate,blukInsert,blukdelete
3.1 findAll
Model.findAll({
attributes:你可以使用 sequelize.fn 来做聚合:
Model.findAll({
attributes: [
'foo',
[sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats'],
'bar'
]
});
SELECT foo, COUNT(hats) AS n_hats, bar FROM ... ['foo', ['bar', 'baz'], 'qux']
});
你可以使用 sequelize.fn 来做聚合:
Model.findAll({
attributes: [
'foo',
[sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats'],
'bar'
]
});
SELECT foo, COUNT(hats) AS n_hats, bar FROM ...
3.1findAll({
where: {
userId,
deleted: 'exist'
},
order: [['startTime', 'asc']],
attributes: [['DISTINCT(barId)', 'id']],
})
3.2 await this.ctx.model.QnResource.findAll({
where: { category: 'h5', type, status: 'on', public: 1 },
order: [
['weight', 'DESC'],
['createTime', 'DESC']
],
attributes: ['_key']
})).map(item => item.toJSON());
await ctx.service.playRankInfo.findAll({
where: {
[Op.and]: [
where(fn('date', col('createTime')), date),
],
userId,
},
attributes: [[literal('max(rating)'), 'score'], 'musicId'],
group: ['musicId'],
order: [[literal('max(rating)'), 'asc']],
offset: 0,
limit: 3
});
const data = await ctx.service.playDurationInfo.findAll({
where: {
createTime: {[Op.gt]: dayjs().subtract(6, 'month').format('YYYY-MM-DD')},
userId
},
attributes: [[literal('date(createTime)'), 'day'], [literal('month(createTime)'), 'month'], 'read',
[literal('sum(duration)'), 'duration']],
group: [literal('date(createTime)')],
order: [['createTime', 'desc'] /*, [literal('month(createTime)'), 'desc']*/]
});
User.findAll({
where: {
'$Instruments.size$': { [Op.ne]: 'small' }
},
include: [{
model: Tool,
as: 'Instruments'
}]
});
Generated SQL:
生成的 SQL:
SELECT
`user`.`id`,
`user`.`name`,
`Instruments`.`id` AS `Instruments.id`,
`Instruments`.`name` AS `Instruments.name`,
`Instruments`.`size` AS `Instruments.size`,
`Instruments`.`userId` AS `Instruments.userId`
FROM `users` AS `user`
LEFT OUTER JOIN `tools` AS `Instruments` ON
`user`.`id` = `Instruments`.`userId`
WHERE `Instruments`.`size` != 'small';
区别：
// Inner where, with default `required: true`
await User.findAll({
include: {
model: Tool,
as: 'Instruments',
where: {
size: { [Op.ne]: 'small' }
}
}
});
// Inner where, `required: false`
await User.findAll({
include: {
model: Tool,
as: 'Instruments',
where: {
size: { [Op.ne]: 'small' }
},
required: false //==左连接==
}
});
// Top-level where, with default `required: false`
await User.findAll({
where: {
'$Instruments.size$': { [Op.ne]: 'small' }
},
include: {
model: Tool,
as: 'Instruments'
}
});
// Top-level where, `required: true`
await User.findAll({
where: {
'$Instruments.size$': { [Op.ne]: 'small' }
},
include: {
model: Tool,
as: 'Instruments',
required: true
}
});
Generated SQLs, in order:
按顺序生成的 SQLs:
-- Inner where, with default `required: true`
SELECT [...] FROM `users` AS `user`
INNER JOIN `tools` AS `Instruments` ON
`user`.`id` = `Instruments`.`userId`
AND `Instruments`.`size` != 'small';
-- Inner where, `required: false`
SELECT [...] FROM `users` AS `user
LEFT OUTER JOIN `tools` AS `Instruments` ON
`user`.`id` = `Instruments`.`userId`
AND `Instruments`.`size` != 'small';
-- Top-level where, with default `required: false`
SELECT [...] FROM `users` AS `user`
LEFT OUTER JOIN `tools` AS `Instruments` ON
`user`.`id` = `Instruments`.`userId`
WHERE `Instruments`.`size` != 'small';
-- Top-level where, `required: true`
SELECT [...] FROM `users` AS `user`
INNER JOIN `tools` AS `Instruments` ON
`user`.`id` = `Instruments`.`userId`
WHERE `Instruments`.`size` != 'small';
User.findAll({
include: [
{
model: Grant,
include: [User, Profile]
},
{
model: Profile,
include: {
model: User,
include: {
model: Grant,
include: [User, Profile]
}
}
}
]
});
await Foo.findAll({
include: {
model: Bar,
limit: 2,
include: [{
model: Baz,
limit: 2,
attributes: {
exclude: ['name'] //除了Name属性不显示
},
include: Qux
}]
}
});
3.3 Post.findAll({
attributes: {
include: [
[
sequelize.literal(`(
SELECT COUNT(*)
FROM reactions AS reaction
WHERE
reaction.postId = post.id
AND
reaction.type = "Laugh"
)`),
'laughReactionsCount'
]
]
},
order: [
[sequelize.literal('laughReactionsCount'), 'DESC']
]
});
以上提供了以下输出:
[
{
"id": 1,
"content": "Hello World",
"laughReactionsCount": 1
},
{
"id": 2,
"content": "My Second Post",
"laughReactionsCount": 3
}
]
3.2 findAndCountAll
const { count, rows } = await Project.findAndCountAll({
where: {
title: {
[Op.like]: 'foo%'
}
},
offset: 10,
limit: 2
});
console.log(count);
console.log(rows);
const result = await Model.findAndCountAll({
where: ...,
limit: 12,
offset: 12
});
# In the above example, `result.rows` will contain rows 13 through 24, while `result.count` will return the total number of rows that matched your query.
# When you add includes, only those which are required (either because they have a where clause, or because `required` is explicitly set to true on the include) will be added to the count part.
# Suppose you want to find all users who have a profile attached:
User.findAndCountAll({
include: [
{ model: Profile, required: true}
],
limit: 3
});
# Because the include for `Profile` has `required` set it will result in an inner join, and only the users who have a profile will be counted. If we remove `required` from the include, both users with and without profiles will be counted
3.3 .bulkInsert,bulkDelete
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('Users', [{
firstName: 'John',
lastName: 'Doe',
email: 'example@example.com',
createdAt: new Date(),
updatedAt: new Date()
}]);
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('Users', null, {});
}
};
3.4 findByPk
const ourUser = await User.findByPk(1, {
include: [User.associations.projects],
rejectOnEmpty: true, // Specifying true here removes `null` from the return type!
});
// Note the `!` null assertion since TS can't know if we included
// the model or not
console.log(ourUser.projects![0].name);
async function doStuff() {
const instance = await UserModel.findByPk(1, {
rejectOnEmpty: true,
});
console.log(instance.id);
}
const project = await Project.findByPk(123);
if (project === null) {
console.log('Not found!');
} else {
console.log(project instanceof Project); // true
// Its primary key is 123
}
3.5 findOne
const project = await Project.findOne({ where: { title: 'My Title' } });
if (project === null) {
console.log('Not found!');
} else {
console.log(project instanceof Project); // true
console.log(project.title); // 'My Title'
}
3.6 destory
// Delete everyone named "Jane"
await User.destroy({
where: {
firstName: "Jane"
}
});
To destroy everything the TRUNCATE SQL can be used:
为了摧毁 TRUNCATE SQL 所能使用的一切:
// Truncate the table
await User.destroy({
truncate: true
});
3.7 blukCreate
const captains = await Captain.bulkCreate([
{ name: 'Jack Sparrow' },
{ name: 'Davy Jones' }
]);
console.log(captains.length); // 2
console.log(captains[0] instanceof Captain); // true
console.log(captains[0].name); // 'Jack Sparrow'
console.log(captains[0].id);
4.一对一，多对一关系model定义
https://sequelize.org/master/manual/assocs.html#implementation
4.1 Defining the Sequelize associations
The four association types are defined in a very similar way. Let's say we have two models, A and B. Telling Sequelize that you want an association between the two needs just a function call:
const A = sequelize.define('A', /* ... */);
const B = sequelize.define('B', /* ... */);
A.hasOne(B); // A HasOne B
A.belongsTo(B); // A BelongsTo B
A.hasMany(B); // A HasMany B
A.belongsToMany(B, { through: 'C' }); // A BelongsToMany B through the junction table C
They all accept an options object as a second parameter (optional for the first three, mandatory for belongsToMany containing at least the through property):
A.hasOne(B, { /* options */ });
A.belongsTo(B, { /* options */ });
A.hasMany(B, { /* options */ });
A.belongsToMany(B, { through: 'C', /* options */ });
4.2
const Movie = sequelize.define('Movie', { name: DataTypes.STRING });
const Actor = sequelize.define('Actor', { name: DataTypes.STRING });
const ActorMovies = sequelize.define('ActorMovies', {
MovieId: {
type: DataTypes.INTEGER,
references: {
model: Movie, // 'Movies' would also work
key: 'id'
}
},
ActorId: {
type: DataTypes.INTEGER,
references: {
model: Actor, // 'Actors' would also work
key: 'id'
}
}
});
Movie.belongsToMany(Actor, { through: ActorMovies });
Actor.belongsToMany(Movie, { through: ActorMovies });
Belongs-To-Many 在 through model 上创建了一个独特的键。可以使用 uniqueKey 选项重写此唯一键名。若要防止创建此唯一键，请使用 unique: false 选项。
Project.belongsToMany(User, { through: UserProjects, uniqueKey: 'my_custom_unique' })
Goods.hasMany(GoodsCompose, {
foreignKey: 'goodsId', sourceKey: 'id', as: 'goodsComposes'
})
GoodsCompose.belongsTo(Goods, {
foreignKey: 'goodsId', targetKey: 'id', as: 'goods'
})
A.belongsToMany (b)涉及一个额外的表(连接表) ，因此 sourceKey 和 targetKey 都是可用的，sourceKey 对应于 a (源)中的某个字段，targetKey 对应于 b (目标)中的某个字段。
//多对多
为了提供最大的灵活性，让我们在这里再次使用超多对多关系结构:
// Super Many-to-Many relationship between Player and GameTeam
const PlayerGameTeam = sequelize.define('PlayerGameTeam', {
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false
}
});
Player.belongsToMany(GameTeam, { through: PlayerGameTeam });
GameTeam.belongsToMany(Player, { through: PlayerGameTeam });
PlayerGameTeam.belongsTo(Player);
PlayerGameTeam.belongsTo(GameTeam);
Player.hasMany(PlayerGameTeam);
GameTeam.hasMany(PlayerGameTeam);
//完整例子
const { Sequelize, Op, Model, DataTypes } = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:', {
define: { timestamps: false } // Just for less clutter in this example
});
const Player = sequelize.define('Player', { username: DataTypes.STRING });
const Team = sequelize.define('Team', { name: DataTypes.STRING });
const Game = sequelize.define('Game', { name: DataTypes.INTEGER });
// We apply a Super Many-to-Many relationship between Game and Team
const GameTeam = sequelize.define('GameTeam', {
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false
}
});
Team.belongsToMany(Game, { through: GameTeam });
Game.belongsToMany(Team, { through: GameTeam });
GameTeam.belongsTo(Game);
GameTeam.belongsTo(Team);
Game.hasMany(GameTeam);
Team.hasMany(GameTeam);
// We apply a Super Many-to-Many relationship between Player and GameTeam
const PlayerGameTeam = sequelize.define('PlayerGameTeam', {
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false
}
});
Player.belongsToMany(GameTeam, { through: PlayerGameTeam });
GameTeam.belongsToMany(Player, { through: PlayerGameTeam });
PlayerGameTeam.belongsTo(Player);
PlayerGameTeam.belongsTo(GameTeam);
Player.hasMany(PlayerGameTeam);
GameTeam.hasMany(PlayerGameTeam);
(async () => {
await sequelize.sync();
await Player.bulkCreate([
{ username: 's0me0ne' },
{ username: 'empty' },
{ username: 'greenhead' },
{ username: 'not_spock' },
{ username: 'bowl_of_petunias' }
]);
await Game.bulkCreate([
{ name: 'The Big Clash' },
{ name: 'Winter Showdown' },
{ name: 'Summer Beatdown' }
]);
await Team.bulkCreate([
{ name: 'The Martians' },
{ name: 'The Earthlings' },
{ name: 'The Plutonians' }
]);
// Let's start defining which teams were in which games. This can be done
// in several ways, such as calling `.setTeams` on each game. However, for
// brevity, we will use direct `create` calls instead, referring directly
// to the IDs we want. We know that IDs are given in order starting from 1.
await GameTeam.bulkCreate([
{ GameId: 1, TeamId: 1 }, // this GameTeam will get id 1
{ GameId: 1, TeamId: 2 }, // this GameTeam will get id 2
{ GameId: 2, TeamId: 1 }, // this GameTeam will get id 3
{ GameId: 2, TeamId: 3 }, // this GameTeam will get id 4
{ GameId: 3, TeamId: 2 }, // this GameTeam will get id 5
{ GameId: 3, TeamId: 3 } // this GameTeam will get id 6
]);
// Now let's specify players.
// For brevity, let's do it only for the second game (Winter Showdown).
// Let's say that that s0me0ne and greenhead played for The Martians, while
// not_spock and bowl_of_petunias played for The Plutonians:
await PlayerGameTeam.bulkCreate([
// In 'Winter Showdown' (i.e. GameTeamIds 3 and 4):
{ PlayerId: 1, GameTeamId: 3 }, // s0me0ne played for The Martians
{ PlayerId: 3, GameTeamId: 3 }, // greenhead played for The Martians
{ PlayerId: 4, GameTeamId: 4 }, // not_spock played for The Plutonians
{ PlayerId: 5, GameTeamId: 4 } // bowl_of_petunias played for The Plutonians
]);
// Now we can make queries!
const game = await Game.findOne({
where: {
name: "Winter Showdown"
},
include: {
model: GameTeam,
include: [
{
model: Player,
through: { attributes: [] } // Hide unwanted `PlayerGameTeam` nested object from results
},
Team
]
}
});
console.log(`Found game: "${game.name}"`);
for (let i = 0; i < game.GameTeams.length; i++) {
const team = game.GameTeams[i].Team;
const players = game.GameTeams[i].Players;
console.log(`- Team "${team.name}" played game "${game.name}" with the following players:`);
console.log(players.map(p => `--- ${p.username}`).join('
'));
}
})();
5.延时加载，即时加载
const Ship = sequelize.define('ship', {
name: DataTypes.TEXT,
crewCapacity: DataTypes.INTEGER,
amountOfSails: DataTypes.INTEGER
}, { timestamps: false });
const Captain = sequelize.define('captain', {
name: DataTypes.TEXT,
skillLevel: {
type: DataTypes.INTEGER,
validate: { min: 1, max: 10 }
},
type: {
type: DataTypes.STRING(20),
allowNull: true,
comment: '',
},
plat: {
type: DataTypes.STRING(20),
allowNull: true,
comment: '',
},
state: {
type: DataTypes.ENUM,
values: ['dimission', 'active'],
allowNull: false,
defaultValue: 'active',
comment: '人员户目前状态（离职，在职）',
}
}, { timestamps: false });
Captain.hasOne(Ship);
Ship.belongsTo(Captain);
5.1延时加载
const awesomeCaptain = await Captain.findOne({
where: {
name: "Jack Sparrow"
}
});
// Do stuff with the fetched captain
console.log('Name:', awesomeCaptain.name);
console.log('Skill Level:', awesomeCaptain.skillLevel);
// Now we want information about his ship!
const hisShip = await awesomeCaptain.getShip();
// Do stuff with the ship
console.log('Ship Name:', hisShip.name);
console.log('Amount of Sails:', hisShip.amountOfSails);
5.2即时加载
const awesomeCaptain = await Captain.findOne({
where: {
name: "Jack Sparrow"
},
include: Ship
});
// Now the ship comes with it
console.log('Name:', awesomeCaptain.name);
console.log('Skill Level:', awesomeCaptain.skillLevel);
console.log('Ship Name:', awesomeCaptain.ship.name);
console.log('Amount of Sails:', awesomeCaptain.ship.amountOfSails);
await this.model.findAll({
attributes: ['id', 'name', 'compose', 'discount'],
where: {
compose: 'topup',
},
include: [{
model: GoodsCompose,
as: 'goodsComposes',
}]})
await this.service.mktExam.findOne({
where: { id },
include: [{
model: MktExamQuestionLib, as: 'mktExamQuestionLibs',
include: [{
model: MktQuestionLib, as: 'mktQuestionLib',
include: [{ model: MktQuestionOpt, as: 'mktQuestionOpts' }],
}]
}]
})
6.sequelize数据库查询的Op方法条件查询
const { Op } = require("sequelize");
Post.findAll({
where: {
[Op.and]: [
{ authorId: 12 },
{ status: 'active' }
]
}
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';
An OR can be easily performed in a similar way:
一个或者可以很容易地以类似的方式执行:
const { Op } = require("sequelize");
Post.findAll({
where: {
[Op.or]: [
{ authorId: 12 },
{ authorId: 13 }
]
}
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
Since the above was an OR involving the same field, Sequelize allows you to use a slightly different structure which is more readable and generates the same behavior:
因为上面的是一个涉及相同字段的 OR，Sequelize 允许你使用稍微不同的结构，这个结构更具可读性，并产生相同的行为:
const { Op } = require("sequelize");
Post.destroy({
where: {
authorId: {
[Op.or]: [12, 13]
}
}
});
// DELETE FROM post WHERE authorId = 12 OR authorId = 13;
const op = models.Sequelize.Op;
let {age} = req.query;
let user = await models.User.findAll({
where: {
age: {
[op.between]: [0, 24] // 查询年龄在0-24岁的
}
}
})
[Op.like]: 'Foo %'
adjacent: 邻近的 [Op.adjacent]: [1, 2]
all：所有 [Op.gt]: { [Op.all]: literal('SELECT 1') }
and：并且 [Op.and]: {a: 5}
any：任意 [Op.any]: [2,3]
between：之间 [Op.between]: [10, 20]
col：
contained：
contains：
endsWith：以结束 [Op.endsWith]: 'm'
eq：= 等于 [Op.eq]: 12
gt：> 大于 [Op.gt]: 6
gte：>= 大于等于 [Op.gte]: 6
iLike：
in：查询包含的状态 [Op.in]: [12, 25]
iRegexp：
is：是否，判断类 [Op.is]: null
like：模糊匹配，包含 [Op.like]: '%m%'
lt：< 小于 [Op.lt]: 23
lte：<= 小于等于 [Op.lte]: 10
ne：!= 不等于 [Op.ne]: 23
noExtendLeft：
noExtendRight：
not：非查询 [Op.not]: null
notBetween：不在xx和xx之间的 [Op.notBetween]: [11, 23]
notILike：
notIn：查询不包含的状态 [Op.notIn]: [12, 25]
notIRegexp：
notLike：模糊匹配，不包含 [Op.notLike]: '%m%'
notRegexp：正则，不以开始 [Op.notRegexp]: '^[h|a|t]'
or：或者 where:{ [Op.or]:[ {parent_id:id}, {id:id} ] }
overlap：重叠部分 [Op.overlap]: [1, 2]
placeholder：占位符
regexp：正则，以开始 [Op.regexp]: '^[h|a|t]'
startsWith：字符串，以*开始 [Op.startsWith]: 'j'
strictLeft：
strictRight：
substring：模糊匹配 [Op.substring]: 'oh'
values：
const {or, and, gt, lt} = Sequelize.Op;
Model.findAll({
where: {
name: 'a project',
[or]: [
{id: [1, 2, 3]},
{
[and]: [
{id: {[gt]: 10}},
{id: {[lt]: 100}}
]
}
]
}
});
# WHERE `Model`.`name` = 'a project' AND (`Model`.`id` IN (1, 2, 3) OR (`Model`.`id` > 10 AND `Model`.`id` < 100));
onst {gt, lte, ne, in: opIn} = Sequelize.Op;
Model.findAll({
where: {
attr1: {
[gt]: 50
},
attr2: {
[lte]: 45
},
attr3: {
[opIn]: [1,2,3]
},
attr4: {
[ne]: 5
}
}
})
7.删除
class Post extends Model {}
Post.init({ /* attributes here */ }, {
sequelize,
paranoid: true,
// If you want to give a custom name to the deletedAt column
deletedAt: 'destroyTime'
});
当你调用 destroy 方法时，会发生一个软删除:
await Post.destroy({
where: {
id: 1
}
});
// UPDATE "posts" SET "deletedAt"=[timestamp] WHERE "deletedAt" IS NULL AND "id" = 1
如果你真的想要一个硬删除，而你的模型是偏执型的，你可以使用 force: true 选项:
await Post.destroy({
where: {
id: 1
},
force: true
});
// DELETE FROM "posts" WHERE "id" = 1
如果确实想让查询查看软删除记录，可以将 paranoid: false 选项传递给查询方法。例如:
await Post.findByPk(123); // This will return `null` if the record of id 123 is soft-deleted
await Post.findByPk(123, { paranoid: false }); // This will retrieve the record
await Post.findAll({
where: { foo: 'bar' }
}); // This will not retrieve soft-deleted records
await Post.findAll({
where: { foo: 'bar' },
paranoid: false
})
8.左右连接
注意: 只有在需要是假的时候才尊重右。
User.findAll({
include: [{
model: Task // will create a left join
}]
});
User.findAll({
include: [{
model: Task,
right: true // will create a right join
}]
});
User.findAll({
include: [{
model: Task,
required: true,
right: true // has no effect, will create an inner join
}]
});
User.findAll({
include: [{
model: Task,
where: { name: { [Op.ne]: 'empty trash' } },
right: true // has no effect, will create an inner join
}]
});
User.findAll({
include: [{
model: Tool,
where: { name: { [Op.ne]: 'empty trash' } },
required: false // will create a left join
}]
});
User.findAll({
include: [{
model: Tool,
where: { name: { [Op.ne]: 'empty trash' } },
required: false
right: true // will create a right join
}]
});
要包含所有关联的模型，可以使用 all 和 nested 选项:
// Fetch all models associated with User
User.findAll({ include: { all: true }});
// Fetch all models associated with User and their nested associations (recursively)
User.findAll({ include: { all: true, nested: true }});
9.create的时候建立连接
https://sequelize.org/master/manual/creating-with-associations.html
class Product extends Model {}
Product.init({
title: Sequelize.STRING
}, { sequelize, modelName: 'product' });
class User extends Model {}
User.init({
firstName: Sequelize.STRING,
lastName: Sequelize.STRING
}, { sequelize, modelName: 'user' });
class Address extends Model {}
Address.init({
type: DataTypes.STRING,
line1: Sequelize.STRING,
line2: Sequelize.STRING,
city: Sequelize.STRING,
state: Sequelize.STRING,
zip: Sequelize.STRING,
}, { sequelize, modelName: 'address' });
// We save the return values of the association setup calls to use them later
Product.User = Product.belongsTo(User);
User.Addresses = User.hasMany(Address);
return Product.create({
title: 'Chair',
user: {
firstName: 'Mick',
lastName: 'Broadstone',
addresses: [{
type: 'home',
line1: '100 Main St.',
city: 'Austin',
state: 'TX',
zip: '78704'
}]
}
}, {
include: [{
association: Product.User,
include: [ User.Addresses ]
}]
});
//别名：
const Creator = Product.belongsTo(User, { as: 'creator' });
return Product.create({
title: 'Chair',
creator: {
firstName: 'Matt',
lastName: 'Hansen'
}
}, {
include: [ Creator ]
});
10.子查询只选择需要的字段显示
10.1 但是，如果只需要 through 表的某些属性，可以在 attributes 选项中提供具有所需属性的数组。例如，如果您只想要 through 表中的 selfGranted 属性:
User.findOne({
include: {
model: Profile,
through: {
attributes: ['selfGranted']
}
}
});
结果：{
"id": 4,
"username": "p4dm3",
"points": 1000,
"profiles": [
{
"id": 6,
"name": "queen",
"grant": {
"selfGranted": false
}
}
]
}
10.2 如果您根本不需要嵌套的授权字段，请使用属性: [] :
User.findOne({
include: {
model: Profile,
through: {
attributes: []
}
}
});
Output:
输出:
{
"id": 4,
"username": "p4dm3",
"points": 1000,
"profiles": [
{
"id": 6,
"name": "queen"
}
]
}
11.事务隔离级别
启动事务时可能使用的隔离级别:
const { Transaction } = require('sequelize');
// The following are valid isolation levels:
Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED // "READ UNCOMMITTED"
Transaction.ISOLATION_LEVELS.READ_COMMITTED // "READ COMMITTED"
Transaction.ISOLATION_LEVELS.REPEATABLE_READ // "REPEATABLE READ"
Transaction.ISOLATION_LEVELS.SERIALIZABLE // "SERIALIZABLE"
By default, sequelize uses the isolation level of the database. If you want to use a different isolation level, pass in the desired level as the first argument:
默认情况下，sequelize 使用数据库的隔离级别。如果您想使用不同的隔离级别，请将所需的级别作为第一个参数传入:
const { Transaction } = require('sequelize');
await sequelize.transaction({
isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE
}, async (t) => {
// Your code
});
你也可以全局覆盖 isolationLevel 设置，在 Sequelize 构造函数中有一个选项:
const { Sequelize, Transaction } = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:', {
isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE
});
12.model定义的类型
import { DataTypes } from 'sequelize';
12.1枚举
sequelize.define('foo', {
states: {
type: DataTypes.ENUM,
values: ['active', 'pending', 'deleted'],
defaultValue: 'pending'
}
});
12.2 String
apk: {
type: DataTypes.STRING(32),//不写括号，默认255长度
allowNull: false,
comment: '',
}
12.3 Integer
romIndex: {
type: DataTypes.INTEGER,
allowNull: false,
comment: '',
}
12.4 table和model名字不一样时
class User extends Model {}
User.init({
// ...
}, {
modelName: 'user',
tableName: 'users',
sequelize,
});
12.4 列名和model属性名不一样时
class MyModel extends Model {}
MyModel.init({
userId: {
type: DataTypes.INTEGER,
field: 'user_id'
}
}, { sequelize });
12.5 在model中定义主键
class Collection extends Model {}
Collection.init({
uid: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true // Automatically gets converted to SERIAL for postgres
}
}, { sequelize });
class Collection extends Model {}
Collection.init({
uuid: {
type: DataTypes.UUID,
primaryKey: true
}
}, { sequelize class Collection extends Model {}
Collection.init({
uid: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true // Automatically gets converted to SERIAL for postgres
}
}, { sequelize });
class Collection extends Model {}
Collection.init({
uuid: {
type: DataTypes.UUID,
primaryKey: true
}
}, { sequelize });
id: {
type: DataTypes.INTEGER.UNSIGNED,
autoIncrement: true,
primaryKey: true,
}
)
12.6 在model中定义外键
Organization.belongsTo(User, { foreignKey: 'owner_id' });
User.hasOne(Organization, { foreignKey: 'owner_id' });
// 1:M
Project.hasMany(Task, { foreignKey: 'tasks_pk' });
Task.belongsTo(Project, { foreignKey: 'tasks_pk' });
// N:M
User.belongsToMany(Role, { through: 'user_has_roles', foreignKey: 'user_role_user_id' });
Role.belongsToMany(User, { through: 'user_has_roles', foreignKey: 'roles_identifier' });
12.7 Boolean
Project.init({
columnA: {
type: Sequelize.BOOLEAN,
validate: {
is: ['[a-z]','i'], // will only allow letters
max: 23, // only allow values <= 23
isIn: {
args: [['en', 'zh']],
msg: "Must be English or Chinese"
}
},
field: 'column_a'
// Other attributes here
},
columnB: Sequelize.STRING,
columnC: 'MY VERY OWN COLUMN TYPE'
}, {sequelize})
13.在没有约束的情况下强制执行外键引用
有时您可能希望引用另一个表，而不需要添加任何约束或关联。在这种情况下，您可以手动将引用属性添加到模式定义中，并标记它们之间的关系.
class Trainer extends Model {}
Trainer.init({
firstName: Sequelize.STRING,
lastName: Sequelize.STRING
}, { sequelize, modelName: 'trainer' });
// Series will have a trainerId = Trainer.id foreign reference key
// after we call Trainer.hasMany(series)
class Series extends Model {}
Series.init({
title: Sequelize.STRING,
subTitle: Sequelize.STRING,
description: Sequelize.TEXT,
// Set FK relationship (hasMany) with `Trainer`
trainerId: {
type: DataTypes.INTEGER,
references: {
model: Trainer,
key: 'id'
}
}
}, { sequelize, modelName: 'series' });
/ Video will have seriesId = Series.id foreign reference key
// after we call Series.hasOne(Video)
class Video extends Model {}
Video.init({
title: Sequelize.STRING,
sequence: Sequelize.INTEGER,
description: Sequelize.TEXT,
// set relationship (hasOne) with `Series`
seriesId: {
type: DataTypes.INTEGER,
references: {
model: Series, // Can be both a string representing the table name or a Sequelize model
key: 'id'
}
}
}, { sequelize, modelName: 'video' });
Series.hasOne(Video);
Trainer.hasMany(Series);
14.模型添加索引
Sequelize supports adding indexes to the model definition which will be created on sequelize.sync().
const User = sequelize.define('User', { /* attributes */ }, {
indexes: [
// Create a unique index on email
{
unique: true,
fields: ['email']
},
// Creates a gin index on data with the jsonb_path_ops operator
{
fields: ['data'],
using: 'gin',
operator: 'jsonb_path_ops'
},
// By default index name will be [table]_[fields]
// Creates a multi column partial index
{
name: 'public_by_author',
fields: ['author', 'status'],
where: {
status: 'public'
}
},
// A BTREE index with an ordered field
{
name: 'title_index',
using: 'BTREE',
fields: [
'author',
{
attribute: 'title',
collate: 'en_US',
order: 'DESC',
length: 5
}
]
}
]
});
15.副本集
https://sequelize.org/master/manual/read-replication.html
const sequelize = new Sequelize('database', null, null, {
dialect: 'mysql',
port: 3306
replication: {
read: [
{ host: '8.8.8.8', username: 'read-1-username', password: process.env.READ_DB_1_PW },
{ host: '9.9.9.9', username: 'read-2-username', password: process.env.READ_DB_2_PW }
],
write: { host: '1.1.1.1', username: 'write-username', password: process.env.WRITE_DB_PW }
},
pool: { // If you want to override the options used for the read/write pool you can do so here
max: 20,
idle: 30000
},
})
16.其他辅助包
https://sequelize.org/master/manual/resources.html
17.原始查询
https://blog.csdn.net/awhlmcyn/article/details/79816494
const [results, metadata] = await sequelize.query("UPDATE users SET y = 42 WHERE x = 12");
// Results will be an empty array and metadata will contain the number of affected rows.
const { QueryTypes } = require('sequelize');
const users = await sequelize.query("SELECT * FROM `users`", { type: QueryTypes.SELECT });
// We didn't need to destructure the result here - the results were returned directly
18.排序
Subtask.findAll({
order: [
// Will escape title and validate DESC against a list of valid direction parameters
['title', 'DESC'],
// Will order by max(age)
sequelize.fn('max', sequelize.col('age')),
// Will order by max(age) DESC
[sequelize.fn('max', sequelize.col('age')), 'DESC'],
// Will order by otherfunction(`col1`, 12, 'lalala') DESC
[sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
// Will order an associated model's createdAt using the model name as the association's name.
[Task, 'createdAt', 'DESC'],
// Will order through an associated model's createdAt using the model names as the associations' names.
[Task, Project, 'createdAt', 'DESC'],
// Will order by an associated model's createdAt using the name of the association.
['Task', 'createdAt', 'DESC'],
// Will order by a nested associated model's createdAt using the names of the associations.
['Task', 'Project', 'createdAt', 'DESC'],
// Will order by an associated model's createdAt using an association object. (preferred method)
[Subtask.associations.Task, 'createdAt', 'DESC'],
// Will order by a nested associated model's createdAt using association objects. (preferred method)
[Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
// Will order by an associated model's createdAt using a simple association object.
[{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
// Will order by a nested associated model's createdAt simple association objects.
[{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
],
// Will order by max age descending
order: sequelize.literal('max(age) DESC'),
// Will order by max age ascending assuming ascending is the default order when direction is omitted
order: sequelize.fn('max', sequelize.col('age')),
// Will order by age ascending assuming ascending is the default order when direction is omitted
order: sequelize.col('age'),
// Will order randomly based on the dialect (instead of fn('RAND') or fn('RANDOM'))
order: sequelize.random()
});
Foo.findOne({
order: [
// will return `name`
['name'],
// will return `username` DESC
['username', 'DESC'],
// will return max(`age`)
sequelize.fn('max', sequelize.col('age')),
// will return max(`age`) DESC
[sequelize.fn('max', sequelize.col('age')), 'DESC'],
// will return otherfunction(`col1`, 12, 'lalala') DESC
[sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
// will return otherfunction(awesomefunction(`col`)) DESC, This nesting is potentially infinite!
[sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
]
});
19.查询条件操作符
const Op = Sequelize.Op;
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
};
20. 特殊问题处理
1. findAndCountAll 主模型一条数据，子模型3条数据，最外层count为3，，加参数 distinct:true
2.模型关系代码建立对了，运行总报错，说关系没建立对，可以将代码换文件写入。
21.group语句
1. Order.sum('price', {attributes:['name'], group:'name', plain:false, having:['COUNT(?)>?', 'name', 1]}).then(function(result){
console.log(result);
})
SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;
2. Order.findAll({attributes:['name', [sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'name', having:['COUNT(?)>?', 'name', 1], raw:true}).then(function(result){
console.log(result);
})
const datas = await ctx.model.Data.findAll({
where: { xxx },
group: 'groupId',
});
连接查询及分组：
1.订单表如下
> select * from orders;
+---------+-------------+--------+------------+---------------------+
| orderId | orderNumber | price | customerId | createdOn |
+---------+-------------+--------+------------+---------------------+
| 1 | 00001 | 128.00 | 1 | 2016-11-25 10:12:49 |
| 2 | 00002 | 102.00 | 1 | 2016-11-25 10:12:49 |
| 3 | 00003 | 199.00 | 4 | 2016-11-25 10:12:49 |
| 4 | 00004 | 99.00 | 3 | 2016-11-25 10:12:49 |
+---------+-------------+--------+------------+---------------------+
2.客户表结构如下：
> select * from customers;
+----+-----------+-----+---------------------+---------------------+
| id | name | sex | birthday | createdOn |
+----+-----------+-----+---------------------+---------------------+
| 1 | 张小三 | 1 | 1986-01-22 08:00:00 | 2016-11-25 10:16:35 |
| 2 | 李小四 | 2 | 1987-11-12 08:00:00 | 2016-11-25 10:16:35 |
| 3 | 王小五 | 1 | 1988-03-08 08:00:00 | 2016-11-25 10:16:35 |
| 4 | 赵小六 | 1 | 1989-08-11 08:00:00 | 2016-11-25 10:16:35 |
+----+-----------+-----+---------------------+---------------------+
select c.name, SUM(o.price) AS sum from customers AS c INNER JOIN orders AS o ON o.customerId =c.id GROUP BY c.name;
var include = [{
model: Customer,
required: true,
attributes: ['name'],
}]
Order.findAll({include:include, attributes:[[sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'Customer.name', having:['COUNT(?)>?', 'name', 1], raw:true, rollup:true}).then(function(result){
console.log(result);
})// raw:true ,Set this to true if you don't have a model definition for your query.
Table.findAll({
attributes: ['column1',
sequelize.fn('count', sequelize.col('column2'))],
group: ["Table.column1"]
}).then(function (result) { });
22.Sequelize Aggregate Functions (MIN, MAX, SUM, COUNT, etc.) Examples
Sometimes you may need to use aggregate functions when fetching data from database, such as SUM, COUNT, MIN, MAX, etc. In this tutorial, I'm going to give some examples of how to use aggregate functions with Sequelize.js.
22.1 Database Models and Assocations
For this tutorial, first I define two simple models Item and SaleItem. Each models represent a real table in the database. Item stores the details of items. SaleItem stores data of item sales, with each record represents a sale for an item. For each sales, user can buy multiple amounts of an item, stored in amount column.
(adsbygoogle = window.adsbygoogle || []).push({});
models/Item.js
const mappings = {
id: {
type: Sequelize.UUID,
primaryKey: true,
defaultValue: Sequelize.DataTypes.UUIDV4,
},
name: {
type: Sequelize.TEXT,
allowNull: false,
},
stock: {
type: Sequelize.INTEGER,
allowNull: false,
},
price: {
type: Sequelize.DECIMAL,
allowNull: false,
},
active: {
type: Sequelize.BOOLEAN,
allowNull: false,
defaultValue: true,
},
};
models/SaleItem.js
const mappings = {
id: {
type: Sequelize.UUID,
primaryKey: true,
defaultValue: Sequelize.DataTypes.UUIDV4,
},
itemId: {
type: Sequelize.UUID,
allowNull: false,
},
itemPrice: {
type: Sequelize.TEXT,
allowNull: false,
},
amount: {
type: Sequelize.INTEGER,
allowNull: false,
},
cancelled: {
type: Sequelize.BOOLEAN,
defaultValue: false,
},
};
A SaleItem record belongs to an Item record and each Item may have multiple SaleItem records. We need to define the association.
(adsbygoogle = window.adsbygoogle || []).push({});
models/associations.js
const Item = require('./Item').getModel();
const SalesItem = require('./SaleItem').getModel();
SalesItem.belongsTo(Item, { foreignKey: 'itemId', targetKey: 'id' });
Item.hasMany(SalesItem, { foreignKey: 'itemId', targetKey: 'id' });
22.2 MIN Example
In order to find the price of the cheapest item, we can use MIN clause on price column of `Item` table.
exports.getMinPrice = () => Item.findAll({
attributes: [[sequelize.fn('min', sequelize.col('price')), 'minPrice']],
});
It generates the following query.
SELECT min("price") AS "minPrice" FROM "Items" AS "Item";
The above example returns result in the following format.
[ Item {
dataValues: { minPrice: '5000000' },
_previousDataValues: { minPrice: '5000000' },
_changed: {},
_modelOptions:
{ timestamps: true,
validate: {},
freezeTableName: false,
underscored: false,
underscoredAll: false,
paranoid: false,
rejectOnEmpty: false,
whereCollection: null,
schema: null,
schemaDelimiter: '',
defaultScope: {},
scopes: [],
indexes: [Array],
name: [Object],
omitNull: false,
sequelize: [Object],
hooks: [Object],
uniqueKeys: {} },
_options:
{ isNewRecord: false,
_schema: null,
_schemaDelimiter: '',
raw: true,
attributes: [Array] },
__eagerlyLoadedAssociations: [],
isNewRecord: false } ]
To make the result more readable, add raw: true as the argument.
exports.getMinPrice = () => Item.findAll({
attributes: [[sequelize.fn('min', sequelize.col('price')), 'minPrice']],
raw: true,
});
(adsbygoogle = window.adsbygoogle || []).push({});
Here's the result with raw: true.
[ { minPrice: '5000000' } ]
22.3 MAX Example
The query for finding the price of the most expensive item is very similar to the query for finding the price of the cheapest item, just replace MIN with MAX
exports.getMaxPrice = () => Item.findAll({
attributes: [[sequelize.fn('max', sequelize.col('price')), 'maxPrice']],
raw: true,
});
It generates the following query.
SELECT max("price") AS "maxPrice" FROM "Items" AS "Item";
22.4 COUNT Example
Using COUNT clause, we can find how many times each items have been sold. This is not the total number of item sold. In other word, we ignore the value of amount column. For this case, we simply count how many times an item appear in the SaleItem table
(adsbygoogle = window.adsbygoogle || []).push({});
exports.getItemSaleCount = () => SaleItem.findAll({
attributes: ['itemId', [sequelize.fn('count', sequelize.col('itemId')), 'count']],
group : ['SaleItem.itemId'],
raw: true,
order: sequelize.literal('count DESC')
});
It generates the following query.
SELECT "itemId", count("itemId") AS "count" FROM "SaleItems" AS "SaleItem" GROUP BY "SaleItem"."itemId" ORDER BY count DESC;
22.5 SUM Example
If we want to get the list of best seller items, we need to sum up the value of amount column by using SUM clause.
exports.getBestSellerItems = () => SaleItem.findAll({
attributes: ['itemId', [sequelize.fn('sum', sequelize.col('amount')), 'total']],
group : ['SaleItem.itemId'],
raw: true,
order: sequelize.literal('total DESC')
});
It generates the following query.
SELECT "itemId", sum("amount") AS "total" FROM "SaleItems" AS "SaleItem" GROUP BY "SaleItem"."itemId" ORDER BY total DESC;
22.6 Aggregate + Populate Example
Like the previous example, but now we also populate the Item detail.
exports.getBestSellerItems = () => SaleItem.findAll({
attributes: [[sequelize.fn('sum', sequelize.col('amount')), 'total']],
include : [
{
model : Item,
}
],
group : ['Item.id'],
raw: true,
order: sequelize.literal('total DESC')
});
It generates the following query.
SELECT sum("amount") AS "total", "Item"."id" AS "Item.id", "Item"."name" AS "Item.name", "Item"."stock" AS "Item.stock", "Item"."price" AS "Item.price", "Item"."active" AS "Item.active", "Item"."createdAt" AS "Item.createdAt", "Item"."updatedAt" AS "Item.updatedAt" FROM "SaleItems" AS "SaleItem" LEFT OUTER JOIN "Items" AS "Item" ON "SaleItem"."itemId" = "Item"."id" GROUP BY "Item"."id" ORDER BY total DESC;
That's how to use aggregate functions in Sequelize.js
23.在模型中没有定义关联时
1. include[ {Sequelize.literal( ' INNER JOIN user_report reports ON user.id = reports.user_id' )} ]
2. bank.findAll({
include: [{
model: account,
required: false,
on: {
bank_name: Sequelize.col('bank.name')
}
}]
})
生成的sql: select * from bank left outer join account on account.bank_name = bank.name
24.fn/col/literal函数应用
1.fn/col: attributes: [
[fn("concat", `${imgPre}`, col("imgPath")), "imgPath"],
["imgPath", "key"],
],
2.literal:
await ctx.service.class.findAll({
where: {
numberOfPeople: {
[Op.lt]: literal('classroom.count')
},
},
include: [{
model: Classroom, as: 'classroom', attributes: ['count']
}]
});
25.case...when
var query = {
attributes: [
'userId',
[Sequelize.literal(`MAX(CASE Type WHEN 'Employee' THEN Rate ELSE 0 END)`), 'Employee'],
[Sequelize.literal(`MAX(CASE Type WHEN 'School' THEN Rate ELSE 0 END)`), 'School'],
[Sequelize.literal(`MAX(CASE Type WHEN 'Public' THEN Rate ELSE 0 END)`), 'Public'],
[Sequelize.literal(`MAX(CASE Type WHEN 'Other' THEN Rate ELSE 0 END)`), 'Other'],
],
where: {
user: userId,
Type: ['Employee', 'School', 'Public', 'Other'],
},
group: ['userId'],
raw: true
};
models.invoice.findAll(query).then(result => {
console.log(result);
});
26.literal总结
1.指定列增加值
在 Sequelize 中要实现：
UPDATE user SET age=age + 1 WHERE number > 10;
可以通过 Model.update() 并借助 sequelize 中的顶级方法 sequelize.literal() 来实现：
User.update(
{ age: sequelize.literal("age +1") },
{ where: { number: { $gt: 10 } } }
);
2.DISTINCT
在 Sequelize 中要实现：
SELECT COUNT(DISTINCT group) FROM user WHERE number > 10;
User.findAll({
where: { number: { $gt: 10 } },
attributes: [[sequelize.literal("COUNT(DISTINCT(group))"), "count"]],
}).then(function (list) {
console.log(list[0].count);
});
本文来自官网和网络资源整理，，希望可以帮助到大家。