SQL(Structured Query Language) is used to query Relational Databases. RDBMS (Relational DataBase Management Systems) continue to uphold their popularity among developers and often ORMs(Object Relation Mappers) are used for a better experience. Sequelize is an ORM for SQL (Structured Query Language). It can be used in conjunction with Node.js for SQL databases like MySQL, Postgres, MariaDB etc. In this article, we will discuss how to query a MySQL database using Node.js. This will be a step by step guide from scratch.

Prerequisites :

• Node.js

• MySQL

Setting up the project

Move into an empty folder and enter into the terminal:

npm init

This would give you multiple prompts on the terminal asking details about the project. You can add in specific values or just press Enter all the way through. Now we are ready to install NPM packages.

Run the following install commands :

npm install sequelize mysql2 sequelize-cli

• Sequelize : SQL ORM for Node.js
• MySql2 : Driver for MySQL database
• Sequelize-CLI : Sequelize functionality from CLI ( Command Line Interface ). This gives us easy-to-use CLI commands for Sequelize.

Now that we have all the necessary packages, we can go ahead and initialize the sequelize-cli on the terminal.

npx sequelize init

Here we will see multiple folders added to our project and the directory should look something like this :

Creating SQL Database and Tables

Now we have our basic file structure ready and we can start creating our SQL tables and querying them! But before that we must make a few changes to the config.json file. It will be located in the configs folder. If we open it up, we can see that it mentions three environments with different databases, usernames, passwords etc. We will be using the development database by default. Here we must change the values as per our system for the development database only. Refer to the below screenshot for the changes made :

The database mentioned here can be a new database or an existing database also.

Now that we have configured our database details, we can go ahead and create the database if not already existing. This step is only required if the database is NOT already existing :

npx sequelize db:create

This will create a new database in your system and you can verify using MySQL. Now we can create our models. These models are nothing but the SQL tables in our database. We will again use the simplicity of the sequelize-cli to our advantage:

npx sequelize model:generate --name TableName --attributes attribute1:dataType,attribute2:dataType....

Example :

npx sequelize model:generate --name Restaurant --attributes name:string,location:string,no_of_dishes:integer

At a quick glance, we can see that the data-types are similar to programming language style keywords and not SQL style syntax. Thus, we can create tables without actually writing SQL queries and SQL syntax. This step would have created a migration and a model file in their respective folders. In the migration file, we can see the Sequelize has automatically created an id(integer, primary key, not null, auto increment) for us along with createdAt(date) and updatedAt(date) attributes. These are very useful to keep a track of model data changes. The id field is a must have for almost every table as it helps to not only identify the records uniquely but also create associations with other tables.

We can tweak the properties of the attributes of the table just with the help of sequelize commands. For example,

Here the name attribute is of type string, it is not null and it must have a unique value in the table. All of these features without having to write a single query ! Once your attributes are ready in the migrations file, we are ready to migrate this model to our MySQL database.

We can also tweak the model file in the models folder like validating data, writing hooks etc. but we wont go deeper into that for the scope of this article.

To migrate the model to the database :

npx sequelize db:migrate

If you made a mistake and want to go back then :

npx sequelize db:migrate:undo

Make sure to migrate again after you undo the migration and made changes. Now we can start querying the database using Sequelize. For this article, we will :

1. Create Data in a table
2. Read Data from a table
3. Update Data in a table
4. Delete Data from a table

Querying the database

First, we need a JavaScript file in our project directory. We would start by importing our model in the JavaScript file.

const restaurantTable = require('./models/index').Restaurant;

Now we will need some sample data. We need to create a JS object with the data that we want to add in our table.

let data = {
    name : 'Pizza Hut',
    location : 'Mumbai',
    cuisine : 'Italian',
    no_of_dishes : 12
}

Inserting Data into the table ( Creating Data )

Now we will use the imported table to create the data in that table. The sequelize command Table.create({}) makes us do that. I will wrap this command in a function. It should be noted that the Sequelize commands return promise objects as querying databases is an asynchronous process so we must use the required asynchronous JS commands. Promise .then() methods can also be used here. Ideally, this code should also be enclosed within try-catch blocks. Here is an example :

const createRestaurant = async(data) => {
    const restaurant = await restaurantTable.create({
        name : data.name,
        location : data.location,
        cuisine : data.cuisine,
        no_of_dishes : data.no_of_dishes
    })

    return restaurant;
}

createRestaurant(data);

Here, we are passing the data object that we created and mapping the values to their corresponding MySQL table attributes. When this function is called then the data would be inserted into our table. If we check the console, then this command actually runs the INSERT INTO TABLE query. To verify these changes, we can either log the restaurant object to the console or check the MySQL table by running the SELECT * FROM TABLE query.

As we can see, the values for Id, createdAt and updatedAt are automatically added. I changed the data object and created a few more records in the table :

Retrieving Data from the table ( Reading Data )

Now let's try to retrieve the data from the table. We will again use a function provided to us by Sequelize. We will make use of the findAll() and findOne() functions here to retrieve the data :

const getRestaurants = async() => {
    const restaurant = await restaurantTable.findAll();
    console.log(restaurant);
    return restaurant;
}

const getRestaurantById = async(restaurantId) => {
    const restaurant = await restaurantTable.findOne({
        where:{
            id : restaurantId
        }
    });
    return restaurant;
}

const display = async() => {
    const allRestaurants = await getRestaurants();
    const restaurantById = await getRestaurantById(5);
    console.log(allRestaurants);
    console.log(restaurantById);
}

display();

Here we have used an async display function to consume the get functions. It should also be noted that similar to an SQL query, we can use a where clause by passing it as an object to the function. We can also have multiple attributes inside the where function. If we check the console, then this command actually runs the SELECT FROM TABLE query. Now that we know how to create and read data from a table in a database, we will move onto how to update the data.

Updating Data in the table ( Updating Data )

For updating, we will use the Sequelize function table.update() for updating the records :

const updateRestaurant = async(restaurantId, data) => {
    const restaurant = await restaurantTable.update({
        name : data.name,
        location : data.location,
        cusine : data.cuisine,
        no_of_dishes : data.no_of_dishes
    },{
        where:{
            id : restaurantId
        }
    });

    return restaurant;
}

updateRestaurant(2, data);

We have passed in two objects to the update function. One with all the data mapping and other with the where object. This will update the data on the restaurant with the id as passed in the argument. If we check the console, then this command actaually runs the UPDATE TABLE query. We can check the updates to the table by again calling the display function or by checking the MySQL database table.

Removing Data from the table ( Deleting Data )

Now let's try deleting the data of a record. For this, we will use the table.destroy function from Sequelize. As explained above, we can pass the where object with the attributes and the matching data would be deleted from the table :

const deleteRestaurant = async(restaurantId) => {
    const restaurant = await restaurantTable.destroy({
        where:{
            id : restaurantId
        }
    });
}

Similarly, how we retrieved and updated the data using the restaurantId, we can call the destroy function and delete the record from the table. If we check the console, then this command actually runs the DELETE FROM TABLE query. If we check the table now, we wouldn't find the deleted records.

Thus, we have successfully performed all our desired operations of creating, reading, updating and deleting data to/from a table in a MySQL database using the Sequelize ORM and Sequelize-CLI. If we simply connect this to a server, then this essentially a CRUD web application.

Project Repository