Reading Time: 10 minutes

11,662 Views

Node JS is an environment where we can deploy and execute javascript based applications. We are going to use Node JS with Sequelize ORM for database related works.

Models are the functional database layer by the help of which we can connect and work with database tables. Each model shows the connectivity with one database table. Sequelize is a third party package which we installs via NPM inside node application.

So, inside this article we will discuss about Create Models in Node Js Sequelize ORM. Very easy and step by step guide to implement in your node application.

Learn More –

We will discuss all possible to ways to define Models in Node Js ORM.

Let’s get started.

Node Js Application Setup

We need to create a Node application setup at system. To create node application please make sure you have installed node js first. If not installed, click here to go at official website of Node community for download and setup.

Create a folder with any name. I am creating “node-orm“. Open this folder into terminal or command prompt.

Create package.json file inside that created folder and needs to install packages. To create package.json file –

$ npm init -y

This command will automatically creates a package.json file with all default settings.

Next, we need to install packages.

$ npm install express mysql2 sequelize nodemon

After this installation, you should see that package.json now updated with all the packages.

{
  "name": "node-orm",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "mysql2": "^2.2.5",
    "nodemon": "^2.0.4",
    "sequelize": "^6.3.5"
  }
}

Let’s create a file named as index.js in “node-orm” folder. Inside this file we will do our application code.

Successfully, we have installed & created a node project.

MySQL Database Connection with Application

As you can see we had installed mysql2 package. mysql2 is the database driver for mysql. Apart from mysql2 we have many more drivers available to make use of different different drivers.

So now, we are going to connect mysql database with node application. It’s pretty simple to connect.

const Sequelize = require("sequelize");

// connection with mysql database
// we need to pass database name, username, password, hostname and database driver
// node_orm : database
// root : username
// root : password
// localhost : hostname
// dialect : database driver
const sequelize = new Sequelize("node_orm", "root", "root", {
    host: "localhost",
    dialect: "mysql"
});

// check database connection
sequelize.authenticate().then(function (success) {

    console.log("Successfully we are connected with the database");
}).catch(function (error) {

    console.log(error);
});

Create Models in Node Js Sequelize ORM

Models can be created in several ways. In this we will see by two. In the connect with mysql database we have an object or say a connection variable const sequelize. We will use this connection variable to create model.

Method #1 Create Model in Sequelize ORM

var User = sequelize.define("tbl_users", {
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false
    },
    email: {
        type: Sequelize.STRING
    },
    rollNo: {
        type: Sequelize.INTEGER
    },
    status: {
        type: Sequelize.ENUM("1", "0"),
        defaultValue: "1"
    },
    created_at: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal("CURRENT_TIMESTAMP")
    },
    updated_at: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal("CURRENT_TIMESTAMP")
    }
}, {
    modelName: "User"
    //  timestamps: false   
});

// sync model
sequelize.sync();

When we run this, it will create a table in the connected database named as tbl_users.
This table have columns as defined in definition of model as – id, name, email, rollNo, status, created_at, updated_at.
When we don’t want date or timestamp columns simply we need to make it false in configuration of second object.

Method #2 – Using Class Based Concept of Sequelize

const Model = Sequelize.Model;

class User extends Model {}

User.init(
  {
    // table definition
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    name: {
      type: Sequelize.STRING,
      allowNull: false,
    },
    email: {
      type: Sequelize.STRING,
    },
    rollNo: {
      type: Sequelize.INTEGER,
    },
    status: {
      type: Sequelize.ENUM("1", "0"),
      defaultValue: "1",
    },
    created_at: {
      type: Sequelize.DATE,
      defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
    },
    updated_at: {
      type: Sequelize.DATE,
      defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
    },
  },
  {
      // timestamps: false,
      modelName: "Users",
      sequelize
  }
);

Complete Source Code – Model in Node Js ORM

Open up the file app.js and paste the complete code from here.

const express = require("express");
const Sequelize = require("sequelize");

const app = express();

const PORT = 8088;

// connection with mysql database
const sequelize = new Sequelize("node_orm", "root", "root", {
    host: "localhost",
    dialect: "mysql"
});

// check database connection
sequelize.authenticate().then(function (success) {

    console.log("Successfully we are connected with the database");
}).catch(function (error) {

    console.log(error);
});

// create model => First way to create models in sequelize
var User = sequelize.define("tbl_users", {
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false
    },
    email: {
        type: Sequelize.STRING
    },
    rollNo: {
        type: Sequelize.INTEGER
    },
    status: {
        type: Sequelize.ENUM("1", "0"),
        defaultValue: "1"
    },
    created_at: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal("CURRENT_TIMESTAMP")
    },
    updated_at: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal("CURRENT_TIMESTAMP")
    }
}, {
    modelName: "User",
    timestamps: false
});

// sync model
sequelize.sync();

// default welcome page route
app.get("/", function (req, res) {

    res.status(200).json({
        status: 1,
        message: "Welcome to Home page"
    });
});

// listed request here
app.listen(PORT, function () {
    console.log("Application is running");
})

Back to terminal and start server using nodemon.

$ nodemon

Once, the server will start. Back to database –

Also, to test the running server. Back to browser and type localhost:8088, we should see the output of this route.

{"status": 1,"message": "Welcome to Home page"}

Successfully, we have created Model in Node Js with Sequelize ORM.

Perform Database Operations with Models

At initial stage of this article we had discussed that Models are the functional database layer which interact and perform operations. So we are going to use model here.

As we have created modelName: “User” in above create model discussion. Now, here we have model name is User. In your case it will different.

Next,

To perform database operations like data insert, data insert in bulk, data update, data delete etc we use Model for these operations.

Data Insert

User.create({
 //.. data
}).then(function (response) {
 //.. success response
}).catch(function (error) {
 //.. error response
});

Data Select

User.findAll({
  //.. conditions
}).then((data) => {
  //.. data
}).catch((error) => {
 //.. error
});

Same for delete we use User.destroy(), User.update()

We hope this article helped you to learn Create Models in Node Js Sequelize ORM in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.