How to execute/ use a raw query with Sequelize

When using Sequelize to manipulate your database from a JavaScript application, there might be cases where you want to just execute a raw SQL query instead of using Sequelize Model methods.

Sequelize instance comes with the query() method which you can use to run a raw query.

The syntax of the method is as shown below:

const [results, metadata] = await sequelize.query(
  "Your query here",
  { options }
);

The query() method has two parameters:

• The first parameter is the query string to execute
• The second parameter is an optional object containing options to adjust the behavior of the method

The method returns an array of two elements:

• The first element is the results array containing the retrieved rows as objects
• The second element is the metadata information (such as the number of affected rows by UPDATE statement)

Note that the metadata information is dialect-specific. When there’s no metadata passed by your database server, then metadata will contain the same value as the results array.

Let’s see an example of Sequelize raw query in action.

Suppose you have a MySQL table named Cities with the following fictional data:

+----+------------+------------+
| id | cityName   | population |
+----+------------+------------+
|  1 | York       |     467000 |
|  2 | Bristol    |     394000 |
|  3 | Manchester |     210000 |
|  4 | London     |    8000000 |
|  5 | Glasgow    |      99999 |
+----+------------+------------+

You can query the cityName column from the table with Sequelize raw query as shown below:

const sequelize = new Sequelize("DATABASE", "USERNAME", "PASSWORD", {
  host: "localhost",
  dialect: "mysql",
});

const [results, metadata] = await sequelize.query("SELECT cityName FROM Cities");
console.log(results)
console.log(metadata)

The returned array will contain the following values:

// results
[
  { cityName: 'York' },
  { cityName: 'Bristol' },
  { cityName: 'Manchester' },
  { cityName: 'London' },
  { cityName: 'Glasgow' }
]

// metadata
[
  { cityName: 'York' },
  { cityName: 'Bristol' },
  { cityName: 'Manchester' },
  { cityName: 'London' },
  { cityName: 'Glasgow' }
]

The above result is because MySQL doesn’t send any metadata after executing a query.

If you connect use PostgreSQL as your database server and perform the same query, the returned values will be as follows:

// results
[
  { cityName: 'York' },
  { cityName: 'Bristol' },
  { cityName: 'Manchester' },
  { cityName: 'London' },
  { cityName: 'Glasgow' }
]

// metadata
Result {
  command: 'SELECT',
  rowCount: 5,
  oid: null,
  rows: [
    { cityName: 'York' },
    { cityName: 'Bristol' },
    { cityName: 'Manchester' },
    { cityName: 'London' },
    { cityName: 'Glasgow' }
  ],
  fields: [
    Field {
      name: 'cityName',
      tableID: 16441,
      columnID: 2,
      dataTypeID: 1043,
      dataTypeSize: -1,
      dataTypeModifier: 259,
      format: 'text'
    }
  ],
  _parsers: [ [Function: noParse] ],
  _types: TypeOverrides {
    _types: { getTypeParser: [Function: bound getTypeParser] },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: false
}

Now you know what a metadata value looks like in MySQL and PostgreSQL.

The Sequelize query() method can be used to perform any data manipulation statement such as an UPDATE, INSERT, or DELETE statement.

But please be careful when you’re using raw queries while connecting to databases of different dialects.

A raw query won’t be transformed to fit the dialect, so a query that works fine in one dialect may cause an error in another.

For example, in PostgreSQL you need to specify a capitalized table name enclosed in double quotes to preserve the capital letter:

SELECT "cityName" FROM "Cities"; -- PostgreSQL OK

But in MySQL you can specify a capitalized table name without any symbol:

SELECT cityName FROM Cities; -- MySQL OK

Both SELECT statements above won’t work when you use the other dialect:

SELECT "cityName" FROM "Cities"; -- MySQL ERROR
SELECT cityName FROM Cities; -- PostgreSQL ERROR

But even when you use multiple database servers, a single Sequelize instance can only connect to a single database dialect.

You won’t encounter this kind of problem often. But if you ever change your database dialect, make sure you adjust any raw query you use in your project.

Sequelize raw query options

The query() method of the Sequelize instance also allows you to pass an object containing pre-defined options.

These options can be used to manipulate the behavior of Sequelize when running the query() method.

For example, specifying the type option as SELECT will cause Sequelize to return a single results value instead of an array containing [results, metadata].

This means you don’t need to destructure the returned value as shown below:

const results = await sequelize.query(
  "SELECT cityName FROM Cities", 
  { 
    type: sequelize.QueryTypes.SELECT 
  }
);

console.log(results) // returned rows

The full list of options you can add to the method can be found in Sequelize query() documentation.

Now you’ve learned how to execute a raw query in Sequelize. Nice work! 👍