What is ORM?

Simply speaking, it is the encapsulation of SQL query statements, which enables us to operate the database in the OOP way and generate safe and maintainable SQL code gracefully. Intuitively, it is a mapping relationship between Model and SQL.

sequelize.query (original query)

By default, the function returns two parameters - an array of results, and an object containing metadata (affected rows, and so on). Note that since this is a raw query, metadata (attribute names, etc.) is a specific dialect. Some dialects return the metadata "within" result object as an attribute on the array. However, two parameters will always be returned, but for MSSQL and MySQL, it will be two references to the same object.

 const result1 =  await mysql.query('SELECT id, img_url, url from carousel where status = 1');
 //The return value is an array of two identical elements
 "result1": [
        [
            {
                "id": 1,
                "url": "/ClassDetail?id=4"
            },
            {
                "id": 4,
                "url": "/ClassDetail?id=2"
            }
        ],
        [
            {
                "id": 1,
                "url": "/ClassDetail?id=4"
            },
            {
                "id": 4,
                "url": "/ClassDetail?id=2"
            }
        ]
    ]

There are two solutions

1. Pass a query type to tell you how to format the results later

  const result= await mysql.query('SELECT id, img_url, url from carousel where status = 1', {
    replacements: {},
    type: mysql.QueryTypes.SELECT
  });

1. Transfer model

const Carousel = require('../../models/Carousel');

const result2 = await mysql.query('SELECT id, img_url, url from carousel where status = 1',{ model: Carousel });

replace

The substitution in a query can be done in two different ways: using named parameters (starting with), or by using the? Represents an unnamed parameter of. The substitution is passed in the options object.

• ?

If you pass an array,? Will be replaced in the order they appear in the array

const banner2 = await mysql.query('SELECT id, img_url, url from carousel where id = ?', {
    replacements: [1,4,5,6],
    type: mysql.QueryTypes.SELECT,
});
// Only the data corresponding to the first element subscript of the array is returned

• :

const banner3 = await mysql.query('SELECT id, img_url, url from carousel where id in (:[1,4,5,6])', {
    replacements: {[1,4,5,6]},
    type: mysql.QueryTypes.SELECT,
});
// Return the data that matches the array

// Extension: fuzzy query
sequelize.query('SELECT * FROM users WHERE name LIKE :search_name ',
  { replacements: { search_name: 'ben%'  }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

Reference documents:
Sequenize Chinese document v4 - Raw queries - original query