Introduction
Sequelize is a promise based ORM (Object Relational Mapper) for NodeJS. We can use multiple databases with Sequelize like Oracle, Postgres, MySQL, MariaDB, SQLite and SQL Server, and more.
What does ORM actually mean?
An Object Relational Mapper represents the database records as objects. It lets you create and manipulate data from a database using an object oriented paradigm.
So, using Sequelize, you can perform DML operations like SELECT, INSERT, UPDATE, DELETE etc. using class methods. You can also define relationships on your database tables using class methods like hasOne(), belongsTo() and hasMany(), etc.
So, now let's get started
Create a NodeJS application
Create a new folder at your desired location and initialize this as your node.js app using below command
npm init
Keep pressing the enter key after adding the required information and your node.js app is ready.
Now install all the required dependencies using below command
npm install express mysql2 cors sequelize - save
The package.json file will look like below after you have all the dependencies successfully installed.
The next step is to create a new express web server. Add a filename.js file at the root of your folder and add below code.
const cors = require("cors");
const express = require("express");
const app = express();
var corsOptions = {
origin: "http://localhost:8081"
};
app.use(cors(corsOptions));
// parse requests of content-type - application/json
app.use(express.json());
// parse requests of content-type - application/x-www-form-urlencoded
app.use(express.urlencoded({ extended: true }));
// simple route
app.get("/", (req, res) => {
res.json({ message: "Welcome to NodeJs App!!!" });
});
// set port, listen for requests
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
console.log(`Server is up and running on port ${PORT}.`);
});
Use the following command to execute the server
node filename.js
You will get the following message
Now, if you go to the browser and type the URL -> [http://localhost:8080/] you can see the application is up and running
Create a database
You can go to MSSQL Server and create a new database. In my case, I created it on Microsoft Azure. The creation of tables can be done with the help of Sequelize.
Next step is to put all the database configurations in a file. So, I have created a config.js file as below.
module.exports = {
HOST: "localhost",
USER: "root",
PASSWORD: "",
DB: "student_db",
dialect: "mysql",
pool: {//pool configuration
max: 5,//maximum number of connection in pool
min: 0,//minimum number of connection in pool
acquire: 30000,//maximum time in ms that pool will try to get connection before throwing error
idle: 10000//maximum time in ms, that a connection can be idle before being released
}
};
Initialize Sequelize
Create a new folder called models in the root directory and add a new file called index.js. Add below code there.
const dbConfig = require(“../config/config.js:);
const Sequelize = require(“Sequelize”);
const Sequelize = new Sequelize(dbCofig.DB, dbConfig.USER,
dbConfig.PASSWORD, {
host: dbConfig.HOST,
dialect: dbConfig.dialect,
operationsAliases: false,
pool: {
max: dbConfig.pool.max,
min: dbConfig.pool.min,
acquire: dbConfig.pool.acquire,
idle: dbConfig.pool.idle
}
};
const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.student= require(“./student.js”) (sequelize, Sequelize);
module.exports = db;
The user should not forget to summon the sync() method in the server.js.
const app = express();
app.use(....);
const db = require(“./models”);
db.sequelize.sync();
When you need to drop the existing tables and the database is required to be resynchronized, enter the force: true code like the below:
db.sequelize.sync({force: true}).then(() => {
console.log(“Drop and resync db.”);
});
We now need to create a new model named student.js
module.exports = (sequelize, Sequelize) => {
const Student = sequelize.define("student", {
name: {
type: Sequelize.STRING
},
admission:{
type:Sequelize.INTEGER
},
class: {
type: Sequelize.INTEGER
},
city: {
type: Sequelize.STRING
}
});
return Student;
};
Creating Controller
Below is the code for a controller.
const db = require(“../models”);
// models path depends on your structure
const Student= db.student;
exports.create = (req, res) => {
// Validating the request
if (!req.body.title) {
res.status(400).send ({
message: “Content can be placed here!”
});
return;
}
// Creating a Student
const student = {
name: req.body.name,
admission: req.body.admission,
class: req.body.class,
city: req.body.city
};
// Saving the Student in the database
Student .create(student). then(data => {
res.send(data);
}) .catch(err => {
res.status(500).send ({
Message:
err.message || “Some errors will occur when creating a student”
});
});
};
Retrieving Data
You can use below code to retrieve data.
exports.findAll = (req, res) => {
const name = req.query.name;
var condition = name ? { name: { [Op.like]: `%${name}%` } } : null;
Student.findAll({ where: condition })
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving data."
});
});
};
Now that we are done adding the controller and model, we need to have a route defined in our application that will execute the controller. Let's go ahead and create a route.
Defining Route
You can create a new folder called routes and add a new route.js file in it. Add below code in that file.
module.exports = app => {
const students = require("../controllers/student.js");
var router = require("express").Router();
// add new student
router.post("/", students.create);
// view all students
router.get("/", students.findAll);
};
Now include the route in the student_server.js file using below code
require("./routes/routes.js")(app);
You can test the API by calling the routes in postman.
Thank you for reading.
Top comments (0)