Version: v7 - alpha

Raw Queries

As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can use the sequelize.query method.

By default the function will return two arguments - a results array, and an object containing metadata (such as amount of affected rows, etc). Note that since this is a raw query, the metadata are 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.

const [results, metadata] = await sequelize.query("UPDATE users SET y = 42 WHERE x = 12");
// Results will be an empty array and metadata will contain the number of affected rows.

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:

const { QueryTypes } = require('@sequelize/core');
const users = await sequelize.query("SELECT * FROM `users`", { type: QueryTypes.SELECT });
// We didn't need to destructure the result here - the results were returned directly

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

A second option is the model. If you pass a model the returned data will be instances of that model.

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

See more options in the query API reference. Some examples:

const { QueryTypes } = require('@sequelize/core');
await sequelize.query('SELECT 1', {
  // A function (or false) for logging your queries
  // Will get called for every SQL query that gets sent
  // to the server.
  logging: console.log,

  // If plain is true, then sequelize will only return the first
  // record of the result set. In case of false it will return all records.
  plain: false,

  // Set this to true if you don't have a model definition for your query.
  raw: false,

  // The type of query you are executing. The query type affects how results are formatted before they are passed back.
  type: QueryTypes.SELECT
});

// Note the second argument being null!
// Even if we declared a callee here, the raw: true would
// supersede and return a raw object.
console.log(await sequelize.query('SELECT * FROM projects', { raw: true }));

"Dotted" attributes and the `nest` option

If an attribute name of the table contains dots, the resulting objects can become nested objects by setting the nest: true option. This is achieved with dottie.js under the hood. See below:

Without nest: true:

const { QueryTypes } = require('@sequelize/core');
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
}

With nest: true:

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

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

Replacements

Replacements are a way to pass variables in your Query. They are an alternative to Bind Parameters.

The difference between replacements and bind parameters is that replacements are escaped and inserted into the query by Sequelize before the query is sent to the database, whereas bind parameters are sent to the database separately from the SQL query text, and 'escaped' by the Database itself.

Replacements can be written in two different ways:

Either using numeric identifiers (represented by a ?). The replacements option must be an array. The values will be replaced in the order in which they appear in the array and query.
Or by using alphanumeric identifiers (e.g. :firstName, :status, etc…). These identifiers follow common identifier rules (alphanumeric & underscore only, cannot start with a number). The replacements option must be a plain object which includes each parameter (without the : prefix).

The replacements option must contain all bound values, or Sequelize will throw an error.

Examples:

const { QueryTypes } = require('@sequelize/core');

await sequelize.query(
  'SELECT * FROM projects WHERE status = ?',
  {
    replacements: ['active'],
    type: QueryTypes.SELECT,
  },
);

await sequelize.query(
  'SELECT * FROM projects WHERE status = :status',
  {
    replacements: { status: 'active' },
    type: QueryTypes.SELECT,
  },
);

When using operators like LIKE, keep in mind that special characters in your replacement do keep their special meaning.
e.g. the following query matches users with names that start with 'ben':

const { QueryTypes } = require('@sequelize/core');

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

caution

Sequelize does not currently support a way to specify the DataType of a replacement, and will try to guess its type prior to serializing it.

For instance, Arrays will not be serialized as the SQL ARRAY type. Instead, the following query:

const { QueryTypes } = require('@sequelize/core');

await sequelize.query(
  'SELECT * FROM projects WHERE status IN (:status)',
  {
    replacements: { status: ['active', 'inactive'] },
    type: QueryTypes.SELECT
  }
);

Will result in this SQL:

SELECT * FROM projects WHERE status IN ('active', 'inactive')

Until such a feature is implemented, you can use a bind parameter and cast it instead.

Bind Parameters

Bind parameters are a way to pass variables in your Query. They are an alternative to Replacements.

A query can have both bind parameters and replacements.

Each database uses a different syntax for bind parameters, but Sequelize provides its own unification layer.

Inconsequentially to which database you use, in Sequelize bind parameters are written following a postgres-like syntax. You can either:

Use numeric identifiers (e.g. $1, $2, etc…). Note that these identifiers start at 1, not 0. The bind option must be an array which contains a value for each identifier used in the query ($1 is bound to the 1st element in the array (bind[0]), etc…).
Use alphanumeric identifiers (e.g. $firstName, $status, etc…). These identifiers follow common identifier rules (alphanumeric & underscore only, cannot start with a number). The bind option must be a plain object which includes each bind parameter (without the $ prefix).

The bind option must contain all bound values, or Sequelize will throw an error.

info

Bind Parameters can only be used for data values. Bind Parameters cannot be used to dynamically change the name of a table, a column, or other non-data values parts of the query.

Your database may have further restrictions with bind parameters.

Examples:

const { QueryTypes } = require('@sequelize/core');

await sequelize.query(
  'SELECT * FROM projects WHERE status = $1',
  {
    bind: ['active'],
    type: QueryTypes.SELECT,
  },
);

await sequelize.query(
  'SELECT * FROM projects WHERE status = $status',
  {
    bind: { status: 'active' },
    type: QueryTypes.SELECT,
  },
);

Sequelize does not currently support a way to specify the DataType of a bind parameter.
Until such a feature is implemented, you can cast your bind parameters if you need to change their DataType:

const { QueryTypes } = require('@sequelize/core');

await sequelize.query(
  'SELECT * FROM projects WHERE id = CAST($1 AS int)',
  {
    bind: [5],
    type: QueryTypes.SELECT,
  },
);

Did you know?

Some dialects, such as PostgreSQL and IBM Db2, support a terser cast syntax that you can use if you prefer:

await sequelize.query('SELECT * FROM projects WHERE id = $1::int');

"Dotted" attributes and the nest option​

Replacements​

Bind Parameters​

"Dotted" attributes and the `nest` option

Replacements

Bind Parameters