RESTful CRUD API using Nodejs,express and mysql : Part 2 – Integrate with MySQL database using ORM

In this second part of the series, we will integrate our code created in part 1 with the Mysql database using sequelize ORM.

Setup Sequelize

Sequelize is one of the many ORMs (object-relational mappers) that can be used to work with databases in nodejs. ORMs are used to access data from relational databases in an object-oriented fashion. You can read this StackOverflow post to know more about it.

The first step is to install sequelize and sequelize-cli.

Next, initialize sequelize.

Create model

The next step is to create your model. Model are the entities that represent your domain. For example in the case of an organization or company you would have entities such as Employee, Department, Compensation etc. All of these would be implemented as models.

The power of using an ORM is that it can directly translate these models and operations onto database tables and corresponding operations on those tables. Let’s create a model called Employee.

We can use sequelize CLI to create and configure models.

npx sequelize-cli  model:generate --name Employee --attributes empId:string,firstName:string,lastName:string,email:string,age:integer,onContract:boolean

Running this command creates two files viz. a model and a migration file.

Below is the model class fully configured based on the options provided in CLI.

Migration files consist of the code used to perform corresponding operations in the actual database. Every operation done on a model needs to be in a migration file which would do the same in the actual database.

When we will run the above migration file it will create a new table in whichever database is configured in our project.

There are two functions in the migration file viz. up and down. As the name suggests ‘up’ function runs when you run the migrations and the ‘down’ runs when we revert the migration. Reverting the migration is equivalent to reverting the changes and moving back to the previous state.

If you are new to ORMs, at this point, you must be wondering that we have not configured or created any database till now. The reason is that all the operations till now are database agnostic i.e. it will be the same for any type of database (SQL Server, Oracle, MySQL, etc.) that we plugin or configure.

This is another major advantage of using an ORM in that it provides database abstraction out of the box without needing to do anything specific.

Next, let’s integrate an actual database.

Integrate MySQL

At this point, you can choose any database of your choice and the steps for each of them would be similar but not exactly the same. We would be using MySQL and for that, we need to install the mysql2 package.

Next, we need to set up the config file created during the sequelize initialize. This creates a link between ORM and the database.

Use the below command to create the ‘express-api-db’ database.

npx sequelize-cli db:create

The above command does not have any details related to the database because all the details would be picked up from the config.json file.

Next, let’s run the migration file to create an Employee table in the database corresponding to our model.

npx sequelize-cli db:migrate

This creates the table in the database.

Setup Employee API endpoint

Now we are at the stage where we need to wire up everything and set up our employee API endpoint by following the same steps which we did in the last post for health check endpoint.

• Create employee controller

• Create employee router

• Configure the router in index.js file

For testing, I have inserted some data directly into the table.

Now we should be able to access this data using our employees get API endpoint as shown below.

In the next post, we will see how to convert this employee’s API to a full-fledged CRUD API with the ability to add, update and delete data.

If you would like to know more about sequelize go to this excellent pluralsight course. All the code is available here.

If you are looking into a deep dive into nodejs i.e. basically going from beginner to advanced level do checkout this pluralsight skills path.