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 that we install via NPM inside the node application.
So, in this article, we will discuss Create Models in Node Js Sequelize ORM. A very easy and step-by-step guide to implement in your node application.
Read Also: Payment Gateway Integration In Java
What is Sequelize
Sequelize is an SQL ORM for Nodejs. ORM means Object-Relational Mapping. This type of interface allows you to take some distance from the database administration hassle. It will create the tables, and the indexes and auto-generate and execute the queries directly for you. You don’t have to execute those very painful and repetitive tasks.
Sequelize is an ORM that is developed for databases using SQL as a query language. MySQL for instance or Postgresql.
Installation
Go to the directory of your nodejs project :
1 | $ cd my_project_forlder |
Then use npm to install sequelize and all its dependencies.
We will use the --save
option to write in our package.json
file that the project needs this module.
It will be more easier when you will deploy it, or update your packages.
1 | $ npm install sequelize —-save |
Configuration
Prerequisite is a running SQL database. For demonstration purposes, we will use a MySQL one.
Create a new file in your project folder named sequelize.js
with the following content :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | // Let’s require our module var Sequelize=require(‘equalize); // Let’s create a new sequelize instance // And connect to our database var sequelize = new Sequelize(‘database_name’, ‘username’, ‘password’, { host: ‘XXX.us-west-2.rds.amazonaws.com’, dialect: ‘mysql’, pool: { max: 5, min: 0, idle: 10000 }, }); |
That’s it! You have now a brand new connection to your server.
By default, sequelize will use a pool connection. We give in the last lines some additional options to configure the pooling system. A pool of connections is a cache of database connections that can be used again and again in case of multiple queries to perform.
Read Also: How to Install Android Studio on Ubuntu
Creation of the model
You can see the model as an abstract representation of data that will be stored in your system. By defining a model you will warn Sequelize that you want to be able to insert, select, delete or update rows of data that will be stored and retrieved following the instructions you specified when you define your model.
Do not be afraid of this abstraction step. I have experienced with many of my students that being able to fully understand what is a model and what it brings to the developer can be hard. Just keep in mind that you are just defining the structure of a traditional database table with indexes, constraints, and types.
1 2 3 4 5 6 7 8 | // Creation of the model “client” var client = sequelize.define(‘client’, { // Here are the columns of the table family_name: {type: Sequelize.STRING}, surname: {type: Sequelize.STRING}, title: {type: Sequelize.STRING}, email: {type: Sequelize.STRING} }); |
In this code we have created a model client, (that will be stored in the table client) with the columns family_name, surname …
Synchronization with the database
Creating a model is cool, but now you have to synchronize it with your physics database. If you forget this step you will get errors because the table will not be created and it will be impossible to insert or select data for a nonexisting table.
1 2 3 4 5 6 7 8 9 10 | client.sync().then(function () { // Table created return client.create({ family_name: ‘Jean’, surname: ‘Dupont’, title: “Mr”, email: “jean.dupont@gmail.com” }); }); |
This code will create the tables and the columns defined before. Bonus: Sequelize will add automatically an id
, createdAt
and updatedAt
column!
After the creation is done it will create a client with the information provided in the create method of our model object (client
).
Read Also: How to Compare String in Python?
Enjoy and use without moderation but be careful! Never use (unless you know what you are doing) the method sync with the option force: true. It will drop the table if one exist with the same name and create a new one! It can be a real disaster in a production environment!
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 a 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 modelsequelize.sync();
- When we run this, it will create a table in the connected database named tbl_users.
- This table has columns as defined in the definition of the model as – id, name, email, role, status, created_at, and updated_at.
- When we don’t want the date or timestamp columns simply we need to make it false in the configuration of the 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 });
Read Also: subprocess-exited-with-error in Python
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 databaseconst 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 sequelizevar 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 modelsequelize.sync();// default welcome page routeapp.get(“/”, function (req, res) { res.status(200).json({ status: 1, message: “Welcome to Home page” });});// listed request hereapp.listen(PORT, function () { console.log(“Application is running”);})
Back to the terminal and start the server using nodemon.$ nodemon
Read Also: Popular Tool Used In Agile Software Development
Once, the server starts. Back to database –
Also, to test the running server. Back to the browser and type localhost:8088, we should see the output of this route.{“status”: 1, “message”: “Welcome to Home page”}
Successfully, we have created a Model in Node Js with Sequelize ORM.