How To Use Sequelize-Cli In Node


Introduction

 In this article we will see how to use sequelize-cli for creating table in our database.and how can we setup a relationship between two table using foreignKey using sequelize

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 below cmd.

       # cd seqlcli

Setup Node In Project
  • Now  type the below cmd on console and that will generate the package.json file.
      # npm init 
  • It will ask you to provide the basic details related to the package.json file have like name,version,author etc.
  • After the file is initialize 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 which are required for building our application.
  • On console type 
       # npm install express,body-parser,sequelize,mysql2,sequelize-cli
  • So what these packages will do
  1. Express – this is a framework on which the complete application will be build.
  2. Body-parser – this will fetch the form data from the coming request.
  3. Sequelize – this will contain all the files related to sequelize orm.
  4. Mysql2- this is a driver related to our database.
  5. Sequelize-cli – this allow us to make the use of sequelize files from the command line
Setup Sequelize
  •  On console type 

         # sequelize

this will give you the following output 

this show that sequelize-cli is rightly initialized.
  • Now initialize our project by typing this
         # 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 the 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.Treat migrations like a commit or a log for some change in database.
  • seeders - It will contain the seeder files.The seeder files are those which will contain data which we want to put in the table by default.
Create Model
Now we will generate the model or table for our database.
  • On console type  
        # sequelize model:generate --name department --attributes dbName:string
  • Again  type
       # sequelize model:generate --name emp --attributes name:string,salary:integer

The model:generate command require two options.
a)name – Name of the model.
b)attributes- The fields or attributes of model/table.

Now we will get two models in our model folder with department,emp name.
And two file will be added in 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 emp table through foreign Key.

For making relationship between two we  use

a)hasMany.
b)belongsTo

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


b)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 relationship to it.


department.hasMany(models.emp,{foreignKey:'depId'}).
here department model can have multiple relationship.One Out of many it will be with emp model.
Now here 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 that 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 relationship with department.And 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 id.
here target _Key will contain the linking column/attribute and foreignKey will contain the reference column/attribute.

Now we have to do changes in migration file to.
  • Open the migration file for the emp model/table.And update it

Here we add deptId attribute/column on which we apply the foreign key constraint.
the references will contain the model,key
a)model - refer to the table name to which we want to create relation.
b)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 these steps:
  1. Will ensure a table called SequelizeMeta in database. This table is used to record which migrations have run on the current database.
  2. It will start looking for any migration files which haven't run yet. This is possible by checking SequelizeMeta table. 
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 most recent migration.
When we run the above command then the down function in the migration file will be called.
Setup app.js
Open app.js and add the below code.
Output
adding dept


 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 get deleted.
Because as you see above we have using onDelete:'CASCADE'  and you can read about it more from the links given in references.

 References 

https://sequelize.org/v5/manual/migrations.html

https://softwareengineering.stackexchange.com/questions/152731/what-is-the-main-difference-between-has-many-and-belongs-to-relationship-in-mysq

https://stackoverflow.com/questions/50584682/sequelize-model-migration-file-and-foreign-key-which-one-is-right

https://dba.stackexchange.com/questions/44956/good-explanation-of-cascade-on-delete-update-behavior

https://gist.github.com/zcaceres/83b554ee08726a734088d90d455bc566

Comments

Post a Comment

Popular posts from this blog

Upload And Download File In Node

Image
Here we will use multer middleware for uploading file . Click the link to know what is  multer . Setup The Project Folder create a new folder using console/terminal. open console/terminal type mkdir followed by the folder name you want to give. change to that folder by typing cd  folder name. Setup Node For The Project now type npm init on console/terminal. the  npm init  will create a  package.json  file.But we can create it manually too. just open the folder and  add a new file  and name it  package.json  and then we can structure it the same way as its been structured when using  npm init . the package.json will look like this. now we will install packages for our project using npm install followed by package names we want to install. the following packages will be installed express ejs body-parser multer mongoose . this will generate a folder name node_modules wh...
Read more

Import CSV File Data Into MongoDB In Node

Image
Setup Folder Open the command prompt and create new folder using the following command followed by folder name. mkdir csv After creating the folder.Change to that folder by using the following command cd csv Setup Node In Folder To setup node in folder use the following command npm init -y This will setup node in our folder.And  after the execution of this command you will see package.json file which means node is initialised. This package.json file will contain the metadata related to our project. The package.json will look like this Install Packages Now we have to install packages to be used to build our application. To install packages we use the following command followed by package name.  npm install csvtojson express ejs mongoose multer body-parser  After installation package.json will look like this Add New Folders Add few new folders in our project folder  models public views Models Fold...
Read more