In this article, I will do a simple CRUD APIs in NodeJS, Express and MySQL. We learn how to create restful (create, read, update, delete) api in NodeJS.
API End Points, test on Postman:
- GET /learns: Give all learns stored in database
- GET /learns/{id}: Give a specific user with id.
- POST /learns : Create a record
- PUT /learns/{id}: Update a record
- DELETE /learns/{id}: Delete a record
Modules to install and use in this example:
- express.js: Web framework express.
- mysql: Node.js driver for MySQL.
- body-parser: Converting the POST data into the request body.
- nodemon: Automatically restart the server whenever the code changes.
1. Install the modules to use in NodeJS
Run [npm init] to initialize the source file, package.json. Select the js code file is index.js
npm init
Install these packages, type in the following command on Git Bash or your favorite cmd:
npm i –s express express-handlebars mysql body-parser
Installing nodemon with the global command:
npm i -g nodemon
We will have the following soure:
Source folder
Package.json file content:
{
“name”: “nodemysql”,
“version”: “1.0.0”,
“description”: “”,
“main”: “index.js”,
“scripts”: {
“test”: “echo \”Error: no test specified\” && exit 1″
},
“author”: “”,
“license”: “ISC”,
“dependencies”: {
“body-parser”: “^1.19.0”,
“express”: “^4.17.1”,
“express-handlebars”: “^5.3.0”,
“mysql”: “^2.18.1”
}
}
2. MySQL Database
Create Database learns, with node_mysql_simple table as simple as follows:
// Schema: learns, DB: node_mysql_simple
CREATE DATABASE `learns` /*DEFAULT CHARACTER SET utf8 */;
CREATE TABLE `node_mysql_simple` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MySQL Database
3. Index.js file server code
Use the required libraries that have been installed in NodeJS and establish a connection with MySQL.
const mysql = require(‘mysql’);
const express = require(‘express’);
const bodyparser = require(‘body-parser’);
var app = express();// Express server
app.use(bodyparser.json());// MySQL connection details
var mysqlConnection = mysql.createConnection({
host: ‘localhost’,
user: ‘root’,
password: ”,
database: ‘learns’,
multipleStatements: true
});mysqlConnection.connect((err) => {
if (!err) {
console.log(‘Connect success.’);
} else {
console.log(‘Connect faile: ‘ + JSON.stringify(err, undefined, 2));
}
});const port = process.env.PORT || 8080;
app.listen(port, () => console.log(`Listen on port ${port}…`));
Now you run the web server using command: node index.js
node index.js
And your server is running at port 8080.
Command run the web server
3.1. Insert data to Database, method POST route
var urlencodedParser = bodyparser.urlencoded({ extended: false });
// Post route value get from params
app.post(‘/learns’, urlencodedParser, (req, res) => {
let name = req.body.name;
let email = req.body.email;
let course_id = req.body.course_id;let sql = “INSERT INTO node_mysql_simple (name, email, course_id) VALUES (?, ?, ?)”;
mysqlConnection.query(sql, [name, email, course_id], (err, rows, fields) => {
if (!err) {
res.send(‘Inserted ID : ‘ + rows.insertId);
} else {
console.log(err);
}
})
});
– Test on Postman: Input simple data with name, email. course_id.
Method Post
Or you can use MySQL insert multiple rows from array.
// Post route direct value
app.post(‘/learnDirect’, (req, res) => {
// Multi values
let values = [
[‘Amit’, ‘amit@gmail’, 1],
[‘Rishi’, ‘rishi@gmail’, 1],
[‘Akash’, ‘akash@gmail’, 1],
];let sql = ‘INSERT INTO node_mysql_simple (name, email, course_id) VALUES ?’;
mysqlConnection.query(sql, [values], (err, rows, fields) => {
if (!err) {
res.send(‘Inserted ID ‘ + rows.insertId);
} else {
console.log(‘Error: ‘ + err);
}
});
});
3.2. Show data list and detail data with method GET route
– Get route list:
// Get route list
app.get(‘/learns’, (req, res) => {
mysqlConnection.query(‘SELECT * FROM node_mysql_simple’, (err, rows, field) => {
if (!err) {
res.send(rows);
} else {
console.log(‘Query error: ‘ + err);
}
})
});
After inserting the second record, we have the results, test on Postman:
Method Get, get data list
– Get route detail:
// Get route detail
app.get(‘/learns/:id’, (req, res) => {
mysqlConnection.query(‘SELECT * FROM node_mysql_simple WHERE id = ?’, [req.params.id], (err, row, field) => {
if (!err) {
res.send(row);
} else {
console.log(‘Query error: ‘ + err);
}
})
});
Method Get, get detail on Postman
3.3. Update data with method PUT route
// Put route value get from params
app.put(‘/learns/:id’, urlencodedParser, (req, res) => {
let id = req.params.id;let name = req.body.name;
let email = req.body.email;
let course_id = req.body.course_id;let sql = “UPDATE node_mysql_simple SET name = ?, email = ?, course_id = ? WHERE id = ?”;
mysqlConnection.query(sql, [name, email, course_id, id], (err, rows, fields) => {
if (!err) {
res.send(‘Updated with ID : ‘+ id);
} else {
console.log(err);
}
})
});
Method Update
3.4. Delete data with method Delete
// Route Delete
app.delete(‘/learns/:id’, (req, res) => {
mysqlConnection.query(‘DELETE FROM node_mysql_simple WHERE id = ?’, [req.params.id], (err, rows, fields) => {
if (!err) {
res.send(‘Delete success ID: ‘ + req.params.id);
} else {
console.log(‘Delete error: ‘ + err);
}
})
});
Method Delete
– Maybe you are interested: PHP – MVC CRUD and Connect to MySQL using PDO
Thank for reading.
