Here you will learn how to build a very simple CRUD application in Node JS with MySQL Database.
1. Create the MySQL Database first
First, create a database with the name you want, I named it node_api
. After creating the database –
We have to create a table called posts
, and there will be 6 columns inside the table – id
, title
, content
, author
, created_at
, updated_at
.
Use the following SQL code to create the posts
table and its structure.
CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`content` text NOT NULL,
`author` varchar(30) NOT NULL,
`created_at` date NOT NULL DEFAULT current_timestamp(),
`updated_at` date NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;
2. Building the Node JS CRUD REST API
First, create a folder called node-mysql-crud-api
, this is our project folder. After that, run npm init -y
on your terminal to initialize the npm into this folder.
Now you have to install three packages – express
, express-validator
, and mysql2
.
npm i express express-validator mysql2
In this project, we will use es6 import, so you have to add "type": "module"
in the package.json
.

Application folder structure
Here given in the below image are the files and folders that we have to create to build this Node JS CRUD API.

Database Connection
database.js
contains the code for the database connection.
import mysql from "mysql2";
const connection = mysql.createPool({
host: "localhost",
user: "root",
password: "",
database: "node_api",
});
export default connection.promise();
Routes and Controllers
routes.js
– contains all the routes with validation rules.Controller.js
– The Controller class contains all the callbacks that handle all the requests and perform all CRUD operations.
- routes.js
- Controller.js
import { Router } from "express";
import { body, param } from "express-validator";
import Controller from "./Controller.js";
const routes = Router({ strict: true });
routes.post(
"/create",
[
body("title", "Must not be empty.").trim().not().isEmpty().escape(),
body("body", "Must not be empty.").trim().not().isEmpty().escape(),
body("author", "Must not be empty.").trim().not().isEmpty().escape(),
],
Controller.validation,
Controller.create
);
routes.get("/posts", Controller.show_posts);
routes.get(
"/post/:id",
[param("id", "Invalid post ID.").exists().isNumeric().toInt()],
Controller.validation,
Controller.show_posts
);
routes.put(
"/edit",
[
body("post_id", "Invalid post ID").isNumeric().toInt(),
body("title", "Must not be empty.")
.optional()
.trim()
.not()
.isEmpty()
.escape(),
body("body", "Must not be empty.")
.optional()
.trim()
.not()
.isEmpty()
.escape(),
body("author", "Must not be empty.")
.optional()
.trim()
.not()
.isEmpty()
.escape(),
],
Controller.validation,
Controller.edit_post
);
routes.delete(
"/delete",
[
body("post_id", "Please provide a valid post ID.")
.exists()
.isNumeric()
.toInt(),
],
Controller.validation,
Controller.delete_post
);
export default routes;
import { validationResult, matchedData } from "express-validator";
import DB from "./database.js";
const validation_result = validationResult.withDefaults({
formatter: (error) => error.msg,
});
class Controller {
static validation = (req, res, next) => {
const errors = validation_result(req).mapped();
if (Object.keys(errors).length) {
return res.status(422).json({
ok: 0,
status: 422,
errors,
});
}
next();
};
static create = async (req, res, next) => {
const { title, body, author } = matchedData(req);
try {
const [result] = await DB.execute(
"INSERT INTO `posts` (`title`,`content`,`author`) VALUES (?,?,?)",
[title, body, author]
);
res.status(201).json({
ok: 1,
status: 201,
message: "Post has been created successfully",
post_id: result.insertId,
});
} catch (e) {
next(e);
}
};
static show_posts = async (req, res, next) => {
try {
let sql = "SELECT * FROM `posts`";
if (req.params.id) {
sql = `SELECT * FROM posts WHERE id=${req.params.id}`;
}
const [row] = await DB.query(sql);
if (row.length === 0 && req.params.id) {
return res.status(404).json({
ok: 0,
status: 404,
message: "Invalid post ID.",
});
}
const post = req.params.id ? { post: row[0] } : { posts: row };
res.status(200).json({
ok: 1,
status: 200,
...post,
});
} catch (e) {
next(e);
}
};
static edit_post = async (req, res, next) => {
try {
const data = matchedData(req);
const [row] = await DB.query("SELECT * FROM `posts` WHERE `id`=?", [
data.post_id,
]);
if (row.length !== 1) {
return res.json({
ok: 0,
statu: 404,
message: "Invalid post ID.",
});
}
const post = row[0];
const date = new Date().toISOString();
const title = data.title || post.title;
const content = data.body || post.content;
const author = data.author || post.author;
await DB.execute(
"UPDATE `posts` SET `title`=?, `content`=?,`author`=?, `updated_at`=? WHERE `id`=?",
[title, content, author, date, data.post_id]
);
res.json({
ok: 1,
status: 200,
message: "Post Updated Successfully",
});
} catch (e) {
next(e);
}
};
static delete_post = async (req, res, next) => {
try {
const [result] = await DB.execute(
"DELETE FROM `posts` WHERE `id`=?",
[req.body.post_id]
);
if (result.affectedRows) {
return res.json({
ok: 1,
status: 200,
message: "Post has been deleted successfully.",
});
}
res.status(404).json({
ok: 0,
status: 404,
message: "Invalid post ID.",
});
} catch (e) {
next(e);
}
};
}
export default Controller;
index.js – where the execution of the application will start
import express from "express";
import routes from "./routes.js";
const app = express();
const PORT = 3000;
app.use(express.json());
app.use(routes);
app.use((err, req, res, next) => {
err.statusCode = err.statusCode || 500;
err.message = err.message || "Internal Server Error";
res.status(err.statusCode).json({
message: err.message,
});
});
app.listen(PORT, () => console.log(`Server is running on port ${PORT}`));
3. Testing of the Node JS CRUD API
- Create Post
- Fetch Post
- Update Post
- Delete Post
End-Point: /create
Method: Post,
Payload:
{
"title":"Post Title",
"body":"Post Body",
"author":"Author Name"
}

# Fetch all Posts
End-Point: /posts
Method: GET,
# Fetch a single post
/post/{post_id} -> /post/2
Method: Get

End-Point: /edit
Method: PUT,
Payload: (post_id and at least one field is required)
{
"post_id": "post id",
"field_name (title | body | author)" : "New Value"
}

End-Point: /delete
Method: DELETE,
Payload:
{
"post_id": "post id"
}
