sequelize 增加数据库字段_使用Sequelize操作MySQL

由于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

}

数字类型查询比字符串查询快

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:

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

})

}

}

提前定义好 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

}

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

})

})

}