Sequelize ORM 實(shí)踐
由于sequelize依賴于mysql2所以也需要安裝mysql2：
npm install mysql2 --save
創(chuàng)建數(shù)據(jù)庫：
字符集：utf8mb4
排序規(guī)則：utf8mb4_genaral_ci
字段數(shù)據(jù)類型：
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', // 自定義字段名磺陡，默認(rèn)為'createdAt'脚线，將其改為'created_at'
updatedAt:'updated_at',
deletedAt:'deleted_at',
underscored:true, // 字段駝峰轉(zhuǎn)下劃線
// 禁止修改表名略荡，默認(rèn)情況下丑孩，sequelize將自動將所有傳遞的模型名稱（define的第一個參數(shù)）轉(zhuǎn)換為復(fù)數(shù)
// 但是為了安全著想摄悯，復(fù)數(shù)的轉(zhuǎn)換可能會發(fā)生變化朽缎，所以禁止該行為
freezeTableName:true,
scopes:{
bh:{ // 過濾不必要的字段（這里會有bug）
attributes:{
exclude:['updated_at','deleted_at','created_at']
}
}
}
}
})
sequelize.sync({
force:false // true 清空數(shù)據(jù)庫表
})
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
}
數(shù)字類型查詢比字符串查詢快
Sequelize 中文API文檔－5. 實(shí)例的使用稠集、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) {
/** 這里需要創(chuàng)建多個表
* 1：商品表
* 2：規(guī)格值表
* 3：商品和規(guī)格關(guān)系表
* 4：sku表
* 創(chuàng)建商品 */
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']] // 內(nèi)置方法
- 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寫入數(shù)據(jù)庫有兩種方式：
1、通過實(shí)例
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
};
}
事務(wù)（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: '分組已存在，不可創(chuàng)建同名分組'
});
}
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
})
})
}
