In this tutorial, I will show you how to build a full-stack React + Node.js + MySQL CRUD example with Express.CRUD Operation Using React & Node
The back-end server uses Node.js + Express for REST APIs, front-end side is a React.js client with React Router, Axios & Bootstrap.
React, Node.js & MySQL CRUD example Short Description
- REST APIs in Nodejs & interacts with MySQL Database using Sequelize ORM. React Client sends HTTP Requests and retrieves HTTP Responses using Axios, consume data on the components. React Router is used for navigating to pages.CRUD Operation Using React, Nodejs, Express & MySQL
Data base Design
--
-- Database: `node_react`
--
-- --------------------------------------------------------
--
-- Table structure for table `employees`
--
CREATE TABLE `employees` (
`id` bigint(20) UNSIGNED NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`salary` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employees`
--
INSERT INTO `employees` (`id`, `first_name`, `last_name`, `email`, `phone`, `salary`) VALUES
(11, 'Anthony ', 'Dsza', 'anst@desza', '910202222', '20000'),
(12, 'Baljeet ', 'Singh', 'baljeet @singh', '627292925', '60000'),
(13, 'Anisha', 'Rai', 'anisha@gmail.com', '378282823', '100000'),
(14, 'Jennifer', 'Sembard', 'jenn@yahoo.com', '920202005', '50000'),
(15, 'Ashish', 'Singh', 'bbsingh@gmail.com', '331311478', '5000'),
(16, 'Imran ', 'Sheikh', 'imran@gmail.com', '922828224', '30000'),
(17, 'Benard', 'Litham', 'benard@gmail.com', '829299202', '80000');
Node.js Back-end
We will follow MVC Pattern for making REST API in Nodejs .
The src folder will have 3 folders :- controller , model & routes
First make MySQL Connection file inside config folder . We will call below config file in our src/models/employee.model.js file using this line of code . var dbConn = require(‘../../config/db.config’); .
db.config.js
const mysql = require('mysql');
// create here mysql connection
const dbConn = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node_react'
});
dbConn.connect(function(error){
if(error) throw error;
console.log('Database Connected Successfully!!!');
})
module.exports = dbConn;
Now, We will make an index.js file which will include some important code of express js. You must install cors otherwise You will get a cors error in Reactjs form. ( npm install cors ) . We have already called cors in index.js file
index.js
const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors'); //
const app = express();
app.use(cors())
// create express app
// setup the server port
const port = process.env.PORT || 5000;
// parse request data content type application/x-www-form-rulencoded
app.use(bodyParser.urlencoded({extended: false}));
// parse request data content type application/json
app.use(bodyParser.json());
// define root route
app.get('/', (req, res)=>{
res.send('Hello World');
});
// import employee routes
const employeeRoutes = require('./src/routes/employee.route');
// create employee routes
app.use('/api/v1/employee', employeeRoutes);
// listen to the port
app.listen(port, ()=>{
console.log(`Express is running at port ${port}`);
});
Now We will make a controller file employee.controller.js inside the controllers’ folder. The Controller file will pass react form-data request to the model. The model will perform ADD, DELETE , UPDATE & DISPLAY Queries.
employee.controller.js ( src/controllers/employee.controller.js )
const EmployeeModel = require('../models/employee.model');
// get all employee list
exports.getEmployeeList = (req, res)=> {
//console.log('here all employees list');
EmployeeModel.getAllEmployees((err, employees) =>{
console.log('We are here');
if(err)
res.send(err);
console.log('Employees', employees);
res.send(employees)
})
}
// get employee by Name for earch by Name
exports.getEmployeeByName = (req, res)=>{
//console.log('get emp by id');
EmployeeModel.getEmployeeByName(req.params.first_name, (err, employee)=>{
if(err)
res.send(err);
console.log('single employee data',employee);
res.send(employee);
})
}
// create new employee
exports.createNewEmployee = (req, res) =>{
const employeeReqData = new EmployeeModel(req.body);
console.log('employeeReqData', employeeReqData);
// check null
if(req.body.constructor === Object && Object.keys(req.body).length === 0){
res.send(400).send({success: false, message: 'Please fill all fields'});
}else{
EmployeeModel.createEmployee(employeeReqData, (err, employee)=>{
if(err)
res.send(err);
res.json({status: true, message: 'Employee Created Successfully', data: employee.insertId})
})
}
}
// get employee by ID for Update
exports.getEmployeeByID = (req, res)=>{
//console.log('get emp by id');
EmployeeModel.getEmployeeByID(req.params.id, (err, employee)=>{
if(err)
res.send(err);
console.log('single employee data',employee);
// res.json({"first_name":"Dheeraj"});
res.send(JSON.stringify({ status: 200, error: null, response: employee }));
})
}
// update employee
exports.updateEmployee = (req, res)=>{
const employeeReqData = new EmployeeModel(req.body);
console.log('employeeReqData update', employeeReqData);
// check null
if(req.body.constructor === Object && Object.keys(req.body).length === 0){
res.send(400).send({success: false, message: 'Please fill all fields'});
}else{
EmployeeModel.updateEmployee(req.params.id, employeeReqData, (err, employee)=>{
if(err)
res.send(err);
res.json({status: true, message: 'Employee updated Successfully'})
})
}
}
// delete employee
exports.deleteEmployee = (req, res)=>{
EmployeeModel.deleteEmployee(req.params.id, (err, employee)=>{
if(err)
res.send(err);
res.json({success:true, message: 'Employee deleted successully!'});
})
}
Now , We create a model file with named employee.model.js for CRUD Request .
employee.model.js ( src/controllers/employee.model.js )
var dbConn = require('../../config/db.config');
var Employee = function(employee){
this.first_name = employee.fname;
this.last_name = employee.lname;
this.email = employee.email;
this.phone = employee.phone;
this.salary = employee.salary;
}
// get all employees
Employee.getAllEmployees = (result) =>{
dbConn.query('SELECT * FROM employees', (err, res)=>{
if(err){
console.log('Error while fetching employess', err);
result(null,err);
}else{
console.log('Employees fetched successfully');
result(null,res);
}
})
}
// get employee by Name for Search Data by name
Employee.getEmployeeByName = (first_name, result)=>{
dbConn.query('SELECT * FROM employees WHERE first_name=?', first_name, (err, res)=>{
if(err){
console.log('Error while fetching employee by id', err);
result(null, err);
}else{
result(null, res);
}
})
}
// create new employee
Employee.createEmployee = (employeeReqData, result) =>{
dbConn.query('INSERT INTO employees SET ?', employeeReqData, (err, res)=>{
if(err){
console.log('Error while inserting data');
result(null, err);
}else{
console.log('Employee created successfully');
result(null, res)
}
})
}
// get employee by ID for update
Employee.getEmployeeByID = (id, result)=>{
dbConn.query('SELECT * FROM employees WHERE id=?', id, (err, res)=>{
if(err)
{
console.log('Error while fetching employee by id', err);
result(null, err);
}
else
{
result(null, res);
}
})
}
// update employee
Employee.updateEmployee = (id, employeeReqData, result)=>{
dbConn.query("UPDATE employees SET first_name=?,last_name=?,email=?,phone=?,salary=? WHERE id = ?", [employeeReqData.first_name,employeeReqData.last_name,employeeReqData.email,employeeReqData.phone,employeeReqData.salary, id], (err, res)=>{
if(err){
console.log('Error while updating the employee');
result(null, err);
}else{
console.log("Employee updated successfully");
result(null, res);
}
});
}
// delete employee
Employee.deleteEmployee = (id, result)=>{
dbConn.query('DELETE FROM employees WHERE id=?', [id], (err, res)=>{
if(err){
console.log('Error while deleting the employee');
result(null, err);
}else{
result(null, res);
}
})
// dbConn.query("UPDATE employees SET is_deleted=? WHERE id = ?", [1, id], (err, res)=>{
// if(err){
// console.log('Error while deleting the employee');
// result(null, err);
// }else{
// console.log("Employee deleted successfully");
// result(null, res);
// }
// });
}
module.exports = Employee;
Now Last file is routing Which very important for any project . In this file We will make some routes for our React form request.
employee.route.js ( src/routes/employee.route.js )
const express = require('express');
const router = express.Router();
const employeeController = require('../controllers/employee.controller');
// get all employees
router.get('/', employeeController.getEmployeeList);
// get employee by ID
router.get('/:id',employeeController.getEmployeeByID);
// get ID for Update
router.get('/searchRecord/:first_name',employeeController.getEmployeeByName);
// create new employee
router.post('/', employeeController.createNewEmployee);
// update employee
router.put('/:id', employeeController.updateEmployee);
// delete employee
router.delete('/:id',employeeController.deleteEmployee);
module.exports = router;
Our REST API is completed for NODEJS here now We will make React form.
ReactJS Front-end
We will use axios and fetch request in our React app . Read about more on Reactjs CRUD
To Install AXIOS You can use NPM or YARN npm install axios OR yarn add axios . We will also use Bootstrap for responsive Page . npm install bootstrap or yarn add bootstrap .
I am using Axios for data sending to Laravel so you can use fetch or Axios. To Install Axios write command npm install axios or yarn add axios. Read more about axios() and fetch()
Axios: Axios is a Javascript library used to make HTTP requests from node.js or XMLHttpRequests from the browser and it supports the Promise API that is native to JS ES6.
Fetch: The Fetch API provides a fetch() method defined on the window object. It also provides a JavaScript interface for accessing and manipulating parts of the HTTP pipeline (requests and responses).
I will use Functional Component not Class component btw You can use Class component but hooks make our life easy in Function Component .
We will install more packages npm install react-router-dom ( for routing ) , and npm install react-bootstrap
"axios": "^0.21.1",
"bootstrap": "^5.0.1",
"react": "^17.0.2",
"react-bootstrap": "^1.6.0",
"react-dom": "^17.0.2",
"react-router-dom": "^5.2.0",
"react-scripts": "4.0.3",
Now make a file named with EmployeeDetails.js . In this file we will have Code for DELETE , LIST and ADD .
EmployeeDetails.js
import 'bootstrap/dist/css/bootstrap.min.css';
import React, { useState, useEffect} from "react";
import axios from "axios";
import { Link} from 'react-router-dom';
function EmployeeDetail()
{
const [search,setSearch] =useState('');
const [record,setRecord] = useState([]);
const [user, setUser] = useState({
fname: "",
lname: "",
email: "",
phone: "",
salary: ""
});
// Object Destructuring
const { fname, lname, email,phone,salary} = user;
const onInputChange = e => {
setUser({ ...user, [e.target.name]: e.target.value });
};
// On Page load display all records
const loadEmployeeDetail = async () =>
{
var response = fetch('http://localhost:5000/api/v1/employee')
.then(function(response){
return response.json();
})
.then(function(myJson) {
setRecord(myJson);
});
}
useEffect(() => {
loadEmployeeDetail();
}, []);
// Insert Employee Records
const submitEmployeeRecord = async (e) => {
e.preventDefault();
e.target.reset();
await axios.post("http://localhost:5000/api/v1/employee",user);
alert('Data Inserted');
loadEmployeeDetail();
};
// Search Records here
const searchRecords = () =>
{
alert(search)
axios.get(`http://localhost:5000/api/v1/employee/searchRecord/${search}`)
.then(response => {
setRecord(response.data);
});
}
// Delete Employee Record
const deleteRecord = (productId) =>
{
axios.delete(`http://localhost:5000/api/v1/employee/${productId}`)
.then((result)=>{
loadEmployeeDetail();
})
.catch(()=>{
alert('Error in the Code');
});
};
return(
<section>
<nav class="navbar navbar-expand-lg navbar-light bg-dark">
<div class="collapse navbar-collapse" id="navbarNavDropdown">
<ul class="navbar-nav">
<li class="nav-item active ">
sssssssss
</li>
<li class="nav-item active">
<a class="nav-link text-white" href="#">Home <span class="sr-only">(current)</span></a>
</li>
<li class="nav-item">
<a class="nav-link text-white" href="#">Detail</a>
</li>
<li class="nav-item">
<a class="nav-link text-white" href="#">Address</a>
</li>
<li class="nav-item">
<a class="nav-link text-white" href="#">Contact</a>
</li>
</ul>
</div>
</nav>
<div class="container">
<h4 className="mb-3 text-center mt-4">CRUD Operation in MERN</h4>
<div class="row mt-3">
<div class="col-sm-4">
<div className="box p-3 mb-3 mt-5" style={{border:"1px solid #d0d0d0"}}>
<form onSubmit={submitEmployeeRecord}>
<h5 className="mb-3 ">Insert Employee Records</h5>
<div class="form-group">
<input type="text" class="form-control mb-4" name="fname" value={fname} onChange={e => onInputChange(e)} placeholder="Enter name" required=""/>
</div>
<div class="form-group">
<input type="text" class="form-control mb-4" name="lname" value={lname} onChange={e => onInputChange(e)} placeholder="Enter Sirname" required=""/>
</div>
<div class="form-group">
<input type="text" class="form-control mb-4" name="email" value={email} onChange={e => onInputChange(e)} placeholder="Enter Email" required=""/>
</div>
<div class="form-group">
<input type="text" class="form-control mb-4" name="phone" value={phone} onChange={e => onInputChange(e)} placeholder="Enter Phone" required=""/>
</div>
<div class="form-group">
<input type="text" class="form-control mb-2" name="salary" value={salary} onChange={e => onInputChange(e)} placeholder="Enter Salary" required=""/>
</div>
<button type="submit" class="btn btn-primary btn-block mt-4">Insert Record</button>
</form>
</div>
</div>
<div class="col-sm-8">
<h5 class="text-center ml-4 mt-4 mb-5">View Records</h5>
<div class="input-group mb-4 mt-3">
<div class="form-outline">
<input type="text" id="form1" onChange={(e)=>setSearch(e.target.value)} class="form-control" placeholder="Search Employee Here" style={{backgroundColor:"#ececec"}}/>
</div>
<button type="button" onClick={searchRecords} class="btn btn-success">
<i class="fa fa-search" aria-hidden="true"></i>
</button>
</div>
<table class="table table-hover table-striped table-bordered ml-4 ">
<thead>
<tr>
<th>Name</th>
<th>Surname</th>
<th>Email</th>
<th>Phone</th>
<th>Salary</th>
<th>Action</th>
</tr>
</thead>
<tbody>
{record.map((name)=>
<tr>
<td>{name.first_name}</td>
<td>{name.last_name}</td>
<td>{name.email}</td>
<td>{name.phone}</td>
<td>{name.salary}</td>
<td>
<a className="text-danger mr-2"
onClick={() => {
const confirmBox = window.confirm(
"Do you really want to delete "+ name.first_name
)
if (confirmBox === true) {
deleteRecord(name.id)
}
}}> <i class="far fa-trash-alt" style={{fontSize:"18px",marginRight:"5px"}}></i> </a>
<Link class=" mr-2" to={`/EditEmployee/editID/${name.id}`}>
<i class="fa fa-edit" aria-hidden="true"></i>
</Link>
</td>
</tr>
)}
</tbody>
</table>
</div>
</div>
</div>
</section>
)
}
export default EmployeeDetail;
Now We will make EditEmployee file for EDIT and UPDATE Employee details
EditEmployee.js
import React, { useState, useEffect } from "react";
import axios from "axios";
import { useHistory, useParams } from "react-router-dom";
const EditEmployee = () => {
let history = useHistory(); //The useHistory hook gives you access to the history instance that you may use to navigate.
const { id } = useParams(); //The useParams() hook helps us to access the URL parameters from a current route.
const [user ,setUser] = useState({
fname:"",
lname:"",
email:"",
phone:"",
salary:""
})
const { fname, lname, email, phone, salary } = user;
const onInputChange = e => {
setUser({ ...user,[e.target.name]: e.target.value });
};
useEffect(() => {
loadUser();
}, []);
const updateEmployee = async e => {
e.preventDefault();
await axios.put(`http://localhost:5000/api/v1/employee/${id}`, user);
history.push("/");
};
const loadUser = () => {
fetch(`http://localhost:5000/api/v1/employee/${id}`,{
method: "GET",
})
.then((response) => response.json())
.then((result) => {
console.log(result);
setUser({
id: id,
update: true,
fname: result.response[0].first_name,
lname: result.response[0].last_name,
email: result.response[0].email,
phone: result.response[0].phone,
salary: result.response[0].salary,
});
})
.catch((error) => console.log("error", error));
};
return (
<div className="container">
<div className="row mt-4">
<div className="col-sm-5 col-offset-3 mx-auto shadow p-5">
<h4 className="text-center mb-4">Edit A employee</h4>
<h5 className="text-success">Employee ID : {user.id} </h5>
<div className="form-group mb-3">
<input
type="text"
className="form-control form-control-lg"
placeholder="Enter Product Name"
name="fname"
value={fname}
onChange={e => onInputChange(e)}
/>
</div>
<div className="form-group mb-3">
<input
type="text"
className="form-control form-control-lg"
placeholder="Enter Product Price"
name="lname"
value={lname}
onChange={e => onInputChange(e)}
/>
</div>
<div className="form-group mb-3">
<input
type="text"
className="form-control form-control-lg"
placeholder="Enter Product Description"
name="email"
value={email}
onChange={e => onInputChange(e)}
/>
</div>
<div className="form-group mb-3">
<input
type="text"
className="form-control form-control-lg"
placeholder="Enter Product Description"
name="phone"
value={phone}
onChange={e => onInputChange(e)}
/>
</div>
<div className="form-group mb-3">
<input
type="text"
className="form-control form-control-lg"
placeholder="Enter Product Description"
name="salary"
value={salary}
onChange={e => onInputChange(e)}
/>
</div>
<button onClick={updateEmployee} className="btn btn-secondary btn-block">Update Employee</button>
</div>
</div>
</div>
);
};
export default EditEmployee;
Now Call both files in App.js
App.js
import './App.css';
import EmployeeDetail from './EmployeeDetail';
import EditEmployee from './EditEmployee';
import { BrowserRouter as Router, Route,Switch,withRouter} from "react-router-dom";
function App(props) {
return (
<Router>
<div className="App">
<Switch>
<Route exact path="/" component={EmployeeDetail} />
<Route exact path="/EditEmployee/editID/:id" component={EditEmployee} />
</Switch>
</div>
</Router>
);
}
export default App;
Finally, We have completed our Project Nodejs will run on http://localhost:5000/ and ReactJS will run on http://localhost:3000/ . You can change your PORT Setting.
Subscribe to My Programming YouTube Channel Before Downloading the code :
Download the Source Code : MERN Project
Read Our More Articales on ReactJS and Nodejs
How to Load millions record in React
Building an Image Gallery with Laravel and React
Excelente work!!,i really want to Learn more of this kind of architecture node sql and react ,thanks again you are awezome
Thanks Dear
Sir all projects are very excellent work. one more thing please one project make to react js and firebase
I am preparing for it …I will upload soon
Thank You dear
Ok sir Thank you
Great
Thank You Dear
This is great! wondering if there’s a github repo for this, wann learn it from there, thank you.
Hi dear You can download it from Google Drive
https://drive.google.com/file/d/1fjyIW4PBiDUAWuKquDnOo5xoF9CTV5W5/view?usp=sharing
It asks for permission. Which i seent
Given dear
Hello sir, I need the source code. I have requested for the access. Please give access.
Already given check your mail
Is there a gutlab repo?
No I have uploaded on Google Drive dear
sir please give me access to code.(drive)
already given
Hey there. can you please give me access for source code.. i requested the access
Already given dear
can you please give me access for source code
Given
Please give me access of source code
Already given dear
pls give me access
Already given
Hello, can you please accept my request access?
Done