6 Common Sequelize Queries Explained in SQL
Tags: node, javascript, sql, database
Published: 2019-12-19 12:34:32

*This article was originally published at https://maximorlov.com/6-common-sequelize-queries-rewritten-in-sql/*

You’re comfortable writing queries in Sequelize ORM but when it comes to doing anything in raw SQL you have no clue where to begin.

“SQL feels like *magic*“

“Developers who can write clever SQL queries are ninja-rockstar gurus”

These are some of the quotes I hear when SQL comes up and I understand. Writing SQL queries can feel overwhelming. You spend valuable time trying to understand how a query works instead of writing the actual application and solving real-world problems.

When you finally have something you run the query and NOPE. A syntax error knocks you back down and the error message isn’t particularly helpful, to say the least.

Wouldn’t it be nice if writing SQL came to you as naturally as JavaScript? Instead of relying on the ORM you can just jump into raw SQL, get the job done, and continue writing your application.

Next time you get a weird bug using Sequelize you can look at the SQL query output. Not only will you understand how the generated query translates to your JavaScript code, but you will know how to fix it!

So let’s unveil that not-so-magic behind SQL. Here are 6 queries you *know* how to make in Sequelize. Rewritten and explained in SQL.

## 1. Insert one user

This is a simple one, to begin with. We insert a new user in the `users` table with some basic values.

```js
User.create({
  first_name: 'Maxim',
  last_name: 'Orlov',
  gender: 'male',
});
```
```sql
INSERT INTO users(first_name, last_name, gender)
     VALUES ('Maxim', 'Orlov', 'male');
```
`INSERT INTO` is the `.create()` equivalent in SQL. The values are in the same order as the columns above them. That’s how the database understands which value to assign to which column. You can specify the columns in whichever order you like, they don’t have to match the order in the database, as long as the columns are in sync with the `VALUES`.

## 2. Find all users that match a condition

Then we have the most basic find of all, one that matches a single condition. In this case, we want to retrieve all female users from the database.

```js
User.findAll({
  gender: 'female',
});
```
```sql
SELECT *
  FROM users;
 WHERE gender = 'female';
```
The `*` in the `SELECT` statement tells the database we want to get all columns. `FROM` indicates which table to look at and in the `WHERE` statement we specify a condition the rows should match.

## 3. Find all orders that belong to a user

Of all examples, this might be the most difficult one to wrap your head around. Here comes the dreaded `JOIN`. In SQL you use a join when you want to query data based on two or more tables. In our database we have another table called `orders` and we want to get all orders that belong to a user.

```js
Order.findAll({
  include: [
    {
      model: User,
      where: { first_name: 'Maxim' },
    },
  ],
});
```
```sql
    SELECT orders.*
      FROM orders
INNER JOIN users ON orders.user_id = users.id AND users.first_name = 'Maxim';
```
Because we have more than one table we use the dot notation to specify we want all columns just from the orders table like so: `orders.*`. We use an `INNER JOIN`¹ to fetch only the orders that match `user_id` with the `id` of the user that fulfills the condition.

¹ There are several types of joins in the SQL world (RIGHT JOIN, LEFT JOIN, INNER JOIN, FULL JOIN). I find the chart in this Stack Overflow answer helpful in getting a visual understanding of how they all differ from each other https://stackoverflow.com/a/6188334/4763270

## 4. Find X orders within a date range sorted by a column

Back to a single table query but with a few additional elements. We want to find the 10 highest priced orders created in the last day.

```js
Order.findAll({
  where: {
    created_at: {
      [Op.lt]: new Date(),
      [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000),
    },
  },
  order: [['price', 'DESC']],
  limit: 10,
});
```
```sql
  SELECT *
    FROM orders
   WHERE orders.created_at < now() AND orders.created_at > now() - interval '1 day'
ORDER BY orders.price DESC
   LIMIT 10;
```
SQL gives you access to functions you can use to make your queries more dynamic. `now()` and `now() - interval '1 day'`² lets us define a query that always returns results from the last day, regardless of when it’s run.

The `ORDER BY` statement lets us define a column to sort the result by and we use `DESC` to do this in descending order. With `LIMIT` we tell SQL we’re only interested in the first 10 results. It can discard the rest.

² SQL has several flavors depending on which database you chose. This particular example uses the PostgreSQL database so if you’re using MySQL, SQLite, etc., the date range may look a bit different.

## 5. Update one user

It’s time for an update, a pretty simple one. We want to promote a user to the admin role.

```js
User.update(
  { role: 'admin' },
  { where: { first_name: 'Maxim' } },
);
```
```sql
UPDATE users
   SET role = 'admin'
 WHERE first_name = 'Maxim';
```
The `UPDATE` statement tells SQL we want to update the `users` table followed by `SET` where we specify which column to update with which value. If we wanted to update more than one column we would add more expressions delimited by a comma (eg. `role = 'admin', age = 30, ...` )

## 6. Delete a single user

Lastly, we have a simple delete. Nothing fancy to it.

```js
User.destroy({
  where: { first_name: 'Maxim' },
});
```
```sql
DELETE FROM users
      WHERE first_name = 'Maxim';
```
`DELETE FROM` is how we tell SQL in which table we want to delete the records that match the `WHERE` clause.

In case you’re using the default `paranoid: true` in the Model configuration, Sequelize will not delete the row but update the `deletedAt` column instead with the current timestamp.

## Practice makes perfect

SQL can be daunting at first but like with everything, practice makes perfect. It’s ok if you don’t understand every bit of syntax at first. It’ll get easier with time. Keep moving forward and in no time you’ll look back and realize you’ve become a ninja-rockstar guru yourself.

## Become a skilled Node.js developer

Every other Tuesday I send an email with tips on building solid Node.js applications. If you want to grow as a web developer and move your career forward with me, [drop your email here 💌](https://maximorlov.ck.page/0076aaf499).