Skip to content
react

Build CRUD app using React + Node.js + Express + MySQL

Apr 2, 2022Abhishek EH11 Min Read
Build CRUD app using React + Node.js + Express + MySQL

In a previous article, I have explained how to build rest APIs using Node.js, Express and MySQL. In this article, we will see how to use those APIs to display the list of employees, edit their addresses, create a new employee record and delete an employee.

Prerequisites

Project setup

First, create a react application using the following command:

1npx create-react-app

We will be using BluePrintJS to style the app and Axios to make API calls. So install them using the following command:

1npm i axios @blueprintjs/core

Update the index.css file with the following contents:

index.css
1@import "~normalize.css";
2@import "[email protected]/core/lib/css/blueprint.css";
3@import "[email protected]/icons/lib/css/blueprint-icons.css";
4
5body {
6 max-width: 900px;
7 margin: 10px auto;
8}

Here we are including the BluePrintJS styles and some custom styling.

Adding CORS

Since we need to access the APIs from the react application, we need to add the front-end URL to the CORS. I have explained in detail how to fix the CORS issue in my previous article.

In the Node.js project, install the cors package using the following command

1npm i cors

Update the index.js with the highlighted code:

index.js
1// ...
2const cors = require("cors")
3
4const genericError = "Sorry, something went wrong!"
5
6app.use(express.json())
7
8const whitelist = ["http://localhost:3001"] //Change to the port in which react app is running
9const corsOptions = {
10 origin: function (origin, callback) {
11 if (!origin || whitelist.indexOf(origin) !== -1) {
12 callback(null, true)
13 } else {
14 callback(new Error("Not allowed by CORS"))
15 }
16 },
17 credentials: true,
18}
19app.use(cors(corsOptions))
20//...

Adding all departments route

While adding a new employee, we need to display the list of departments. So let's add it to the Node.js project:

queryHelpers.js
1// ...
2const getAllDepartments = async () => {
3 const connection = await getConnection()
4 return connection.execute("select id,name from department")
5}
6
7module.exports = {
8 addEmployee,
9 getAllEmployees,
10 getEmployee,
11 updateAddress,
12 deleteEmployee,
13 getAllDepartments,
14}

Update the index.js with the /departments route:

index.js
1const express = require("express")
2const {
3 addEmployee,
4 getAllEmployees,
5 getEmployee,
6 updateAddress,
7 deleteEmployee,
8 getAllDepartments,
9} = require("./utils/queryHelpers")
10const app = express()
11const cors = require("cors")
12
13const genericError = "Sorry, something went wrong!"
14
15app.use(express.json())
16
17const whitelist = ["http://localhost:3001"] //Change to the port in which react app is running
18const corsOptions = {
19 origin: function (origin, callback) {
20 if (!origin || whitelist.indexOf(origin) !== -1) {
21 callback(null, true)
22 } else {
23 callback(new Error("Not allowed by CORS"))
24 }
25 },
26 credentials: true,
27}
28app.use(cors(corsOptions))
29
30app.get("/departments", async function (request, response) {
31 try {
32 const [result] = await getAllDepartments()
33 response.send({ success: true, result })
34 } catch (error) {
35 response.status(500).send({
36 success: false,
37 error: genericError,
38 })
39 }
40})
41//...

Displaying the list of employees

In the react application, update the App.js with the following code:

App.js
1import { Button, EditableText } from "@blueprintjs/core"
2import axios from "axios"
3import { useEffect, useState } from "react"
4
5function App() {
6 const [employees, setEmployees] = useState([])
7
8 useEffect(() => {
9 axios.get("http://localhost:8001/").then(response => {
10 const { data } = response
11 setEmployees(data.result)
12 })
13 }, [])
14
15 return (
16 <div className="App">
17 <table className="bp4-html-table .modifier">
18 <thead>
19 <tr>
20 <th>Employee ID</th>
21 <th>Name</th>
22 <th>Department</th>
23 <th>Address</th>
24 <th>Action</th>
25 </tr>
26 </thead>
27 <tbody>
28 {employees.map(employee => {
29 const { id, name, address, department } = employee
30 return (
31 <tr key={id}>
32 <td>{id}</td>
33 <td>{name}</td>
34 <td>{department}</td>
35 <td>
36 <EditableText value={address} />
37 </td>
38 <td>
39 <Button intent="primary">Update</Button>
40 &nbsp;
41 <Button intent="danger">Delete</Button>
42 </td>
43 </tr>
44 )
45 })}
46 </tbody>
47 </table>
48 </div>
49 )
50}
51
52export default App

In the above code:

  • We have a useEffect hook where we fetch the list of employees.
  • We are displaying the fetched list of employees in a table.
  • We are using EditableText from BluePrintJS to make the address editable when hovered.
  • In the actions, we have 2 buttons, one to delete the employee and another to update the address.

If you run the application now, you will see the list of employees:

display employees

Adding a new employee

To add a new employee, we need to fetch the list of departments. We can do so in the useEffect:

App.js
1// ...
2import { Button, EditableText } from "@blueprintjs/core"
3import axios from "axios"
4import { useEffect, useState } from "react"
5
6function App() {
7 const [employees, setEmployees] = useState([])
8 const [departments, setDepartments] = useState([])
9
10 useEffect(() => {
11 axios.get("http://localhost:8001/").then(response => {
12 const { data } = response
13 setEmployees(data.result)
14 })
15
16 axios.get("http://localhost:8001/departments").then(response => {
17 const { data } = response
18 setDepartments(data.result)
19 })
20 }, [])
21
22 //...
23}
24
25export default App
26
27// ...

Now let's display fields to input the name, department, and address.

App.js
1import { Button, EditableText, InputGroup } from "@blueprintjs/core"
2import axios from "axios"
3import { useEffect, useState } from "react"
4
5function App() {
6 const [employees, setEmployees] = useState([])
7 const [departments, setDepartments] = useState([])
8 const [newName, setNewName] = useState("")
9 const [newDepartment, setNewDepartment] = useState("")
10 const [newAddress, setNewAddress] = useState("")
11
12 useEffect(() => {
13 axios.get("http://localhost:8001/").then(response => {
14 const { data } = response
15 setEmployees(data.result)
16 })
17
18 axios.get("http://localhost:8001/departments").then(response => {
19 const { data } = response
20 setDepartments(data.result)
21 })
22 }, [])
23
24 return (
25 <div className="App">
26 <table className="bp4-html-table .modifier">
27 <thead>
28 <tr>
29 <th>Employee ID</th>
30 <th>Name</th>
31 <th>Department</th>
32 <th>Address</th>
33 <th>Action</th>
34 </tr>
35 </thead>
36 <tbody>
37 {employees.map(employee => {
38 const { id, name, address, department } = employee
39 return (
40 <tr key={id}>
41 <td>{id}</td>
42 <td>{name}</td>
43 <td>{department}</td>
44 <td>
45 <EditableText value={address} />
46 </td>
47 <td>
48 <Button intent="primary">Update</Button>
49 &nbsp;
50 <Button intent="danger">Delete</Button>
51 </td>
52 </tr>
53 )
54 })}
55 </tbody>
56 <tfoot>
57 <tr>
58 <td></td>
59 <td>
60 <InputGroup
61 placeholder="Add name here..."
62 value={newName}
63 onChange={e => setNewName(e.target.value)}
64 />
65 </td>
66 <td>
67 <div class="bp4-html-select .modifier">
68 <select
69 onChange={e => setNewDepartment(e.target.value)}
70 value={newDepartment}
71 >
72 <option selected value="">
73 Select department
74 </option>
75 {departments.map(department => {
76 const { id, name } = department
77 return (
78 <option key={id} value={id}>
79 {name}
80 </option>
81 )
82 })}
83 </select>
84 <span class="bp4-icon bp4-icon-double-caret-vertical"></span>
85 </div>
86 </td>
87 <td>
88 <InputGroup
89 placeholder="Add address here..."
90 value={newAddress}
91 onChange={e => setNewAddress(e.target.value)}
92 />
93 </td>
94 <td>
95 <Button intent="success">Add Employee</Button>
96 </td>
97 </tr>
98 </tfoot>
99 </table>
100 </div>
101 )
102}
103
104export default App

Finally, let's write a function called addEmployee, which calls the API to create a new employee record:

App.js
1import { Button, EditableText, InputGroup } from "@blueprintjs/core"
2import axios from "axios"
3import { useEffect, useState } from "react"
4
5function App() {
6 const [employees, setEmployees] = useState([])
7 const [departments, setDepartments] = useState([])
8 const [newName, setNewName] = useState("")
9 const [newDepartment, setNewDepartment] = useState("")
10 const [newAddress, setNewAddress] = useState("")
11
12 useEffect(() => {
13 axios.get("http://localhost:8001/").then(response => {
14 const { data } = response
15 setEmployees(data.result)
16 })
17
18 axios.get("http://localhost:8001/departments").then(response => {
19 const { data } = response
20 setDepartments(data.result)
21 })
22 }, [])
23
24 const addEmployee = () => {
25 const name = newName.trim()
26 const department = newDepartment
27 const address = newAddress.trim()
28 if (name && department && address) {
29 axios
30 .post("http://localhost:8001/", {
31 name,
32 department,
33 address,
34 })
35 .then(response => {
36 const { data } = response
37 setEmployees([...employees, data.result])
38 setNewName("")
39 setNewAddress("")
40 setNewDepartment("")
41 })
42 }
43 }
44
45 return (
46 <div className="App">
47 <table className="bp4-html-table .modifier">
48 <thead>
49 <tr>
50 <th>Employee ID</th>
51 <th>Name</th>
52 <th>Department</th>
53 <th>Address</th>
54 <th>Action</th>
55 </tr>
56 </thead>
57 <tbody>
58 {employees.map(employee => {
59 const { id, name, address, department } = employee
60 return (
61 <tr key={id}>
62 <td>{id}</td>
63 <td>{name}</td>
64 <td>{department}</td>
65 <td>
66 <EditableText value={address} />
67 </td>
68 <td>
69 <Button intent="primary">Update</Button>
70 &nbsp;
71 <Button intent="danger">Delete</Button>
72 </td>
73 </tr>
74 )
75 })}
76 </tbody>
77 <tfoot>
78 <tr>
79 <td></td>
80 <td>
81 <InputGroup
82 placeholder="Add name here..."
83 value={newName}
84 onChange={e => setNewName(e.target.value)}
85 />
86 </td>
87 <td>
88 <div class="bp4-html-select .modifier">
89 <select
90 onChange={e => setNewDepartment(e.target.value)}
91 value={newDepartment}
92 >
93 <option selected value="">
94 Select department
95 </option>
96 {departments.map(department => {
97 const { id, name } = department
98 return (
99 <option key={id} value={id}>
100 {name}
101 </option>
102 )
103 })}
104 </select>
105 <span class="bp4-icon bp4-icon-double-caret-vertical"></span>
106 </div>
107 </td>
108 <td>
109 <InputGroup
110 placeholder="Add address here..."
111 value={newAddress}
112 onChange={e => setNewAddress(e.target.value)}
113 />
114 </td>
115 <td>
116 <Button intent="success" onClick={addEmployee}>
117 Add Employee
118 </Button>
119 </td>
120 </tr>
121 </tfoot>
122 </table>
123 </div>
124 )
125}
126
127export default App

Update address

Update the App.js with the following code:

App.js
1import {
2 Button,
3 EditableText,
4 InputGroup,
5 Toaster,
6 Position,
7} from "@blueprintjs/core"
8import axios from "axios"
9import { useEffect, useState } from "react"
10
11const AppToaster = Toaster.create({
12 position: Position.TOP,
13})
14
15function App() {
16 const [employees, setEmployees] = useState([])
17 const [departments, setDepartments] = useState([])
18 const [newName, setNewName] = useState("")
19 const [newDepartment, setNewDepartment] = useState("")
20 const [newAddress, setNewAddress] = useState("")
21
22 useEffect(() => {
23 axios.get("http://localhost:8001/").then(response => {
24 const { data } = response
25 setEmployees(data.result)
26 })
27
28 axios.get("http://localhost:8001/departments").then(response => {
29 const { data } = response
30 setDepartments(data.result)
31 })
32 }, [])
33
34 const addEmployee = () => {
35 const name = newName.trim()
36 const department = newDepartment
37 const address = newAddress.trim()
38 if (name && department && address) {
39 axios
40 .post("http://localhost:8001/", {
41 name,
42 department,
43 address,
44 })
45 .then(response => {
46 const { data } = response
47 setEmployees([...employees, data.result])
48 setNewName("")
49 setNewAddress("")
50 setNewDepartment("")
51 })
52 }
53 }
54
55 const onChangeHandler = (id, key, value) => {
56 console.log({ id, key, value })
57 setEmployees(values => {
58 return values.map(item =>
59 item.id === id ? { ...item, [key]: value } : item
60 )
61 })
62 }
63
64 const updateAddress = id => {
65 const data = employees.find(item => item.id === id)
66 axios.put(`http://localhost:8001/${id}`, data).then(response => {
67 AppToaster.show({
68 message: "Data updated successfully",
69 intent: "success",
70 timeout: 3000,
71 })
72 })
73 }
74
75 return (
76 <div className="App">
77 <table className="bp4-html-table .modifier">
78 <thead>
79 <tr>
80 <th>Employee ID</th>
81 <th>Name</th>
82 <th>Department</th>
83 <th>Address</th>
84 <th>Action</th>
85 </tr>
86 </thead>
87 <tbody>
88 {employees.map(employee => {
89 const { id, name, address, department } = employee
90 return (
91 <tr key={id}>
92 <td>{id}</td>
93 <td>{name}</td>
94 <td>{department}</td>
95 <td>
96 <EditableText
97 value={address}
98 onChange={value => onChangeHandler(id, "address", value)}
99 />
100 </td>
101 <td>
102 <Button intent="primary" onClick={() => updateAddress(id)}>
103 Update
104 </Button>
105 &nbsp;
106 <Button intent="danger">Delete</Button>
107 </td>
108 </tr>
109 )
110 })}
111 </tbody>
112 <tfoot>
113 <tr>
114 <td></td>
115 <td>
116 <InputGroup
117 placeholder="Add name here..."
118 value={newName}
119 onChange={e => setNewName(e.target.value)}
120 />
121 </td>
122 <td>
123 <div class="bp4-html-select .modifier">
124 <select
125 onChange={e => setNewDepartment(e.target.value)}
126 value={newDepartment}
127 >
128 <option selected value="">
129 Select department
130 </option>
131 {departments.map(department => {
132 const { id, name } = department
133 return (
134 <option key={id} value={id}>
135 {name}
136 </option>
137 )
138 })}
139 </select>
140 <span class="bp4-icon bp4-icon-double-caret-vertical"></span>
141 </div>
142 </td>
143 <td>
144 <InputGroup
145 placeholder="Add address here..."
146 value={newAddress}
147 onChange={e => setNewAddress(e.target.value)}
148 />
149 </td>
150 <td>
151 <Button intent="success" onClick={addEmployee}>
152 Add Employee
153 </Button>
154 </td>
155 </tr>
156 </tfoot>
157 </table>
158 </div>
159 )
160}
161
162export default App

Here we have added 2 functions:

  • onChangeHandler - to update the local state with the new address when user updates it.
  • updateAddress - to call the API to update the address in the DB.

Also, we are displaying a toast message when the address is updated.

Deleting an employee record

We can delete the employee record by calling the DELETE method on the /<employee_id> route:

App.js
1import {
2 Button,
3 EditableText,
4 InputGroup,
5 Toaster,
6 Position,
7} from "@blueprintjs/core"
8import axios from "axios"
9import { useEffect, useState } from "react"
10
11const AppToaster = Toaster.create({
12 position: Position.TOP,
13})
14
15function App() {
16 const [employees, setEmployees] = useState([])
17 const [departments, setDepartments] = useState([])
18 const [newName, setNewName] = useState("")
19 const [newDepartment, setNewDepartment] = useState("")
20 const [newAddress, setNewAddress] = useState("")
21
22 useEffect(() => {
23 axios.get("http://localhost:8001/").then(response => {
24 const { data } = response
25 setEmployees(data.result)
26 })
27
28 axios.get("http://localhost:8001/departments").then(response => {
29 const { data } = response
30 setDepartments(data.result)
31 })
32 }, [])
33
34 const addEmployee = () => {
35 const name = newName.trim()
36 const department = newDepartment
37 const address = newAddress.trim()
38 if (name && department && address) {
39 axios
40 .post("http://localhost:8001/", {
41 name,
42 department,
43 address,
44 })
45 .then(response => {
46 const { data } = response
47 setEmployees([...employees, data.result])
48 setNewName("")
49 setNewAddress("")
50 setNewDepartment("")
51 })
52 }
53 }
54
55 const onChangeHandler = (id, key, value) => {
56 console.log({ id, key, value })
57 setEmployees(values => {
58 return values.map(item =>
59 item.id === id ? { ...item, [key]: value } : item
60 )
61 })
62 }
63
64 const updateAddress = id => {
65 const data = employees.find(item => item.id === id)
66 axios.put(`http://localhost:8001/${id}`, data).then(response => {
67 AppToaster.show({
68 message: "Data updated successfully",
69 intent: "success",
70 timeout: 3000,
71 })
72 })
73 }
74
75 const deleteEmployee = id => {
76 axios.delete(`http://localhost:8001/${id}`).then(response => {
77 setEmployees(values => {
78 return values.filter(item => item.id !== id)
79 })
80
81 AppToaster.show({
82 message: "Employee deleted successfully",
83 intent: "success",
84 timeout: 3000,
85 })
86 })
87 }
88
89 return (
90 <div className="App">
91 <table className="bp4-html-table .modifier">
92 <thead>
93 <tr>
94 <th>Employee ID</th>
95 <th>Name</th>
96 <th>Department</th>
97 <th>Address</th>
98 <th>Action</th>
99 </tr>
100 </thead>
101 <tbody>
102 {employees.map(employee => {
103 const { id, name, address, department } = employee
104 return (
105 <tr key={id}>
106 <td>{id}</td>
107 <td>{name}</td>
108 <td>{department}</td>
109 <td>
110 <EditableText
111 value={address}
112 onChange={value => onChangeHandler(id, "address", value)}
113 />
114 </td>
115 <td>
116 <Button intent="primary" onClick={() => updateAddress(id)}>
117 Update
118 </Button>
119 &nbsp;
120 <Button intent="danger" onClick={() => deleteEmployee(id)}>
121 Delete
122 </Button>
123 </td>
124 </tr>
125 )
126 })}
127 </tbody>
128 <tfoot>
129 <tr>
130 <td></td>
131 <td>
132 <InputGroup
133 placeholder="Add name here..."
134 value={newName}
135 onChange={e => setNewName(e.target.value)}
136 />
137 </td>
138 <td>
139 <div class="bp4-html-select .modifier">
140 <select
141 onChange={e => setNewDepartment(e.target.value)}
142 value={newDepartment}
143 >
144 <option selected value="">
145 Select department
146 </option>
147 {departments.map(department => {
148 const { id, name } = department
149 return (
150 <option key={id} value={id}>
151 {name}
152 </option>
153 )
154 })}
155 </select>
156 <span class="bp4-icon bp4-icon-double-caret-vertical"></span>
157 </div>
158 </td>
159 <td>
160 <InputGroup
161 placeholder="Add address here..."
162 value={newAddress}
163 onChange={e => setNewAddress(e.target.value)}
164 />
165 </td>
166 <td>
167 <Button intent="success" onClick={addEmployee}>
168 Add Employee
169 </Button>
170 </td>
171 </tr>
172 </tfoot>
173 </table>
174 </div>
175 )
176}
177
178export default App

Source code

You can view the complete source code here.

If you have liked article, stay in touch with me by following me on twitter.

Leave a Comment

© 2022 CodingDeft.Com