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.
User.create({
first_name: 'Maxim',
last_name: 'Orlov',
gender: 'male',
});
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.
User.findAll({
gender: 'female',
});
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.
Order.findAll({
include: [
{
model: User,
where: { first_name: 'Maxim' },
},
],
});
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.
Order.findAll({
where: {
created_at: {
[Op.lt]: new Date(),
[Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000),
},
},
order: [['price', 'DESC']],
limit: 10,
});
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.
User.update(
{ role: 'admin' },
{ where: { first_name: 'Maxim' } },
);
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.
User.destroy({
where: { first_name: 'Maxim' },
});
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 💌.
Top comments (0)