First Create table for our tutorial in mysql
CREATE TABLE `courses` (`id` INTEGER NOT NULL auto_increment ,
`name` VARCHAR(255),
`description` VARCHAR(255),
`fees` INTEGER,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)
)
Insert some dummy data
INSERT INTO courses(name,description,fees,createdAt,updatedAt) values
('c','c',100,now(),now()),
('c++','c++',200,now(),now()),
('c#','c#',300,now(),now()),
Now generate express project using expess generator.
Install sequelize & mysql2 packages along with existing packages in package.json.
Create db.config.js inside config folder ,add config folder first inside this file
module.exports = {
HOST: "localhost",
USER: "root",
PASSWORD: "sangram",
DB: "sangram",
dialect: "mysql",
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
};
In models folder create course.js with
module.exports = (sequelize, Sequelize) => {
const Course = sequelize.define("course", {
name: {
type: Sequelize.STRING
},
description: {
type: Sequelize.STRING
},
fees: {
type: Sequelize.INTEGER
}
});
return Course;
};
Inside controllers add course.js with
const db = require("../models"); // models path depend on your structure
const Course = db.courses;
exports.create = (req, res) => {
// Validate request
if (!req.body.name) {
res.status(400).send({
message: "Content can not be empty!"
});
return;
}
// Create a Course
const course = {
name: req.body.name,
description: req.body.description,
fees: req.body.fees
};
// Save Course in the database
Course.create(course)
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while creating the Course."
});
});
};
exports.findAll = (req, res) => {
const name = req.query.name;
var condition = name ? { name: { [Op.like]: `%${name}%` } } : null;
Course.findAll({ where: condition })
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving courses."
});
});
};
exports.findOne = (req, res) => {
const id = req.params.id;
Course.findByPk(id)
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message: "Error retrieving Course with id=" + id
});
});
};
exports.update = (req, res) => {
const id = req.params.id;
Course.update(req.body, {
where: { id: id }
})
.then(num => {
if (num == 1) {
res.send({
message: "Course was updated successfully."
});
} else {
res.send({
message: `Cannot update Course with id=${id}. Maybe Course was not found or req.body is empty!`
});
}
})
.catch(err => {
res.status(500).send({
message: "Error updating Course with id=" + id
});
});
};
exports.delete = (req, res) => {
const id = req.params.id;
Course.destroy({
where: { id: id }
})
.then(num => {
if (num == 1) {
res.send({
message: "Course was deleted successfully!"
});
} else {
res.send({
message: `Cannot delete Course with id=${id}. Maybe Course was not found!`
});
}
})
.catch(err => {
res.status(500).send({
message: "Could not delete Course with id=" + id
});
});
};
exports.deleteAll = (req, res) => {
Course.destroy({
where: {},
truncate: false
})
.then(nums => {
res.send({ message: `${nums} Courses were deleted successfully!` });
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while removing all courses."
});
});
};
Inside routes add course.js with
var express = require('express');
var router = express.Router();
const courses = require("../controllers/course.js");
var router = require("express").Router();
// Create a new Tutorial
router.post("/", courses.create);
// Retrieve all Courses
router.get("/", courses.findAll);
// Retrieve a single Course with id
router.get("/:id", courses.findOne);
// Update a Course with id
router.put("/:id", courses.update);
// Delete a Course with id
router.delete("/:id", courses.delete);
// Create a new Course
router.delete("/", courses.deleteAll);
module.exports = router;
Now in app.js add
var courseRouter = require('./routes/course');
app.use('/course', courseRouter);
and
const db = require("./models");
db.sequelize.sync();
On Postman now you can test endpoints
1) GET localhost:3000/course
2) POST localhost:3000/course
with json body
{
"name": "python",
"description": "python",
"fees": 400
}
3)PUT localhost:3000/course
with json body
{
"name": "CPP",
"description": "CPP",
"fees": 400
}
4)DELETE localhost:3000/course/3
delete records with pertcular id
5)DELETE localhost:3000/course
delete all records
Code for tutorial is available at https://github.com/gitsangramdesai/express-sequelize-mysql-node.
No comments:
Post a Comment