Continuing from our previous project of express.js & mysql used in
previous post at
msdotnetbuddy.blogspot.com/2024/04/expressjscrud-operation-on-mysql-using.html
today we will see how
to use sequelize orm.
Lets first install sequelize package
npm i sequelize@latest
Now add new file sequelizeConnection.js to root of your project.
It's content are
Express.js:CRUD Operation on MySQL using
const Sequelize = require("sequelize");
const sequelize = new Sequelize("demo", "root", "sangram#81", {
dialect: "mysql",
host: "localhost",
port: "3306",
define: {
timestamps: false,
},
dialectOptions: {
dateStrings: true,
typeCast: true,
},
timezone: "+05:30", // for writing to database
});
sequelize
.authenticate()
.then(() => {
console.log("Connection has been established successfully.");
})
.catch((err) => {
console.error("Unable to connect to the database:", err);
});
module.exports = sequelize;
Basically here we are giving our mysql username,password,server &port.
When a database table is created from model ,sequelize automatically add
columnscreatedAt,updatedAt,deletedAt & in any select * from table kind of
query it adds these 3 columns as part of select query columns.To stop it \
from doing so we have added
define: {
timestamps: false,
},
in above configuration.
Now we will create a sequelizeModels folder and add index.js
& foodItemModel.js.
Content of index.js
var fs = require("fs");
var path = require("path");
var Sequelize = require("sequelize");
var sequelizeConnection = require("../sequelizeConnection");
var basename = path.basename(__filename);
var db = {};
fs.readdirSync(__dirname)
.filter((file) => {
return (
file.indexOf(".") !== 0 && file !== basename &&
file.slice(-3) === ".js"
);
})
.forEach((file) => {
var model = require(path.join(__dirname, file))(
sequelizeConnection,
Sequelize.DataTypes
);
db[model.name] = model;
});
Object.keys(db).forEach((modelName) => {
if (db[modelName].associate) {
db[modelName].associate(db);
}
});
db.sequelizeConnection = sequelizeConnection;
db.Sequelize = Sequelize;
module.exports = db;
Here it is basically taking all files from sequelizeModels folder except
index.js loooping over it and adding that model to object 'db' and returning
it.this object then can be used to access each model conveniently like
dm.modelName.
Lets see how we can define our foodItem model.
content of foodItemModel.js
const sequelizeConnection = require("../sequelizeConnection");
const moment = require("moment");
const models = require("../sequelizeModels");
module.exports = function (sequelize, DataTypes) {
const FoodItem = sequelizeConnection.define(
"FoodItem",
{
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true,
},
name: {
type: DataTypes.STRING,
field: "name",
validate: {
notEmpty: {
args: true,
msg: "name is Required",
},
is: {
args: ["^[a-z ]+$", "i"],
msg: "In name Only letters allowed",
},
len: {
args: [2, 255],
msg: "name string length is not in this range",
},
},
},
description: {
type: DataTypes.STRING,
field: "description",
validate: {
notEmpty: {
args: true,
msg: "description is Required",
},
is: {
args: ["^[a-z ]+$", "i"],
msg: "In description Only letters allowed",
},
len: {
args: [10, 255],
msg: "description string length is not in this range",
},
},
},
price: {
type: DataTypes.INTEGER,
validate: {},
},
createdOn: {
type: DataTypes.DATE,
get: function () {
var originalDate = this.getDataValue("createdOn");
return moment(originalDate)
.utcOffset(330)
.format("YYYY-MM-DD HH:mm:ss");
},
},
deletedOn: {
type: DataTypes.DATE,
allowNull: true,
defaultValue: null,
get: function () {
var originalDate = this.getDataValue("deletedOn");
return moment(originalDate)
.utcOffset(330)
.format("YYYY-MM-DD HH:mm:ss");
},
},
isDeleted: {
type: DataTypes.BOOLEAN,
allowNull: true,
defaultValue: false
},
},
{
validate: {},
freezeTableName: true,
tableName: "foodItem",
hooks: {
beforeCreate: async (foodItem, options) => {},
},
classMethods: {},
instanceMethods: {},
}
);
//this.work on instance
FoodItem.prototype.getName = function () {
return this.name;
};
//this.work on instance
FoodItem.prototype.CapitalizeName = function () {
var upperCaseFirstName = this.get("name").toUpperCase();
this.set("name", upperCaseFirstName);
return this.save();
};
return FoodItem;
};
Here we are basically giving table name,column names their datatype some
validation on those columns,default value and allowed null or not.
Now in route folder I am adding file sequelizeDemo.js.
Content of sequelizeDemo.js file:
let express = require("express");
let router = express.Router();
let models = require("../sequelizeModels");
let { Op } = require("sequelize");
const { where } = require("sequelize");
/* insert a new food item */
router.post("/insert", async function (req, res, next) {
let foodItemName = req.body.name;
let foodItemDesc = req.body.description;
let foodItemPrice = req.body.price;
try {
let insertedFoodItem = await models.FoodItem.create({
name: foodItemName,
description: foodItemDesc,
price: foodItemPrice,
});
res.json({
message:insertedFoodItem.id > 0 ? "food item added successfully"
: "no food item added",
error: "",
data: [insertedFoodItem],
success: true,
});
} catch (err) {
res.json({
message: "unable to add new food item",
error: err.message.toString(),
data: [],
success: false,
});
}
});
/* get all food item */
router.get("/getAll", async function (req, res, next) {
try {
let foodItems = await models.FoodItem.findAll({
attributes: [
"id",
"name",
"description",
"price",
"createdOn",
"deletedOn",
"isDeleted",
],
});
res.json({
message: foodItems.length
? "all food items retrived successfully"
: "No food item found",
error: "",
data: foodItems,
success: true,
});
} catch (err) {
res.json({
message: "unable to retreive food items",
error: err.message.toString(),
data: [],
success: false,
});
}
});
/* get all food item */
router.get("/getById", async function (req, res, next) {
let foodItemId = req.query.id;
try {
let foodItem = await models.FoodItem.findAll({
attributes: [
"id",
"name",
"description",
"price",
"createdOn",
"deletedOn",
"isDeleted",
],
where: {
id: foodItemId,
},
});
console.log(foodItem);
res.json({
message: foodItem.length
? "food item retrived successfully"
: "no food item found",
error: "",
data: foodItem,
success: true,
});
} catch (err) {
res.json({
message: "unable to retreive food item",
error: err.message.toString(),
data: [],
success: false,
});
}
});
//update food item all columns
router.put("/update", async function (req, res, next) {
let foodItemId = req.body.id;
let foodItemName = req.body.name;
let foodItemDesc = req.body.description;
let foodItemPrice = req.body.price;
try {
let foodItemId = req.body.id;
let numberOfRowsUpdated = await models.FoodItem.update(
{
price: foodItemPrice,
name: foodItemName,
description: foodItemDesc,
},
{ where: { id: foodItemId } }
);
res.json({
message:
numberOfRowsUpdated[0] == 1
? "food item updated successfully"
: "no food item found",
error: "",
data: [],
success: true,
});
} catch (err) {
res.json({
message: "unable to update food item",
error: err.message.toString(),
data: [],
success: false,
});
}
});
// //update foot item price
router.patch("/update/price", async function (req, res, next) {
let foodItemId = req.body.id;
let foodItemPrice = req.body.price;
try {
let foodItemId = req.body.id;
let numberOfRowsUpdated = await models.FoodItem.update(
{ price: foodItemPrice },
{ where: { id: foodItemId } }
);
res.json({
message:
numberOfRowsUpdated[0] == 1
? "food item price updated successfully"
: "no food item found",
error: "",
data: [],
success: true,
});
} catch (err) {
res.json({
message: "unable to update food item price",
error: err.message.toString(),
data: [],
success: false,
});
}
});
//delete food item
router.delete("/delete", async function (req, res, next) {
let foodItemId = req.body.id;
try {
let numberOfRowsDeleted = await models.FoodItem.destroy({
where: {
id: foodItemId,
},
});
res.json({
message:
numberOfRowsDeleted == 1
? "food item deleted successfully"
: "no food item found",
error: "",
data: [],
success: true,
});
} catch (err) {
res.json({
message: "unable to delete food item",
error: err.message.toString(),
data: [],
success: false,
});
}
});
router.delete("/delete/soft", async function (req, res, next) {
try {
let foodItemId = req.body.id;
let numberOfRowsUpdated = await models.FoodItem.update(
{ isDeleted: 1 },
{ where: { id: foodItemId } }
);
console.log("Number Of Rows Updated:", numberOfRowsUpdated);
res.json({
message:
numberOfRowsUpdated[0] == 1
? "food item soft deleted successfully"
: "no food item found",
error: "",
data: [],
success: true,
});
} catch (err) {
res.json({
message: "unable to soft delete food item",
error: err.message.toString(),
data: [],
success: false,
});
}
});
//search based on keyword
router.get("/search", async function (req, res, next) {
let keyword = req.query.keyword;
try {
let foodItems = await models.FoodItem.findAll({
attributes: [
"id",
"name",
"description",
"price",
"createdOn",
"deletedOn",
"isDeleted",
],
where: {
description: {
[Op.like]: "%" + keyword + "%",
},
},
});
res.json({
message: foodItems.length
? "food item searched successfully"
: "no food item found",
error: "",
data: foodItems,
success: true,
});
} catch (err) {
res.json({
message: "unable to search food item",
error: err.message.toString(),
data: [],
success: false,
});
}
});
module.exports = router;
Here we are implementing same route as our previous example
just this time using sequelize.
Now lets mount our route.
Add following code in suitable place in app.js
var sequelizeDemoRouter = require('./routes/sequelizeDemo');
and
app.use('/sequelizeDemo', sequelizeDemoRouter);
this will make our routes available at mountpoint /sequelizeDemo.
E.g. our insert route willbe available at
http://localhost:3001/sequelizeDemo/insert
The complete code is at
https://github.com/gitsangramdesai/express-mysql2-without-promise
No comments:
Post a Comment