In this article, We will discuss how to create Node API for CRUD (Create, Read, Update and Delete) API with MySQL Database. we will cover all the points from create fresh application in Node.js and create mysql Database in XAMPP local server.
For creating Node.js API with MySQL, we have to create fresh Node.js Applicaion in our System. For creating node.js application you have to run the following commain in terminal or your command prompt.
mkdir node-api-with-mysql
from the above command you can create new directory named : node-api-with-mysql
Now you have to move the newly created directory to make Node.js Project in that directory.
cd node-api-with-mysql
After then we will create package.json file by using the following command. The package.json file stores metadata and dependencies for your package that will allow you to manage your project.
npm init -y
from the above command you will get the following json file
In this application, we will use the following packages:
npm install express mysql2 body-parser
Ohk! Now we have create basic dependencies for our application. Now let's move to make code for create api.
For Creating the database in mysql, we will use the xampp server in our local system. if you dont have any idea how to install xampp in local system. i have created article for tha you can see from the link : how to install xampp?
After insalling the xampp. you have to create new database. In this article we have create node-api named database and import the followiing Table :
--
-- Table structure for table `products`
--
CREATE TABLE `products` (
`id` int(11) NOT NULL,
`product_name` varchar(255) NOT NULL,
`product_description` text NOT NULL,
`product_price` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `products`
--
ALTER TABLE `products`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
Now we have to create database connection with mysql. for that web. have to create directory named `config` and create file `db.js`
const mysql = require("mysql2");
const db = mysql.createConnection({
host:"localhost",
user:"root",
password:"",
database:"node-api"
})
db.connect( (err) => {
if(err) throw err;
if (err) throw err;
console.log("Database connection has been completed");
})
module.exports = db;
Create the directory named `models` and create new file `products.js`
const db = require("../config/db");
const Product = {};
Product.getAll = (callback) => {
let dbQry = "SELECT * FROM products";
db.query(dbQry, (err,results) => {
if(err){
callback(err,null);
}else{
callback(null,results)
}
});
}
Product.create = ( productData , callback ) => {
let inserData = {
"product_name" : productData.product_name,
"product_description" : productData.product_description,
"product_price" : productData.product_price
}
let dbQry = "INSERT INTO products SET ?";
db.query(dbQry, inserData , (err,results) => {
if(err){
callback(err,null)
}else{
callback(null,results);
}
})
}
Product.update = (productData, id, callback) => {
let updateProductData = {
"product_name" : productData.product_name,
"product_description" : productData.product_description,
"product_price" : productData.product_price
}
let dbQry = "UPDATE products SET ? WHERE id=?";
db.query(dbQry,[updateProductData,id],(err, results) => {
if(err){
return callback(err,null);
}else{
return callback(null,results);
}
});
}
Product.delete = (id,callback) => {
let dbQry = "DELETE FROM products WHERE id=?";
db.query(dbQry,id, (err,results) => {
if(err){
return callback(err,null)
}else{
return callback(null,results);
}
})
}
module.exports = Product;
After creating the model we have to create the controller for making the request to perform operation and send response for the perticular request. To create contoller make directory name `controllers` and create file `productController.js`
const Product = require("../model/products");
exports.getAllProducts = (req,res) => {
Product.getAll( (err,products) => {
if(err){
res.status(500).send(err)
}else{
resData = {
status:1,
message:"Product data",
data:products
}
res.json(resData);
}
})
}
exports.createProduct = (req,res) => {
const inserData = {
"product_name" : req.body.product_name,
"product_description" : req.body.product_description,
"product_price" : req.body.product_price
}
Product.create(inserData, (err , results) => {
if(err){
let resData = {
status:0,
message:err,
data:null
}
res.json(resData);
}else{
let resData = {
status:1,
message:"Product has been added successfully!",
data:null
}
res.json(resData);
}
})
}
exports.updateProduct = (req,res) => {
let id = req.params.id;
let updateData = {
"product_name" : req.body.product_name,
"product_description" : req.body.product_description,
"product_price" : req.body.product_price
}
Product.update(updateData,id, async(err,results) => {
if(err){
res.status(500).send(err);
}else{
let resData = {
status:1,
message:"Product has been updated successfully!",
data:null
}
res.json(resData);
}
});
}
exports.deleteProduct = (req,res) => {
let id = req.params.id;
Product.delete(id, (err,results) => {
if(err){
res.status(500).send(err)
}else{
let resData = {
status:1,
message:"Product has been deleted successfully!",
data:null
}
res.json(resData);
}
})
}
After creating the controller we have to define the routes to make request for controller and handel response from the controller. For creating routes we have to create new directory name `routes` and make file `productRoutes.js`
const express = require("express");
const router = express.Router();
const productController = require("../controller/productController");
router.get("/",productController.getAllProducts);
router.post("/add",productController.createProduct);
router.put("/update/:id",productController.updateProduct);
router.delete("/delete/:id",productController.deleteProduct);
module.exports=router;
after creating Model, Controller and routes,we have to setup the server for serve the api, For that we have to create main file like `index.js` or `server.js` file. in this article we will create `index.js` file on root and need to define port number to execute application on perticular port. In this application we have define port no : 3000
const express = require("express");
const bodyParser = require("body-parser");
const productRoutes = require("./routes/productRoutes");
const app = express();
const port = 3000;
app.use(bodyParser.json());
app.use("/products",productRoutes);
app.listen(port,() => {
console.log("Server is running on " + port)
})
Finally, We have complete the application and we can check in postman by executing the following URLS:
1. List the Products :
2. Add New Product:
{
"product_name" : "test",
"product_description" : "test",
"product_price" : "500"
}
3.Update the Product
{
"product_name" : "test",
"product_description" : "test",
"product_price" : "500"
}
4. DELETE the Product:
So in this article we have discuss : How to create node.js API for CRUD in mysql. and in this article we have try to clear each every process step by step. Please read it carefully and try it by your self.
Thank you
Please download the code from the following Link :
Download