Node JS Login and Registration System with MySQL Database

In this post, you will learn how to create a simple cookie-session based Login and Registration system with Node.js + MySQL database.


Steps to create this Node JS Login and Registration System

1. MySQL Database setup.

In the first, we will set up the MySQL DB for this login app. So, start the MySQL server and create a new database called nodejs_login. Then inside this database, we need to create a table called users.

Use the following SQL Code to create the users table and the structure of this table.

CREATE TABLE users (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  email varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY email (email)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2. Create the app folder and Install the Node Packages.

After completing the Database configuration, create the application folder on your desktop or elsewhere and name it whatever you wish (I named it nodejs-login-registration).

Now inside the app folder initialize the NPM and install the following Node packages.

  • express Framework
  • ejs Template Engine
  • express-session
  • express-validator for form validation
  • bcryptjs
  • mysql2
npm i express ejs express-session express-validator bcryptjs mysql2
{
  "name": "nodejs-login-registration",
  "version": "1.0.0",
  "description": "Node JS Login And Registration System",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "w3jar.com (chandan)",
  "license": "ISC",
  "dependencies": {
    "bcryptjs": "^2.4.3",
    "ejs": "^3.1.6",
    "express": "^4.17.1",
    "express-session": "^1.17.2",
    "express-validator": "^6.11.1",
    "mysql2": "^2.2.5"
  }
}

3. Creation of Node js login application.

In this section we will create the files and folders of the Node js login app.

If your curious to know what files and folders you need to create to build this application, then see the following folder structure image.

node js loginn registration folder structure

3.1. dbConnection.js

First, we will create the dbConnection.js for making the database connection. So, in the root of the app folder, create a new folder called utils, and inside the utils folder you have to create the dbConnection.js.

const mysql = require('mysql2');

const dbConnection = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs_login'
});

module.exports = dbConnection.promise();

Now we will create the views. So, again in the root of the app folder create a new folder called views. All the views we create will be inside this folder.

3.2. header.ejs

<!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><%= (typeof(title) !== 'undefined') ? title : 'Node JS Login and Registration System - W3jar' %></title>
    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Ubuntu:[email protected];700&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="./style.css">
</head>
<body>

3.3. footer.ejs

</body>
</html>

3.4. login.ejs

<%- include('header',{
    title:'Login'
}); -%>

<div class="container">
    <h1>Login</h1>
    <form action="" method="POST">
        <label for="user_email">Email</label>
        <input type="email" class="input" name="_email" id="user_email" placeholder="Enter your email">
        <label for="user_pass">Password</label>
        <input type="password" class="input" name="_password" id="user_pass" placeholder="Enter new password">
        <% if(typeof error !== 'undefined'){ %>
        <div class="err-msg"><%= error %></div><% } %>
        <input type="submit" value="Login">
        <div class="link"><a href="./signup">Sign Up</a></div>
    </form>
</div>

<%- include('footer'); -%>

3.5. register.ejs

<%- include('header',{
    title:'Signup'
}); -%>

<div class="container">
    <h1>Sign Up</h1>
    <form action="" method="POST">
        <label for="user_name">Name</label>
        <input type="text" class="input" name="_name" id="user_name" placeholder="Enter your name">
        <label for="user_email">Email</label>
        <input type="email" class="input" name="_email" id="user_email" placeholder="Enter your email">
        <label for="user_pass">Password</label>
        <input type="password" class="input" name="_password" id="user_pass" placeholder="Enter new password">
        <% if(typeof msg != 'undefined'){ %>
        <div class="success-msg"><%= msg %></div><% }
        if(typeof error !== 'undefined'){ %>
        <div class="err-msg"><%= error %></div><% } %>
        <input type="submit" value="Sign Up">
        <div class="link"><a href="./login">Login</a></div>
    </form>
</div>

<%- include('footer'); -%>

3.6. home.ejs

<%- include('header'); -%>
<div class="container">
    <div class="profile">
        <div class="img">👦</div>
        <h2><%= user.name %></h2>
        <span><%= user.email %></span>
        <a href="/logout">Log Out</a>
    </div>
</div>
<%- include('footer'); -%>

After creating the views, now we need to set up the routes. So, at the root of the app folder create a new js file called routes.js. Inside this js file, we will define all the routes.

3.6. routes.js

const router = require("express").Router();
const { body } = require("express-validator");

const {
    homePage,
    register,
    registerPage,
    login,
    loginPage,
} = require("./controllers/userController");

const ifNotLoggedin = (req, res, next) => {
    if(!req.session.userID){
        return res.redirect('/login');
    }
    next();
}

const ifLoggedin = (req,res,next) => {
    if(req.session.userID){
        return res.redirect('/');
    }
    next();
}

router.get('/', ifNotLoggedin, homePage);

router.get("/login", ifLoggedin, loginPage);
router.post("/login",
ifLoggedin,
    [
        body("_email", "Invalid email address")
            .notEmpty()
            .escape()
            .trim()
            .isEmail(),
        body("_password", "The Password must be of minimum 4 characters length")
            .notEmpty()
            .trim()
            .isLength({ min: 4 }),
    ],
    login
);

router.get("/signup", ifLoggedin, registerPage);
router.post(
    "/signup",
    ifLoggedin,
    [
        body("_name", "The name must be of minimum 3 characters length")
            .notEmpty()
            .escape()
            .trim()
            .isLength({ min: 3 }),
        body("_email", "Invalid email address")
            .notEmpty()
            .escape()
            .trim()
            .isEmail(),
        body("_password", "The Password must be of minimum 4 characters length")
            .notEmpty()
            .trim()
            .isLength({ min: 4 }),
    ],
    register
);

router.get('/logout', (req, res, next) => {
    req.session.destroy((err) => {
        next(err);
    });
    res.redirect('/login');
});

module.exports = router;

3.7. userController.js

Now, we need to create the controllers for the routes. So, at the root of the app folder create the controllers folder, and inside this folder, we will create the userController.js.

This user controller file will contain all the user-related controllers that we defined in the routes.js.

const { validationResult } = require("express-validator");
const bcrypt = require('bcryptjs');
const dbConnection = require("../utils/dbConnection");

// Home Page
exports.homePage = async (req, res, next) => {
    const [row] = await dbConnection.execute("SELECT * FROM `users` WHERE `id`=?", [req.session.userID]);

    if (row.length !== 1) {
        return res.redirect('/logout');
    }

    res.render('home', {
        user: row[0]
    });
}

// Register Page
exports.registerPage = (req, res, next) => {
    res.render("register");
};

// User Registration
exports.register = async (req, res, next) => {
    const errors = validationResult(req);
    const { body } = req;

    if (!errors.isEmpty()) {
        return res.render('register', {
            error: errors.array()[0].msg
        });
    }

    try {

        const [row] = await dbConnection.execute(
            "SELECT * FROM `users` WHERE `email`=?",
            [body._email]
        );

        if (row.length >= 1) {
            return res.render('register', {
                error: 'This email already in use.'
            });
        }

        const hashPass = await bcrypt.hash(body._password, 12);

        const [rows] = await dbConnection.execute(
            "INSERT INTO `users`(`name`,`email`,`password`) VALUES(?,?,?)",
            [body._name, body._email, hashPass]
        );

        if (rows.affectedRows !== 1) {
            return res.render('register', {
                error: 'Your registration has failed.'
            });
        }
        
        res.render("register", {
            msg: 'You have successfully registered.'
        });

    } catch (e) {
        next(e);
    }
};

// Login Page
exports.loginPage = (req, res, next) => {
    res.render("login");
};

// Login User
exports.login = async (req, res, next) => {

    const errors = validationResult(req);
    const { body } = req;

    if (!errors.isEmpty()) {
        return res.render('login', {
            error: errors.array()[0].msg
        });
    }

    try {

        const [row] = await dbConnection.execute('SELECT * FROM `users` WHERE `email`=?', [body._email]);

        if (row.length != 1) {
            return res.render('login', {
                error: 'Invalid email address.'
            });
        }

        const checkPass = await bcrypt.compare(body._password, row[0].password);

        if (checkPass === true) {
            req.session.userID = row[0].id;
            return res.redirect('/');
        }

        res.render('login', {
            error: 'Invalid Password.'
        });


    }
    catch (e) {
        next(e);
    }

}

3.8. style.css

For beautifying our node js login app we will add a style.css file, and this file should be inside the public folder.

Create the public folder at the root and inside the folder create style.css.

*,
*::before,
*::after {
    box-sizing: border-box;
}

html {
    -webkit-text-size-adjust: 100%;
    -webkit-tap-highlight-color: rgba(0, 0, 0, 0);
    font-size: 16px;
}

body {
    background-color: #f7f7f7;
    font-family: "Ubuntu", sans-serif;
    margin: 0;
    padding: 0;
    color: #222222;
    overflow-x: hidden;
    overflow-wrap: break-word;
    -moz-osx-font-smoothing: grayscale;
    -webkit-font-smoothing: antialiased;
    padding: 50px;
}

.container {
    background-color: white;
    max-width: 450px;
    margin: 0 auto;
    padding: 40px;
    box-shadow: 0 1rem 3rem rgba(0, 0, 0, 0.175);
    border-radius: 3px;
}

.container h1 {
    margin: 0 0 40px 0;
    text-align: center;
}

input,
button {
    font-family: "Ubuntu", sans-serif;
    outline: none;
    font-size: 1rem;
}

.input {
    padding: 10px;
    width: 100%;
    margin-bottom: 10px;
    border: 1px solid #bbbbbb;
    border-radius: 3px;
}

.input:hover {
    border-color: #999999;
}

.input:focus {
    border-color: #0d6efd;
}

[type="submit"] {
    background: #0d6efd;
    color: white;
    border: 1px solid rgba(0, 0, 0, 0.175);
    border-radius: 3px;
    padding: 12px 0;
    cursor: pointer;
    box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
    margin-top: 5px;
    font-weight: bold;
    width: 100%;
}

[type="submit"]:hover {
    box-shadow: 0 0.5rem 1rem rgba(0, 0, 0, 0.15);
}

label {
    font-weight: bold;
}

.link {
    margin-top: 10px;
    text-align: center;
}

.link a {
    color: #0d6efd;
}

.success-msg,
.err-msg {
    color: #dc3545;
    border: 1px solid #dc3545;
    padding: 10px;
    border-radius: 3px;
}

.success-msg {
    color: #ffffff;
    background-color: #20c997;
    border-color: rgba(0, 0, 0, 0.1);
}

.profile {
    text-align: center;
}

.profile .img {
    font-size: 50px;
}

.profile h2 {
    margin-bottom: 3px;
    text-transform: capitalize;
}

.profile span {
    display: block;
    margin-bottom: 20px;
    color: #999999;
}

.profile a {
    display: inline-block;
    padding: 10px 20px;
    text-decoration: none;
    border: 1px solid #dc3545;
    color: #dc3545;
    border-radius: 3px;
}

.profile a:hover {
    border-color: rgba(0, 0, 0, 0.1);
    background-color: #dc3545;
    color: #ffffff;
}

3.9. index.js

At the end we have to code the the main file the index.js file.

const express = require('express');
const session = require('express-session');
const path = require('path');
const routes = require('./routes');
const app = express();

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

app.use(express.urlencoded({ extended: false }));
app.use(session({
    name: 'session',
    secret: 'my_secret',
    resave: false,
    saveUninitialized: true,
    cookie: {
        maxAge: 3600 * 1000, // 1hr
    }
}));

app.use(express.static(path.join(__dirname, 'public')));
app.use(routes);

app.use((err, req, res, next) => {
    // console.log(err);
    return res.send('Internal Server Error');
});

app.listen(3000, () => console.log('Server is runngin on port 3000'));

The End

Now start your node js login registration app and test it.

Node js login and registration system with mysql db

26 Comments

  1. The codes are not displaying any more on you site, can i get the source code it will be really helpful for me

  2. Access denied for user ‘root’@’localhost’ (using password: NO)

    how to solve this one when register

    • Hey Suyash Tanwar,
      you have to enter your root user password, it should not be empty.

      const mysql = require('mysql2');
      const dbConnection = mysql.createPool({
          host     : 'localhost', // MYSQL HOST NAME
          user     : 'root', // MYSQL USERNAME
          password : 'YOUR_ROOT_USER_PASSWORD', // MYSQL PASSWORD
          database : 'nodejs_login' // MYSQL DB NAME
      }).promise();
      module.exports = dbConnection;
  3. hi sir,

    It just says server is running, I am unable to see any more results. I updated my password as well and checked. But still showing the same result.

  4. i am getting :”TypeError: Bind parameters must not contain undefined. To pass SQL NULL specify JS null” error
    can u please help me in providing the solution?

    • I don’t understand your error properly. But, when I install the application in my machine from github, I’ve faced some error with the bcrypt package, so I replaced the bcrypt package to bcryptjs, as well as I updated my article.

      Here is the updated repository – Download code from GitHub

  5. Hello, I am getting error connection.execute().then is not a function. Please guide me and also I want to intergrate node cht app with this login and registration..

    • If you imported the database.js as the same name I imported, then –

      it is not – connection.execute
      it is – dbConnection.execute

  6. Create Node JS API to register a user with username/pwd, name, email , phone using html for front-end
    and mysql

  7. hello sir can u please help with this when i try to run i get this error
    [nodemon] starting `node app index.js`
    internal/modules/cjs/loader.js:892
    throw err;
    ^
    Error: Cannot find module ‘./controllers/userController’
    Require stack:
    – E:\login\routes.js
    – E:\login\index.js

    • I know it’s quite difficult to understand others’ projects just by reading the codes, 🙏 but sorry to say that, currently I don’t have any video.

    • In the index.js file, uncomment the console.log(err), now you can see what is the exact error.

  8. sir i created a new view after the home page even if i render it am not able to view that page in browser it says “cannot GET” what logic has to be added so that instead of logout i should able to move another page which i created

Leave a Reply