Simple User Management System – Nodejs, Express, MySQL & Handlebars
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 than I could on this post. Saying this, you can use this post as a reference.
The article has been updated. – Removed Connection Pool
Express-Handlebars code has been updated from 5.3.4 to 6.0.1 – 6.0.2. You can read the full Express-Handlebars Migration 6.0.1, 6.0.2 article here.
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.hbs 📂 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 // No longer required [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'); // updated to 6.0.X
//const bodyParser = require('body-parser'); // Remove
//const mysql = require('mysql'); // Remove
require('dotenv').config();
const app = express();
const port = process.env.PORT || 5000;
// Parsing middleware
// Parse application/x-www-form-urlencoded
// app.use(bodyParser.urlencoded({ extended: false }));
app.use(express.urlencoded({extended: true})); // New
// Parse application/json
// app.use(bodyParser.json());
app.use(express.json()); // New
// Static Files
app.use(express.static('public'));
// Templating engine
// app.engine('hbs', exphbs({ extname: '.hbs' })); // v5.3.4
// app.set('view engine', 'hbs'); // v5.3.4
// Update to 6.0.X
const handlebars = exphbs.create({ extname: '.hbs',});
app.engine('.hbs', handlebars.engine);
app.set('view engine', '.hbs');
// Connection Pool
// You don't need the connection here as we have it in userController
// let connection = mysql.createConnection({
// host: process.env.DB_HOST,
// user: process.env.DB_USER,
// password: process.env.DB_PASS,
// database: process.env.DB_NAME
// });
const routes = require('./server/routes/user');
app.use('/', routes);
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/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous"> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/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/[email protected]/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-form.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 protected]" 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 = passwordCreate your server folder and add the following two folders and files.
Controllers folder – userController.js
const mysql = require('mysql');
// Connection Pool
let connection = mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASS,
database: process.env.DB_NAME
});
// View Users
exports.view = (req, res) => {
// User the connection
connection.query('SELECT * FROM user WHERE status = "active"', (err, rows) => {
// When done with the connection, release it
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 = (req, res) => {
let searchTerm = req.body.search;
// User the connection
connection.query('SELECT * FROM user WHERE first_name LIKE ? OR last_name LIKE ?', ['%' + searchTerm + '%', '%' + searchTerm + '%'], (err, rows) => {
if (!err) {
res.render('home', { rows });
} else {
console.log(err);
}
console.log('The data from user table: \n', rows);
});
}
exports.form = (req, res) => {
res.render('add-user');
}
// Add new user
exports.create = (req, res) => {
const { first_name, last_name, email, phone, comments } = req.body;
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], (err, rows) => {
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 = (req, res) => {
// User the connection
connection.query('SELECT * FROM user WHERE id = ?', [req.params.id], (err, rows) => {
if (!err) {
res.render('edit-user', { rows });
} else {
console.log(err);
}
console.log('The data from user table: \n', rows);
});
}
// Update User
exports.update = (req, res) => {
const { first_name, last_name, email, phone, comments } = req.body;
// 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], (err, rows) => {
if (!err) {
// User the connection
connection.query('SELECT * FROM user WHERE id = ?', [req.params.id], (err, rows) => {
// When done with the connection, release it
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 = (req, res) => {
// Delete a record
// User the connection
// connection.query('DELETE FROM user WHERE id = ?', [req.params.id], (err, rows) => {
// if(!err) {
// res.redirect('/');
// } else {
// console.log(err);
// }
// console.log('The data from user table: \n', rows);
// });
// Hide a record
connection.query('UPDATE user SET status = ? WHERE id = ?', ['removed', req.params.id], (err, rows) => {
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 = (req, res) => {
// User the connection
connection.query('SELECT * FROM user WHERE id = ?', [req.params.id], (err, rows) => {
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', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Alexander', 'Volkanovski', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Khabib', 'Nurmagomedov', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Kamaru', 'Usman', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Israel', 'Adesanya', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Henry', 'Cejudo', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Valentina', 'Shevchenko', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Tyron', 'Woodley', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Rose', 'Namajunas ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Tony', 'Ferguson ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Jorge', 'Masvidal ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Nate', 'Diaz ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Conor', 'McGregor ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Cris', 'Cyborg ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Tecia', 'Torres ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Ronda', 'Rousey ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Holly', 'Holm ', '[email protected]', '012345 678910', '', 'active'), (NULL, 'Joanna', 'Jedrzejczyk ', '[email protected]', '012345 678910', '', 'active')
That is pretty much everything.
Frequently Asked Question – Issues
- My Delete Button Doesn’t work. – Cannot GET /users/1
Answer: Make sure that the “router.get” is at the bottom of the routes. (Explanation coming soon)
For further learning, you could try an ORM package called Sequelize. It features solid transaction support, relations, eager and lazy loading, read replication and more.
Credit
- How to store Node.js Deployment Settings configuration files
- How to redirect in Express while passing some data
Download
Thank you for reading this article. Please consider subscribing to my YouTube Channel.
More Resources:
Author
Hi! I’m Raddy. I am a web developer, often making YouTube Videos, Blog Posts and side projects. Enquiries: [email protected]
Hi Raddy,
Τhank you very much,
I regularly watch the videos you upload and I really state that I have learned many things and I have understood so much..
Having completed the video (user-management)and I would like to go a little further normally validate the form..
but I do not think I am strong enough to complete it,I already watch a relevant video of yours where you talk about format validation, but I do not know how to combine them.Would it be easy for you to give me some comments so that I could create it myself?
Best regards,
Nikos
Hey Nikos,
Glad to hear that you found the videos useful and thank you for watching.
For the front-end, you can use the Bootstrap input validation and for the backend, you can use the Express-validator.
To make the backend validation work you might have to import the Express-Validator on top of your controller where your form logic lives. I think that this is where people go wrong.
To get started with the input validation, you might have to just use the Express validator GitHub page. They have plenty of examples that you can use
I hope this helps
Raddy
hi raddy
i am getting an error which is as follows
Error: Cannot find module ‘hbs’
Require stack:
– C:\Users\KIIT\Desktop\UserManagement\node_modules\express\lib\view.js
– C:\Users\KIIT\Desktop\UserManagement\node_modules\express\lib\application.js
– C:\Users\KIIT\Desktop\UserManagement\node_modules\express\lib\express.js
– C:\Users\KIIT\Desktop\UserManagement\node_modules\express\index.js
– C:\Users\KIIT\Desktop\UserManagement\app.js
at Function.Module._resolveFilename (internal/modules/cjs/loader.js:902:15)
at Function.Module._load (internal/modules/cjs/loader.js:746:27)
at Module.require (internal/modules/cjs/loader.js:974:19)
at require (internal/modules/cjs/helpers.js:93:18)
at new View (C:\Users\KIIT\Desktop\UserManagement\node_modules\express\lib\view.js:81:14)
at Function.render (C:\Users\KIIT\Desktop\UserManagement\node_modules\express\lib\application.js:587:12)
at ServerResponse.render (C:\Users\KIIT\Desktop\UserManagement\node_modules\express\lib\response.js:1039:7)
at C:\Users\KIIT\Desktop\UserManagement\app.js:20:8
at Layer.handle [as handle_request] (C:\Users\KIIT\Desktop\UserManagement\node_modules\express\lib\router\layer.js:95:5)
at next (C:\Users\KIIT\Desktop\UserManagement\node_modules\express\lib\router\route.js:144:13)
Ah, the comment was stuck in spam for some reason. Did you npm install everything?
Would you update the pool thing ?
Yes, of course. I will update it in the next few hours.
Done 🙂
Hi, why is the connection pool removed? And will the project work without the connection pool ?
It was my mistake, I didn’t use it properly. Both examples will work absolutely fine, but I would remove the connection pool as you don’t need it there.
hi Randy, please i keep getting this error whenever i try query the database.
C:\Users\Yoo\Desktop\new Project\schmng\server\controllers\userController.js:22
throw err;
^
Error: Handshake inactivity timeout
at Handshake. (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\mysql\lib\protocol\Protocol.js:160:17)
at Handshake.emit (events.js:315:20)
at Handshake._onTimeout (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\mysql\lib\protocol\sequences\Sequence.js:124:8)
at Timer._onTimeout (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\mysql\lib\protocol\Timer.js:32:23)
at listOnTimeout (internal/timers.js:554:17)
at processTimers (internal/timers.js:497:7)
——————–
at Protocol._enqueue (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Protocol.handshake (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\mysql\lib\protocol\Protocol.js:51:23)
at PoolConnection.connect (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\mysql\lib\Connection.js:116:18)
at Pool.getConnection (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\mysql\lib\Pool.js:48:16)
at exports.view (C:\Users\Yoo\Desktop\new Project\schmng\server\controllers\userController.js:20:8)
at Layer.handle [as handle_request] (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\express\lib\router\layer.js:95:5)
at next (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\express\lib\router\route.js:137:13)
at Route.dispatch (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\express\lib\router\route.js:112:3)
at Layer.handle [as handle_request] (C:\Users\Yoo\Desktop\new Project\schmng\node_modules\express\lib\router\layer.js:95:5)
at C:\Users\Yoo\Desktop\new Project\schmng\node_modules\express\lib\router\index.js:281:22 {
code: ‘PROTOCOL_SEQUENCE_TIMEOUT’,
fatal: true,
timeout: 10000
It sounds like you are not able to connect to your database. Check if your database is working and then check the credentials
Hi there,
I absolutley love your channel! I stumpled upon it yesterday when I searched for node.js tutorials for beginners. So far I’ve done your news website and im currently following this tutorial absout the User Management System.
Everything works great except I have a strange problem. At 1.47.39 into the clip you add {{#each rows}} and {{/each}}. Everywhere else this works fine but when i add this here, the form dissapers from the website. If I remove the handlebars the form shows again. Because of this I cant poplulate my form with data from the DB. Any suggestions?
Since my only skills are in html/css im trying to learn something new and then it’s important to me to understand whats going wrong and how to fix it. Not just the solution 🙂
Thanks again man and big thumbs up for your content! Will definitely buy you a cup of coffee 😉
Hi Johan! I can only assume that your ‘rows’ is empty or you are not passing it. You can always reference the code here on the blog & github and if you can’t figure out feel free to share your code and hopefully I can tell you what went wrong. Thank you for being here and I am glad that you love the channel!
Thanks for answering! Can’t say exactly what went wrong but when i referenced your code here, everything works! The thing I change was the connection pool in userController.js.
Now I can continue with the rest of the project 🙂
Cheers!
Hi,
Great tutorial! Thanks for sharing. I am having only 1 issue: when clicking on the + add user button the user record is deleted, instead of a user form showing up.
Thanks in advance!
Hi, and thank you!
Make sure that your form action is set to ‘ action=”/adduser” ‘.
Then you need to make sure that you have your adduser route created which should be going to userController.create. I think that this should fix it.
If you have any problems, let me know.
Hi,
Yes, that fixed it. Thanks again!
Cheers!
Hi, you are the best! This was my first time working with Node / express JS and already from your work I have an idea and basic understanding of all what is happening. Make more tutorials.
Thanks, Mathew! I appreciate your comment. I am working on a new one right now. It’s gonna be pretty cool (hopefully) haha
Nice page,, just spotted a typo in the partial file name:
user-forms.hbs ==> user-form.hbs (with no ‘s’ I suppose)
Plus if you could add a memo on how to connect to new Mysql 8 server authentication method “caching_sha2_password”, that would be great.
Cheers
Hi Seyed, I’ve updated the typo, thank you for letting me know. I will have to have a look into the Mysql 8 server authentication method.
Sir while get the id for “edituser”, everything did the as you did you when i click on submit then in URL I’m not getting the user’s id
Sir while getting the id for “edituser”, everything did same as you did but when i click on submit, I’m not getting the user’s id in URL it should be like “localhost:5000/edituser/1” but I’m getting as “localhost:5000/edituser”
So when you click to edit the user everything works fine and you get ‘localhost:5000/edituser/1’, but when you submit it breaks?
Sir now it’s working!! that’s my small mistake. Actually in DATABASE the name I’ve given to id section was in capital letters “ID” and on edituser page in form action I had written it in small letters (action=”/edituser/{{this.id}}”). Some small mistakes make huge problems 😂😂, BTW thank you sir for making such great videos, they are really helping us ❣️❣️
😂 it happens haha. Glad that you solved it and thank you for your kind message
Well Said, Thanks for sharing this useful blog.
Thanks, Lokesh!
Why am i getting this error theres absolutley no reason why tis should be coming up.
it doesnt like
“`
router.get(”, (req, res, next) => {
res.render(‘homes’)
})
“`
saying router is not in any files.
TypeError: Cannot read properties of undefined (reading ‘get’)
at Object. (C:\Users\Gee\Desktop\projects\UserManagement\server\routes\user.js:5:7)
at Module._compile (node:internal/modules/cjs/loader:1101:14)
at Object.Module._extensions..js (node:internal/modules/cjs/loader:1153:10)
at Module.load (node:internal/modules/cjs/loader:981:32)
at Function.Module._load (node:internal/modules/cjs/loader:822:12)
at Module.require (node:internal/modules/cjs/loader:1005:19)
at require (node:internal/modules/cjs/helpers:102:18)
at Object. (C:\Users\Gee\Desktop\projects\UserManagement\app.js:48:16)
at Module._compile (node:internal/modules/cjs/loader:1101:14)
at Object.Module._extensions..js (node:internal/modules/cjs/loader:1153:10)
[nodemon] app crashed – waiting for file changes before starting…
Have you got ” const router = express.Router(); “. ?
For find a user by firstname or lastname, when i submit a form then it shows “Cannot POST /”
can you help me to resolve this?
Have you got:
router.post('/', userController.find);and the ‘userController’ code?yes i have
this code https://prnt.sc/nlPuyDhLqYUr in user.js and
this code https://prnt.sc/t14dEVupIHs_ in userController.js
One last thing, can you take a screenshot of your search user input / form and your app.js file, please?
If I can’t spot the problem from the screenshots, you are more than welcome to zip and email your code and I will have a look for you. We’ll figure it out
Hi Raddy,
A big thanks for this pings. I sent a email with my files.
my mail id starts with ‘csevprasath’ and subject as ‘Help me to resolve my Issue’. I hope this will help you to address my mistakes in my code.
once again Thanks a lot.
Sorted 👌
Hi Raddy. Great! its worked. thanks a lot buddy.
hello! i’ve recently started watching your tutorial on youtube and it’s really really super helpful. but i’m having problems with displaying the handlebars output on my port 5000 when i open it in my browser. i’ve followed your code in main.hbs and home.hbs but i still get the Cannot GET / error. can you please tell me how to fix this? :((
Hey Dazai, could you check if you have ‘ app.use(‘/’, routes); ‘ in your app.js?
I’ve had a few people use ‘app.get’ instead and that was the problem.
Let me know how it goes
good day raddy! i love your video tutorial, it’s really detailed and easy to follow. also appreciate this written documentation for easy reference!
i was just wondering, can you give me any ideas on how to actually implement this crud app in a webpage that’s an html file? because so far we’ve been running thus app in our port 5000…
your feedback is very much appreciated, bless up and stay safe!
Hey Bo,
I hope that you had a good weekend!
I think that there are two main ways you can use Node.Js to create a complete CRUD application. Since Node.Js executes JavaScript on the server – outside the browser, you do need to create a server and give it a port number.
The first and more common way is to use Node.JS as the middle application that communicates with the front end and your back end. Essentially you can create an API using Node.JS that can be used to communicate with a database and you can use that API in your front end application using HTML and JavaScript. This is where you essentially decouple your application and this can be extremely powerful.
You can decauple your application like so:
Front End (HTML,CSS, JS) — Middle Application (Node.Js) — Database (MySQL)
Angular – NodeJs – MySQL
React – NodeJs – MySQL
Vue – NodeJs – MySQL
NodeJs essentially lets you communicate with the database and you can do all sorts of other things like write files on the server.
The second option is the one I have used here. Where we use a templating engine such as handlebars, ejs and so on. Again you need to have a server that runs Node.Js.
The thing is that you can’t run Node.Js everywhere. You need a server that supports it. I guess it’s kind of like PHP. There are quite a few free options that you can use and some of them are great and make the process of installing your application super easy. Especially if you have your project uploaded on GitHub. For example, Heroku is one of them. The only issue with free platforms is that there are always some limitations, but that’s how they make their money. Cloud providers such as AWS, Google and Linode are always great options.
I know that a lot of the front end frameworks let you pre-render applications into static HTML, but unfortunately, you need NodeJs running to communicate with the server, databases and so on.
I hope that this answers your questions and if you have more questions feel free to comment. Thanks for the question!
Hello Raddy,
I was looking for how to start with NodeJS and I found your video on youtube.
I want to thank you so much for the content!
I have one technical question; if I have my web page (html, css and js) and I just installed nodejs, how can I link my javascript code to nodejs (for example to use MySQL or to use Files on server), I’m not talking about the code of connection to database, but how can I link my code to use nodejs in oreder to use mysql or mongodb (it’s confusing me a little bit, shall I copy my entire js code into app.js in node ?
Thank you in advance for any comment
Best regards
Hey Geek59,
There are two ways you can do that:
The first way is to split your application into two parts. Front-End and Backend. This is a fairly popular way of building fast, interactive applications. You might have seen or heard about MERN and MEAN. That’s when you have a Front-End and back-end decoupled. The idea is that they are separate applications that can talk to each other.
Mern is MongoDB, Express, Angular and NodeJs.
Mean is MonogDB, Express, React and NodeJs.
In your case you can do HTML, CSS, JS as a Front End and NodeJs, Express and MySQL as a Back-end.
Example
You can use NodeJs to create an API Endpoint. An API endpoint can be literally a JSON file that contains some data. Let’s say that you have a list of Music Artists that might come from the database or be hard coded.
myapi.com/get-music-artists -> returns artists in a JSON format.
With your Front End (HTML,CSS, JS) you can then make a “fetch” request to “myapi.com/get-music-artists” and use JavaScript to loop through them and display them on the page.
That’s the basics of it.
The second way is to do what I’ve done in this tutorial and create one project using NodeJs and a Templating Engine like Handlebars or EJS. If you are familiar with PHP, it’s kind of like that. You can do everything I mentioned above, but it’s all processed on a server and then rendered. This is more of a Server Side approach.
It’s kind of difficult to put it all in words while being concise. Maybe I can do an explanation video one day. I hope that this gives you an idea of what you can do. If you have any further questions, I will be more than happy to try and help.
Thank you for this great tutorial. I’ve got everything working except for delete
when I hit the delete button it responds with
Cannot GET /users/1
I’ve got the routes setup like this
router.delete(‘/users/:id’, userController.deleteUser);
userController has this
exports.deleteUser = (req, res) => {
//delete stuff
}
the html form renders like this
View
Edit
Delete
I added a users prefix to my routes but other than that all things are identical
Any ideas?
I recently helped someone with the same issue. It’s almost impossible to spot and it’s the way the routers get hit. Try moving the router.delete at the bottom of your other routers like in the example of this article. Let me know if this works for you
Hello, when i tried connecting to “localhost:5000” the console sent this:
Error: ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@’localhost’ (using password: YES)
how can i fix this?
Hello, have you got a password set for your MySQL database? If not leave the password as an empty string. That’s the most common problem that people have.
password: ''Thank You! the issue is resolved after i changed it
Awesome! I appreciate you letting me know
Hey Raddy! Love the video – thank you so much for putting the time into it!
I’m having an issue with rendering the edit-user form. When I wrap the form with {{#each rows}} the form just disappears. The moment I unwrap it, the form renders but without any data. Same thing with the view-user form.
Please help!
Hey Cameron, I am glad that you liked the video!
It sounds like “rows” is empty and you can’t do the for each looop. Double check if you are passing the “rows” data like this: res.render(‘edit-user’, { rows });
And also double-check your query. See if you can log out the data from the database first. The same goes for the view user. Let me know how it goes, I’ll be more than happy to help
Getting this error
Error: Failed to lookup view “home” in views directory “E:\DatabaseProject\views”
at Function.render (E:\DatabaseProject\node_modules\express\lib\application.js:597:17)
at ServerResponse.render (E:\DatabaseProject\node_modules\express\lib\response.js:1039:7)
at Query. (E:\DatabaseProject\server\controllers\userController.js:18:11)
at Query. (E:\DatabaseProject\node_modules\mysql\lib\Connection.js:526:10)
at Query._callback (E:\DatabaseProject\node_modules\mysql\lib\Connection.js:488:16)
at Query.Sequence.end (E:\DatabaseProject\node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24)
at Query._handleFinalResultPacket (E:\DatabaseProject\node_modules\mysql\lib\protocol\sequences\Query.js:149:8)
at Query.EofPacket (E:\DatabaseProject\node_modules\mysql\lib\protocol\sequences\Query.js:133:8)
at Protocol._parsePacket (E:\DatabaseProject\node_modules\mysql\lib\protocol\Protocol.js:291:23)
at Parser._parsePacket (E:\DatabaseProject\node_modules\mysql\lib\protocol\Parser.js:433:10)
In the “view” folder you need to create the file “home.hbs”
Please help getting this error
RangeError: Maximum call stack size exceeded
at Object.eval [as main] (eval at createFunctionContext (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\compiler\javascript-compiler.js:262:23), :11:159)
at main (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\runtime.js:208:32)
at ret (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\runtime.js:212:12)
at ret (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\compiler\compiler.js:519:21)
at Object.invokePartial (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\runtime.js:334:12)
at Object.invokePartialWrapper [as invokePartial] (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\runtime.js:84:39)
at Object.eval [as main] (eval at createFunctionContext (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\compiler\javascript-compiler.js:262:23), :13:28)
at main (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\runtime.js:208:32)
at ret (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\runtime.js:212:12)
at ret (E:\DatabaseProject\node_modules\handlebars\dist\cjs\handlebars\compiler\compiler.js:519:21)
There might be a recursive loop somewhere in your code. A function that calls itself again and again until the stack is full. Double-check your code and use the GitHub for reference
Hi Raddy,
Thanks for the tutorial, much appreciated.
The application is working fine, i am just getting this error :
Can you guide me how to resolve this.
Thanks again
——————-
Error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect DOUBLE value: ‘favicon.ico’
at Query.Sequence._packetToError (/root/UserManagementApp/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/root/UserManagementApp/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
at Protocol._parsePacket (/root/UserManagementApp/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/root/UserManagementApp/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/root/UserManagementApp/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/root/UserManagementApp/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket. (/root/UserManagementApp/node_modules/mysql/lib/Connection.js:88:28)
at Socket. (/root/UserManagementApp/node_modules/mysql/lib/Connection.js:526:10)
at Socket.emit (events.js:315:20)
at addChunk (internal/streams/readable.js:309:12)
——————–
at Protocol._enqueue (/root/UserManagementApp/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/root/UserManagementApp/node_modules/mysql/lib/Connection.js:198:25)
at exports.delete (/root/UserManagementApp/server/controllers/userController.js:119:14)
at Layer.handle [as handle_request] (/root/UserManagementApp/node_modules/express/lib/router/layer.js:95:5)
at next (/root/UserManagementApp/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (/root/UserManagementApp/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (/root/UserManagementApp/node_modules/express/lib/router/layer.js:95:5)
at /root/UserManagementApp/node_modules/express/lib/router/index.js:281:22
at param (/root/UserManagementApp/node_modules/express/lib/router/index.js:360:14)
at param (/root/UserManagementApp/node_modules/express/lib/router/index.js:371:14) {
code: ‘ER_TRUNCATED_WRONG_VALUE’,
errno: 1292,
sqlMessage: “Truncated incorrect DOUBLE value: ‘favicon.ico'”,
sqlState: ‘22007’,
index: 0,
sql: “UPDATE user SET status = ‘removed’ WHERE id = ‘favicon.ico'”
}
It looks like you are comparing a string “favicon.ico” to an integer– “id”. That’s where the error comes from
Hi Raddy, I keep getting this error too… how do I go about approaching it? How do I stop comparing the string to the integer?
Could you please paste the code where you get the error from so I can have a look?
Thanks for replying! Here’s my error:
Error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect DOUBLE value: ”favicon.ico”
at Sequence._packetToError (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
at Protocol._parsePacket (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket. (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/Connection.js:88:28)
at Socket. (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/Connection.js:526:10)
at Socket.emit (node:events:518:28)
at addChunk (node:internal/streams/readable:559:12)
——————–
at Protocol._enqueue (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/Applications/AMPPS/www/user-management-system/node_modules/mysql/lib/Connection.js:198:25)
at exports.delete (/Applications/AMPPS/www/user-management-system/server/controllers/userController.js:138:14)
at Layer.handle [as handle_request] (/Applications/AMPPS/www/user-management-system/node_modules/express/lib/router/layer.js:95:5)
at next (/Applications/AMPPS/www/user-management-system/node_modules/express/lib/router/route.js:144:13)
at Route.dispatch (/Applications/AMPPS/www/user-management-system/node_modules/express/lib/router/route.js:114:3)
at Layer.handle [as handle_request] (/Applications/AMPPS/www/user-management-system/node_modules/express/lib/router/layer.js:95:5)
at /Applications/AMPPS/www/user-management-system/node_modules/express/lib/router/index.js:284:15
at param (/Applications/AMPPS/www/user-management-system/node_modules/express/lib/router/index.js:365:14)
at param (/Applications/AMPPS/www/user-management-system/node_modules/express/lib/router/index.js:376:14) {
code: ‘ER_TRUNCATED_WRONG_VALUE’,
errno: 1292,
sqlMessage: “Truncated incorrect DOUBLE value: ”favicon.ico””,
sqlState: ‘22007’,
index: 0,
sql: `DELETE FROM photo_uploads WHERE id=”‘favicon.ico'”`
}
The data from photo_uploads table:
undefined
The data from photo_uploads table:
undefined
[nodemon] restarting due to changes…
[nodemon] starting `node app.js`
Listening on port 3000
Any chance I can see that bit of code please? Feel free to email me and I will help
i really need a help not able to start my localhost:5000 its not working , My database phpmyadmin is also connected at port 3306 ,
In starting phpmyadmin was not getting connected as the 3306 , port was not free so i changed the configuration in sql workbench
Do you get any errors? What happens?
Hi Mr. Raddy.
Thank you so much for this great and informative work.
I however, managed to get the app well in running mode but at every load of the web app
page I get the following error although nothing seems to break in normal operation:
Error: Truncated incorrect DOUBLE value: ‘favicon.ico’
at Packet.asError (/home/eliud/Documents/Programming/Javascript/Node-Js/Projects/management_system/node_modules/mysql2/lib/packets/packet.js:728:17)
at Query.execute (/home/eliud/Documents/Programming/Javascript/Node-Js/Projects/management_system/node_modules/mysql2/lib/commands/command.js:29:26)
at Connection.handlePacket (/home/eliud/Documents/Programming/Javascript/Node-Js/Projects/management_system/node_modules/mysql2/lib/connection.js:488:32)
at PacketParser.onPacket (/home/eliud/Documents/Programming/Javascript/Node-Js/Projects/management_system/node_modules/mysql2/lib/connection.js:94:12)
at PacketParser.executeStart (/home/eliud/Documents/Programming/Javascript/Node-Js/Projects/management_system/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket. (/home/eliud/Documents/Programming/Javascript/Node-Js/Projects/management_system/node_modules/mysql2/lib/connection.js:101:25)
at Socket.emit (events.js:314:20)
at addChunk (_stream_readable.js:297:12)
at readableAddChunk (_stream_readable.js:272:9)
at Socket.Readable.push (_stream_readable.js:213:10) {
code: ‘ER_TRUNCATED_WRONG_VALUE’,
errno: 1292,
sqlState: ‘22007’,
sqlMessage: “Truncated incorrect DOUBLE value: ‘favicon.ico'”,
sql: “UPDATE ms_users SET status = ‘removed’ WHERE id = ‘favicon.ico'”
}
Your assistance please I’ll appreciate.
Thank you!
Why is the “id” a “favicon.ico”? Maybe the “id” needs to be a number?
Thank you for your response.
Otherwise, I can’t clearly tell why the id is being assigned to “favicon.ico” because the actual section of the user controller from where the error issue is being pointed to is under this function script below:
// user delete control handler
exports.userDelete = (req, res) => {
let target_id = req.params.id;
connection.query(‘UPDATE ms_users SET status = ? WHERE id = ?’, [‘removed’, target_id], (err, user_data) => {
if (!err) {
let removedUser = encodeURIComponent(‘User successeflly removed.’);
res.redirect(‘/?removed=’ + removedUser);
} else {
console.log(err);
}
});
}
Strange as somebody else had the same issue. Double-check the “target_id” variable. That’s the only thing that can output favicon.ico there…
Yeah, I had the same issue previously when I was directly using the id value (req.params.id) within the SQL query hence decided to do this with initial hope of trying to typecast its value so that we perform the comparison(under WHERE id = ?).
However, on some research under this, it’s hinted that the variable being compared to the id being in single quotes (WHERE id = ‘id_value’).
Thank you!
Thank you for mentioning this!
code: ‘ER_ACCESS_DENIED_ERROR’,
errno: 1045,
sqlMessage: “Access denied for user ”@’localhost’ (using password: NO)”,
sqlState: ‘28000’,
fatal: true
}
can u plz help me to solve this error
You have to set up a new password for the user. If you don’t have a password set it wont work. What you can try to do is to remove the password line from the connection and just try to login with username only.
`password: process.env.DB_PASS,` This you can try to remove. Best to create a password 🙂
sqlMessage: “Access denied for user ”@’localhost’ (using password: NO)”
can u plz help me to solve this error
i keep getting this error. Error: Failed to lookup view “home” in views directory “C:\Users\HP\user_management_system\views”
at Function.render (C:\Users\HP\user_management_system\node_modules\express\lib\application.js:597:17)
at ServerResponse.render (C:\Users\HP\user_management_system\node_modules\express\lib\response.js:1039:7)
at Query.onResult (C:\Users\HP\user_management_system\server\controllers\userController.js:22:17)
at C:\Users\HP\user_management_system\node_modules\mysql2\lib\commands\query.js:86:16
at process.processTicksAndRejections (node:internal/process/task_queues:77:11)
It says that it can’t find the home in the views directory. Look for typos, it could be something small
Hello Raddy, thanks for the tutorial i really learned alot from it, i want to ask if i can connect with a login form not using php but ordinary html and css, so it can be use as an admin panel, if yes can you direct me on how it should go, i will be waiting for you reply
I did a video series for TheNetNinja which shows how to create a login form and a protected dashboard. You’ll just have to skip to the part that you need: https://www.youtube.com/watch?v=-foo92lFIto
Error: ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@’localhost’ (using password: YES)
1) localhost/phpmyadmin
User accounts > root (localhost) > Change password
2) Find config.inc.php in XAMPP …/phpmyadmin folder and change
$cfg[‘Servers’][$i][‘auth_type’] = ‘config’;
TO
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;