Simple Full Stack Products CRUD App using React, Node JS & MySQL
Hello friends, Today in this post we will learn how to build a simple Full Stack Products CRUD App using React, Node & MySQL. So here we will go next further “Learn React JS by building this one Web App**”**and try to implement other React concepts by building this Product Inventory CRUD web app so by the end of this post you will be confident about React and able to build your own web apps. So first step will be creating our API in Node JS with the help of MySQL Database and later we will build UI for accessing this API. Also, this post will be updated based on your suggestion, please feel to contact/comment with us for suggestions that will help in improving this post.
Prerequisites —
Basic Understanding of React you can refer my previous post on “Learn React JS by building this one Web App”.
Basic Understanding of Node JS & MySQL.
MySQL installed in your machine. If not installed please follow official docs for installation and setup your login details. Install along with MySQL Workbench
I am using VSCode as my code editor, feel free to choose your own favourite editor.
Setup MySQL
Open MySQL Workbench, write a new query to create a new database products
create database products;
Now create a new table product inside product database
use products;
CREATE TABLE `product` (
`productId` varchar(200) NOT NULL,
`productTitle` varchar(300) DEFAULT NULL,
`productDescription` varchar(500) DEFAULT NULL,
`productPrice` float DEFAULT NULL,
`availableQuantity` int DEFAULT NULL,
`productThumbnail` varchar(500) DEFAULT NULL,
PRIMARY KEY (`productId`)
) ;
Create a NODE JS App —
Lets create a new project product-crud-backend and open command prompt and run the command
npm init -y
Lets install all necessary packages express (node js web application framework), mysql(for connecting node js app with sql) and cors (for configuring cross origin request ), dotenv for loading environment variable file where we store password , multer for uploading files. We will discuss this package later when we use it.
npm i express mysql cors dotenv multer
CRUD API using NODE JS and MySQL —
Lets create an API using Node JS, we will use express framework to create a Node app and we will expose API’s using get, post, put and delete request. Calling this express() will help us in using various features and allow us to http requests.
For exposing a port where our application can run we will use app.listen method of express and specify a PORT number which will give a callback.
./index.js
const express = require("express"); //import express
const app = express()
app.listen(8081, () => {
console.log("listening")
})
If you run node index in console you can see server listening.
Middleware is a function which is have access to request and response object and can modify it and pass it to the next cycle.
Now lets use some middleware functions of express,
express.json() — it helps us in parsing incoming request as JSON
cors() — To avoid errors like
Access has been blocked by CORS policy: Response to preflight request doesn’t pass access control check: No ‘Access-Control-Allow-Origin’ header is present on the requested resource.
because by default browser will not allow to access resources from one host to another host.
./index.js
const express = require("express"); //import express
const cors = require('cors');
const app = express()
app.use(express.json())
app.use(cors())
app.listen(8081, () => {
console.log("listening")
})
Now lets import mysql, fore starting with for mysql connection, we will createConnection function of mysql package which requires object as a parameter containing key host, user, password, database(database name). For improving the security instead of directly specifying password in index.js file lets create .env file.
.env
DB_USERNAME=test
DB_PASSWORD=test
Now to use this we need load .env file by calling config method of dotenv. After we can get the values using process.env.DB_USERNAME.
./index.js
const mysql = require("mysql");
require('dotenv').config()
console.log(process.env)
console.log(process.env.DB_PASSWORD)
const db = mysql.createConnection({
host: "localhost",
user: process.env.DB_USERNAME,
password: process.env.DB_PASSWORD,
database: "products",
});
Lets add query to read, write, update and delete operations in the corresponding request. For reading all products, create a variable q and assign to ‘select * from products’
To insert a new product the syntax would be like —
insert into tablename(column1, column2…) values (value1, value2…)
For updating a new product —
update tablename set column1=value1, column2=value2… where column=value
For deleting a product
delete from tablename where condition1,..
? — used for dynamic values
....
app.use(cors())
app.get("/products", (req, res) => {
const q = "select * from product";
db.query(q, (err, data) => {
console.log(err, data);
if (err) return res.json({ error: err.sqlMessage });
else return res.json({ data });
});
});
app.post("/products", (req, res) => {
const q = `insert into product(productId, productTitle, productDescription, productPrice, availableQuantity, productThumbnail)
values(?)`;
const values = [...Object.values(req.body)];
console.log("insert", values);
db.query(q, [values], (err, data) => {
console.log(err, data);
if (err) return res.json({ error: err.sqlMessage });
else return res.json({ data });
});
});
app.get("/products/:productId", (req, res) => {
const id = req.params.productId;
const q = "SELECT * FROM product where productId=?";
db.query(q, [id], (err, data) => {
console.log(err, data);
if (err) return res.json({ error: err.sqlMessage });
else return res.json({ data });
});
});
app.put("/products/:productId", (req, res) => {
const id = req.params.productId;
console.log("updated " + req.body);
const data = req.body;
const q =
"update product set " +
Object.keys(data)
.map((k) => `${k} = ?`)
.join(",") +
" where productId='" +
id +
"'";
console.log(q);
db.query(q, [...Object.values(data)], (err, out) => {
console.log(err, out);
if (err) return res.json({ error: err.message });
else {
return res.json({ data: out });
}
});
});
app.delete("/products/:productId", (req, res) => {
const id = req.params.productId;
console.log("deleting " + id, req.body);
const { productThumbnail } = req.body;
console.log(req.body);
const q = `DELETE FROM product WHERE productId= ?`;
db.query(q, [id], (err, data) => {
console.log(err, data);
if (err) return res.json({ error: err.sqlMessage });
else res.json({data})
})
});
...
You can test this API to create, read, update and delete product using Postman.
We will look on how to upload file once we have our frontend ready. As of now we will now create a new folder uploads and put a sample image in it so that until we add that feature we can use this.
Now lets create a frontend for using this API.
Create a new React app —
npx create-react-app fullstack-product-crud-app
Open your command prompt Ctrl + J in vs code, and type npm run start. Now In your browser, you will be able to view the web application.
You can try your own styles but here we will install tailwindcss for speed up styling, you can install tailwind by following the official docs.
First of all lets create a file where we can define all our api calls, create folder features under src and inside that create apiCalls.js. Import axios and we will create and export functions for each request.
src/features/apiCalls.js
import axios from "axios";
export const getProducts = async () => {
try {
const res = await axios.get("http://localhost:8081/products/");
return res.data;
} catch (err) {
console.log(err);
return err;
}
};
export const getProductById = async (id) => {
try {
const res = await axios.get("http://localhost:8081/products/" + id);
return res.data
} catch (err) {
return {error: err.message}
}
};
export const addProduct = async (product) => {
try {
const res = await axios.post("http://localhost:8081/products/", product);
return res.data;
} catch (err) {
console.log(err);
return { error: err };
}
};
export const updateProduct = async (product, productId) => {
try {
const res = await axios.put(
"http://localhost:8081/products/" + productId,
product
);
return res.data;
} catch (err) {
return {
error: err,
};
}
};
export const deleteProduct = async (productId, productThumbnail) => {
try {
const res = await axios.delete(
"http://localhost:8081/products/" + productId
);
return res.data;
} catch (err) {
return { error: err };
}
};
Lets start by creating a simple component for displaying product, create a new folder components under src and create new file named Product.js. We will have a tags to show productThumbnail, productTitle, productDescription, productPrice, availableQuantity. I will be using some basic styles like flex, fonts, width, height, etc.
import React from "react";
const BASE_API_URL = "http://localhost:8081";
const Product = ({
productId,
productThumbnail,
productTitle,
productPrice,
productDescription,
availableQuantity,
}) => {
return (
<>
<div className="border-2 rounded overflow-hidden flex flex-col">
{productThumbnail ? (
<img
className="h-52 w-full object-cover"
alt={productId + "-thumbnail"}
src={`${BASE_API_URL}/uploads/${productThumbnail}`}
/>
) : (
<img
className="h-52 w-full object-cover"
alt={productId + "-thumbnail"}
src={`${BASE_API_URL}/uploads/sample.png`}
/>
)}
<div className="flex flex-col p-4">
<h4 className="mb-1 text-xl font-medium mt-5">{productTitle}</h4>
<p className="text-lg mb-4">{productDescription}</p>
<div className="flex items-center justify-between">
<p className="py-1 px-3 bg-slate-200 w-fit text-2xl font-semibold rounded">
₹ {productPrice}
</p>
<p className="font-bold text-xl">
{availableQuantity > 0
? `In Stock: ${availableQuantity}`
: "Out of Stock"}
</p>
</div>
</div>
</div>
</>
);
};
export default Product;
This would be multi page app so lets create new folder pages under src and create two files Products.js and AddProduct.js. Now in the Product.js, we will add code to fetch all products, so here we will create a useEffect with an empty dependency array and for side effect we will call our api using axios get request and store that response into an state. In return statement we will map this stored products and return the Product component and pass the data as props to it.
import React from "react";
const BASE_API_URL = "http://localhost:8081";
const Product = ({
productId,
productThumbnail,
productTitle,
productPrice,
productDescription,
availableQuantity,
}) => {
return (
<>
<div className="border-2 rounded overflow-hidden flex flex-col">
{productThumbnail ? (
<img
className="h-52 w-full object-cover"
alt={productId + "-thumbnail"}
src={`${BASE_API_URL}/uploads/${productThumbnail}`}
/>
) : (
<img
className="h-52 w-full object-cover"
alt={productId + "-thumbnail"}
src={`${BASE_API_URL}/uploads/sample.png`}
/>
)}
<div className="flex flex-col p-4">
<h4 className="mb-1 text-xl font-medium mt-5">{productTitle}</h4>
<p className="text-lg mb-4">{productDescription}</p>
<div className="flex items-center justify-between">
<p className="py-1 px-3 bg-slate-200 w-fit text-2xl font-semibold rounded">
₹ {productPrice}
</p>
<p className="font-bold text-xl">
{availableQuantity > 0
? `In Stock: ${availableQuantity}`
: "Out of Stock"}
</p>
</div>
</div>
</div>
</>
);
};
export default Product;
./src/App.js
import Products from "./pages/Products";
function App() {
return (
<div><Products/></div>
}
export default App;
Now for adding a new product we need a separate page so for working on different screens for navigation we need a new package react-router-dom. Here in our App.js in return statement clear everything and add <BrowserRouter> which is a parent element for where we wrap our Routes. Now under BrowserRouter add <Routes> and inside that we will add two Route one for Products page and other for AddProduct. We will use AddProduct component for update as well so lets another route /updateProduct/:id and pass AddProduct as element. Here, :id is param which is dynamic.
import Navbar from "./components/Navbar";
import { BrowserRouter, Route, Routes } from "react-router-dom";
import AddProduct from "./pages/AddProduct";
import Products from "./pages/Products";
import Modal from "./components/Modal";
function App() {
return (
<div>
<Navbar />
<BrowserRouter>
<Routes>
<Route path="/addProduct" element={<AddProduct />} />
<Route path="/updateProduct/:id" element={<AddProduct />} />
<Route path="/" element={<Products />} />
</Routes>
</BrowserRouter>
</div>
);
}
export default App;
In AddProduct create a form with multiple inputs necessary for inserting product into database. Now when submit button is clicked we will axios post request for adding products and pass the data.
import React, { useEffect, useState } from "react";
import {
addProduct,
} from "../features/products/apiActions";
const BASE_API_URL = "http://localhost:8081";
const AddProduct = () => {
const [defaultValue, setDeafaultValue] = useState({
productTitle: "",
productDescription: "",
productPrice: "",
availableQuantity: "",
});
const navigate = useNavigate()
const handleSubmit = async (e) => {
e.preventDefault();
console.log(e.target);
let formData = new FormData(e.target);
try {
let formValues = { productId: pId, ...values, productImage:"test-product.jpg" };
const success = await addProduct(formValues);
if (success) navigate("/")
} catch (err) {
console.log(err);
}
};
return (
<div className="container max-w-5xl py-10">
<div className="flex space-x-6 mb-10 items-center">
<button onClick={()=>navigate(-1)} className="h-10 leading-none text-xl">{"<"}</button>
<h2 className="text--title">
{defaultValue.productTitle ? "Update Product" : "Add Product"}
</h2>
</div>
<div className="flex flex-col">
<form onSubmit={handleSubmit}>
<div className="mb-4">
<label>Product Title</label>
<input
name="productTitle"
placeholder="Enter Product Title..."
type="text"
/>
</div>
<div className="mb-4">
<label>Product Description</label>
<textarea
name="productDescription"
className="resize-none"
rows={5}
></textarea>
</div>
<div className="mb-4">
<label>Product Price</label>
<input
name="productPrice"
placeholder="Enter Product Price..."
/>
</div>
<div className="mb-4">
<label>Available Quantity</label>
<input
name="availableQuantity"
placeholder="Enter Available Quantity..."
/>
</div>
<div className="flex items-center mb-5">
<button className="w-full">Submit</button>
</div>
</form>
</div>
</div>
);
};
export default AddProduct;
Now for update product lets create a state for storing default values and also we need to have useEffect to load a default values by using the id from url parameter. Using useParams() we can get the id from the url. In useEffect we can call getProductById and store it in default values. Now lets set default values for input and in submit function lets add condition to check product id exists then updateProduct to be called else addProduct will be called.
import React, { useEffect, useState } from "react";
import { useNavigate, useParams } from "react-router-dom";
import { addProduct, updateProduct, getProductById } from "../features/apiCalls";
const BASE_API_URL = "http://localhost:8081";
const AddProduct = () => {
const { id } = useParams();
const [defaultValue, setDeafaultValue] = useState({
productTitle: "",
productDescription: "",
productPrice: "",
availableQuantity: "",
});
const navigate = useNavigate();
useEffect(() => {
const getProduct = async () => {
const {data} = await getProductById(id);
console.log(data);
if (data) setDeafaultValue({ ...data[0] });
};
getProduct();
}, [id]);
const {
productTitle,
productDescription,
productPrice,
availableQuantity,
productThumbnail,
productId,
} = defaultValue;
console.log(productId);
const handleSubmit = async (e) => {
e.preventDefault();
console.log(e.target);
let formData = new FormData(e.target);
const values = Object.fromEntries(formData.entries());
const pId = !productId
? values.productTitle.toLowerCase().replaceAll(/[\s\t]+/g, "-")
: productId;
try {
if (pId && !!productId) {
let { data, error } = await updateProduct(values, productId);
if (error) throw new Error(error);
} else if (pId) {
let formValues = {
productId: pId,
...values,
productThumbnail: "test-product.jpg",
};
let { data, error } = await addProduct(formValues);
if (error) throw new Error(error);
}
} catch (err) {
console.log(err);
}
};
return (
<div className="container max-w-5xl py-10">
<div className="flex space-x-6 mb-10 items-center">
<button
onClick={() => navigate(-1)}
className="h-10 leading-none text-xl"
>
{"<"}
</button>
<h2 className="text--title">
{defaultValue.productTitle ? "Update Product" : "Add Product"}
</h2>
</div>
<div className="flex flex-col">
<form onSubmit={handleSubmit}>
<div className="mb-4">
<label>Product Title</label>
<input
defaultValue={productTitle || ""}
name="productTitle"
placeholder="Enter Product Title..."
type="text"
/>
</div>
<div className="mb-4">
<label>Product Description</label>
<textarea
defaultValue={productDescription || ""}
name="productDescription"
className="resize-none"
rows={5}
></textarea>
</div>
<div className="mb-4">
<label>Product Price</label>
<input
defaultValue={productPrice}
name="productPrice"
placeholder="Enter Product Price..."
/>
</div>
<div className="mb-4">
<label>Available Quantity</label>
<input
defaultValue={availableQuantity}
name="availableQuantity"
placeholder="Enter Available Quantity..."
/>
</div>
<div className="flex items-center mb-5">
<button className="w-full">Submit</button>
</div>
</form>
</div>
</div>
);
};
export default AddProduct;
We will also add a new API route which will perform task of uploading thumbnail image to our server in the uploads path and store the image name in the database.
For handling image file lets jump on to our node js project and install a new dependency **multer, so basically multer will help us in accepting multipart form data so that we can transfer files from our frontend to backend.**For multer we need to configure a storage option, for that lets call multer.diskStorage which requires object with properties destination which is callback function where need to pass folder in which we want to store the file and other property is filename where we need to pass the file name which we need to store, i will pass product id as a filename with its extension.
Now create a new variable to store multer({storage}), it provides upload object which provides a function single function using which we can upload file.
./index.js
...
const multer = require("multer");
const fs = require("fs");
const storage = multer.diskStorage({
destination: (req, file, cb) => {
cb(null, "uploads");
},
filename: (req, file, cb) => {
console.log(req.body, "in");
cb(null, `${req.body.productId}${path.extname(file.originalname)}`);
},
});
const upload = multer({ storage: storage });
...
app.post("/thumbnailUpload", upload.single("productThumbnail"), (req, res) => {
try {
console.log(req.file);
return res.json({ data: req.file.filename });
} catch (err) {
res.json({ error: err.message });
}
});
...
Lets create a function to call this API in ./features/apiCalls.js
./src/features/apiCalls.js
...
export const uploadProductThumbnail = async (formData) => {
try {
const res = await axios.post(
"http://localhost:8081/thumbnailUpload/",
formData
);
return res.data
} catch (err) {
console.log(err);
return {error: err.message};
}
};
For image preview we will have state on whenever it changed we will generate a object url which we can use with img tag.
./src/pages/AddProduct.js
...
const [selectedImage, setSelectedImage] = useState();
const [previewUrl, setPreviewUrl] = useState();
...
useEffect(() => {
let url;
if (selectedImage) {
url = URL.createObjectURL(selectedImage);
setPreviewUrl(url);
}
return () => {
URL.revokeObjectURL(url);
};
}, [selectedImage]);
...
return (
<div className="container max-w-5xl py-10">
...
<div className="mb-10">
<label>Product Thumnail</label>
<input
onChange={(e) => {
setSelectedImage(e.target.files[0]);
}}
accept="image/*"
name="productThumbnail"
type={"file"}
/>
{(productThumbnail || previewUrl) && (
<img
className="h-48"
alt="thumbnail"
src={
previewUrl
? previewUrl
: `${BASE_API_URL}/uploads/${productThumbnail}`
}
/>
)}
</div>
</div>
...
);
...
Now in our submit function we will add logic to fetch files from input and pass it to the api.
const handleSubmit = async (e) => {
e.preventDefault();
console.log(e.target);
let formData = new FormData(e.target);
let fileFormData = new FormData();
let files = e.target[4].files;
const values = Object.fromEntries(formData.entries());
const pId = !productId
? values.productTitle.toLowerCase().replaceAll(/[\s\t]+/g, "-")
: productId;
fileFormData.append("productId", pId);
delete values.productThumbnail
try {
if (!!selectedImage) {
fileFormData.append("productThumbnail", files[0]);
let {data, error} = await uploadProductThumbnail(fileFormData);
if (error) throw new Error(error)
values["productThumbnail"] = filename;
}
if (pId && !!productId) {
let { data, error } = await updateProduct(values, productId);
if (error) throw new Error(error);
} else if (pId) {
let formValues = {
productId: pId,
...values,
productThumbnail: "test-product.jpg",
};
let { data, error } = await addProduct(formValues);
if (error) throw new Error(error);
}
} catch (err) {
console.log(err);
}
};
...
In our product component lets add a delete button and onClick we will call delete request.
import React from "react";
import { Link } from "react-router-dom";
import { deleteProduct } from "../features/apiCalls";
const BASE_API_URL = "http://localhost:8081";
const Product = ({
productId,
productThumbnail,
productTitle,
productPrice,
productDescription,
availableQuantity,
}) => {
return (
<>
<div className="border-2 rounded overflow-hidden flex flex-col">
{productThumbnail ? (
<img
className="h-52 w-full object-cover"
alt={productId + "-thumbnail"}
src={`${BASE_API_URL}/uploads/${productThumbnail}`}
/>
) : (
<div className="w-full h-48 bg-slate-200 rounded"></div>
)}
<div className="flex flex-col p-4">
<h4 className="mb-1 text-xl font-medium mt-5">{productTitle}</h4>
<p className="text-lg mb-4">{productDescription}</p>
<div className="flex items-center justify-between">
<p className="py-1 px-3 bg-slate-200 w-fit text-2xl font-semibold rounded">
₹ {productPrice}
</p>
<p className="font-bold text-xl">
{availableQuantity > 0
? `In Stock: ${availableQuantity}`
: "Out of Stock"}
</p>
</div>
<div>
<div className="flex flex-col sm:flex-row items-center space-y-5 sm:space-y-0 sm:space-x-5 my-5">
<Link className="w-full" to={`/updateProduct/${productId}`}>
<button className="uppercase w-full font-medium">Update</button>
</Link>
<button
onClick={async () => {
await deleteProduct(productId, productThumbnail)
}}
className="uppercase border-gray-500 text-gray-500 w-full font-medium"
>
Delete
</button>
</div>
</div>
</div>
</div>
</>
);
};
export default Product;
Now we have CRUD operations ready we can now test this app. Also we will add some enhancements like adding state management with redux, adding Modal, Toast , etc in our next post.
Github repository
FullStack Product CRUD App | Github
Thanks for reading this post, hope you learned and built the application and try to design and implement your own features to it. if you found this post helpful please share maximum, Thanks for reading 😊 Stay tuned.
If you are facing any issues please contact us from our contact section.
Also please don’t forget to subscribe to our youtube channel codewithmarish for all web development-related challenges.