How to Run Sub Queries in Sequelize.js (3 Ways)

This succinct, example-based article will show you some different ways to execute sub-queries with Sequelize.js in Node.js.

Solution 1: Raw Sub Query

Directly inserting a raw sub-query string into the Sequelize query method allows for maximal flexibility, while still utilizing Sequelize for parts of the query.

The steps:

1. Write the raw sub-query SQL statement you wish to execute.
2. Embed the statement within a Sequelize Query interface, like sequelize.query.
3. Execute the query and handle the response or errors accordingly.

Example:

const subQuery = '(SELECT id FROM Users WHERE active = true)';
const result = await sequelize.query(
`SELECT * FROM Posts WHERE authorId IN ${subQuery}`,
{ type: sequelize.QueryTypes.SELECT }
);

Pros: High level of control; Can utilize database-specific features not supported by Sequelize.

Cons: Risk of SQL injection if not handled carefully; Loses many Sequelize ORM benefits.

Solution 2: Using Sequelize literal

Sequelize’s Sequelize.literal is used within a Sequelize query to embed a raw SQL expression that the library afterwards integrates correctly into the final SQL query.

Here’re the steps:

1. Write the sub-query as a raw SQL literal.
2. Integrate this literal into a Sequelize OR Mapper query, such as findAll or any other finder method.
3. Process results as usual with all Sequelize returned structures.

Example:

const subQueryLiteral = sequelize.literal('(SELECT id FROM Users WHERE active = true)');
const Posts = sequelize.define('posts');
const activeUserPosts = await Posts.findAll({
 where: { authorId: subQueryLiteral }
});

Pros: Better integration into Sequelize syntax; Partial protection against SQL injection.

Cons: Still less safe than using ORM methods fully; Can become complex.

Solution 3: Sequelize Scope for Sub-Queries

Create a predefined scope that includes the sub-query, which can be reused in various find operations around your model.

You can follow these steps:

1. Define a model with a scope that contains the sub-query conditions.
2. Use the scope in your find operation.
3. Handle the results returned by Sequelize normally.

Example:

const User = sequelize.define('user', {/*...*/}, {
 scopes: {
 active: {
 where: {
 /* Define sub-query conditions here */
 }
 }
 }
});
const activeUsers = await User.scope('active').findAll();

Pros: Reusable for many queries; Full Sequelize ORM support.

Cons: Scopes can be less flexible; Sub-query conditions are defined globally.

That’s it. Happy coding!