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

Make sure that you go through the previous article and have the Node.js API server running.
You may go through another article on how to make API calls using Axios, if you are new to React.

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 "~@blueprintjs/core/lib/css/blueprint.css";
3@import "~@blueprintjs/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:

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.

Do follow me on twitter where I post developer insights more often!

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

Table of Contents

Prerequisites

Project setup

Adding CORS

Adding all departments route

Displaying the list of employees

Adding a new employee

Update address

Deleting an employee record

Source code

Related Articles

Leave a Comment