Sequelize Plus

查看中文文档

Get started

Introduction

Sequelize Plus encapsulate Sequelize, and provides a set of chain programming call methods.

Create a SequelizePlus instance

// define a sequelize model
const UserInfo = sequelize.define('User', {
  // ... attributes
});

let userInfoModel

// create an instance by new SequelizePlus()
const SequelizePlus = require('sequelize-plus')
userInfoModel = new SequelizePlus(UserInfo)

// create an instance by plus() (recommended)
const { plus } = require('sequelize-plus')
userInfoModel =  plus(UserInfo)

Autoload

Import all models of sequelize to ModelManager(for association)

const { ModelManager } = require('sequelize-plus')
ModelManager.autoload(sequelize)	// sequelize instance

Quick example

const collector = await userInfoModel
	.fileds(['id', 'user_name', 'birth'])	// SELECT `id`, `user_name`, `birth`
    .where('user_name', 'like', '%J%')		// WHERE `user_name` LIKE '%J%'
    .where('age', 'gt', 18)					// WHERE `age` > 18
    .page(10, 2)							// LIMIT 10, 1
    .findAll()
const resultSet = collector.items

// code above equals to 
const resultSet = await UserInfo.findAll({
    attributes: ['id', 'user_name', 'birth'],
    where: {
        'user_name': {
            [Op.like]: '%J%'
        },
        'age': {
            [Op.gt]: 18
        }
    },
    limit: 10,
    offset: 1
})

Association

Simple association

// assume that UserModel, UserProfile, userVipModel are sequelize model classes
const { plus } = require('sequelize-plus')
const userModel = plus(UserModel)
const userProfileModel = plus(UserProfileModel)
const userVipModel = plus(UserVipModel)

// default alias: combine upper case chars in model name and turn to lower case.
userModel.innerJoin(userProfileModel)

// custom alias
userModel.leftJoin(userProfileModel, 'up')

// set where
userModel.rightJoin(userVipModel, 'uv', {
    expireTime: ['lt', '2021-04-30 00:00:00']
})

Complex association

// leftJoin(models: Array, alias: string)
userModel
	.leftJoin(userVipModel, 'uv')
	.leftJoin([userSupremeVipModel, userVipModel], 'usv')

Fields selection

We assume that table user contains id, user_name and age field, corresponding model User contains id, userName and age; table user_profile contains id, user_id and address and company_name, corresponding model UserProfile contains id, userId, address and companyName.

No association

// filed(name, alias)
userModel.field('userName')				// SELECT `user_name` AS `userName`
userModel.field('userName', 'name')		// SELECT `user_name` AS `name`

const { fn, col } = require('sequelize')
userModel.field(fn('MAX', col('age')), 'maxAge')
// SELECT MAX(`age`) AS 'maxAge'

// fields(fields)
userModel.fields([
    'age', 'userName'
]) 	// SELECT `age`, `user_name` AS `userName`
userModel.fields({
    // alias => field name
    userAge: 'age',
    name: 'userName'
})	// SELECT `age` AS `userAge`, `name` AS `userName`

With associations

userModel.innerJoin('UserProfile', 'up')

// fields(model, fields)
userModel.fields('userProfile', ['address', 'companyName'])
// SELECT `up`.`address`, `up`.`company_name`
userModel.fields('userProfile', {
    name: 'companyName'
})	// SELECT `up`.`company_name` AS 'name'
userModel.fields('up', { ... })	// the same as above

冲突检测

// 冲突检测默认关闭，需要开启冲突检测
SequelizePlus.settings.set('checkFieldNameConflict', true)

userModel
    .leftJoin('UserProfle')
    .field('id')
	.field('up.id')
// Error: Field name conflict: id

Query condition(where)

No association

// setWhere(fieldName, value)
// setWhere(fieldName, operator, value)
userModel.setWhere('name', 'James')			// WHERE `name` = `James`
userModel.setWhere('name', 'like', '%J%')	// WHERE `name` LIKE `%J%`

// where(pk)
userModel.where(1055)		// WHERE `id` = 1055
// where(fieldName, value)
userModel.where('age', 20)	// WHERE `age` = 20
// where(fieldName, operator, value)
userModel.where('age', 'gte', 20)		// WHERE `age` >= 20
// where(Object)
userModel.where({
    name: 'James',
    age: ['gte', 20],
})

With association

userModel.leftJoin(UserVipModel, 'uv')
userModel.leftJoin([UserSupremeVipModel, UserVipModel], 'usv')

userModel.where('usv.expireTime', 'lt', '2021-4-30 00:00:00')
userModel.where('uv.expireTime', {
  lt: '2021-4-30 00:00:00',
  gte: '2021-4-29 00:00:00',
})
console.log(userModel.getOption('where'))
// {
//   '$uv.usv.expire_time$': { [Symbol(lt)]: '2021-4-30 00:00:00' },
//   '$uv.expire_time$': {
//     [Symbol(lt)]: '2021-4-30 00:00:00',
//     [Symbol(gte)]: '2021-4-29 00:00:00'
//   }
// }

userModel.clearOption()
userModel.where$('usv', {
  level: 5,
  expireTime: ['gt', '2021-4-30 00:00:00'],
  id: { in: [100, 101] },
})
console.log(userModel.getOption('where'))
// {
//   '$uv.usv.level$': 5,
//   '$uv.usv.expire_time$': { [Symbol(gt)]: '2021-4-30 00:00:00' },
//   '$uv.usv.id$': { [Symbol(in)]: [ 100, 101 ] }
// }

Aggregation

const func = async () => {
    // count all
  	const count = await userModel.count()
  	console.log(count)

    // get max value
  	const maxAge = await userModel.max('age')
  	console.log(maxAge)

    // get min value
  	const minAge = await userModel.min('age')
  	console.log(minAge)

    // get average value
  	const avgAge = await userModel.avg('age')
  	console.log(avgAge)

    // get summation value
  	const sumAge = await userModel.sum('age')
  	console.log(sumAge)
}

More

// order(field: string, sort: 'asc'|'ASC'|'desc'|'DESC')
userModel.order('id', 'DESC')
// order(items: array)
userModel.order([ ['userName', 'ASC'], ['Age', 'DESC'] ])

// page(pageSize: number, pageNumber: number = 1)
userModel.page(10, 2)
// { limit: 10, offset: 10 }

// group(field)
userModel.group('userName')