Sequelize Raw Queries

Introduction
Sequelize is an Object-Relational Mapping (ORM) library for Node.js that supports multiple SQL databases like MySQL, PostgreSQL, and SQLite. It abstracts SQL queries into JavaScript code, providing models and methods to interact with the database in a structured way.

However, sometimes you may need to execute raw SQL queries, especially for complex or highly optimized queries that aren't easily expressed with Sequelize's built-in methods. Sequelize allows executing raw SQL queries using the sequelize.query() method.
Using raw queries in Sequelize allows for -

i) Custom SQL queries beyond Sequelize's abstraction.

ii) Optimizing performance for specific scenarios.

iii) Flexibility in handling complex joins, subqueries, and other SQL features.

At the same time, raw queries should be used with care, especially regarding SQL injection, for which Sequelize provides mechanisms like replacements to keep things safe.

Basic Syntax
The sequelize.query() method executes raw SQL queries and returns a promise, which can resolve into the query results. It has the following basic syntax,

 sequelize.query(sql, options);

sql: A string containing the raw SQL query.

options: An object that contains additional options for the query, such as the query type, model, replacements, etc.

Example
i) Simple SELECT Query

 sequelize.query("SELECT * FROM Users", { type: Sequelize.QueryTypes.SELECT })
     .then(users => {
         console.log(users);
     });

In this example, the query returns all rows from the Users table.

The type: Sequelize.QueryTypes.SELECT specifies that the query is a SELECT statement, meaning the result will be rows from the database.

ii) Inserting Data

 sequelize.query("INSERT INTO Users (name, age) VALUES ('John', 30)", { type: Sequelize.QueryTypes.INSERT })
     .then(() => {
         console.log("User added");
     });

Here, a new row is inserted into the Users table.
iii) Using Replacements to Avoid SQL Injection

Instead of concatenating variables directly into the query (which is vulnerable to SQL injection), Sequelize supports replacements.

 const name = 'John';
 const age = 30;

 sequelize.query("INSERT INTO Users (name, age) VALUES (:name, :age)", {
     replacements: { name, age },
     type: Sequelize.QueryTypes.INSERT
 }).then(() => {
     console.log("User added");
 });

In this case, :name and :age are placeholders that are safely replaced with the values from the replacements object.

iv) Parameterized Queries (Array Replacement)

Alternatively, you can use an array of values for replacement.

 sequelize.query("SELECT * FROM Users WHERE age > ?", {
     replacements: [25],
     type: Sequelize.QueryTypes.SELECT
 }).then(users => {
     console.log(users);
 });

v) Returning Auto-generated Values

If the query inserts data and the database generates an ID or other values (e.g., primary key), you can retrieve it as part of the result.

 sequelize.query("INSERT INTO Users (name, age) VALUES ('Jane', 28)", { 
     type: Sequelize.QueryTypes.INSERT, 
     returning: true 
 }).then(result => {
     console.log(result); // Contains inserted row's information.
 });

Query Types in Sequelize

Sequelize provides a variety of query types that can be specified using Sequelize.QueryTypes. Some common query types are:

Sequelize.QueryTypes.SELECT: Used for SELECT queries that return rows of data.

Sequelize.QueryTypes.INSERT: Used for INSERT queries that insert data into the database.

Sequelize.QueryTypes.UPDATE: Used for UPDATE queries to modify data.

Sequelize.QueryTypes.DELETE: Used for DELETE queries to delete rows.

Sequelize.QueryTypes.BULKUPDATE and BULKDELETE: Used for bulk operations.

Sequelize.QueryTypes.RAW: Used for queries that don't neatly fit into other categories, returning the raw results.

Advanced Example

 sequelize.query(
     "SELECT u.name, p.title FROM Users u JOIN Posts p ON u.id = p.userId WHERE u.age > :age", 
     {
         replacements: { age: 25 },
         type: Sequelize.QueryTypes.SELECT
     }
 ).then(results => {
     console.log(results);
 });

This query performs a join between Users and Posts and fetches the names of users along with the titles of their posts, but only for users older than 25.

Raw Query with Model Mapping

You can also map the raw query results to Sequelize models.

 sequelize.query(
     "SELECT * FROM Users WHERE age > :age", 
     {
         replacements: { age: 25 },
         model: User, // Sequelize model
         mapToModel: true // Enables mapping to model
     }
 ).then(users => {
     console.log(users);
 });

Here, the query results are automatically mapped to instances of the User model.