How to make Node.JS CRUD application with MySQL Database?

Here we will going the create the following CRUD application using Node JS with MySQL Database.

demo of node js crud application with mysql database

1. Create the MySQL Database first

First, create a database with the name you want, I named it test. 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` tinytext NOT NULL,
  `author` varchar(20) NOT NULL,
  `created_at` date NOT NULL DEFAULT current_timestamp(),
  `updated_at` date NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. Install the required node packages

Create a folder on your desktop or wherever you like and the folder name is totally up to you. This is our Node CRUD application folder.

Initialize the NPM –

npm init -y

Here are the packages you need to install –

express
express-validator – for form validation.
ejs – Template engine.
mysql2

npm i express express-validator ejs mysql2

Add type module to the package.json

Add "type": "module" in package.json file, because we will use the es6 import in this project.

3. Building Node.js CRUD app

Here given in the below image are the files and folders that we have to create to build this Node.js CRUD application.

Folder structure of the CRUD app

Database Connection

The database.js file contains the code for the connection with the MySQL database.

import mysql from "mysql2";

const connection = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "",
    database: "test",
});

export default connection.promise();

Routing & Validation

routes.js – Contains all the routes.
validation.js – Validates the client’s request data.
controllers.js – Contains a Controller class that contains all of the route’s callbacks as static methods.

routes.js
validation.js
controller.js

import { Router } from "express";
import { param } from "express-validator";
import Controllers from "./controllers.js";
import Validation from "./validation.js";

const router = Router({ strict: true });

router.get("/", Controllers.post_list);
router.get("/create", Controllers.create_post);
router.get(
    "/edit/:id",
    param("id").exists().isNumeric().toInt(),
    Validation.validate,
    Controllers.edit_post
);
router.get(
    "/post/:id",
    [param("id").exists().isNumeric().toInt()],
    Controllers.single_post
);
router.get(
    "/delete/:id",
    [param("id").exists().isNumeric().toInt()],
    Controllers.delete_post
);

router.post(
    "/create",
    Validation.default(["title", "author", "content"]),
    Validation.validate,
    Controllers.insert_post
);
router.post(
    "/edit/:id",
    [
        param("id").exists().isNumeric().toInt(),
        ...Validation.default(["title", "author", "content"]),
    ],
    Validation.validate,
    Controllers.update_post
);

export default router;

import { body, validationResult } from "express-validator";

class Validation {
    static default(fields) {
        const checks = [];
        for (let i of fields) {
            checks.push(
                body(i, `Must not be empty.`)
                    .unescape()
                    .trim()
                    .not()
                    .isEmpty()
                    .escape()
            );
        }
        return checks;
    }

    static validate = (req, res, next) => {
        const errors = validationResult(req);
        if (errors.isEmpty()) {
            return next();
        }
        res.locals.validationError = errors;
        next();
    };
}

export default Validation;

import DB from "./database.js";

class Controllers {
    static create_post = (req, res) => {
        res.render("create-post");
    };

    static post_list = async (req, res, next) => {
        try {
            const [row] = await DB.query("SELECT * FROM `posts`");
            res.render("post-list", {
                posts: row,
            });
        } catch (e) {
            next(e);
        }
    };

    static insert_post = async (req, res, next) => {
        if (res.locals.validationError !== undefined) {
            return res.render("create-post", {
                validationErrors: JSON.stringify(
                    res.locals.validationError.errors
                ),
                body: req.body,
            });
        }
        const { title, content, author } = req.body;
        try {
            await DB.execute(
                "INSERT INTO `posts` (`title`,`content`,`author`) VALUES (?,?,?)",
                [title, content, author]
            );
            res.redirect("/");
        } catch (e) {
            next(e);
        }
    };

    static edit_post = async (req, res, next) => {
        if (res.locals.validationError !== undefined) {
            return res.redirect("/");
        }
        try {
            const [row] = await DB.query("SELECT * FROM `posts` WHERE `id`=?", [
                req.params.id,
            ]);
            if (Object.keys(row).length === 0) {
                return res.redirect("/");
            }
            res.render("edit-post", {
                post: Object.values(row)[0],
            });
        } catch (e) {
            next(e);
        }
    };

    static update_post = async (req, res, next) => {
        if (isNaN(+req.params.id)) {
            return res.redirect("/");
        }
        try {
            const [row] = await DB.execute(
                "SELECT * FROM `posts` WHERE `id`=?",
                [req.params.id]
            );
            if (Object.keys(row).length === 0) {
                return res.redirect("/");
            }
            if (res.locals.validationError !== undefined) {
                return res.render("edit-post", {
                    validationErrors: JSON.stringify(
                        res.locals.validationError.errors
                    ),
                    body: req.body,
                    post: Object.values(row)[0],
                });
            }
            const date = new Date().toISOString();
            const { title, content, author } = req.body;
            await DB.execute(
                "UPDATE `posts` SET `title`=?, `content`=?,`author`=?, `updated_at`=? WHERE `id`=?",
                [title, content, author, date, req.params.id]
            );
            res.render("edit-post", {
                body: req.body,
                updated: 1,
            });
        } catch (e) {
            next(e);
        }
    };

    static delete_post = async (req, res, next) => {
        if (isNaN(+req.params.id)) {
            return res.redirect("/");
        }
        await DB.execute("DELETE FROM `posts` WHERE `id`=?", [req.params.id]);
        return res.redirect("/");
    };

    static single_post = async (req, res, next) => {
        if (isNaN(+req.params.id)) {
            return res.redirect("/");
        }
        try {
            const [row] = await DB.query("SELECT * FROM `posts` WHERE `id`=?", [
                req.params.id,
            ]);
            if (Object.keys(row).length === 0) {
                return res.redirect("/");
            }
            res.render("view", {
                post: Object.values(row)[0],
            });
        } catch (e) {
            next(e);
        }
    };
}

export default Controllers;

Main file (index.js) of the app

index.js from where the app will start execution.

import express from "express";
import path, {dirname} from "path";
import { fileURLToPath } from 'url';
import routes from "./routes.js";

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const app = express();

app.set("view engine", "ejs");
app.set("views", path.join(__dirname, "views"));

app.use(express.urlencoded({ extended: false }));
app.use(express.static(path.join(__dirname, "public")));

app.use(routes);
app.use((err, req, res, next) => {
    console.log(err.message);
    res.send("Error. See console");
});

app.listen(3000, () => console.log("Server is running on port: 3000"));

It’s time to create views

All the views will be inside the views folder. Here is a list of the views we have to create –

header.ejs – Contains navigation links.
create-post.ejs – Contains an HTML form that will be used to create a new post.
post-list.ejs – Here all posts will be shown, and this view will render at the root of the URL.
edit-post.ejs
view.ejs – Show a single post by ID.

header.ejs
create-post.ejs
post-list.ejs
edit-post.ejs
view.ejs

<header>
  <nav>
    <ul>
      <li><a href="/">All Posts</a></li>
      <li><a href="/create">Create Post</a></li>
    </ul>
  </nav>
</header>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Create Post</title>
  <link rel="stylesheet" href="./css/style.css">
</head>
<body><%- include("./header"); %>
<% if(typeof body === 'undefined'){ var body = false; } %>
  <div class="container">
    <h1 class="heading">✍🏻 Create Post</h1>
    <div class="form">
      <form action="" method="POST">
        <label for="post_title">Title: <span class="error-msg title"></span></label>
        <input type="text" name="title" id="post_title" placeholder="Title" value="<%- (typeof body.title !== 'undefined') ? body.title : '' %>">
        <label for="post_content">Content: <span class="error-msg content"></span></label>
        <textarea name="content" id="post_content" placeholder="Your thought..."><%- (typeof body.content !== 'undefined') ? body.content : '' %></textarea>
        <label for="post_author">Author: <span class="error-msg author"></span></label>
        <input type="text" name="author" id="post_author" placeholder="Author name" value="<%- (typeof body.author !== 'undefined') ? body.author : '' %>">
        <button type="submit">Add Post</button>
      </form>
    </div>
  </div><% if(typeof validationErrors !== "undefined"){ %>
    <script>
      let spanItem;
      let item;
      const validationErrors = <%- validationErrors %>;  
      for(i of validationErrors){
        spanItem = document.querySelector(`.error-msg.${i.path}`);
        item = document.querySelector(`[name="${i.path}"]`);
        item.classList.add('error');
        spanItem.innerText = i.msg;
      }
    </script>
  <% } %>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Post List</title>
  <link rel="stylesheet" href="./css/style.css">
</head>
<body><%- include("./header"); %>
  <div class="container">
    <h1 class="heading">All Posts</h1>
    <div class="post-list"><% if(typeof posts !== "undefined" && posts.length){ %>
      <table>
        <thead>
          <tr>
            <th>ID</th>
            <th>Title</th>
            <th>Actions</th>
          </tr>
        </thead>
        <tbody><% for(post of posts) {%>
          <tr>
            <td><%- post.id %></td>
            <td><a class="view-post" href="/post/<%- post.id %>"><%- post.title %></a></td>
            <td><a href="/edit/<%- post.id %>" class="edit">✎Edit</a><a href="/delete/" onclick="deleteMe(event,<%- post.id %>)" class="del">Delete</a></td>
          </tr>
          <%} %>
        </tbody>
      </table>
      <script>
        function deleteMe(e,i){
          e.preventDefault();
          if(confirm("Do you want to delete the post?")){
            window.location.href = e.target.getAttribute("href")+i;
          }
        }
      </script>
      <% }else{%>
        <p>😊 Please <a href="/create"><strong>insert</strong></a> some posts.</p>
        <%} %>
    </div>
  </div>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Edit Post</title>
  <link rel="stylesheet" href="/css/style.css">
</head>
<body><%- include("./header"); %>
<% if(typeof body === 'undefined'){ var body = false; } %>
  <div class="container">
    <h1 class="heading">✍🏻 Edit Post</h1>
    <div class="form">
      <form action="" method="POST">
        <label for="post_title">Title: <span class="error-msg title"></span></label>
        <input type="text" name="title" id="post_title" placeholder="Title" value="<%- (typeof body.title !== 'undefined') ? body.title : post.title %>">
        <label for="post_content">Content: <span class="error-msg content"></span></label>
        <textarea name="content" id="post_content" placeholder="Your thought..."><%- (typeof body.content !== 'undefined') ? body.content : post.content %></textarea>
        <label for="post_author">Author: <span class="error-msg author"></span></label>
        <input type="text" name="author" id="post_author" placeholder="Author name" value="<%- (typeof body.author !== 'undefined') ? body.author : post.author %>">
        <% if(typeof updated !== 'undefined') {%><div class="s-msg">Post has been updated.</div><% } %>
        <button type="submit">Update Post</button>
      </form>
    </div>
  </div>
  <script>
    if(window.history.replaceState){
      window.history.replaceState(null, null, window.location.href);
    }<% if(typeof validationErrors !== "undefined"){ %>
    let spanItem;
    let item;
    const validationErrors = <%- validationErrors %>;  
    for(i of validationErrors){
      spanItem = document.querySelector(`.error-msg.${i.path}`);
      item = document.querySelector(`[name="${i.path}"]`);
      item.classList.add('error');
      spanItem.innerText = i.msg;
    }<% } %>
  </script>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Single Post</title>
  <link rel="stylesheet" href="/css/style.css">
</head>
<body><%- include("./header"); %>
  <div class="container">
    <h1 class="heading"><%- post.title %></h1>
    <div class="post">
      <p><%- post.content %></p>
      <p class="date"><strong>Created at:</strong> <span><%- post.created_at.toLocaleDateString().replace(/\//g, '-') %></span> | <strong>Last Updated:</strong> <span><%- post.updated_at.toLocaleDateString().replace(/\//g, '-'); %></span></p>
    </div>
  </div>
</body>
</html>

Stylesheet for the views

Here is the Stylesheet (style.css) which will be created inside the css folder that is inside the public folder.

@import url("https://fonts.googleapis.com/css2?family=Open+Sans:wght@400;700&display=swap");
* {
    box-sizing: border-box;
}
html {
    font-size: 16px;
}
body {
    margin: 0;
    background: #f7f7f7;
    font-family: "Open Sans", sans-serif;
}
button,
input,
textarea {
    border: 1px solid rgb(0 0 0 / 0.3);
    border-radius: 3px;
    font-size: 1rem;
    font-family: "Open Sans", sans-serif;
    outline: none;
    padding: 10px;
}
input:is(:hover, :focus),
textarea:is(:hover, :focus) {
    border-color: #c7217f;
}
textarea {
    resize: vertical;
}

header {
    background-color: #c7217f;
}
header ul {
    display: flex;
    gap: 15px;
    justify-content: center;
    list-style: none;
    margin: 0;
    padding: 20px;
}
header a {
    color: white;
    text-decoration: none;
    text-transform: uppercase;
}

.heading {
    text-align: center;
}

.post,
.post-list,
.form form {
    background: white;
    box-shadow: 0 10px 15px -3px rgb(0 0 0 / 0.1),
        0 4px 6px -4px rgb(0 0 0 / 0.1);
    border-radius: 3px;
    padding: 30px;
    width: 500px;
    margin: 0 auto;
}
.post-list {
    width: 600px;
}

.form form {
    display: flex;
    flex-direction: column;
    gap: 5px;
}
label {
    font-weight: bold;
    margin-top: 7px;
}
label:first-child {
    margin-top: 0;
}
button[type="submit"] {
    background: #c7217f;
    border-color: rgb(0 0 0 / 0.2);
    color: white;
    cursor: pointer;
    margin-top: 10px;
}
button[type="submit"]:hover {
    background: #fdf7fa;
    border-color: #c7217f;
    color: #000;
}

.error:is(input, textarea) {
    border-color: red !important;
    outline: 1px solid red;
    outline-offset: 3px;
}
span.error-msg {
    color: red;
    font-weight: 400;
}
.s-msg {
    background: #edf8ed;
    border: 1px solid #87ce8b;
    border-radius: 3px;
    color: #4ab54f;
    padding: 10px;
}

.post-list table {
    border-collapse: collapse;
    text-align: center;
    width: 100%;
}
.post-list table th,
.post-list table td {
    border: 1px solid rgb(0 0 0 / 0.1);
    padding: 10px 5px;
}
.post-list table tbody tr:nth-child(odd) {
    background: #fdf7fa;
}

.post-list table tbody tr td:last-child a {
    border: 1px solid rgb(0 0 0 / 0.3);
    border-radius: 3px;
    font-size: 14px;
    margin: 0 3px;
    padding: 0 3px;
    text-decoration: none;
}
a.view-post {
    color: inherit;
}
a.edit {
    background-color: #f5f5ef;
    color: #000;
}
a.del {
    background-color: #c7217f;
    color: white;
}
.date span {
    font-style: italic;
    color: #777;
}

Its time to test the app

All thing is done, now test your app… ❤❤