I have created common function for CRUD operations.
- I am using Sequelize JS, I have created all models in Sequelize
- I have written stored procedures in mysql for CRUD operations for each module/tables I have
GetItemsGetItemByIdCreateItemUpdateItemDeleteitem
- Then I have created Common CRUD function in Utilities which has below functions.
- Get All Data
exports.getAllData = (spname, res, next) => {
sequelize
.query(
spname,
Sequelize.QueryTypes.SELECT)
.then(result => {
res.status(200).json({
message: "data Fetched from database",
statusCode: 200,
result: result,
});
})
.catch(error => {
if (!error.statusCode) {
error.statusCode = 500;
}
next(error);
});
}- Get Data by ID
exports.getDataById = (spname, parameter, res, next) => {
sequelize.query(
spname,
{
replacements: parameter
},
Sequelize.QueryTypes.SELECT
)
.then(result => {
res.status(200).json({
message: "data Fetched from database",
statusCode: 200,
result: result,
});
})
.catch(error => {
if (!error.statusCode) {
error.statusCode = 500;
}
next(error);
});
}- Create Item
exports.postData = (spname, parameters, res, next) => {
sequelize.query(
spname,
{
replacements: parameters
},
Sequelize.QueryTypes.INSERT
)
.then(result => {
res.status(200).json({
message: "data added to database",
statusCode: 200,
result: result,
});
})
.catch(error => {
if (!error.statusCode) {
error.statusCode = 500;
}
next(error);
});
}- Call that functions in Controller functions
const Category = require("../models/category");
const crud = require('../utility/crud');
exports.getAllCategories = (req, res, next) => {
crud.getAllData('call USP_GetAllCategories()', res, next);
}
exports.getCategoryById = (req, res, next) => {
let parameter = {
categoryId: req.params.id
}
crud.getDataById('CALL USP_GetCategoryById(:categoryId)', parameter, res, next);
}
exports.addCategory = (req, res, next) => {
let parameter = {
name: req.body.name,
image: req.body.image,
createdBy: req.body.createdBy
}
let query = 'CALL USP_CreateCategory(:name,:image,:createdBy)';
crud.postData(query, parameter, res, next);
}I have below questions about it.
- Is it good approach to create stored procedures and call them in application with / without using Sequelize?
- Is it good practice to create common function for CRUD? or should go with writing function for each module ?(I checked on internet, I never found any good satisfactory solution about this)
- Could you please help to review code so I can make it more better ?
