Skip to content
react

Build Node.js Rest APIs with Express & MySQL

Mar 26, 2022Abhishek EH12 Min Read
Build Node.js Rest APIs with Express & MySQL

In this article, we will see how to set up a MySQL database in your local machine, connect to it from Node.js, and build REST APIs using express.

We will be creating 2 tables, one to store the employee details and another to store department names. EMPLOYEE table will have a foreign key called department, which will refer to the DEPARTMENT table.

Installing MySQL

First, download and install MySQL from the below link:

https://dev.mysql.com/downloads/installer/

You will have to create an account with Oracle since MySQL is now an Oracle product.

Setting up the database

Once MySQL is installed, open the command prompt and run the command mysql. If you have set up a password, enter it when prompted.

Now create a database by running the following command:

1CREATE DATABASE codingdeft;

Once the database is created, you should tell MySQL that you want to perform operations on that database. To do so, run the below command:

1use codingdeft;

Creating tables

Now inside the created database, create a table named DEPARTMENT:

1CREATE TABLE `department` (
2 `id` INT NOT NULL AUTO_INCREMENT,
3 `name` VARCHAR(45) NULL,
4 PRIMARY KEY (`id`));

Now create the EMPLOYEE table:

1CREATE TABLE `employee` (
2 `id` int NOT NULL AUTO_INCREMENT,
3 `name` varchar(45) NOT NULL,
4 `department` int DEFAULT NULL,
5 `address` varchar(256) NOT NULL,
6 PRIMARY KEY (`id`),
7 KEY `department_idx` (`department`),
8 CONSTRAINT `department` FOREIGN KEY (`department`)
9 REFERENCES `department` (`id`)
10 ON DELETE CASCADE ON UPDATE CASCADE
11);

Now let's add few departments:

1INSERT INTO `department` (`id`,`name`) VALUES (1,'Finance');
2INSERT INTO `department` (`id`,`name`) VALUES (2,'IT');
3INSERT INTO `department` (`id`,`name`) VALUES (3,'Human Resources');
4INSERT INTO `department` (`id`,`name`) VALUES (4,'Sales');

We are done with the database setup. You can run quit and exit the mysql prompt.

Node.js project setup

Create a directory named nodejs-mysql-rest, navigate to that directory in command prompt and initialize a node project by running:

1npm init -y

Open package.json and update it to the following:

package.json
1{
2 "name": "nodejs-mysql-rest",
3 "version": "1.0.0",
4 "description": "",
5 "main": "index.js",
6 "scripts": {
7 "test": "echo \"Error: no test specified\" && exit 1",
8 "start": "node index.js"
9 },
10 "keywords": [],
11 "author": "",
12 "license": "ISC",
13 "dependencies": {
14 "express": "^4.17.3",
15 "mysql2": "^2.3.3"
16 }
17}

Here we have added a start script and added express and mysql dependencies.

Troubleshooting

If you are using mysql package instead of mysql2, you may receive the following error while connecting to the database:

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

There are 2 ways to fix it.

  1. Running the following command in the mysql prompt (not recommended since it will reduce security):
1ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
2FLUSH PRIVILEGES;
3quit
  1. Using mysql2 instead of mysql (recommended) by running the following command and updating the imports in your project.
1npm un mysql && npm i mysql2

Connecting to the database

Create a folder named utils and a file called db.js inside it with the following content.

db.js
1const mysql = require("mysql2/promise")
2async function getConnection() {
3 // create the connection
4 const connection = await mysql.createConnection({
5 host: "localhost",
6 user: "root",
7 password: "",
8 database: "codingdeft",
9 })
10 return connection
11}
12
13module.exports = getConnection

Adding an employee

Create a file named queryHelpers.js inside the utils directory. Add a function called addEmployee and export it:

queryHelpers.js
1const getConnection = require("./db")
2
3const addEmployee = async (name, department, address) => {
4 const connection = await getConnection()
5 return connection.execute(
6 "INSERT INTO EMPLOYEE (name,department,address) values (?,?,?)",
7 [name, department, address]
8 )
9}
10
11module.exports = {
12 addEmployee,
13}

In the root directory, create a file called index.js with the following code:

1const express = require("express")
2const { addEmployee, getEmployee } = require("./utils/queryHelpers")
3const app = express()
4
5const genericError = "Sorry, something went wrong!"
6
7app.use(express.json())
8
9app.post("/", async function (request, response) {
10 try {
11 const { name, department, address } = request.body
12 const [result] = await addEmployee(name, department, address)
13 if (result.insertId) {
14 const [data] = await getEmployee(result.insertId)
15 response.send({ success: true, result: data[0] })
16 } else {
17 response.status(500).send({
18 success: false,
19 error: genericError,
20 })
21 }
22 } catch (error) {
23 response.status(500).send({
24 success: false,
25 error: genericError,
26 })
27 }
28})
29
30app.listen(8001)

Now start the application and send a post request to http://localhost:8001 from postman:

postman add employee

If the record is created successfully, you will get the employee record in the response.

Fetching an individual record

Now let's fetch the created employee record.

In the queryHelpers.js, add a function called getEmployee:

queryHelpers.js
1const getConnection = require("./db")
2
3const addEmployee = async (name, department, address) => {
4 const connection = await getConnection()
5 return connection.execute(
6 "INSERT INTO EMPLOYEE (name,department,address) values (?,?,?)",
7 [name, department, address]
8 )
9}
10
11const getEmployee = async id => {
12 const connection = await getConnection()
13 return connection.execute(
14 "select e.id,e.name,d.name department,e.address from employee e,department d where e.department=d.id and e.id=?",
15 [id]
16 )
17}
18
19module.exports = {
20 addEmployee,
21 getEmployee,
22}

Now add a GET router to fetch the employee details:

index.js
1const express = require("express")
2const { addEmployee, getEmployee } = require("./utils/queryHelpers")
3const app = express()
4
5const genericError = "Sorry, something went wrong!"
6
7app.use(express.json())
8
9app.post("/", async function (request, response) {
10 try {
11 const { name, department, address } = request.body
12 const [result] = await addEmployee(name, department, address)
13 if (result.insertId) {
14 const [data] = await getEmployee(result.insertId)
15 response.send({ success: true, result: data[0] })
16 } else {
17 response.status(500).send({
18 success: false,
19 error: genericError,
20 })
21 }
22 } catch (error) {
23 response.status(500).send({
24 success: false,
25 error: genericError,
26 })
27 }
28})
29
30app.get("/:id", async function (request, response) {
31 const { id } = request.params
32 try {
33 const [result] = await getEmployee(id)
34 if (result.length > 0) {
35 response.send({ success: true, result: result[0] })
36 } else {
37 response.status(404).send({
38 success: false,
39 error: `No employee found with id ${id}`,
40 })
41 }
42 } catch (error) {
43 response.status(500).send({
44 success: false,
45 error: genericError,
46 })
47 }
48})
49
50app.listen(8001)

Now if you make a GET request to http://localhost:8001/<employee_id>, you will get the employee details in the response:

postman get employee

If you pass an employee id that is not present, you will get an error message accordingly:

postman 404 employee not found

Fetching all employees

You can add a few employees and fetch them by adding getAllEmployees function to queryHelpers.js:

queryHelpers.js
1const getConnection = require("./db")
2
3const addEmployee = async (name, department, address) => {
4 const connection = await getConnection()
5 return connection.execute(
6 "INSERT INTO EMPLOYEE (name,department,address) values (?,?,?)",
7 [name, department, address]
8 )
9}
10
11const getAllEmployees = async () => {
12 const connection = await getConnection()
13 return connection.execute(
14 "select e.id,e.name,d.name department,e.address from employee e,department d where e.department=d.id order by e.id"
15 )
16}
17
18const getEmployee = async id => {
19 const connection = await getConnection()
20 return connection.execute(
21 "select e.id,e.name,d.name department,e.address from employee e,department d where e.department=d.id and e.id=?",
22 [id]
23 )
24}
25
26module.exports = {
27 addEmployee,
28 getAllEmployees,
29 getEmployee,
30}

Now add a get handler to use getAllEmployees function:

index.js
1const express = require("express")
2const {
3 addEmployee,
4 getEmployee,
5 getAllEmployees,
6} = require("./utils/queryHelpers")
7const app = express()
8
9const genericError = "Sorry, something went wrong!"
10
11app.use(express.json())
12
13app.get("/", async function (request, response) {
14 try {
15 const [result] = await getAllEmployees()
16 response.send({ success: true, result })
17 } catch (error) {
18 response.status(500).send({
19 success: false,
20 error: genericError,
21 })
22 }
23})
24
25app.get("/:id", async function (request, response) {
26 const { id } = request.params
27 try {
28 const [result] = await getEmployee(id)
29 if (result.length > 0) {
30 response.send({ success: true, result: result[0] })
31 } else {
32 response.status(404).send({
33 success: false,
34 error: `No employee found with id ${id}`,
35 })
36 }
37 } catch (error) {
38 response.status(500).send({
39 success: false,
40 error: genericError,
41 })
42 }
43})
44
45app.post("/", async function (request, response) {
46 try {
47 const { name, department, address } = request.body
48 const [result] = await addEmployee(name, department, address)
49 if (result.insertId) {
50 const [data] = await getEmployee(result.insertId)
51 response.send({ success: true, result: data[0] })
52 } else {
53 response.status(500).send({
54 success: false,
55 error: genericError,
56 })
57 }
58 } catch (error) {
59 response.status(500).send({
60 success: false,
61 error: genericError,
62 })
63 }
64})
65
66app.listen(8001)

By hitting http://localhost:8001/, you should be able to retrieve all employees.

Updating the employee address

As part of updating the database, let's update employee address:

queryHelpers.js
1const getConnection = require("./db")
2
3const addEmployee = async (name, department, address) => {
4 const connection = await getConnection()
5 return connection.execute(
6 "INSERT INTO EMPLOYEE (name,department,address) values (?,?,?)",
7 [name, department, address]
8 )
9}
10
11const getAllEmployees = async () => {
12 const connection = await getConnection()
13 return connection.execute(
14 "select e.id,e.name,d.name department,e.address from employee e,department d where e.department=d.id order by e.id"
15 )
16}
17
18const getEmployee = async id => {
19 const connection = await getConnection()
20 return connection.execute(
21 "select e.id,e.name,d.name department,e.address from employee e,department d where e.department=d.id and e.id=?",
22 [id]
23 )
24}
25
26const updateAddress = async (id, address) => {
27 const connection = await getConnection()
28 return connection.execute("update employee set address=? where id=?", [
29 address,
30 id,
31 ])
32}
33
34module.exports = {
35 addEmployee,
36 getAllEmployees,
37 getEmployee,
38 updateAddress,
39}

In the index.js, add a PUT handler:

1const express = require("express")
2const {
3 addEmployee,
4 getEmployee,
5 getAllEmployees,
6 updateAddress,
7} = require("./utils/queryHelpers")
8const app = express()
9
10const genericError = "Sorry, something went wrong!"
11
12app.use(express.json())
13
14app.get("/", async function (request, response) {
15 try {
16 const [result] = await getAllEmployees()
17 response.send({ success: true, result })
18 } catch (error) {
19 response.status(500).send({
20 success: false,
21 error: genericError,
22 })
23 }
24})
25
26app.get("/:id", async function (request, response) {
27 const { id } = request.params
28 try {
29 const [result] = await getEmployee(id)
30 if (result.length > 0) {
31 response.send({ success: true, result: result[0] })
32 } else {
33 response.status(404).send({
34 success: false,
35 error: `No employee found with id ${id}`,
36 })
37 }
38 } catch (error) {
39 response.status(500).send({
40 success: false,
41 error: genericError,
42 })
43 }
44})
45
46app.post("/", async function (request, response) {
47 try {
48 const { name, department, address } = request.body
49 const [result] = await addEmployee(name, department, address)
50 if (result.insertId) {
51 const [data] = await getEmployee(result.insertId)
52 response.send({ success: true, result: data[0] })
53 } else {
54 response.status(500).send({
55 success: false,
56 error: genericError,
57 })
58 }
59 } catch (error) {
60 response.status(500).send({
61 success: false,
62 error: genericError,
63 })
64 }
65})
66
67app.put("/:id", async function (request, response) {
68 try {
69 const { address } = request.body
70 const { id } = request.params
71 const [result] = await updateAddress(id, address)
72 if (result.affectedRows > 0) {
73 const [data] = await getEmployee(id)
74 response.send({ success: true, result: data[0] })
75 } else {
76 response.status(400).send({
77 success: false,
78 error: genericError,
79 })
80 }
81 } catch (error) {
82 console.log(error)
83 response.status(500).send({
84 success: false,
85 error: genericError,
86 })
87 }
88})
89app.listen(8001)

Now if you call the PUT API on http://localhost:8001/<employee_id>, with the address field in the request body, it will return the employee record with the updated details.

postman update employee

Deleting an employee record

Finally, you can delete an employee record by adding deleteEmployee function to queryHelpers.js:

queryHelpers.js
1const getConnection = require("./db")
2
3const addEmployee = async (name, department, address) => {
4 const connection = await getConnection()
5 return connection.execute(
6 "INSERT INTO EMPLOYEE (name,department,address) values (?,?,?)",
7 [name, department, address]
8 )
9}
10
11const getAllEmployees = async () => {
12 const connection = await getConnection()
13 return connection.execute(
14 "select e.id,e.name,d.name department,e.address from employee e,department d where e.department=d.id order by e.id"
15 )
16}
17
18const getEmployee = async id => {
19 const connection = await getConnection()
20 return connection.execute(
21 "select e.id,e.name,d.name department,e.address from employee e,department d where e.department=d.id and e.id=?",
22 [id]
23 )
24}
25
26const updateAddress = async (id, address) => {
27 const connection = await getConnection()
28 return connection.execute("update employee set address=? where id=?", [
29 address,
30 id,
31 ])
32}
33
34const deleteEmployee = async id => {
35 const connection = await getConnection()
36 return connection.execute("delete from employee where id=?", [id])
37}
38
39module.exports = {
40 addEmployee,
41 getAllEmployees,
42 getEmployee,
43 updateAddress,
44 deleteEmployee,
45}

In index.js, add a DELETE route:

index.js
1const express = require("express")
2const {
3 addEmployee,
4 getAllEmployees,
5 getEmployee,
6 updateAddress,
7 deleteEmployee,
8} = require("./utils/queryHelpers")
9const app = express()
10
11const genericError = "Sorry, something went wrong!"
12
13app.use(express.json())
14
15app.get("/", async function (request, response) {
16 try {
17 const [result] = await getAllEmployees()
18 response.send({ success: true, result })
19 } catch (error) {
20 response.status(500).send({
21 success: false,
22 error: genericError,
23 })
24 }
25})
26
27app.get("/:id", async function (request, response) {
28 const { id } = request.params
29 try {
30 const [result] = await getEmployee(id)
31 if (result.length > 0) {
32 response.send({ success: true, result: result[0] })
33 } else {
34 response.status(404).send({
35 success: false,
36 error: `No employee found with id ${id}`,
37 })
38 }
39 } catch (error) {
40 response.status(500).send({
41 success: false,
42 error: genericError,
43 })
44 }
45})
46
47app.post("/", async function (request, response) {
48 try {
49 const { name, department, address } = request.body
50 const [result] = await addEmployee(name, department, address)
51 if (result.insertId) {
52 const [data] = await getEmployee(result.insertId)
53 response.send({ success: true, result: data[0] })
54 } else {
55 response.status(500).send({
56 success: false,
57 error: genericError,
58 })
59 }
60 } catch (error) {
61 response.status(500).send({
62 success: false,
63 error: genericError,
64 })
65 }
66})
67
68app.put("/:id", async function (request, response) {
69 try {
70 const { address } = request.body
71 const { id } = request.params
72 const [result] = await updateAddress(id, address)
73 if (result.affectedRows > 0) {
74 const [data] = await getEmployee(id)
75 response.send({ success: true, result: data[0] })
76 } else {
77 response.status(400).send({
78 success: false,
79 error: genericError,
80 })
81 }
82 } catch (error) {
83 console.log(error)
84 response.status(500).send({
85 success: false,
86 error: genericError,
87 })
88 }
89})
90
91app.delete("/:id", async function (request, response) {
92 try {
93 const { id } = request.params
94 const [result] = await deleteEmployee(id)
95 if (result.affectedRows > 0) {
96 response.send({ success: true })
97 } else {
98 response.status(400).send({
99 success: false,
100 error: genericError,
101 })
102 }
103 } catch (error) {
104 console.log(error)
105 response.status(500).send({
106 success: false,
107 error: genericError,
108 })
109 }
110})
111
112app.listen(8001)

Now if you make a DELETE API request on http://localhost:8001/<employee_id> that particular record will be deleted.

You can read how to use these APIs in a react application in this article.

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

© 2024 CodingDeft.Com