Node.js ORM - Sequelize
基本
概述:基于Promise的ORM(Object Relation Mapping),支持多种数据库、事务、关联等
安装: npm i sequelize mysql2 -S
基本使用:
const Sequelize = require("sequelize");
// 建立连接
const sequelize = new Sequelize("kaikeba", "root", "admin", {
host: "localhost",
dialect: "mysql",
operatorsAliases: false
});
// 定义模型
const Fruit = sequelize.define("Fruit", {
name: {type: Sequelize.STRING(20), allowNull: false},
price: {type: Sequelize.FLOAT, allowNull: false},
stock: {type: Sequelize.INTEGER, defaultValue: 0}
});
// 同步数据库,force: true则会删除已存在表
Fruit.sync().then(() => {
// 添加测试数据
return Fruit.create({name: "香蕉", price: 3.5});
}).then(() => {
// 查询
Fruit.findAll().then(fruits => {
console.log(JSON.stringify(fruits));
});
});
强制同步是什么?如何使用?(会重置已有的表)
Fruit.sync({force: true})
表是默认生成时间戳的,如何去掉?
const Fruit = sequelize.define("Fruit", {}, { timestamps: false });
指定表名: freezeTableName: true 或 tableName:'xxx'
设置前者则以modelName作为表名;设置后者则按其值作为表名。
Getters & Setters有什么作用?:可用于定义伪属性或映射到数据库字段的保护属性
// 定义为属性的一部分
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)`;
}
}
// 定义为模型选项
{
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();
});
如何进行校验?:可以通过校验功能验证模型字段格式、内容,校验会在 create 、 update 和 save 时自动运行
price: {
validate: {
isFloat: {msg: "价格字段请输入数字"},
min: {args: [0], msg:"价格字段必须大于0"}
}
},
stock: {
validate: {
isNumeric: {msg: "库存字段请输入数字"}
}
}
如何进行模型扩展?:可添加模型实例方法或类方法扩展模型
// 添加类级别方法
Fruit.classify = function (name) {
const tropicFruits = ['香蕉', '芒果', '椰子'];
// 热带水果
return tropicFruits.includes(name) ? '热带水果':'其他水果'; };
// 添加实例级别方法
Fruit.prototype.totalPrice = function (count) {
return (this.price * count).toFixed(2);
};
// 使用类方法
['香蕉', '草莓'].forEach(f => console.log(f + '是' + Fruit.classify(f)));
// 使用实例方法
Fruit.findAll().then(fruits => {
const [f1] = fruits;
console.log(`买5kg${f1.name}需要¥${f1.totalPrice(5)}`);
});
增删改查
1增
ret = await Fruit.create({
name: "香蕉",
price: 3.5
})
2删
// 方式1
Fruit.findOne({ where: { id: 1 } }).then(r => r.destroy());
// 方式2
Fruit.destroy({ where: { id: 1 } }).then(r => console.log(r));
3改
// 方式1
Fruit.findById(1).then(fruit => {
fruit.price = 4;
fruit.save().then(() => console.log('update!!!!'));
});
// 方式2
Fruit.update({price: 4}, {where: {id: 1}}).then(r => {
console.log(r);
console.log('update!!!!')
})
4查 op链接(https://www.cnblogs.com/zhaom...)
// 通过属性查询
Fruit.findOne({where: {name: "香蕉"}}).then(fruit => {
// fruit是首个匹配项,若没有则为null
console.log(fruit.get());
});
// 获取数据和总条数
Fruit.findAndCountAll().then(result => {
console.log(result.count);
console.log(result.rows.length);
});
// 查询操作符
const Op = Sequelize.Op;
Fruit.findAll({
// where: { price: { [Op.lt()]:4 }, stock: { [Op.gte()]: 100 } }
where: {price: {[Op.lt]: 4, [Op.gt]: 2}}
}).then(fruits => {
console.log(fruits.length);
});
// 或语句
Fruit.findAll({
// where: { [Op.or]:[{price: { [Op.lt]:4 }}, {stock: { [Op.gte]: 100 }}] }
where: {price: {[Op.or]: [{[Op.gt]: 3}, {[Op.lt]: 2}]}}
}).then(fruits => {
console.log(fruits[0].get());
});
// 分页
Fruit.findAll({offset: 0, limit: 2,})
// 排序
Fruit.findAll({order: [['price', 'DESC']],})
// 聚合
setTimeout(() => {
Fruit.max("price").then(max => {
console.log("max", max)
})
Fruit.sum("price").then(sum => {
console.log("sum", sum);
});
}, 500)
4-2关联查询
关联
//1:N关系
const Player = sequelize.define('player', {name: Sequelize.STRING});
const Team = sequelize.define('team', {name: Sequelize.STRING});
// 会添加teamId到Player表作为外键
Player.belongsTo(Team);// 1端建立关系
Team.hasMany(Player); // N端建立关系
// 同步
sequelize.sync({force: true}).then(async () => {
await Team.create({name: '火箭'});
await Player.bulkCreate([{name: '哈登', teamId: 1}, {name: '保罗', teamId: 1}]);
// 1端关联查询
const players = await Player.findAll({include: [Team]});
console.log(JSON.stringify(players, null, '\t'));
// N端关联查询
const team = await Team.findOne({where: {name: '火箭'}, include: [Player]});
console.log(JSON.stringify(team, null, '\t'));
});
// 多对多关系
const Fruit = sequelize.define("fruit", {name: Sequelize.STRING});
const Category = sequelize.define("category", {name: Sequelize.STRING});
Fruit.FruitCategory = Fruit.belongsToMany(
Category, {through: "FruitCategory"}
);
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。