Sequelize ORM 实践
由于sequelize依赖于mysql2所以也需要安装mysql2：
npm install mysql2 --save
创建数据库：
字符集：utf8mb4
排序规则：utf8mb4_genaral_ci
字段数据类型：
const {Sequelize,Model} = require('sequelize')
const {unset, clone, isArray} = require('lodash')
const {
dbName,
host,
port,
user,
password
} = require('../config/config1').database
const sequelize = new Sequelize(dbName,user,password,{
dialect:'mysql',
host,
port,
logging:true,
timezone: '+08:00',
// 个性化配置
define:{
//create_time update_time delete_time
timestamps:true,
paranoid:true, // 开启软删除
createdAt:'created_at', // 自定义字段名，默认为'createdAt'，将其改为'created_at'
updatedAt:'updated_at',
deletedAt:'deleted_at',
underscored:true, // 字段驼峰转下划线
// 禁止修改表名，默认情况下，sequelize将自动将所有传递的模型名称（define的第一个参数）转换为复数
// 但是为了安全着想，复数的转换可能会发生变化，所以禁止该行为
freezeTableName:true,
scopes:{
bh:{ // 过滤不必要的字段（这里会有bug）
attributes:{
exclude:['updated_at','deleted_at','created_at']
}
}
}
}
})
sequelize.sync({
force:false // true 清空数据库表
})
Model.prototype.toJSON= function(){
// let data = this.dataValues
let data = clone(this.dataValues)
unset(data, 'updated_at')
unset(data, 'created_at')
unset(data, 'deleted_at')
for (key in data){
if(key === 'image'){
if(!data[key].startsWith('http'))
data[key]=global.config.host + data[key]
}
}
if(isArray(this.exclude)){
this.exclude.forEach(
(value)=>{
unset(data,value)
}
)
}
// this.exclude
// exclude
// a,b,c,d,e
return data
}
module.exports = {
sequelize
}
数字类型查询比字符串查询快
Sequelize 中文API文档－5. 实例的使用、Instance类介绍：：
- findOne：
async validateEmail(vals) {
const email = vals.body.email
const user = await User.findOne({
where: {
email: email
}
})
if (user) {
throw new Error('email已存在')
}
}
- setDataValue：
static _getEachBookStatus(book, favors){
let count = 0
favors.forEach(favor=>{
if(book.id === favor.art_id){
count = favor.get('count')
}
})
book.setDataValue('fav_nums',count)
return book
}
- count：
static async getMyFavorBookCount(uid) {
const count = await Favor.count({
where: {
type: 400,
uid
}
})
return count
}
increment
decrement
static async getAll(){
const books =await HotBook.findAll({
// 排序
order:[
'index'
]
})
const ids = []
books.forEach((book)=>{
ids.push(book.id)
})
const favors =await Favor.findAll({
where:{
art_id:{
[Op.in]:ids,
},
type:400
},
group:['art_id'], // 排序
attributes:['art_id', [Sequelize.fn('COUNT','*'),'count']]
})
books.forEach(book=>{
HotBook._getEachBookStatus(book, favors)
})
//python 二维矩阵
return books
}
linvalidator:
- isOptional
- alias ？
- parsed ？
lin-cms 不能拦截sequelize的错误？？？
Unhandled rejection SequelizeValidationError: string violation: banner cannot be an array or an object
Unhandled rejection SequelizeDatabaseError: Unknown column 'place_orders_nums' in 'field list'
class ProductDao {
async createGoods (v) {
/** 这里需要创建多个表
* 1：商品表
* 2：规格值表
* 3：商品和规格关系表
* 4：sku表
* 创建商品 */
const goods = new Product();
goods.name = v.get('body.name');
goods.banner = v.get('body.banner');
goods.desc_imgs = v.get('body.descImg');
goods.cate_id = 22;
return goods.save(); // 开始没有加return，所以没捕获到，加了return就可以了
}
}
attributes：
- attributes:['art_id', [Sequelize.fn('COUNT','*'),'count']] // 内置方法
- attributes:决定返回什么字段
static async getAll(){
const books =await HotBook.findAll({
order:[
'index'
]
})
const ids = []
books.forEach((book)=>{
ids.push(book.id)
})
const favors =await Favor.findAll({
where:{
art_id:{
[Op.in]:ids,
},
type:400
// 国画
// 漫画
},
group:['art_id'],
attributes:['art_id', [Sequelize.fn('COUNT','*'),'count']]
})
books.forEach(book=>{
HotBook._getEachBookStatus(book, favors)
})
//python 二维矩阵
return books
}
SQL语句：
async getUserNames (start, count) {
const logs = await db.query(
'SELECT lin_log.user_name AS names FROM lin_log GROUP BY lin_log.user_name HAVING COUNT(lin_log.user_name)>0 limit :count offset :start',
{
replacements: {
start: start * count,
count: count
}
}
);
const arr = Array.from(logs[0].map(it => it['names']));
return arr;
}
Sequelize写入数据库有两种方式：
1、通过实例
save
async updateGroup (ctx, v) {
const id = v.get('path.id');
const exit = await ctx.manager.groupModel.findByPk(id);
if (!exit) {
throw new NotFound({
msg: '分组不存在，更新失败'
});
}
exit.name = v.get('body.name');
exit.info = v.get('body.info');
exit.save();
}
2、通过类方法
create increment
return sequelize.transaction(async t => {
static async like(art_id, type, uid) {
const favor = await Favor.findOne({
where: {
art_id,
type,
uid
}
})
if (favor) {
throw new global.errs.LikeError()
}
return sequelize.transaction(async t => {
await Favor.create({
art_id,
type,
uid
}, {
transaction: t
})
const art = await Art.getData(art_id, type, false)
await art.increment('fav_nums', {
by: 1,
transaction: t
})
})
}
static async addComment(bookID, content){
const comment = await Comment.findOne({
where:{
book_id:bookID,
content
}
})
if(!comment){
// 近似
// 你好酷 你真酷，
return await Comment.create({
book_id: bookID,
content,
nums:1
})
}else{
return await comment.increment('nums', {
by: 1
})
}
}
Sequelize 中文API文档－7. Scopes 作用域的使用：
提前定义好 where 条件，然后将这种定义好的条件又可以重新组合
- 先在define定义：
const sequelize = new Sequelize(dbName,user,password,{
dialect:'mysql',
host,
port,
logging:true,
timezone: '+08:00',
define:{
//create_time update_time delete_time
timestamps:true,
paranoid:true,
createdAt:'created_at',
updatedAt:'updated_at',
deletedAt:'deleted_at',
underscored:true,
freezeTableName:true,
scopes:{
bh:{
attributes:{
exclude:['updated_at','deleted_at','created_at']
}
}
}
}
})
- 后使用：
static async _getListByType(ids, type) {
let arts = []
const finder = {
where: {
id: {
[Op.in]: ids
}
}
}
const scope = 'bh'
switch (type) {
case 100:
arts = await Movie.scope(scope).findAll(finder)
break
case 200:
arts = await Music.scope(scope).findAll(finder)
break
case 300:
arts = await Sentence.scope(scope).findAll(finder)
case 400:
break
default:
break
}
return arts
}
sequelize 的op模块:
async searchLogs (v, keyword) {
const start = v.get('query.page');
const count1 = v.get('query.count');
let condition = {};
v.get('query.name') && set(condition, 'user_name', v.get('query.name'));
v.get('query.start') &&
v.get('query.end') &&
set(condition, 'time', {
[Sequelize.Op.between]: [v.get('query.start'), v.get('query.end')]
});
let { rows, count } = await Log.findAndCountAll({
where: Object.assign({}, condition, {
message: {
[Sequelize.Op.like]: `%${keyword}%`
}
}),
offset: start * count1,
limit: count1,
order: [['time', 'DESC']]
});
return {
rows,
total: count
};
}
事务（transaction）：
const { db } = require('lin-mizar/lin/db');
async createGroup (ctx, v) {
const exit = await ctx.manager.groupModel.findOne({
where: {
name: v.get('body.name')
}
});
if (exit) {
throw new Forbidden({
msg: '分组已存在，不可创建同名分组'
});
}
let transaction;
try {
transaction = await db.transaction();
const group = await ctx.manager.groupModel.create(
{
name: v.get('body.name'),
info: v.get('body.info')
},
{
transaction
}
);
for (const item of v.get('body.auths')) {
const { auth, module } = findMetaByAuth(item);
await ctx.manager.authModel.create(
{
auth,
module,
group_id: group.id
},
{
transaction
}
);
}
await transaction.commit();
} catch (err) {
if (transaction) await transaction.rollback();
}
return true;
}
static async like(art_id, type, uid) {
const favor = await Favor.findOne({
where: {
art_id,
type,
uid
}
})
if (favor) {
throw new global.errs.LikeError()
}
return sequelize.transaction(async t => {
await Favor.create({
art_id,
type,
uid
}, {
transaction: t
})
const art = await Art.getData(art_id, type, false)
await art.increment('fav_nums', {
by: 1,
transaction: t
})
})
}
static async disLike(art_id, type, uid) {
const favor = await Favor.findOne({
where: {
art_id,
type,
uid
}
})
if (!favor) {
throw new global.errs.DislikeError()
}
// Favor 表 favor 记录
return sequelize.transaction(async t => {
await favor.destroy({
force: true,
transaction: t
})
const art = await Art.getData(art_id, type, false)
await art.decrement('fav_nums', {
by: 1,
transaction: t
})
})
}
网友评论