Raw queries

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

    In cases where you don't need to access the metadata you can pass in a query type to tell sequelize how to format the results. For example, for a simple select query you could do:

    1. sequelize.query("SELECT * FROM `users`", { type: sequelize.QueryTypes.SELECT})
    2.   .then(users => {
    3.     // We don't need spread here, since only the results will be returned for select queries
    4.   })

    Several other query types are available. Peek into the source for details

    1. // Callee is the model definition. This allows you to easily map a query to a predefined model
    2. sequelize
    3.   .query('SELECT * FROM projects', {
    4.     model: Projects,
    5.     mapToModel: true // pass true here if you have any mapped fields
    6.   })
    7.     // Each record will now be an instance of Project

    See more options in the . Some examples below:

    If an attribute name of the table contains dots, the resulting objects will be nested. This is due to the usage of dottie.js under the hood. See below:

    1. sequelize.query('select 1 as `foo.bar.baz`').then(rows => {
    2.   console.log(JSON.stringify(rows))
    3. })
    1. [{
    2.   "foo": {
    3.     "bar": {
    4.       "baz": 1
    5.     }
    6.   }
    7. }]

    Replacements in a query can be done in two different ways, either using named parameters (starting with :), or unnamed, represented by a ?. Replacements are passed in the options object.

    • If an array is passed, ? will be replaced in the order that they appear in the array
    2.   { replacements: { status: ['active', 'inactive'] }, type: sequelize.QueryTypes.SELECT }
    3. ).then(projects => {
    4.   console.log(projects)
    5. })

    To use the wildcard operator %, append it to your replacement. The following query matches users with names that start with 'ben'.

    1. sequelize.query('SELECT * FROM users WHERE name LIKE :search_name ',
    2.   { replacements: { search_name: 'ben%'  }, type: sequelize.QueryTypes.SELECT }
    3. ).then(projects => {
    4.   console.log(projects)
    5. })

    Bind parameters are like replacements. Except replacements are escaped and inserted into the query by sequelize before the query is sent to the database, while bind parameters are sent to the database outside the SQL query text. A query can have either bind parameters or replacements. Bind parameters are referred to by either $1, $2, … (numeric) or $key (alpha-numeric). This is independent of the dialect.

    • If an array is passed, $1 is bound to the 1st element in the array (bind[0])
    • If an object is passed, $key is bound to object['key']. Each key must begin with a non-numeric char. $1 is not a valid key, even if object['1'] exists.
    • In either case $$ can be used to escape a literal sign.

    The array or object must contain all bound values or Sequelize will throw an exception. This applies even to cases in which the database may ignore the bound parameter.