How to Install and Use Sequelize CLI in Node.js

How to Install and Use Sequelize CLI in Node.js

In this article, 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.


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.


  • 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.


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 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.  
  //it will add data to department table  
  //it will add data to emp table  
  //it will delete particular department data   
  //this will join the tables and display data  
    empModel.findAll({include: [{ model:deptModel}]})  
//assign the port  
var port = process.env.port || 3000;  
app.listen(port,()=>console.log('server running at port '+port));  
  • 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 !

Learn Sequelize ORM in Node.js with MySQL From Scratch

Learn Sequelize ORM in Node.js with MySQL From Scratch

This Nodejs and MySQL tutorial, we'll learn Sequelize ORM in Nodejs with MySQL From Scratch. Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It supports the dialects PostgreSQL, MySQL, SQLite, and MSSQL plus some cool features like transactions and eager-loading.

Getting Started

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite, and MSSQL plus some cool features like transactions and eager-loading.

Q: Why use Sequelize instead of standard SQL Queries?

A: Basically because it supports more than one database system and it provides you an OOP Database API with all queries in form of methods and function so you don't have to write or care about queries plus its advanced Association system for putting relations between Models.

I assume you have a basic Node.js project for working with Sequelize.

Now let's start by installing Sequelize on our project.

npm install sequelize --save 

Sequelize Also requires your database system driver (depending on what dialect you choose) on our case we will be working with MySQL.

npm install mysql2 --save 

This will install MySQL adapter which allows Sequelize to connect and to the MySQL Deamon server.

We also, need to install Sequelize-CLI for managing our project and keeping track of migrations.

npm install sequelize-cli -g 

We save the CLI globally because you will be using on more than one project and you need to access directly through the command line.

Connecting to Database

First, we need to open connection steam between Sequelize and the running MySQL Deamon.

Create a database folder under src/database and add a connection.js file under it.

/* connection/js */

const Sequelize = require("sequelize");

const sequelize = new Sequelize("socialnetwork", "root", "", {
  host: "",
  dialect: "mysql",
  operatorsAliases: false

module.exports = sequelize;
global.sequelize = sequelize;

Change the config depending on your database server settings.

we export the connection instance as well as we export it globally which means we can access the Sequelize instance from any module on our environment without import (Global variables aren't good but in our case, it is the best scenario).

Now under the app.js (or main entry point module), you can require the connection module to connect to the database and expose the connection instance globally.

Models & Schemas

Models are the building blocks of your tables each model represents a database table where you define a model by putting the schema and giving it a name.
The Schema is a set of attributes (table columns) and associations (table relations).

So let's say we are going to create a social media application something like twitter clone, ao first we start by creating the database, this is better done manually from the CLI of any other MySQL client.
mysql$ CREATE DATABASE socialnetwork
The above command will create a database for us.

For a basic Social network, we would have a tweet and a user (tweet's author), the user can tweet one or more tweets but a tweet belongs to a single user.

Let's create the tweet model.

const Sequelize = require("sequelize");

module.exports = sequelize.define("Tweet", {
  id: {
    type: Sequelize.INTEGER(11),
    allowNull: false,
    autoIncrement: true,
    primaryKey: true
  content: Sequelize.STRING(300)

The Sequelize define method registers a new model using the provided name and schema.
Attributes can have the type directly, all are defined in the main namespace of Sequelize (STRING, DATE...).
Or you can define attributes using options with type and allowNull, unique.

for type sizes, you simply use the type as a function and pass the length (number) as an argument.

the id attributes are the primary key that uniquely identifies each tweet.

We export the defined model so later we will be able to use it for inserting, fetching, updating or deleting model's data.

We also need to define and register the user model the same way we did the tweet but with different attributes.

const Sequelize = require("sequelize");

module.exports = sequelize.define("User", {
  id: {
    type: Sequelize.INTEGER(11),
    allowNull: false,
    autoIncrement: true,
    primaryKey: true
  username: {
    type: Sequelize.STRING(35),
    allowNull: false,
    unique: true
  passwd: {
    type: Sequelize.STRING(20),
    allowNull: false

The username has to be unique so we make it unique (only one persistence can be saved with a certain username string), we set unique to true.

You can create default attributes on Sequelize which gets added to any model (like the id).

Sequelize Migrations

Sequelize other than the ORM comes with a Database Builder which allow you to create and manage the structure of your database tables over time without manually interacting with the CLI or typing silly commands.

All Migrations features are manipulated through Sequelize-CLI that we have installed earlier.

First, for migrations to work we need to initialize our project with the CLI and add some database configuration.

sequelize init 

This, in fact, it is used to initialize a whole Sequelize project to work properly with models, migrations, and seeds and since we already created the connection stream and add some models we will remove the models folder.

This what will be created under the root of our project.

  • config: database connection configuration for (development, testing, and production). So make sure to put your settings in the development object.
  • migrations: All migrations will be stored in here with their timestamp of creation.
  • seeders: A seed is a data that gets added to the database by default.
  • models: All models should go here where there is also a connection stream to Sequelize (in our case we will delete this).

Make sure to delete the newly added models folder by the CLI.

Let's add our tweets and users tables migrations, we will use the CLI to generate a new migration which will be placed by default under the migrations/ folder with some pre-defined code.

sequelize migration:generate --name create_tweets_table

This will generate a new migration named (create_tweets_table) and place it under migrations/ for the naming convention of migrations you always want it to be like a verb or a sentence tells what the migration does exactly.

Open the tweets migration file (Sequelize will add a timestamp represents when the migration has been generated to the filename), and add your column types which to be created on up method and dropTable command to drop and remove the table from the database if down is executed.

/* 20190214224339-create_tweets_table.js */
"use strict";

module.exports = {
  up: (queryInterface, Sequelize) => {
    //You must return a promise 
    return queryInterface.createTable("tweets", {
      id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
      content: Sequelize.STRING(300),
      userId: Sequelize.INTEGER(11),
      //Those are added by default on insertion (make sure to create the their columns)
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE

  down: (queryInterface, Sequelize) => {
    //Return a promise that drops a table in case of (migration:undo)
    return queryInterface.dropTable("tweets");

If we run migrate command the up method will be called and the table will be created, but Sequelize is smart enough to only run the up command once (it keeps track of migrated tables).

the userId attribute we will use it later to associate a user with one or more tweets.

And in case you run migration:undo or migration:undo:all it will rollback either the last submitted migration or simply or migrations in this case whatever you put inside the down method will be executed.

Add the user's migration too.

sequelize migration:generate --name create_users_table

And add the schema of the table on up and drop table on down.

/* 20190214225010-create_users_table.js */

"use strict";

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable("users", {
      id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
      username: {
        type: Sequelize.STRING(35),
        allowNull: false,
        unique: true
      passwd: {
        type: Sequelize.STRING(20),
        allowNull: false
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable("users");

Now our migrations are created now we need to migrate them to the database.

sequelize db:migrate

It will output the successfully migrated migration and errors in case of any.

You can use the CLI to take a look at the users and tweets tables structure using the describe command on your MySQL CLI.

use socialnetwork;
describe tweets;
describe users;
Inserting to Database

Let's create a bootstrap.js module that will do the fake data insertion on the database for our social network.

/* src/bootstrap.js */
//Export a default function (we use Async/Await)
module.exports = async () => {
  //Require models
  const Tweet = require("./models/Tweet");
  const User = require("./models/User");
  //Create Relations 
  User.hasMany(Tweet, { as: "Tweets", foreignKey: "userId" });
  Tweet.belongsTo(User, { as: "User", foreignKey: "userId" });

For our Models Association (Relation) we have two constraints:

  • User: can have one or more Tweets (one-to-many).
  • Tweet: can have one user only as an author (one-to-one)

Each Sequelize model has a built-in methods used to describe associations among other models, the first parameter it takes is the target and an options object as the second parameter.

  • hasMany: for a one-to-many association, will associate User with many tweets with configuration (foreignKey will be added to the tweets table).
  • as option: used to set setters in the User Model so you can call userInstance.setTweets(tweets).
  • belongsTo: for a one-to-one association to associate each tweet with one and only one user.

Check the Association Docs for more detailed info.

Let's insert some User to the database.

//Generic Error Handler 
const errHandler = err => {
  //Catch and log any error.
  console.error("Error: ", err);
//create returns a promise which gets resolved to the user instance 
//We also use await, you can use standard then callback.
const user = await User.create({
  username: "alexdmc",
  passwd: "1234567890"
}).catch(errHandler); ///< Catch any errors that gets thrown
//You must provide the userId to get each tweet linked to a single user.
const tweet = await Tweet.create({
  content: "This is actually the tweet content Tweeted from Iphone",

A model.create method is used to create a user instance using the provided data and then save it and finally returns a promise holding the new user instance.

We use await which gets resolved if the creation and saving succeed otherwise the catch callback (errHandler) will get called.

To associate a tweet with its author (user) we have to put the userId attribute under the tweet to point to the author's unique id.

Fetching from Database (Eager Loading)

For fetching specific data related to a specific model we have to use the Model class providing it with a filter (condition) to fetch a specific record(s) from the database.

Let's try to fetch users data plus all its tweets (using eager-loading).

eager-loading is the process of fetching a models data that is associated with another model in one fetch call (also known as Populating).

//Find All Users with Thier Tweets
const users = await User.findAll({
  where: { username: "alexdmc" },
  include: [{ model: Tweet, as: "Tweets" }] ///< include used to eager-load associated model 
//log users & tweets
console.log("AlexDMC Tweets: ", JSON.stringify(users));

We findAll the users with their associated tweets the where attributes take the filter object and include is an array on which you specify the associated model you want to eager-load alongside the current model (User).

the include takes an array of object we need to specify associated model's model class and the as attribute (which you used to define the association between the two models).

The output should have all the registered users with an array of their tweets.

Read More on Eager-Loading Docs.

What's Next

This tutorial is just a quick introduction to Sequelize and how to use its main features, if you want to master and work with Sequelize you must read the http://docs.sequelizejs.comDocs for more detailed pieces of information and instructions.


Node.js for Beginners - Learn Node.js from Scratch (Step by Step)

Node.js for Beginners - Learn Node.js from Scratch (Step by Step)

Node.js for Beginners - Learn Node.js from Scratch (Step by Step) - Learn the basics of Node.js. This Node.js tutorial will guide you step by step so that you will learn basics and theory of every part. Learn to use Node.js like a professional. You’ll learn: Basic Of Node, Modules, NPM In Node, Event, Email, Uploading File, Advance Of Node.

Node.js for Beginners

Learn Node.js from Scratch (Step by Step)

Welcome to my course "Node.js for Beginners - Learn Node.js from Scratch". This course will guide you step by step so that you will learn basics and theory of every part. This course contain hands on example so that you can understand coding in Node.js better. If you have no previous knowledge or experience in Node.js, you will like that the course begins with Node.js basics. otherwise if you have few experience in programming in Node.js, this course can help you learn some new information . This course contain hands on practical examples without neglecting theory and basics. Learn to use Node.js like a professional. This comprehensive course will allow to work on the real world as an expert!
What you’ll learn:

  • Basic Of Node
  • Modules
  • NPM In Node
  • Event
  • Email
  • Uploading File
  • Advance Of Node

How to Use Express.js, Node.js and MongoDB.js

How to Use Express.js, Node.js and MongoDB.js

In this post, I will show you how to use Express.js, Node.js and MongoDB.js. We will be creating a very simple Node application, that will allow users to input data that they want to store in a MongoDB database. It will also show all items that have been entered into the database.

In this post, I will show you how to use Express.js, Node.js and MongoDB.js. We will be creating a very simple Node application, that will allow users to input data that they want to store in a MongoDB database. It will also show all items that have been entered into the database.

Creating a Node Application

To get started I would recommend creating a new database that will contain our application. For this demo I am creating a directory called node-demo. After creating the directory you will need to change into that directory.

mkdir node-demo
cd node-demo

Once we are in the directory we will need to create an application and we can do this by running the command
npm init

This will ask you a series of questions. Here are the answers I gave to the prompts.

The first step is to create a file that will contain our code for our Node.js server.

touch app.js

In our app.js we are going to add the following code to build a very simple Node.js Application.

var express = require("express");
var app = express();
var port = 3000;
app.get("/", (req, res) => {
&nbsp;&nbsp;res.send("Hello World");
app.listen(port, () => {
  console.log("Server listening on port " + port);

What the code does is require the express.js application. It then creates app by calling express. We define our port to be 3000.

The app.use line will listen to requests from the browser and will return the text “Hello World” back to the browser.

The last line actually starts the server and tells it to listen on port 3000.

Installing Express

Our app.js required the Express.js module. We need to install express in order for this to work properly. Go to your terminal and enter this command.

npm install express --save

This command will install the express module into our package.json. The module is installed as a dependency in our package.json as shown below.

To test our application you can go to the terminal and enter the command

node app.js

Open up a browser and navigate to the url http://localhost:3000

You will see the following in your browser

Creating Website to Save Data to MongoDB Database

Instead of showing the text “Hello World” when people view your application, what we want to do is to show a place for user to save data to the database.

We are going to allow users to enter a first name and a last name that we will be saving in the database.

To do this we will need to create a basic HTML file. In your terminal enter the following command to create an index.html file.

touch index.html

In our index.html file we will be creating an input filed where users can input data that they want to have stored in the database. We will also need a button for users to click on that will add the data to the database.

Here is what our index.html file looks like.

<!DOCTYPE html>
    <title>Intro to Node and MongoDB<title>

    <h1>Into to Node and MongoDB<&#47;h1>
    <form method="post" action="/addname">
      <label>Enter Your Name<&#47;label><br>
      <input type="text" name="firstName" placeholder="Enter first name..." required>
      <input type="text" name="lastName" placeholder="Enter last name..." required>
      <input type="submit" value="Add Name">

If you are familiar with HTML, you will not find anything unusual in our code for our index.html file. We are creating a form where users can input their first name and last name and then click an “Add Name” button.

The form will do a post call to the /addname endpoint. We will be talking about endpoints and post later in this tutorial.

Displaying our Website to Users

We were previously displaying the text “Hello World” to users when they visited our website. Now we want to display our html file that we created. To do this we will need to change the app.use line our our app.js file.

We will be using the sendFile command to show the index.html file. We will need to tell the server exactly where to find the index.html file. We can do that by using a node global call __dirname. The __dirname will provide the current directly where the command was run. We will then append the path to our index.html file.

The app.use lines will need to be changed to
app.use("/", (req, res) => {   res.sendFile(__dirname + "/index.html"); });

Once you have saved your app.js file, we can test it by going to terminal and running node app.js

Open your browser and navigate to “http://localhost:3000”. You will see the following

Connecting to the Database

Now we need to add our database to the application. We will be connecting to a MongoDB database. I am assuming that you already have MongoDB installed and running on your computer.

To connect to the MongoDB database we are going to use a module called Mongoose. We will need to install mongoose module just like we did with express. Go to your terminal and enter the following command.
npm install mongoose --save

This will install the mongoose model and add it as a dependency in our package.json.

Connecting to the Database

Now that we have the mongoose module installed, we need to connect to the database in our app.js file. MongoDB, by default, runs on port 27017. You connect to the database by telling it the location of the database and the name of the database.

In our app.js file after the line for the port and before the app.use line, enter the following two lines to get access to mongoose and to connect to the database. For the database, I am going to use “node-demo”.

var mongoose = require("mongoose"); mongoose.Promise = global.Promise; mongoose.connect("mongodb://localhost:27017/node-demo");

Creating a Database Schema

Once the user enters data in the input field and clicks the add button, we want the contents of the input field to be stored in the database. In order to know the format of the data in the database, we need to have a Schema.

For this tutorial, we will need a very simple Schema that has only two fields. I am going to call the field firstName and lastName. The data stored in both fields will be a String.

After connecting to the database in our app.js we need to define our Schema. Here are the lines you need to add to the app.js.
var nameSchema = new mongoose.Schema({   firstName: String,   lastNameName: String });

Once we have built our Schema, we need to create a model from it. I am going to call my model “DataInput”. Here is the line you will add next to create our mode.
var User = mongoose.model("User", nameSchema);

Creating RESTful API

Now that we have a connection to our database, we need to create the mechanism by which data will be added to the database. This is done through our REST API. We will need to create an endpoint that will be used to send data to our server. Once the server receives this data then it will store the data in the database.

An endpoint is a route that our server will be listening to to get data from the browser. We already have one route that we have created already in the application and that is the route that is listening at the endpoint “/” which is the homepage of our application.

HTTP Verbs in a REST API

The communication between the client(the browser) and the server is done through an HTTP verb. The most common HTTP verbs are

The following table explains what each HTTP verb does.

HTTP Verb Operation
GET Read
POST Create
PUT Update

As you can see from these verbs, they form the basis of CRUD operations that I talked about previously.

Building a CRUD endpoint

If you remember, the form in our index.html file used a post method to call this endpoint. We will now create this endpoint.

In our previous endpoint we used a “GET” http verb to display the index.html file. We are going to do something very similar but instead of using “GET”, we are going to use “POST”. To get started this is what the framework of our endpoint will look like."/addname", (req, res) => {
Express Middleware

To fill out the contents of our endpoint, we want to store the firstName and lastName entered by the user into the database. The values for firstName and lastName are in the body of the request that we send to the server. We want to capture that data, convert it to JSON and store it into the database.

Express.js version 4 removed all middleware. To parse the data in the body we will need to add middleware into our application to provide this functionality. We will be using the body-parser module. We need to install it, so in your terminal window enter the following command.

npm install body-parser --save

Once it is installed, we will need to require this module and configure it. The configuration will allow us to pass the data for firstName and lastName in the body to the server. It can also convert that data into JSON format. This will be handy because we can take this formatted data and save it directly into our database.

To add the body-parser middleware to our application and configure it, we can add the following lines directly after the line that sets our port.

var bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true }));
Saving data to database

Mongoose provides a save function that will take a JSON object and store it in the database. Our body-parser middleware, will convert the user’s input into the JSON format for us.

To save the data into the database, we need to create a new instance of our model that we created early. We will pass into this instance the user’s input. Once we have it then we just need to enter the command “save”.

Mongoose will return a promise on a save to the database. A promise is what is returned when the save to the database completes. This save will either finish successfully or it will fail. A promise provides two methods that will handle both of these scenarios.

If this save to the database was successful it will return to the .then segment of the promise. In this case we want to send text back the user to let them know the data was saved to the database.

If it fails it will return to the .catch segment of the promise. In this case, we want to send text back to the user telling them the data was not saved to the database. It is best practice to also change the statusCode that is returned from the default 200 to a 400. A 400 statusCode signifies that the operation failed.

Now putting all of this together here is what our final endpoint will look like."/addname", (req, res) => {
  var myData = new User(req.body);
    .then(item => {
      res.send("item saved to database");
    .catch(err => {
      res.status(400).send("unable to save to database");
Testing our code

Save your code. Go to your terminal and enter the command node app.js to start our server. Open up your browser and navigate to the URL “http://localhost:3000”. You will see our index.html file displayed to you.

Make sure you have mongo running.

Enter your first name and last name in the input fields and then click the “Add Name” button. You should get back text that says the name has been saved to the database like below.

Access to Code

The final version of the code is available in my Github repo. To access the code click here. Thank you for reading !