How to Install and Use Sequelize CLI in Node.js

Introduction

In this post, we see how to setup up our models with a relationship between them and how we can place them in a database using Sequelize CLI, we will see how to use Sequelize CLI for creating a table for our database and how can we set up a relationship between the two tables using foreignKey.

Prerequisites

• node
• mysql

Project Structure

Setup The Node Project

• Open the console and type the below cmd to make a new directory.
  # mkdir seqlcli

• Then change to that directory by typing the below cmd.
  # cd seqlcli

Setup Node in Project

• Now type the below cmd on console which will generate the package.json file.

  # npm init

• It will ask you to provide the basic details related to the package.json file like name, version, author, etc.

• After the file is initialized, we can build our node project.

• The file will have all the metadata related to our project.

Install Packages

Now, we will install the package required for building our application.

• On console type

  # npm install --save sequelize mysql2 sequelize-cli express body-parser

• Express – this is a framework on which the complete application will be built.

• Body-parser – this will fetch the form data from the coming request.

• Sequelize – this will contain all the files related to Sequelize ORM.

• Mysql2 - this is a driver related to our database.

• Sequelize CLI – this allows us to make the use of sequelize files from the command line

Setup Sequelize

Now we will set up a Sequelize structure required for building the application.

• On console type
  _# sequelize _

This will give you the following output:

This shows that sequelize CLI is properly initialized.

• Now initialize Sequelize by typing:
  # sequelize init

It will generate the following folder.

• config - will contain the config.json file which will contain the connection details.
• models - this will contain index.js file. This file will be generated with Sequelize CLI and collects all the models from the models directory and associates them if needed.
• migrations - This will contain the migration files. The migration file is a change in that model or you can say the table used by CLI. It treats migrations like a commit or a log for changes in the database.
• seeders - It will contain the seeder files. The seeder files are those which will contain data that we want to put in the table by default.

Create a Model

Now we will generate two models or tables for our database.

• department
  # sequelize model:generate --name department --attributes dbName:string)

• emp
  # sequelize model:generate --name emp --attributes name:string,salary:integer

The model:generate command requires two options:

• name – Name of the model.
• attributes- The fields or attributes of model/table.

Now we will get two models in our model folder with department, emp name.

Two files will be added in the migration folder, which will contain the table schema of both these models.

*Remember if we want to add or remove any field from the table then we will have to make changes in both migration file and model file of that particular model.

Make Relationship

Now we will make a relationship between the department and the emp table through foreign Key.

For making a relationship between the two we use:

• hasMany.
• belongsTo

hasMany - it describe 1:n or n:n relationship.The department table can have many relationships.

belongsTo – it describe 1:1 or n:1 relationship.The emp table will have one relationships that is with department.

• Open the department model and apply a relationship to it.

department.hasMany(models.emp,{foreignKey:‘depId’}).

Here, the department model can have multiple relationships. One will be with the emp model.

Now we will specify the name of the column/attribute of the emp model which will contain the department references in the emp model.

So we have to tell the department model which column/attribute in emp will contain its references.

Here the depId will contain the reference of department in emp model.

We do it by providing it in foreignKey,

• Open the emp model/table schema.

emp.belongsTo(models.department,{foreignKey:‘id’,target_key:‘depId’}};

emp will have a relationship with the department. These two will be linked with each other through the depId column/attribute present in emp model.

And the column/attribute of the department which will be used for the reference will be the ID.

here target _Key will contain the linking column/attribute and foreignKey will contain the reference column/attribute.

Now we have to make changes in the migration file.

• Open the migration file for the emp model and update it.

Here we add deptId attribute/column on which we apply the foreign key constraint.

The references will contain the model and key

• model - refers to the table name in which we want to create a relationship.
• key - will contain the column name which will be referred while performing data manipulation in the table/model.

Perform Migration

• Now insert the table into the database.
• On console type

# sequelize db:migrate

This command will execute the following steps:

1. Ensures a table called SequelizeMeta is in the database. This table is used to record which migrations have run on the current database.
2. Starts looking for any migration files which haven’t run yet. This is possible by checking SequelizeMeta table.
3. This will create a table in our database. Here, it will create two tables in the database department, emp.

_*_When we run db:migrate then the up function int the migration file will be called.

Undo Migration

• We can use db:migrate:undo, this command will revert the most recent migration.

*When we run the above command then the down function in the migration file will be called.

Setup app.js

• Now add a new file app.js
• After that open the package.json and in scripts write starts:‘node app.js’

Add the below code in the app.js:

var express    = require('express');  
var bodyParser = require('body-parser');  
var deptModel  = require('./models').department;  
var empModel   = require('./models').emp;  
  
var app = express();  
//fetch form data from the request.  
app.use(bodyParser.urlencoded({extended:false}));  
app.use(bodyParser.json());  
  
app.post('/adddept',(req,res)=>{  
  //it will add data to department table  
  deptModel.create(req.body)  
  .then(function(data){  
      res.json({da:data})  
  }).catch(function(error){  
        res.json({er:error})  
  })  
});  
  
  
app.post('/addemp',(req,res)=>{  
  //it will add data to emp table  
    empModel.create(req.body)  
    .then(function(data){  
          res.json({da:data})  
    }).catch(function(error){  
        res.json({er:error})  
    })  
});  
  
  
app.post('/deldept/:id',(req,res)=>{  
  //it will delete particular department data   
    deptModel.destroy({where:{id:req.params.id}})  
    .then(function(data){  
        res.json({da:data})  
    })  
    .catch(function(error){  
        res.json({er:error})  
    })  
});  
  
app.get('/',(req,res)=>{  
  //this will join the tables and display data  
    empModel.findAll({include: [{ model:deptModel}]})  
    .then(function(data){  
        res.json({da:data})  
    })  
    .catch(function(error){  
        res.json({er:error})  
    })  
});  
  
//assign the port  
var port = process.env.port || 3000;  
app.listen(port,()=>console.log('server running at port '+port));  

Output

• Add department

• Add employee

• Display data

• Delete dept

• Display data after delete

Here we see when we delete the record from the department table then the same records from its child will also be deleted.

Thank you for reading !

#Nodejs #MySQL #Sequelize CLI #node-js