A CRUD (Create, Read, Update, Delete) API is a fundamental component of many web applications, allowing you to perform basic database operations. In this step-by-step tutorial, your will learn how to create a CRUD API in Node with Express.js framework and MySQL Database.
Prerequisites to build this CRUD API:
Before getting started, make sure you have the following prerequisites in place:
- Node.js and npm: Ensure that you have Node.js installed on your machine. You can download it from the official Node.js website.
npm, the Node Package Manager, typically comes with Node.js. - MySQL: You should have MySQL installed on your machine or access to a MySQL server.
Let’s build Node Express CURD API:
Through this RESTful CRUD API we will mange posts such as create a new post, edit or update an existing post, read a post, and delete a post.
Set-up the Project Folder
The first step is to create a new directory called “nodejs-mysql-crud-api” for this CUR API project and navigate to it in your terminal:
mkdir node-mysql-crud-api
cd node-mysql-crud-apiNext, initialize the npm into the project folder:
npm init -yThis command creates a package.json file with default settings.
Install Required Dependencies
Now we need to install some necessary packages that we will use to build this Node CRUD API. Here are the Node packages:
express: The popular node framework.mysql2: is the MySQL driver for Node.js.zod: for data validation.validator: for data sanitization.
npm i express mysql2 zod validatorEnable ES6 Import in Node.js
In this project we will use ES2015 import instead of require() to include modules and therefore we need enable this feature by adding "type":"module" in the package.json file, like the following image:
Create a MySQL Database & the “posts” Table:
As I said we will perform CRUD operations by creating, reading, updating and deleting posts, therefore we have to design the database and table accordingly.
- Database name:
nodejs_crud_api. You can give any name. - Table name:
posts
After creating the database you can use the following SQL code create the posts table and its columns. don’t know how to use the following SQL code to create the posts table – Checkout this.
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` datetime NOT NULL DEFAULT current_timestamp(),
`updated_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;Create Application Files:
To build this simple CRUD API we need to create just 5 JavaScript files in the project folder.
nodejs-mysql-crud-api/
├── node_modules/
├── database.js
├── validations.js
├── controllers.js
├── routes.js
├── index.js
└── package.json“database.js” For Database Connection
To interact with the MySQL database, you need to establish a connection. Therefore the database.js contains the code for the database connection. So create the database.js at root and put the following MySQL DB connection code to it:
import mysql from "mysql2";
const connection = mysql.createPool({
host: "localhost",
user: "root", // Your DB User
password: "", // Your DB Password
database: "nodejs_crud_api",
});
export default connection.promise();“validations.js” Request Data Validation using Zod:
In this Node CRUD API we will use the zod for posts data validations. Create the validations.js at the root and here is the validation schema code for posts data validations, one for when inserting a post and other one is for updating a post.
// Import Zod and validator libraries
import { z } from "zod";
import validator from "validator";
const { unescape, escape } = validator; // Destructure unescape and escape functions from validator
// Define validation schema for inserting a post
export const insertPostSchema = z
.object({
title: z.string().trim().min(10), // Title validation
content: z.string().trim().min(10), // Content validation
author: z.string().min(3), // Author validation
})
.transform((data) => { // Transform data before validation
return {
...data,
title: escape(unescape(data.title)), // Escape and unescape title
content: escape(unescape(data.content)), // Escape and unescape content
author: escape(unescape(data.author)), // Escape and unescape author
};
});
// Define validation schema for updating a post
export const updatePostSchema = z.object({
title: z
.string()
.trim()
.min(10)
.transform((val) => escape(unescape(val))) // Escape and unescape title
.optional(), // Title is optional
content: z
.string()
.trim()
.min(10)
.transform((val) => escape(unescape(val))) // Escape and unescape content
.optional(), // Content is optional
});“controllers.js” Route Handlers for this Node CRUD API
Now create the controllers.js that contains the following CRUD operations handlers for the routes that we will create in the very next.
// Import validation schemas
import { insertPostSchema, updatePostSchema } from "./validations.js";
import dbConnection from "./database.js"; // Import database connection
// Response JSON helper function
const resJson = (res, status, message, extra = {}) => {
return res.status(status).json({
status,
message,
...extra,
});
};
// Insert post controller
export const insertPost = async (req, res, next) => {
try {
const postData = insertPostSchema.safeParse(req.body); // Validate request data
if (!postData.success) {
// If validation fails
return resJson(res, 422, "Please fill in all fields correctly.", {
fieldErrors: postData.error.flatten().fieldErrors,
});
}
const { title, content, author } = postData.data; // Extract post data
// Insert post into database
const [result] = await dbConnection.execute(
"INSERT INTO `posts` (`title`,`content`,`author`) VALUES (?,?,?)",
[title, content, author]
);
resJson(res, 201, "Post has been created successfully", {
post_id: result.insertId,
});
} catch (err) {
next(err);
}
};
// Read post controller
export const readPost = async (req, res, next) => {
try {
let sql = "SELECT * FROM `posts`"; // Default SQL query
const haspostId = req.params.post_id || false; // Check if post ID is provided
if (haspostId) {
// If post ID is provided
const parsedpostId = parseInt(haspostId);
// Validate post ID
if (isNaN(haspostId) || parsedpostId <= 0) {
return resJson(
res,
422,
"Post id must be a number and greater than the 0."
);
}
// Modify SQL query for specific post by ID
sql = `SELECT * FROM posts WHERE id=${parsedpostId}`;
}
const [row] = await dbConnection.query(sql); // Execute SQL query
if (row.length === 0 && haspostId) {
// If post not found
return resJson(res, 404, "Incorrect post ID. Post not found.");
}
const posts = haspostId ? { post: row[0] } : { posts: row }; // Prepare response data
const message = haspostId ? "Single Post" : "All Post";
resJson(res, 200, message, posts);
} catch (err) {
next(err);
}
};
// Update post controller
export const updatePost = async (req, res, next) => {
try {
const postId = req.params.post_id; // Get post ID from request
const parsedpostId = parseInt(postId);
if (isNaN(postId) || parsedpostId <= 0) {
// Validate post ID
return resJson(
res,
422,
"Post id must be a number and greater than the 0."
);
}
// Validate request data
const postData = updatePostSchema.safeParse(req.body);
if (!postData.success) {
// If validation fails
return resJson(res, 422, "Please fill in all fields correctly.", {
fieldErrors: postData.error.flatten().fieldErrors,
});
}
// Check if post exists
const [row] = await dbConnection.query(
"SELECT * FROM `posts` WHERE `id`=?",
[parsedpostId]
);
if (row.length !== 1) {
return resJson(res, 404, "Incorrect post id. Post not foundl");
}
const newPostData = postData.data;
const post = row[0];
const updatedDate = new Date().toISOString();
const title = newPostData.title || post.title;
const content = newPostData.content || post.content;
// Update post in database
const updateSQLTemp =
"UPDATE `posts` SET `title`=?, `content`=?, `updated_at`=? WHERE `id`=?";
await dbConnection.execute(updateSQLTemp, [
title,
content,
updatedDate,
parsedpostId,
]);
resJson(res, 200, "Post Updated Successfully", {
post_id: parsedpostId,
});
} catch (err) {
next(err);
}
};
// Delete post controller
const deletePost = async (req, res, next) => {
try {
const postId = req.body.post_id; // Get post ID from request
const parsedpostId = parseInt(postId);
if (isNaN(postId) || parsedpostId <= 0) {
// Validate post ID
return resJson(
res,
422,
"Post id must be a number and greater than the 0.",
{
fieldErrors: {
post_id: [
"Required and must be a number and greater than the 0.",
],
},
}
);
}
// Delete post from database
const [result] = await dbConnection.execute(
"DELETE FROM `posts` WHERE `id`=?",
[parsedpostId]
);
if (result.affectedRows) {
// If post deleted successfully
return resJson(res, 200, "Post has been deleted successfully.");
}
return resJson(res, 404, "Incorrect post id. Post not found."); // If post not found
} catch (e) {
next(e);
}
};
export default {
insertPost,
readPost,
updatePost,
deletePost,
};“routes.js” Define Routes for this Node Express CRUD app:
The following routes.js code sets up a router with routes to handle CRUD operations (Create, Read, Update, Delete) for posts. Each route is associated with a specific controller function that is defined in the “controllers.js“. Create the routes.js and put the following code into this:
import { Router } from "express";
import controllers from "./controllers.js";
const { insertPost, readPost, updatePost, deletePost } = controllers;
const router = Router({ strict: true });
router.post("/insert-post", insertPost);
router.get("/get-all-posts", readPost);
router.get("/get-post/:post_id", readPost);
router.put("/update-post/:post_id", updatePost);
router.delete("/delete-post", deletePost);
export default router;“index.js” The Start of the Node Express CRUD API App:
The following index.js code sets up an Express server, defines middleware for parsing JSON requests, sets up routes, handles errors, and starts the server after establishing a connection to the database. Create the index.js and here is the for this file:
import express from "express";
import dbConnection from "./database.js";
import routes from "./routes.js";
const app = express();
// Middleware setup
app.use(express.json()); // Middleware for parsing incoming JSON requests
app.use("/api/v1/", routes); // Mounting the routes under the /api/v1/ path
// Error handling middleware
app.use((err, req, res, next) => {
// Set default status code to 500 (Internal Server Error)
err.statusCode = err.statusCode || 500;
err.message = err.message || "Internal Server Error"; // Set default error message
res.status(err.statusCode).json({
status: err.statusCode,
message: err.message,
});
});
const PORT = 3000;
// Start the server after establishing a connection to the database
dbConnection
.getConnection() // Attempt to establish a connection to the database
.then(() => {
// If the connection is successful
app.listen(PORT, async () => {
// Start the Express server and listen for incoming connections
console.log(`Server is running on port ${PORT}`); // Log a message indicating the server is running
});
})
.catch((err) => {
// If there's an error establishing a connection to the database
console.log(err.message); // Log the error message
});Testing of the Node, Express, MySQL CRUD API
# Inserting Post
Method: POST
Endpoint: http://localhost:3000/api/v1/insert-post
Payload (JSON):
{
"title":"Post Title",
"content":"Post Content",
"author":"Author Name"
}
# Fetching All Posts
Method: GET
Endpoint: http://localhost:3000/api/v1/get-all-posts
# Fetching Single Post by Id
Method: GET
Endpoint: http://localhost:3000/api/v1/get-post/{post_id}
# Updating a Post
Method: PUT
Endpoint: http://localhost:3000/api/v1/update-post/{post_id}
Payload (JSON):
{
"title":"New Post Title", (Optional)
"content":"New Post Content", (Optional)
}
# Deleting a Post
Method: DELETE
Endpoint: http://localhost:3000/api/v1/delete-post
Payload (JSON):
{
"post_id":{Post Id},
}