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.

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

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:

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:

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

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.

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.

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

Build Node.js Rest APIs with Express & MySQL

Table of Contents

Installing MySQL

Setting up the database

Creating tables

Node.js project setup

Troubleshooting

Connecting to the database

Adding an employee

Fetching an individual record

Fetching all employees

Updating the employee address

Deleting an employee record

Source code

Related Articles

Leave a Comment

Tools