Simple User Management System – Nodejs, Express, MySQL & Handlebards

By Raddy in NodeJs ·

Today we are going to build a basic user management system using Node.js, Express, Express-Handlebars and MySQL. I would suggest you watch the full video as I go into more detail that I could on this post. Saying this, you can use this post as a reference.

Watch the full tutorial

This article requires some work to make it into a full tutorial. If you love writing and you would like to contribute, please contact me.

What you need:

To complete this project you will need a MySQL Database & Node installed. I usually use XAMPP for local development, but you can use whatever you wish as long as the database is MySQL of course.

Initialize New Project

To initialise a new Node.js project all you have to do is to create a new project folder “user-management-system” and then run the Command line or PowerShell in the same directory. Once you do that to initialise a new project simply put the following command:

npm init

This will initialise a new project for you and it’s going to ask you a few questions about your project. The most important one is to give your package a name and then you can just keep pressing enter until the installation is over.

Project Structure

Let’s create the following folders and files, leaving node_modules, readme.md, package-lock and package-json as that should have been automatically generated by now.

You can structure your project the way you like. I am just going to keep it super simple and partition everything into views, routes and controllers.

📂 node_modules
📂 public
 📂 css
  📜 main.css
  📜 main.scss
 📂 img
  🖼 default.jpg
📂 server
 📂 controllers
  📜 userController.js
 📂 routes
  📜 user.ejs
📂 views
  📂 layouts
   📜 main.hbs
  📂 partials
 📜 edit-user.hbs
 📜 home.hbs
 📜 new-user.hbs
 📜 view-user.hbs
⚙.env
📜 README.md
⚓ .env
🌍 app.js
📜 package-lock.json
📜 package-json

Dependencies Installation

There are a few dependencies that we need to install to get started. Here is the list:

[x] express
[x] Dotenv
[x] express-handlebars
[x] body-parser
[x] mysql

Let’s do that by opening the terminal / powershell and install the dependencies by typing the following command:

npm install express dotenv express-handlebars body-parser mysql

Restarting Express server

Restarting the server automatically would be annoying. To save us some time let’s quickly install Nodemon as a development dependency.

npm install --save-dev nodemon

We need to tell our application to run with nodemon and to do that we can add the “start” line under scripts in your package.json file like in the example below.

  "scripts": {
    "start": "nodemon app.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },

Start our local server

To start our application / our local server simply type the following command in the command line:

npm start

Hopefully, everything should be working just fine and you won’t have any errors. Let’s start working on our application

Application Middleware

Let’s now create our application file. This file will be called app.js and it will sit in the root of our website.

In this file, we need to do a couple of things. We need to require some of the dependencies that we will be working with and we also need to set up our server. Instead of explaining everything, I have added a few comments so you know what the code is supposed to do. Of course research and read the official documentation to learn more or watch the full video.

const express = require('express');
const exphbs  = require('express-handlebars');
const bodyParser = require('body-parser');
const mysql = require('mysql');

require('dotenv').config();

const app = express();
const port = 5000;

// Parsing middleware
// Parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: false }));

// Parse application/json
app.use(bodyParser.json());

// Static Files
app.use(express.static('public'));

// Templating Engine
app.engine('hbs', exphbs({ extname: '.hbs' })); 
app.set('view engine', 'hbs');

/*
connection pool is a cache of database connections maintained so that the connections
can be reused when future requests to the database are required.
*/
const pool  = mysql.createPool({
  connectionLimit : 100,
  host            : process.env.DB_HOST,
  user            : process.env.DB_USER,
  password        : process.env.DB_PASS,
  database        : process.env.DB_NAME
});
  
pool.getConnection((errconnection) => {
    if(err) throw err; // not connected!
    console.log('connected as id ' + connection.threadId)
});

// Routes
const routes = require('./server/routes/user');
app.use('/', routes);

// Listen on port 5000
app.listen(port, () => console.log(`Listening on port ${port}`));

Layout – Node.js Handlebars & Bootstrap

If you haven’t yet, create the following folders and files:

📂 views
  📂 layouts
   📜 main.hbs
  📂 partials
 📜 edit-user.hbs
 📜 home.hbs
 📜 new-user.hbs
 📜 view-user.hbs

To render our pages we are using express-handlebars and for the layout, we will be using Bootstrap v5.0.0-beta1.

<!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>User Management System</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet"
    integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.3.0/font/bootstrap-icons.css">
</head>

<body>
  <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
    <div class="container-fluid">
      <a class="navbar-brand" href="/">User Management System</a>
      <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent"
        aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
        <span class="navbar-toggler-icon"></span>
      </button>
      <div class="collapse navbar-collapse" id="navbarSupportedContent">
        <ul class="navbar-nav me-auto mb-2 mb-lg-0">
          <li class="nav-item">
            <a class="nav-link active" aria-current="page" href="/">Home</a>
          </li>
        </ul>

        <form class="d-flex" method="POST" action="/" novalidate>
          <input class="form-control me-2" type="search" placeholder="Search" name="search" aria-label="Search">
          <button class="btn btn-outline-light" type="submit">Search</button>
        </form>

      </div>
    </div>
  </nav>

  <div class="container pt-5 pb-5">
      {{{body}}}
  </div>

  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/js/bootstrap.bundle.min.js"
    integrity="sha384-ygbV9kiqUc6oa4msXn9868pTtWMgiQaeYH7/t7LECLbyPA2x65Kgf80OJFdroafW"
    crossorigin="anonymous"></script>
</body>

</html>

Create home.hbs

{{#if removedUser}}
<div class="alert alert-success alert-dismissible fade show" role="alert">
  User has been removed.
  <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>
{{/if}}

<div class="row">
  <div class="col-6">
    <h1>Users</h1>
  </div>
  <div class="col-6 d-flex justify-content-end">
    <a href="/adduser" type="button" class="btn btn-primary align-self-center">+ add user</a>
  </div>
</div>

<table class="table table-bordered">
  <thead class="thead-dark">
    <tr>
      <th scope="col">#</th>
      <th scope="col">First Name</th>
      <th scope="col">Last Name</th>
      <th scope="col">Email</th>
      <th scope="col">Phone</th>
      <th scope="col" class="text-end">Action</th>
    </tr>
  </thead>
  <tbody>

    {{#each rows}}
    <tr>
      <th scope="row">{{this.id}}</th>
      <td>{{this.first_name}}</td>
      <td>{{this.last_name}}</td>
      <td>{{this.email}}</td>
      <td>{{this.phone}}</td>
      <td class="text-end">
        <a href="/viewuser/{{this.id}}" type="button" class="btn btn-light btn-small"><i class="bi bi-eye"></i> View</a>
        <a href="/edituser/{{this.id}}" type="button" class="btn btn-light btn-small"><i class="bi bi-pencil"></i>
          Edit</a>
        <a href="/{{this.id}}" type="button" class="btn btn-light btn-small"><i class="bi bi-person-x"></i> Delete</a>
      </td>
    </tr>
    {{/each}}

  </tbody>
</table>

Create view-user.hbs

<nav aria-label="breadcrumb">
  <ol class="breadcrumb">
    <li class="breadcrumb-item"><a href="/">Home</a></li>
    <li class="breadcrumb-item active" aria-current="page">View User</li>
  </ol>
</nav>

<div class="view-user p-5">
  {{#each rows}}
  <div class="row mb-5">
    <div class="col text-center">
      <h3>{{this.first_name}} {{this.last_name}}</h3>
    </div>
  </div>
  <div class="row">
    <div class="col">
      <table class="table">
        <thead>
          <tr>
            <th scope="col">First Name</th>
            <th scope="col">Last Name</th>
            <th scope="col">Email</th>
            <th scope="col">Phone</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <th scope="row">{{this.first_name}}</th>
            <td>{{this.last_name}}</td>
            <td>{{this.email}}</td>
            <td>{{this.phone}}</td>
          </tr>
        </tbody>
      </table>
    </div>
    <div class="row">
      <div class="col">
        <b>Comments</b>
      </div>
    </div>
    <div class="row">
      <div class="col">
        {{this.comments}}
      </div>
    </div>
  </div>
  {{/each}}
</div>

edit-user.hbs

<nav aria-label="breadcrumb">
  <ol class="breadcrumb">
    <li class="breadcrumb-item"><a href="/">Home</a></li>
    <li class="breadcrumb-item active" aria-current="page">Edit User</li>
  </ol>
</nav>

{{#if alert}}
  <div class="alert alert-success alert-dismissible fade show" role="alert">
    {{alert}}
    <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
  </div>
{{/if}}

{{#each rows}}
<form class="row g-3 needs-validation" method="POST" action="/edituser/{{this.id}}" novalidate> 
  {{> user-form}}
</form>
{{/each}}

add-user.hbs

<nav aria-label="breadcrumb">
  <ol class="breadcrumb">
    <li class="breadcrumb-item"><a href="/">Home</a></li>
    <li class="breadcrumb-item active" aria-current="page">New User</li>
  </ol>
</nav>

{{#if alert}}
<div class="alert alert-success alert-dismissible fade show" role="alert">
  {{alert}}
  <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>
{{/if}}

<form class="row g-3 needs-validation" method="POST" action="/adduser" novalidate>
  {{> user-form}}
</form>

In Partials create user-forms.hbs and paste the form:

  <div class="col-6">
    <div class="form-floating mb-3">
      <input type="text" class="form-control" id="floatingInput" value="{{this.first_name}}" placeholder="First Name" name="first_name">
      <label for="floatingInput">First Name</label>
    </div>
  </div>
  <div class="col-6">
    <div class="form-floating mb-3">
      <input type="text" class="form-control" id="floatingInput" value="{{this.last_name}}" placeholder="Last Name" name="last_name">
      <label for="floatingInput">Last Name</label>
    </div>
  </div>
  <div class="col-6">
    <div class="form-floating mb-3">
      <input type="email" class="form-control" id="floatingInput" value="{{this.email}}" placeholder="email@email.com" name="email">
      <label for="floatingInput">Email</label>
    </div>
  </div>
  <div class="col-6">
    <div class="form-floating mb-3">
      <input type="text" class="form-control" id="floatingInput" value="{{this.phone}}" placeholder="Phone" name="phone">
      <label for="floatingInput">Phone</label>
    </div>
  </div>
  <div class="col-12">
    <div class="form-floating mb-3">
      <textarea class="form-control" placeholder="Leave a comment here" id="comments" name="comments" style="height: 200px">{{this.comments}}</textarea>
      <label for="comments">Comments</label>
    </div>
  </div>
  <div class="col-12 d-grid">
    <button class="btn btn-primary" type="submit">Submit</button>
  </div>

Server

Let’s start working on the logic. Once again, ideally, you should watch the video to see how I do it step by step.

Create a .env file and add your database credentials like this:

DB_HOST = localhost
DB_NAME = usermanagement_tut
DB_USER = root
DB_PASS = password

Create your server folder and add the following two folders and files.

Controllers folder – userController.js

const mysql = require('mysql');

// Connection Pool
const pool = mysql.createPool({
  connectionLimit: 100,
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME
});

// View Users
exports.view = (reqres) => {
  pool.getConnection((errconnection) => {
    if (err) throw err; // not connected!
    console.log('Connected as ID ' + connection.threadId);
    // User the connection
    connection.query('SELECT * FROM user WHERE status = "active"', (errrows) => {
      // When done with the connection, release it
      connection.release();
      if (!err) {
        let removedUser = req.query.removed;
        res.render('home', { rows, removedUser });
      } else {
        console.log(err);
      }
      console.log('The data from user table: \n', rows);
    });
  });
}

// Find User by Search
exports.find = (reqres) => {
  pool.getConnection((errconnection) => {
    if (err) throw err; // not connected!
    console.log('Connected as ID ' + connection.threadId);
    let searchTerm = req.body.search;
    // User the connection
    connection.query('SELECT * FROM user WHERE first_name LIKE ? OR last_name LIKE ?', ['%' + searchTerm + '%', '%' + searchTerm + '%'], (errrows) => {
      // When done with the connection, release it
      connection.release();
      if (!err) {
        res.render('home', { rows });
      } else {
        console.log(err);
      }
      console.log('The data from user table: \n', rows);
    });
  });
}

exports.form = (reqres) => {
  console.log("red");
  res.render('add-user');
}

// Add new user
exports.create = (reqres) => {
  const { first_name, last_name, email, phone, comments } = req.body;

  pool.getConnection((errconnection) => {
    if (err) throw err; // not connected!
    console.log('Connected as ID ' + connection.threadId);
    let searchTerm = req.body.search;

    // User the connection
    connection.query('INSERT INTO user SET first_name = ?, last_name = ?, email = ?, phone = ?, comments = ?', [first_name, last_name, email, phone, comments], (errrows) => {
      // When done with the connection, release it
      connection.release();
      if (!err) {
        res.render('add-user', { alert: 'User added successfully.' });
      } else {
        console.log(err);
      }
      console.log('The data from user table: \n', rows);
    });
  });
}


// Edit user
exports.edit = (reqres) => {
  pool.getConnection((errconnection) => {
    if (err) throw err; // not connected!
    console.log('Connected as ID ' + connection.threadId);
    // User the connection
    connection.query('SELECT * FROM user WHERE id = ?', [req.params.id], (errrows) => {
      // When done with the connection, release it
      connection.release();
      if (!err) {
        res.render('edit-user', { rows });
      } else {
        console.log(err);
      }
      console.log('The data from user table: \n', rows);
    });
  });
}


// Update User
exports.update = (reqres) => {
  const { first_name, last_name, email, phone, comments } = req.body;

  pool.getConnection((errconnection) => {
    if (err) throw err; // not connected!
    console.log('Connected as ID ' + connection.threadId);
    // User the connection
    connection.query('UPDATE user SET first_name = ?, last_name = ?, email = ?, phone = ?, comments = ? WHERE id = ?', [first_name, last_name, email, phone, comments, req.params.id], (errrows) => {
      // When done with the connection, release it
      connection.release();

      if (!err) {

        pool.getConnection((errconnection) => {
          if (err) throw err; // not connected!
          console.log('Connected as ID ' + connection.threadId);
          // User the connection
          connection.query('SELECT * FROM user WHERE id = ?', [req.params.id], (errrows) => {
            // When done with the connection, release it
            connection.release();
            if (!err) {
              res.render('edit-user', { rows, alert: `${first_name} has been updated.` });
            } else {
              console.log(err);
            }
            console.log('The data from user table: \n', rows);
          });
        });

      } else {
        console.log(err);
      }
      console.log('The data from user table: \n', rows);
    });
  });
}

// Delete User
exports.delete = (reqres) => {

  // Delete a record
  // pool.getConnection((err, connection) => {
  //   if(err) throw err; // not connected!
  //   console.log('Connected as ID ' + connection.threadId);

  //   // User the connection
  //   connection.query('DELETE FROM user WHERE id = ?', [req.params.id], (err, rows) => {
  //     // When done with the connection, release it
  //     connection.release();
  //     if(!err) {
  //       res.redirect('/');
  //     } else {
  //       console.log(err);
  //     }
  //     console.log('The data from user table: \n', rows);

  //   });
  // });

  // Hide a record
  pool.getConnection((errconnection) => {
    if (err) throw err;
    connection.query('UPDATE user SET status = ? WHERE id = ?', ['removed', req.params.id], (errrows) => {
      connection.release() // return the connection to pool
      if (!err) {
        let removedUser = encodeURIComponent('User successeflly removed.');
        res.redirect('/?removed=' + removedUser);
      } else {
        console.log(err);
      }
      console.log('The data from beer table are: \n', rows);
    });
  });

}


// View Users
exports.viewall = (reqres) => {
  pool.getConnection((errconnection) => {
    if (err) throw err; // not connected!
    console.log('Connected as ID ' + connection.threadId);
    // User the connection
    connection.query('SELECT * FROM user WHERE id = ?', [req.params.id], (errrows) => {
      // When done with the connection, release it
      connection.release();
      if (!err) {
        res.render('view-user', { rows });
      } else {
        console.log(err);
      }
      console.log('The data from user table: \n', rows);
    });
  });
}

routes folder – user.js

const express = require('express');
const router = express.Router();
const userController = require('../controllers/userController');

// Routes
router.get('/', userController.view);
router.post('/', userController.find);
router.get('/adduser', userController.form);
router.post('/adduser', userController.create);
router.get('/edituser/:id', userController.edit);
router.post('/edituser/:id', userController.update);
router.get('/viewuser/:id', userController.viewall);
router.get('/:id',userController.delete);
  
module.exports = router;

SQL Schema & Dummy data

CREATE TABLE `usermanagement_tut`.`user` ( `id` INT NOT NULL AUTO_INCREMENT , `first_name` VARCHAR(45) NOT NULL , `last_name` VARCHAR(45) NOT NULL , `email` VARCHAR(45) NOT NULL , `phone` VARCHAR(45) NOT NULL , `comments` TEXT NOT NULL , `status` VARCHAR(10) NOT NULL DEFAULT 'active' , PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `user` 
(`id`, `first_name`,  `last_name`,    `email`,                 `phone`,         `comments`, `status`) VALUES
(NULL, 'Amanda',      'Nunes',        'anunes@ufc.com',        '012345 678910', '',          'active'),
(NULL, 'Alexander',   'Volkanovski',  'avolkanovski@ufc.com',  '012345 678910', '',          'active'),
(NULL, 'Khabib',      'Nurmagomedov', 'knurmagomedov@ufc.com', '012345 678910', '',          'active'),
(NULL, 'Kamaru',      'Usman',        'kusman@ufc.com',        '012345 678910', '',          'active'),
(NULL, 'Israel',      'Adesanya',     'iadesanya@ufc.com',     '012345 678910', '',          'active'),
(NULL, 'Henry',       'Cejudo',       'hcejudo@ufc.com',       '012345 678910', '',          'active'),
(NULL, 'Valentina',   'Shevchenko',   'vshevchenko@ufc.com',   '012345 678910', '',          'active'),
(NULL, 'Tyron',       'Woodley',      'twoodley@ufc.com',      '012345 678910', '',          'active'),
(NULL, 'Rose',        'Namajunas ',   'rnamajunas@ufc.com',    '012345 678910', '',          'active'),
(NULL, 'Tony',        'Ferguson ',    'tferguson@ufc.com',     '012345 678910', '',          'active'),
(NULL, 'Jorge',       'Masvidal ',    'jmasvidal@ufc.com',     '012345 678910', '',          'active'),
(NULL, 'Nate',        'Diaz ',        'ndiaz@ufc.com',         '012345 678910', '',          'active'),
(NULL, 'Conor',       'McGregor ',    'cmcGregor@ufc.com',     '012345 678910', '',          'active'),
(NULL, 'Cris',        'Cyborg ',      'ccyborg@ufc.com',       '012345 678910', '',          'active'),
(NULL, 'Tecia',       'Torres ',      'ttorres@ufc.com',       '012345 678910', '',          'active'),
(NULL, 'Ronda',       'Rousey ',      'rrousey@ufc.com',       '012345 678910', '',          'active'),
(NULL, 'Holly',       'Holm ',        'hholm@ufc.com',         '012345 678910', '',          'active'),
(NULL, 'Joanna',      'Jedrzejczyk ', 'jjedrzejczyk@ufc.com',  '012345 678910', '',          'active')

That is pretty much everything.

Credit

Download

Thank you for reading this article. Please consider subscribing to my YouTube Channel.

Leave a Reply

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