Integrate Sequelize ORM with Express!

Published in

7Span

5 min readNov 30, 2022

Hello Developers,
Writing short SQL queries to fetch and manipulate data from a database can be easy and without verbosity. But what happens when these queries become complex and complicated?
One of the solutions is to use ORM. ORM (Object-Relational Mapping) is a technique that helps you to do the same querying and manipulation of data using an object-oriented paradigm.
In this article, I will demonstrate how you can integrate the Sequelize ORM with Express to create Node.js CRUD APIs. We’ll be using the MySQL database throughout the process.

· But before that, what is Sequelize, and why use it?
· Creating the Application
· Creating the express web server
· Configure MySQL database & Sequelize
· Conclusion

But before that, what is Sequelize, and why use it?

Sequelize is a promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite, Microsoft SQL Server, Amazon Redshift, and Snowflake’s Data Cloud. It features solid transaction support, relations, eager and lazy loading, read replication, etc. When I say promise-based, it means that Sequelize supports Node.js promises.

Benefits of Using Sequelize :

Promise based
Supports multiple database systems
Database Migrations
Easy to manage and create associations
Avoid writing raw SQL Queries

Creating the Application

We’ll be building REST APIs that can create, retrieve, update, and delete users record.

The following table gives a better glimpse of the suggested APIs:

+--------+---------------+-------------------+
| Method |      URL      |      Action       |
+--------+---------------+-------------------+
| GET    | api/users     | Get all Users     |
| GET    | api/users/:id | Get User by id    |
| POST   | api/users     | Add new User      |
| PUT    | api/users:id  | Update User by id |
| DELETE | api/users/:id | Delete user by id |
| DELETE | api/users     | Delete all Users  |
+--------+---------------+-------------------+

Also, I will be sharing Postman Collection link soon :

Project Setup

First of all, create a folder and then initialize our application with a “package.json” file:

mkdir mysql-sequelize
cd mysql-sequelize
npm init -y

Now add the necessary modules like:

express, sequelize, sequelize-cli, mysql2 and cors via the command:

npm install express sequelize sequelize-cli mysql2 cors — save-dev

Let’s install nodemon so we can restart our app everytime we update it

npm install nodemon

Creating the express web server

In the root folder, create a new “index.js” file:

What I have done here is,

Import express for building the APIs
Import cors to enable CORS with suitable options
Create an Express app, then add body-parser package(JSON and urlencoded) and cors middlewares using app.use() method.
Note for I set origin: http://localhost:8081
I just define a GET route that is simple for testing.
Listen on port 8080 for incoming requests.

Now let’s run the app with the command: node index.js .

node index.js

On the browser, open the URL http://localhost:8080.

Configure MySQL database & Sequelize

In the terminal, run the command to initialize sequelize using its CLI:

sequelize init

This will create the following file structure inside the root folder:

models folder stores the models/table definitions we will create
migrations folder holds up the table migration files.

The file “config.json” inside the config folder holds the database connection details, which can be edited as per our requirement. Finally, it is as follows:

Above parameters are for MySQL connection.

Also, another optional parameter can be defined as “logging”, which receives the value as true or false. It controls the logging of SQL queries whenever the server is started. In this article, I’ve not used it to demonstrate every step.

Inside the models/index.js: