MySQL > Sequelize MySQL

Sequelize is the most popular ORM framework for node development. This series of articles will document the smooth and difficult process of learning and using Sequelize

Sequelize Add, delete, and query functions

The query

Example Query a single piece of data

        const user = await ctx.model.User.findOne({
            attributes: ['id'.'name'].// Only id and name fields are displayed
            // Attributes: {exclude: ['role']} // Does not display the role field
            where: {
                id: id
            },
            order: [ / / sorting
                ['showCount'.'DESC']]});// Field renaming: Query properties (fields) can be renamed by passing in a nested data
        attributes: ['id'['name'.'myName']]
        // Rename field name to myName, so that the returned result is myName
Copy the code

Querying multiple pieces of Data

        const user = await ctx.model.User.findAll({
            limit: 10.// 10 entries per page
            offset: 0 * 10.// page x * number per page
            where: {} / / conditions
        });
Copy the code

Paging query

        // Returns the total number of lists
        const { app, ctx } = this;
        const { gt } = app.Sequelize.Op;
        const user = await ctx.model.User.findAndCountAll({
            limit: 10.// 10 entries per page
            offset: 0 * 10.// page x * number per page
            where: { / / conditions
                id: {
                    [gt]: 6 // id > 6}}});Copy the code

Query by ID

        const user = await ctx.model.User.findByPk(1);
Copy the code

Querying individual data

        const user = await ctx.model.User.findOne({
            where: {} / / conditions
        });
Copy the code

Grouping query

Grouped queries are typically used in conjunction with aggregate functions, which look like this

        Select * from num
        const { app, ctx } = this;
        const { fn, col } = app.Sequelize;
        // fn refers to the function
        // col refers to the field
        const user = await ctx.model.User.findOne({
            attributes: [[fn('SUM', col('num')), 'all_num']],
            where: {} / / conditions
        });

        Select sum('num') as 'all_count'...
Copy the code

new

        // If the id is the primary key, the id is not required to be added to the new data
        const user = await ctx.model.User.create({ name, age });
Copy the code

Modify the

        // Check whether the data exists before modifying it
        const user = await ctx.model.User.findByPk(id);

        // If the data exists, modify it
        await user.update({ name, age }, {
            where: {}});Copy the code

delete

        // Check whether the data exists before deleting it
        const user = await ctx.model.User.findByPk(id);

        // If the data exists, modify it
        await user.destroy();
Copy the code

Next time, record the method of associated query.

Record the record!