How to Execute Raw SQL Queries in Sequelize

Introduction

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more. While Sequelize provides a powerful querying interface through its model API, there are times when you need to execute raw SQL queries. This tutorial provides a step-by-step guide on how to perform raw SQL queries in Sequelize, ranging from simple data retrieval to more complex transactions.

Setting Up

Before executing raw queries, ensure that you have Sequelize installed and your database connection is set up. Here’s a brief setup guide:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql' // or 'postgres', 'sqlite', 'mariadb', 'mssql'
});

Executing a Basic Select Query

To execute a simple SELECT query, use the sequelize.query method. This allows you to pass straight SQL code. Here’s how you would select all records from the Users table:

sequelize.query('SELECT * FROM Users', { type: sequelize.QueryTypes.SELECT})
.then(users => {
    console.log(users);
})
.catch(error => {
    console.error('Error executing query:', error);
});

Using Bind Parameters

To prevent SQL injection, use bind parameters with your raw queries:

sequelize.query('SELECT * FROM Users WHERE id = :userId', {
    replacements: { userId: 1 },
    type: sequelize.QueryTypes.SELECT
})
.then(user => {
    console.log(user);
})
.catch(error => {
    console.error('Error:', error);
});

Inserting Data

To insert data into your database, you can still use raw SQL. Here’s an example of inserting a new user:

sequelize.query('INSERT INTO Users (name, email) VALUES (:name, :email)', {
    replacements: { name: 'John Doe', email: '[[email protected]](mailto:[email protected])' }
}).then(result => {
    console.log('User added:', result);
}).catch(error => {
    console.error('Insert failed:', error);
});

Handling Transactions

Transactions are important when you need to execute multiple queries that must succeed or fail as a group. Sequelize transactions can be managed in raw SQL as shown in the following example:

const transaction = await sequelize.transaction();
try {
    const userResult = await sequelize.query('INSERT INTO Users (name, email) VALUES (:name, :email)', {
        replacements: { name: 'Jane Doe', email: '[email protected]' },
        transaction
    });
    
    const profileResult = await sequelize.query('INSERT INTO Profiles (userId, bio) VALUES (:userId, :bio)', {
        replacements: { userId: userResult[0], bio: 'Experienced coder' },
        transaction
    });

    await transaction.commit();
    console.log('Transaction committed, new user and profile created.');
} catch (error) {
    await transaction.rollback();
    console.error('Transaction rolled back:', error);
}

Join Queries and Complex Selects

Sequelize’s raw query interface allows for complex SQL operations such as joins. Here’s an example of a join query between two tables:

sequelize.query('SELECT Users.name, Profiles.bio FROM Users INNER JOIN Profiles ON Users.id = Profiles.userId', {
    type: sequelize.QueryTypes.SELECT
}).then(results => {
    console.log('Joined results:', results);
}).catch(error => {
    console.error('Failed to execute join query:', error);
});

Conclusion

Executing raw SQL with Sequelize is a powerful tool when you need complete control over your queries or when dealing with complex SQL that falls outside the straightforward ORM patterns. This tutorial covered the basics and some advanced uses of raw SQL in Sequelize. Remember to always use replacements or bind parameters to avoid SQL injection vulnerabilities. With the techniques shown here, you should be able to integrate raw SQL queries into your Sequelize-based applications effectively.