Table of Contents
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 CASCADE11);
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:
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 ofmysql
(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.
1const mysql = require("mysql2/promise")2async function getConnection() {3 // create the connection4 const connection = await mysql.createConnection({5 host: "localhost",6 user: "root",7 password: "",8 database: "codingdeft",9 })10 return connection11}1213module.exports = getConnection
Adding an employee
Create a file named queryHelpers.js
inside the utils
directory.
Add a function called addEmployee
and export it:
1const getConnection = require("./db")23const 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}1011module.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()45const genericError = "Sorry, something went wrong!"67app.use(express.json())89app.post("/", async function (request, response) {10 try {11 const { name, department, address } = request.body12 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})2930app.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
:
1const getConnection = require("./db")23const 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}1011const 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}1819module.exports = {20 addEmployee,21 getEmployee,22}
Now add a GET router to fetch the employee details:
1const express = require("express")2const { addEmployee, getEmployee } = require("./utils/queryHelpers")3const app = express()45const genericError = "Sorry, something went wrong!"67app.use(express.json())89app.post("/", async function (request, response) {10 try {11 const { name, department, address } = request.body12 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})2930app.get("/:id", async function (request, response) {31 const { id } = request.params32 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})4950app.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
:
1const getConnection = require("./db")23const 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}1011const 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}1718const 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}2526module.exports = {27 addEmployee,28 getAllEmployees,29 getEmployee,30}
Now add a get handler to use getAllEmployees
function:
1const express = require("express")2const {3 addEmployee,4 getEmployee,5 getAllEmployees,6} = require("./utils/queryHelpers")7const app = express()89const genericError = "Sorry, something went wrong!"1011app.use(express.json())1213app.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})2425app.get("/:id", async function (request, response) {26 const { id } = request.params27 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})4445app.post("/", async function (request, response) {46 try {47 const { name, department, address } = request.body48 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})6566app.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:
1const getConnection = require("./db")23const 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}1011const 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}1718const 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}2526const 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}3334module.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()910const genericError = "Sorry, something went wrong!"1112app.use(express.json())1314app.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})2526app.get("/:id", async function (request, response) {27 const { id } = request.params28 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})4546app.post("/", async function (request, response) {47 try {48 const { name, department, address } = request.body49 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})6667app.put("/:id", async function (request, response) {68 try {69 const { address } = request.body70 const { id } = request.params71 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
:
1const getConnection = require("./db")23const 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}1011const 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}1718const 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}2526const 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}3334const deleteEmployee = async id => {35 const connection = await getConnection()36 return connection.execute("delete from employee where id=?", [id])37}3839module.exports = {40 addEmployee,41 getAllEmployees,42 getEmployee,43 updateAddress,44 deleteEmployee,45}
In index.js
, add a DELETE route:
1const express = require("express")2const {3 addEmployee,4 getAllEmployees,5 getEmployee,6 updateAddress,7 deleteEmployee,8} = require("./utils/queryHelpers")9const app = express()1011const genericError = "Sorry, something went wrong!"1213app.use(express.json())1415app.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})2627app.get("/:id", async function (request, response) {28 const { id } = request.params29 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})4647app.post("/", async function (request, response) {48 try {49 const { name, department, address } = request.body50 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})6768app.put("/:id", async function (request, response) {69 try {70 const { address } = request.body71 const { id } = request.params72 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})9091app.delete("/:id", async function (request, response) {92 try {93 const { id } = request.params94 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})111112app.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