Since the original / prepared SQL query is often executed in a simple way, you can use serialize Query method

By default, the function will return two parameters - a result array and an object containing metadata (such as the number of rows affected, etc.) Please note that since this is an original query, the metadata is 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 [results, metadata] = await sequelize.query("UPDATE users SET y = 42 WHERE x = 12");
// The result will be an empty array and the metadata will contain the number of rows affected

Without access to metadata, you can pass a query type to tell subsequent formatting results For example, for a simple selection query, you can:

const { QueryTypes } = require('sequelize');
const users = await sequelize.query("SELECT * FROM `users`", { type: QueryTypes.SELECT });
// We don't need to decompose the result here - the result will be returned directly

There are several other query types available   Learn more about sources

 

The second option is the model If a model is passed, the returned data will be an instance of the model

// Callee is a model definition This allows you to easily map queries to predefined models
const projects = await sequelize.query('SELECT * FROM projects', {
  model: Projects,
  mapToModel: true // If you have any mapped fields, pass true here
});
// Now, each element of 'projects' is an instance of Project

Check Query API reference More parameters in Here are some examples:

const { QueryTypes } = require('sequelize');
await sequelize.query('SELECT 1', {
  // The function (or false) used to record the query
  // Each SQL query that sends the call to the server
  logging: console.log,

  // If plain is true, serialize will return only the first record of the result set 
  // If false, it will return all records
  plain: false,

  // If you do not have a query model definition, please set this item to true
  raw: false,

  // The type of query you are executing The query type affects the format of the result before it is returned
  type: QueryTypes.SELECT
});

// Note that the second parameter is null!
// Even if we declare a called object here,
// raw: true will also replace and return an original object

console.log(await sequelize.query('SELECT * FROM projects', { raw: true }));

"Dotted" attribute and {nest} parameter

If the attribute name of the table contains points, you can change the generated object into a nested object by setting the {nest: true} parameter This can be done by dottie.js It is implemented in the background See below:

1. Do not use nest: true:

const { QueryTypes } = require('sequelize');
const records = await sequelize.query('select 1 as `foo.bar.baz`', {
  type: QueryTypes.SELECT
});
console.log(JSON.stringify(records[0], null, 2));

{
  "foo.bar.baz": 1
}

2. Use nest: true:

const { QueryTypes } = require('sequelize');
const records = await sequelize.query('select 1 as `foo.bar.baz`', {
  nest: true,
  type: QueryTypes.SELECT
});

{
  "foo": {
    "bar": {
      "baz": 1
    }
  }
}

replace

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

• If you pass an array? They will appear in the array in the order they are replaced
• If an object is passed,: key ， will be replaced with the key of the object An exception is thrown if the object contains a key that cannot be found in the query, and vice versa

To use the wildcard operator%, attach it to your substitution The following query matches users whose names begin with 'ben'

const { QueryTypes } = require('sequelize');

await sequelize.query(
  'SELECT * FROM users WHERE name LIKE :search_name',
  {
    replacements: { search_name: 'ben%' },
    type: QueryTypes.SELECT
  }
);

summary

Look at a code example on the official website to illustrate

//1
sequelize.query('SELECT 1', {
  logging: console.log,
  plain: false,
  raw: false,
  type: Sequelize.QueryTypes.SELECT
})

//2
sequelize
  .query('SELECT * FROM projects', { raw: true })
  .then(projects => {
    console.log(projects)
  })
//3
sequelize.query('SELECT * FROM projects WHERE status = ?',
  { replacements: ['active'], type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})
//4
sequelize.query('SELECT * FROM projects WHERE status = :status ',
    { replacements: { status: 'active' },
    type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

• The query function is provided in serialize to directly manipulate native statements

• The function will return two parameters: the result array and the object containing metadata. For mysql, it will return two references to an object.

• The second parameter of the query function is an object, which is described by several common parameters.

  1. pain: if plain is true, serialize returns only the first record result set. If false, all records are returned.
  2. Type: the type of query being executed (see the official website api for details). The query type affects how the results are formatted before they are returned.
  3. raw: query whether the type has a model definition. If your query has no model definition, please set this to true.
  4. logging: console.log records whether the query function will be called to the server for each SQL query sent.

• For the fields after where in the lookup criteria

  1. If you pass an array,? They are replaced in the order they appear in the array.
  2. If an object is passed,: key replaces the key in the object. If the object contains a key not found in the query, a query exception is thrown.

• For replacing the variables after where, you can also use the in keyword to match from the array, or use the wildcard like%. The code is as follows:

//Example of using the official website for the in keyword
sequelize.query('SELECT * FROM projects WHERE status IN(:status) ',
  { replacements: { status: ['active', 'inactive'] }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

//Example of using the official website for the like wildcard keyword
sequelize.query('SELECT * FROM users WHERE name LIKE :search_name ',
  { replacements: { search_name: 'ben%'  }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

• When querying, you can also directly pass the model. If you pass the model, the returned data will be an instance of the model, and other fields above can also be used here

sequelize
  .query('SELECT * FROM projects', {
    model: Projects,
    mapToModel: true // If there are any mapped fields, pass true here
  })
  .then(projects => {
    // Each record will now be an instance of Project
  })