Express.js - MySQL CRUD Application with Handlebars (hbs)

Setup Project

Initialize the project

mkdir crud-example
cd crud-example
npm init -y

Install required dependencies

• hbs : A templating engine for rendering views with Handlebars syntax in Express.js.
• mysql : To install the mysql package to connect to the database and perform queries.
• body-parser : body-parser is middleware in Express.js. It processes incoming request data, such as form submissions or JSON, and makes it available in req.body.

Install the dependencies use the command is given below

npm install express
npm install hbs
npm install mysql
npm install body-parser

File & Folder Structure

Here’s the structure of the project

Creating the Database and Table

Execute the following SQL query to create a table named tbl_users inside your MySQL database named db_crud.

CREATE DATABASE db_crud;

USE db_crud;

CREATE TABLE tbl_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    contact VARCHAR(100) NOT NULL
);

index.js

The main Express.js application file

const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql');
const path = require('path');
const hbs = require('hbs');

const app = express();

// Middleware to parse incoming form data
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

// MySQL Connection
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'db_crud', // Replace with your database name
});

db.connect(err => {
  if (err) throw err;
  console.log('Connected to MySQL');
});

// Serve static files from the public directory
app.use(express.static(path.join(__dirname, 'public')));

// Set up handlebars as the view engine
app.set('view engine', 'hbs');

// Set the location of the views
app.set('views', path.join(__dirname, 'views'));

// **CRUD Routes**
// Index page: List all users
app.get('/', (req, res) => {
  const sql = 'SELECT * FROM tbl_users';
  db.query(sql, (err, results) => {
      if (err) throw err;
      res.render('index', { users: results });
  });
});

// Create: Add a new record
app.get('/create', (req, res) => {
  res.render('create'); // Render create form
});

app.post('/create', (req, res) => {
  const { name, email, contact } = req.body;
  const sql = 'INSERT INTO tbl_users (name, email, contact) VALUES (?, ?, ?)';
  db.query(sql, [name, email, contact], (err, result) => {
      if (err) throw err;
      res.redirect('/'); // Redirect to the home page after creating
  });
});

// Update: Modify a record by ID
app.get('/update/:id', (req, res) => {
  const { id } = req.params;
  const sql = 'SELECT * FROM tbl_users WHERE id = ?';
  db.query(sql, [id], (err, result) => {
      if (err) throw err;
      res.render('update', { user: result[0] });
  });
});

app.post('/update/:id', (req, res) => {
  const { id } = req.params;
  const { name, email, contact } = req.body;
  const sql = 'UPDATE tbl_users SET name = ?, email = ?, contact = ? WHERE id = ?';
  db.query(sql, [name, email, contact, id], (err, result) => {
      if (err) throw err;
      res.redirect(`/user/${id}`); // Redirect to user details after update
  });
});

// Delete: Remove a record by ID
app.get('/delete/:id', (req, res) => {
  const { id } = req.params;
  const sql = 'DELETE FROM tbl_users WHERE id = ?';
  db.query(sql, [id], (err, result) => {
      if (err) throw err;
      res.redirect('/'); // Redirect to the home page after deleting
  });
});

const PORT = 5000;

app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});

views/index.hbs

This page displays a list of all users, showing their name, email, and contact number in a table format. Each user has options to view, edit, or delete their details.

<div>
<a href="/create" class="create-link">Create a New User</a>
<table>
    <thead>
        <tr>
            <th>Name</th>
            <th>Email Id</th>
            <th>Contact No</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {{#each users}}
        <tr>
            <td>{{this.name}}</td>
            <td>{{this.email}}</td>
            <td>{{this.contact}}</td>
            <td>
                <a href="/update/{{this.id}}">Edit</a> | 
                <a href="/delete/{{this.id}}">Delete</a>
            </td>
        </tr>
        {{/each}}
    </tbody>
</table>
</div>

views/create.hbs

A form for creating a new user. Users can enter their name, email, and contact number to register a new account.

<h1>Create a New User</h1>
<form action="/create" method="POST">
    <label>Name:</label>
    <input type="text" name="name" required><br>
    <label>Email Id:</label>
    <input type="email" name="email" required><br>
     <label>Contact No:</label>
    <input type="text" name="contact" required><br>
    <button type="submit">Create</button>
</form>

views/update.hbs

A form to update the details of an existing user. The form is pre-filled with the user's current information, allowing them to modify and save changes.

<h1>Update User</h1>
<form action="/update/{{user.id}}" method="POST">
    <label>Name:</label>
    <input type="text" name="name" value="{{user.name}}" required><br>
    <label>Email Id:</label>
    <input type="email" name="email" value="{{user.email}}" required><br>
    <label>Contact No:</label>
    <input type="text" name="contact" value="{{user.contact}}" required><br>
    <button type="submit">Update</button>
</form>

Run the Server

Run the server using the command is given below.

node index.js

D:\crud-example>node index.js
Server is running on http://localhost:5000
Connected to MySQL

Output

Displays a list of all users

Allows you to add a new user.

Lets you update an existing user's information.