Writing RAW SQL Queries in Sequelize for Express JS.

Victor Nwaiwu

This post is a reminder for myself about the way I was able to debug an issue I was having with regards to using Sequelize raw SQL queries in a project I was working on a while ago. I had the same issue in another project I was working on and when I was able to figure it out, I told myself that I would write down the steps I took to make sure that I didn’t face such issue again. Hopefully, this post would also help someone out there and save you countless hours and make you more productive.

Most times when we use Sequelize , we use the inbuilt queries because it handles the basic queries which we might want to run in our application. But there comes a time when we want to do more than just basic queries, perform more complex queries and we wonder if Sequelize can handle it. The answer is yes. But the documentation could be a bit tricky to understand at first. I will be using a sample project I worked on to show how we can write complex raw SQL queries in sequelize and express js.

Having set up the project using sequelize your model/index.js might look something like this:

In this example, we are trying to get the following results from the DB:

• All the earnings for all the cars they own.
• All the earnings for all the cars they own for the current month.

the formatted snippet of code

Here we are using two ways of writing raw SQL queries in sequelize.

The code from line 21–24 uses sequelize queryTpes this determines the way the result will be returned. When we use this type option, the returned result is just a normal array of object which represents the affected/returned rows.

The code from line 27–31 uses the model property which tells sequelize to return the result as an instance of the model specified. This is helpful if you want to use the instance of the model to perform other actions.

Also, from the snippet of code, we see some options passed to the sequelize.query() method.

query option arguments

The replacement option can be in two forms

optional replacement format

If you look at the first snippet, the dynamic values in the raw query are wrapped in () and the key is set in it. This allows sequelize to check in the replacement property for the associated key in the query. So when using this format the replacement property is an object with associated keys in the query defined in it.

For the second snippet, the replacement property is an array which is interpreted by the query by the ? symbol.

Another issue you might have is if the name of your model is in camelCase or different from how it is defined in the table. You would need to escape the table name or else sequelize would throwrelation does not exist error. I hope this helps someone out there and also helps me in the future. Feel free to reach out to me if you have any questions with regards to writing raw queries in Sequelize.