How To Make Node Express CRUD App With MySQL

In this step-by-step tutorial you will learn how to create CRUD application in Node.js with Express.js framework and MySQL database.

In this project we performing CRUD operations on “posts” such as creating post, reading post, updating post and deleting post.

Step 1: Setup the MySQL Database

  • Database name: node-crud-app
  • Table name: posts

Use the following SQL code to create the posts table and its columns:

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;

Step 2: Create the Project Folder

Now create a new folder anywhere you want called node-crud-app-with-mysql and then initialize the npm in the folder:

mkdir node-crud-app-with-mysql
cd node-crud-app-with-mysql
npm init -y

Step 3: Install Express.js & Other Required Packages

Here are the packages you need to install:

  • express: Node.js Framework.
  • express-validator: For form validation.
  • ejs: As template engine.
  • mysql2: MySQL client for Node.js
npm i express express-validator ejs mysql2

Step 4: Enable Es6 Module Import

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

add type module to the package JSON file to use import in node js

CRUD App Folder Structure:

Here is the structure of the node-crud-app-with-mysql folder that reveals what files we need create to build this CRUD Application.

node express mysql crud app folder structure

Step 5: Make Database Connection “database.js”

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();

Step 6: Define Routes & their Controllers with Validation

1. routes.js: Contains all the routes.

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

const router = Router({ strict: true }); // Creating a new router instance

// GET routes
router.get("/", Controllers.post_list); // Route to get list of posts
router.get("/create", Controllers.create_post); // Route to render post creation form
router.get(
    "/edit/:id",
    param("id").exists().isNumeric().toInt(), // Validation for edit post route
    Validation.validate, // Validate request
    Controllers.edit_post // Route to render post editing form
);
router.get(
    "/post/:id",
    [param("id").exists().isNumeric().toInt()], // Validation for single post route
    Controllers.single_post // Route to get a single post
);
router.get(
    "/delete/:id",
    [param("id").exists().isNumeric().toInt()], // Validation for delete post route
    Controllers.delete_post // Route to delete a post
);

// POST routes
router.post(
    "/create",
    Validation.default(["title", "author", "content"]), // Validation for post creation
    Validation.validate, // Validate request
    Controllers.insert_post // Route to insert a new post
);
router.post(
    "/edit/:id",
    [
        param("id").exists().isNumeric().toInt(), // Validation for edit post route
        ...Validation.default(["title", "author", "content"]), // Additional validation for post editing
    ],
    Validation.validate, // Validate request
    Controllers.update_post // Route to update an existing post
);

export default router; // Exporting router instance

2. validation.js: Validation rules are defined that validate the request data.

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

// Validation class with static methods
class Validation {
    // Method to generate default validation checks for specified fields
    static default(fields) {
        const checks = [];
        // Loop through each field
        for (let i of fields) {
            checks.push(
                // Add validation check for the field
                body(i, `Must not be empty.`) // Set error message for empty field
                    .unescape() // Unescape HTML entities
                    .trim() // Trim whitespace
                    .not() // Negate the following validations
                    .isEmpty() // Check if the field is empty
                    .escape() // Escape HTML entities
            );
        }
        return checks; // Return array of validation checks
    }

    // Validation middleware function
    static validate = (req, res, next) => {
        const errors = validationResult(req); // Get validation errors from request
        if (errors.isEmpty()) { // If there are no validation errors
            return next(); // Proceed to the next middleware or route handler
        }
        // If there are validation errors, store them in res.locals
        res.locals.validationError = errors;
        next(); // Proceed to the next middleware or route handler
    };
}

export default Validation; // Export the Validation class

3. controllers.js: The “Controller” class contains all of the route’s callbacks as static methods.

import DB from "./database.js"; // Importing database module

// Controllers class with static methods
class Controllers {
    // Render the form to create a new post
    static create_post = (req, res) => {
        res.render("create-post");
    };

    // Retrieve list of posts from the database and render post list view
    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);
        }
    };

    // Insert a new post into the database
    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);
        }
    };

    // Render the form to edit an existing post
    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);
        }
    };

    // Update an existing post in the database
    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);
        }
    };

    // Delete a post from the database
    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("/");
    };

    // Render a single post view
    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; // Exporting the Controllers class

Step 7: Create the Views for Frontend Post Management UI

Now at the root of the project folder create a new folder called views and create all the view inside this folder:

1. header.ejs: Contains navigation links.

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

2. create-post.ejs: Contains an HTML form that will be used to create a new post.

<!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>

3. post-list.ejs: Here all posts will be shown, and this view will render at the root of the URL.

<!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>

4. edit-post.ejs: For updating a post.

<!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>

5. view.ejs: Show a single post by ID.

<!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>

Step 8: Stylesheet for this Application “style.css”

Create a new folder called public at the root of the project and in the public folder create a folder called css. The style.css will be in this css 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;
}

Step 9: Create the Node Server Using Express “index.js”

index.js: This is the main file where the application will start execution.

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

// Getting the current filename and directory name
const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

// Creating an Express application
const app = express();

// Setting up view engine and views directory
app.set("view engine", "ejs");
app.set("views", path.join(__dirname, "views"));

// Parsing incoming request bodies in middleware before your handlers, available under the req.body property
app.use(express.urlencoded({ extended: false }));

// Serving static files in public directory
app.use(express.static(path.join(__dirname, "public")));

// Using routes middleware
app.use(routes);

// Error handling middleware
app.use((err, req, res, next) => {
    console.log(err.message); // Logging error message to console
    res.send("Error. See console"); // Sending error response to the client
});

// Starting the server and listening on port 3000
app.listen(3000, () => console.log("Server is running on port: 3000"));

Step 10: Start your App and Test it

node index.js
Testing of node express js crud application with mysql

Leave a Reply

Your email address will not be published. Required fields are marked *

We use cookies to ensure that we give you the best experience on our website. Privacy Policy