Basic usage of Sequelize

install

$ npm install --save sequelize
# You also need to install one of the following:
$ npm install --save pg pg-hstore  // postgreSql
$ npm install --save mysql // mysql or mariadb
$ npm install --save sqlite3  
$ npm install --save tedious // MSSQL

Establish connection

const Sequelize = require('sequelize')
const sequelize = new Sequelize(db.database, db.user, db.password, { //Table Name User Name Password
    host: db.host, //address
    port: db.port, //port
    dialect: 'mysql', //Database type:'mysql'|'mariadb'|'sqlite'|'postgres'|'mssql'
    pool: { // Connection pool configuration
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000,
    },
    timezone: '+08:00' //Time zone conversion
})

Definition model

const Sequelize = require('sequelize')
const moment=require('moment');
moment.locale('zh-cn');

User: sequelize.define('user', {
    id: {
        type: Sequelize.STRING(255),
        primaryKey: true, //Primary key
    },
    name: Sequelize.STRING,
    role: Sequelize.INTEGER(11),
    open_id: Sequelize.STRING,
    describe: Sequelize.STRING,
    status: Sequelize.INTEGER(11),
    lv: Sequelize.INTEGER(11),
    token: Sequelize.STRING,
    create_time:{
    type: Sequelize.DATE,
        get() {return moment(this.getDataValue('create_time')).format('YYYY-MM-DD HH:mm:ss');}
    },
    update_time:{
        type: Sequelize.DATE,
        get() {return moment(this.getDataValue('update_time')).format('YYYY-MM-DD HH:mm:ss');}
    }
}, {
    freezeTableName: true,
    timestamps: false
})

The corresponding relation between sql and orm

sql	orm
select	findAll,findOne,findById,findOrCreate,findAndCountAll
update	update
insert	create
delete	destroy

query

Query single data

User.findOne({
    attributes: ['id', 'name', 'role', 'open_id', 'describe'],
    where: {
        id: id
    }
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
});

Query multiple

findAll(opts) or all(opts)

User.findAll()

Paging query

Find AndCount (opts) or Find AndCount All

User.findAndCount({
    limit:10,//10 pages per page
    offset:0*10,//Page x*Number of pages per page
    where:{}
});

Query by id

findById(id,opts)

User.findById(1);

Query, create a new one if it does not exist

findOrCreate(opts) or findCreateFind

User.findOrCreate({
    where: {
        open_id: req.body.open_id
    },
    defaults: {
        id: id,
        name: req.body.name,
        open_id: req.body.open_id,
        token: token,
        create_time: Date.now()
    }
}).then(result => {
    //The return value is an array, [json,created] the first is the data queried or created, and the second is whether or not the new identifier is created.
})

Group query

Grouping queries are usually used with aggregation functions, which include:

Aggregate function	function
COUNT()	Number of entries used for statistical recording
SUM()	The sum of the values used to calculate the fields
AVG()	Average value used to calculate field values
MAX	Maximum value used to find query fields
MIX	Minimum values used to find query fields

//Find the sum of the like field values in the table
orm.Article.findAll({
    attributes: [[Sequelize.fn('SUM', Sequelize.col('like')), 'likes']],
}).then(result=>{
    result[0].get('likes')
})

To update

User.update({
    token: 'token'
}, {
    where: {
        id: l
    }
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
});

Newly added

User.create({
    id: id,
    name: req.body.name,
    open_id: req.body.open_id,
    create_time: Date.now()
}).then(result => {
    console.log(result)
    }).catch(err => {
    console.log(err)
});

delete

User.destroy({
    where: {
        id: 1
    }
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
});

Relational query

One-on-one

sequelize provides two one-to-one relational association methods belongsTo and hasOne

User.belongsTo(Article, { foreignKey: 'id', as: 'article',targetKey:'user_id'})
User.hasOne(Article, { foreignKey: 'user_id', as: 'article'})

The first parameter is a Model and the second is options configuration.
foreignKey: Specify foreign keys
as: Specify an alias
targetKey: Target key, which is the column on the target model pointed by the foreign key column on the source model, is the primary key of the target model by default.
Both methods associate the userInfo table with the User table. The difference is that the tables that expose foreign keys are different.

belongsTo exposes the'id'field of the User table as a foreign key to query the UserInfo table
The hasOne method exposes the `user_id'of the Article table as a foreign key to query the User table.

Use

User.findeOne({
    where: {},
    include: {
        model: Article,
        as: 'article'
        where: {}，
        required: false //Filter only the results of include
    }
})

sql generated by belongsTo

SELECT `user`.`id`, `user`.`name`, `article`.`id` AS `article.id`, `article`.`title` AS `article.title`, `article`.`user_id` AS `article.user_id` FROM `user` AS `user` LEFT OUTER JOIN `article` AS `article` ON `user`.`id` = `article`.`user_id` WHERE `user`.`id` = '1';

sql generated by hasOne

SELECT `user`.`id`, `user`.`name`,`article`.`id` AS `article.id`, `article`.`title` AS `article.title`, `article`.`user_id` AS `article.user_id` FROM `user` AS `user` LEFT OUTER JOIN `article` AS `article` ON `user`.`id` = `article`.`user_id` WHERE `user`.`id` = '1';

belongsTo uses User's foreign key as a condition to query Article's primary key
hasOne uses Article's foreign key as a condition to query User's primary key

One to many

hasMany

Many to many

belongToMany

Common Symbolic Operators

Operators	explain
[Op.and]: {a: 5}	AND (a = 5)
[Op.or]: [{a: 5}, {a: 6}]	(a = 5 OR a = 6)
[Op.gt]: 6,	> 6
[Op.gte]: 6,	>= 6
[Op.lt]: 10,	< 10
[Op.lte]: 10,	<= 10
[Op.ne]: 20,	!= 20
[Op.eq]: 3,	= 3
[Op.not]: true,	IS NOT TRUE
[Op.between]: [6, 10],	BETWEEN 6 AND 10
[Op.notBetween]: [11, 15],	NOT BETWEEN 11 AND 15
[Op.in]: [1, 2],	IN [1, 2]
[Op.notIn]: [1, 2],	NOT IN [1, 2]
[Op.like]: '%hat',	LIKE '%hat'
[Op.notLike]: '%hat'	NOT LIKE '%hat'
[Op.iLike]: '%hat'	ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat'	NOT ILIKE '%hat' (PG only)
[Op.startsWith]: 'hat'	LIKE 'hat%'
[Op.endsWith]: 'hat'	LIKE '%hat'
[Op.substring]: 'hat'	LIKE '%hat%'
[Op.regexp]: '^[h	a	t]'	REGEXP/~ '^[h	a	t]' (MySQL/PG only)
[Op.notRegexp]: '^[h	a	t]'	NOT REGEXP/!~ '^[h	a	t]' (MySQL/PG only)
[Op.iRegexp]: '^[h	a	t]'	~* '^[h	a	t]' (PG only)
[Op.notIRegexp]: '^[h	a	t]'	!~* '^[h	a	t]' (PG only)
[Op.like]: { [Op.any]: ['cat', 'hat']}	LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
[Op.overlap]: [1, 2]	&& [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2]	@> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2]	<@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3]	ANY ARRAY[2, 3]::INTEGER (PG only)
[Op.col]: 'user.organization_id'	= "user"."organization_id", with dialect specific column identifiers, PG in this example

const Op = Sequelize.Op;
//Query age < 18 or less than 5
User.findAll({
    where: {
        age: {
            [Op.or]: {
                [Op.lt]: 18,
                [Op.eq]: 5
            }
        }
    }
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
});

Permanent link: https://blog.qianxiaoduan.com/archives/776

Posted by __greg on Mon, 16 Sep 2019 20:06:00 -0700

Programmer Group