Node js User Authentication using MySQL and Express JS

Node js is providing an event-driven I/O model that makes node js application lightweight and efficient. This node js tutorial helps to create a user authentication system using MySQL and express js.

I am creating a simple web application using nodejs, MySQL, express and ejs. I am using ejs aka Embedded JavaScript module for template engine in node js example.

I have already covered Simple Hello Nodejs tutorial, Add, Edit and Delete Record using MySQL and nodejs and Simple Rest API example using nodejs and MySQL. I am not covering some basics of node js like how to create nodejs?, what is package.json?, How to install npm. I am assuming you guys aware of these things, now let’s go to new things of nodejs.

We will cover following functionality into this node js tutorial:

• How to create database and table into MySQL server
• How to add routing with node js application
• How to handle session in node js application
• ejs template engine integration with node js application
• How to use partial file in node js application
• Notification/Message integration in node js application

Checkout Other NodeJS tutorials,

• User Authentication using JWT (JSON Web Token) with Node.js
• Node js User Authentication using MySQL and Express JS
• Nodejs Example to Upload and Store Image into MySQL Using Express
• NodeJS tutorial to Add, Edit and Delete Record Using Mysql

Simple Example of Node js Authentication with MySQL

We will authenticate user using the MySQL database. We will create GET and POST type HTTP request to show login and post login information to the server.I am using Bootstrap CSS to create beautiful login and registration form.

I am using following files and folder

I am creating a folder ‘nodejs_auth_example’.This is our node js project name.

views folder: This folder will contain all ejs template files.
public folder: This folder will contain all external css and js files.
routes folder: This folder will contain all controller file.
app.php: This file will use to create nodejs application server and routes url.
node_modules folder: This folder will contain all node.js packages.

Create Database and Table Structure for User Authentication

I am using MySQL server for user authentication. You need to create ‘test’ name database into the mysql server. You can create a database using GUI or from the command line whatever you like, let’s create ‘users’ table into ‘test’ MySQL database using the below script.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `first_name` text NOT NULL,
  `last_name` text NOT NULL,
  `mob_no` int(11) NOT NULL,
  `user_name` varchar(20) NOT NULL,
  `password` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

I am storing password as a string you can change into MD5, I am creating this tutorial for laymen nodejs learners so haven’t used md5 or any cipher module.

Create Package.json file

We will create package.json file for this node.js application into the root path of node js application. This file defines what libraries or modules will be used in this node js project. You need to add the below code into package.json file.

{
  "name": "node-restapi",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": {
    "name": "Adam",
    "email": "[email protected]",
    "url": "https://www.js-tutorials.com/"
  },
  "license": "MIT",
  "dependencies": {
    "body-parser": "^1.16.1",
    "ejs": "^2.5.6",
    "express": "^4.14.1",
    "express-session": "^1.15.1",
    "mysql": "^2.13.0",
    "req-flash": "0.0.3"
  }
}

let’s install node js module using npm command, now open the cmd window and go to the path of your node application, like C:/test/nodejs_auth_example and run the below example.

c:/test/nodejs_auth_example> npm install

Above command will install all dependency node js modules into node_modules folder.

Node js Session Management using express-session

We will use express-session node js module to manage sessions into the application. This is a Simple session middleware for the Express application. You need to instantiate session module into app.js file like the below,

var session = require('express-session');
app.use(session({
  secret: 'keyboard cat',
  resave: false,
  saveUninitialized: true,
  cookie: { maxAge: 60000 }
}))

How to Set Value into Session in Node JS Application

To store session data into variable, we simply use the request property req.session, which stores serialized data into JSON, You can store single object or nested objects.

var sess = req.session;  //initialize session variable
req.session.userId = results[0].id; //set user id
req.session.user = results[0];//set user name

How to Get Session Value in Node js Application

We again use req.session property to get session value from session object, below code used to get userId from session,

var userId = req.session.userId;

How to Destroy/Unset Session Value in Node js Application

You can delete a session using destroy method, which unsets the session value.

req.session.destroy(function(err) {
      //cal back method
   })

How to show message/notification using req-flash in node js application

We will use req-flash node module to manage the flash message. You can use this module to send validation messages or success messages or any other notification messages from the controller method to view. You don’t need any configuration into app.js file.

How to set message into node js application

We will create a message variable and assign notification text into it.

var message = '';
message = 'Wrong Credentials.';
res.render('index.ejs',{message: message});

Here we are sending message text to index.ejs view file.

How to Show Message/Notification in node js Application

I am using ejs template module so I will show messages like the below,

<% if (message.length > 0) { %>

<div class="alert alert-danger col-sm-12"><%= message %></div>

<% } %>

How to User Partial file in Node js Application

A partial view is very important in project template layout, We will define the template in partial file(header, footer and sidebar) and include it into the main layout file when needed.

header.ejs partial file

we will create a new header.ejs file into the views folder and put the below code into this file.

<meta charset="UTF-8">
    <title>Sample Site</title>
      	<link rel="stylesheet" href="/stylesheets/style.css">
     	<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css">
   <script src="https://code.jquery.com/jquery-2.2.0.min.js"></script>



<div class="container">
    <!-- Static navbar -->
    <nav class="navbar navbar-default">

<div class="container-fluid">

<div id="navbar" class="navbar-collapse collapse">

<ul class="nav navbar-nav">

 	<li class="active"><a href="/home/dashboard">Home</a></li>

</ul>


<ul class="nav navbar-nav navbar-right">

 	<li><a href="/home/logout">Logout</a></li>

</ul>

</div>
<!--/.nav-collapse -->
</div>
<!--/.container-fluid -->
    </nav>
</div>

footer.ejs partial file

We will create a new footer.ejs file into the views folder and put the below code into this file.

footer

Create Node JS Application file

We will create the main entry file of the nodejs application like app.js file, This file will use to instantiate all modules and create connection with MySQL. You need to add below code into /nodejs_auth_example/app.js file.

/**
* Module dependencies.
*/
var express = require('express')
  , routes = require('./routes')
  , user = require('./routes/user')
  , http = require('http')
  , path = require('path');
//var methodOverride = require('method-override');
var app = express();
var mysql      = require('mysql');
var bodyParser=require("body-parser");
var connection = mysql.createConnection({
              host     : 'localhost',
              user     : 'root',
              password : '',
              database : 'test'
            });
 
connection.connect();
 
global.db = connection;
 
// all environments
app.set('port', process.env.PORT || 8080);
app.set('views', __dirname + '/views');
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname, 'public')));
 
//Middleware
app.listen(8080)

As you can see, I am running node server on 8080 port, you can change it as per your need. You can get more info about nodejs and mysql from my previous article.

Now run node app.js from the command line that will show a blank line without any error that means your server is connected with mysql. You can check on the browser using https://localhost:8080 url.

Login and Registration page in Nodejs application

We will add a login and registration page into this nodejs application. We will create user.js file for control all actions of the login and registration process. We will create an ejs template file for sign-in and signup view into the views folder.

Step 1: Create a new routes/user.js file and store in the routes folder.
Step 2: Create a get route to load the HTML template file in app.js file.

app.get('/', routes.index);//call for main index page
app.get('/login', routes.index);//call for login page
app.get('/signup', user.signup);//call for signup page

Step 3: Create a new views/index.ejs file into the views folder and put the below code.

<meta charset="UTF-8">
<title>Sample Site</title>
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css">

<div class="container">

    <div id="loginbox" style="margin-top:50px;" class="mainbox col-md-6 col-md-offset-3 col-sm-8 col-sm-offset-2">

        <div class="panel panel-info">

            <div class="panel-heading">

                <div class="panel-title">Sign In</div>

                <!--
<div style="float:right; font-size: 80%; position: relative; top:-10px"><a href="#">Forgot password?</a></div>
-->
            </div>

            <div style="padding-top:30px" class="panel-body">
                &lt;% if (message.length &gt; 0) { %&gt;

                <div class="alert alert-danger col-sm-12">&lt;%= message %&gt;</div>

                &lt;% } %&gt;

                <form id="loginform" class="form-horizontal" role="form" method="post" action="/login">

                    <div style="margin-bottom: 25px" class="input-group">
                        <span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
                        <input id="login-username" type="text" class="form-control" name="user_name" value="" placeholder="username">
                    </div>

                    <div style="margin-bottom: 25px" class="input-group">
                        <span class="input-group-addon"><i class="glyphicon glyphicon-lock"></i></span>
                        <input id="login-password" type="password" class="form-control" name="password" placeholder="password">
                    </div>

                    <div class="input-group">
                        <!--
<div class="checkbox">
                                        <label>
                                          <input id="login-remember" type="checkbox" name="remember" value="1"> Remember me
                                        </label>
</div>
-->
                    </div>

                    <div style="margin-top:10px" class="form-group">
                        <!-- Button -->

                        <div class="col-sm-12 controls">
                            <button id="btn-login" type="submit" class="btn btn-success">Login  </button>

                        </div>

                    </div>

                    <div class="form-group">

                        <div class="col-md-12 control">

                            <div style="border-top: 1px solid#888; padding-top:15px; font-size:85%">
                                Don't have an account!
                                <a href="/signup">
                                            Sign Up Here
                                        </a>
                            </div>

                        </div>

                    </div>

                </form>

            </div>

        </div>

    </div>

</div>

Step 4: Create a new views/signup.ejs file into views folder and put below code.

<meta charset="UTF-8">
<title>Sample Site</title>
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-2.2.0.min.js"></script>

<div class="container col-sm-12" id="mainform">

    <div id="signupbox" style=" margin-top:50px" class="mainbox col-md-6 col-md-offset-3 col-sm-8 col-sm-offset-2">

        <div class="panel panel-info">

            <div class="panel-heading">

                <div class="panel-title">Sign Up</div>

                <div style="float:right; font-size: 85%; position: relative; top:-10px"><a id="signinlink" href="/">Sign In</a></div>

            </div>

            <div class="panel-body">
                <form class="form-horizontal" role="form" method="post" action="/signup" onsubmit="return checkblank(this);">
                    &lt;% if (message.length &gt; 0) { %&gt;

                    <div class="alert alert-success col-sm-12">&lt;%= message %&gt;</div>

                    &lt;% } %&gt;

                    <div id="signupalert" style="display:none" class="alert alert-danger">

                        Error:
                        <span></span>
                    </div>

                    <div class="form-group">
                        <label for="first_name" class="col-md-3 control-label">First Name</label>

                        <div class="col-md-9">
                            <input type="text" class="form-control" name="first_name" placeholder="First Name">
                        </div>

                    </div>

                    <div class="form-group">
                        <label for="last_name" class="col-md-3 control-label">Last Name</label>

                        <div class="col-md-9">
                            <input type="text" class="form-control" name="last_name" placeholder="Last Name">
                        </div>

                    </div>

                    <div class="form-group">
                        <label for="mob_no" class="col-md-3 control-label">Mobile Number</label>

                        <div class="col-md-9">
                            <input type="number" class="form-control" name="mob_no" placeholder="Mobile Number">
                        </div>

                    </div>

                    <div class="form-group">
                        <label for="user_name" class="col-md-3 control-label">User Name</label>

                        <div class="col-md-9">
                            <input type="text" class="form-control" name="user_name" placeholder="User Name">
                        </div>

                    </div>

                    <div class="form-group">
                        <label for="password" class="col-md-3 control-label">Password</label>

                        <div class="col-md-9">
                            <input type="password" class="form-control" name="password" placeholder="Password">
                        </div>

                    </div>

                    <div class="form-group">
                        <!-- Button -->

                        <div class="col-md-offset-3 col-md-9">
                            <button id="btn-signup" type="submit" class="btn btn-info"><i class="icon-hand-right"></i> &nbsp; Sign Up</button>

                        </div>

                    </div>

                </form>
            </div>

        </div>

    </div>

</div>

Step 5: Created a new file routes/index.js file into routes folder and action methods into this file for load index.ejs view file.

/*
* GET home page.
*/
 
exports.index = function(req, res){
    var message = '';
  res.render('index',{message: message});

Step 6: Create an action method into user.js file to load signup.ejs file.

exports.signup = function(req, res){
   message = '';
   if(req.method == "POST"){
      //post data

   } else {
      res.render('signup');
   }
};

Above steps will create login and signup page for users, so when you will open https://localhost:8080 into browser, you will get sign-in and signup page.

Authenticate User using NodeJS and MySQL

We will create user authentication using mysql and express JS. We will take input username and password from end user and post data to node server using POST HTTP method, now we will match username and password from MySQL table using SQL query.

If the user match then we will redirect the user to the dashboard page otherwise send it to the login page with an error message. I am using the flash message module for a validation message and the session module for maintaining sessions across the nodejs applications.

We will create a post request to authenticate users using MySQL. We will follow following steps to validate user,

Step 1: Create a new POST HTTP type routes in app.js file.

app.post('/login', user.login);//call for login post

Step 2: Create a login method into user.js file.

exports.login = function(req, res){
   var message = '';
   var sess = req.session; 

   if(req.method == "POST"){
      var post  = req.body;
      var name= post.user_name;
      var pass= post.password;
     
      var sql="SELECT id, first_name, last_name, user_name FROM `users` WHERE `user_name`='"+name+"' and password = '"+pass+"'";                           
      db.query(sql, function(err, results){      
         if(results.length){
            req.session.userId = results[0].id;
            req.session.user = results[0];
            console.log(results[0].id);
            res.redirect('/home/dashboard');
         }
         else{
            message = 'Wrong Credentials.';
            res.render('index.ejs',{message: message});
         }
                 
      });
   } else {
      res.render('index.ejs',{message: message});
   }         
};

As you can see, I have used sessions to store user information into session and message modules for notification.

How to Register User in NodeJS Application

Registration is a very common process for any web application which has a user management system. This step helps to register user and store data in the MySQL database. We have already created register template view file and now create an action method to post form data using express js.

we will create a post request to register users using MySQL. We will follow following steps to validate user,

Step 1: Create a new POST HTTP type routes in app.js file.

app.post('/signup', user.signup);//call for signup post

Step 2: Create a signup() method into user.js file.

exports.signup = function(req, res){
   message = '';
   if(req.method == "POST"){
      var post  = req.body;
      var name= post.user_name;
      var pass= post.password;
      var fname= post.first_name;
      var lname= post.last_name;
      var mob= post.mob_no;

      var sql = "INSERT INTO `users`(`first_name`,`last_name`,`mob_no`,`user_name`, `password`) VALUES ('" + fname + "','" + lname + "','" + mob + "','" + name + "','" + pass + "')";

      var query = db.query(sql, function(err, result) {

         message = "Succesfully! Your account has been created.";
         res.render('signup.ejs',{message: message});
      });

   } else {
      res.render('signup');
   }
};

I have fired insert mysql query to add a record into mysql using express js, after successfully inserting the record we have redirected the user to the login page.

Create Dashboard page in nodejs application

We need to create a dashboard page into this node js example. We have created login functionality so that when the user will authenticate, we will redirect to the dashboard home page.
create routes into app.js file.

app.get('/home/dashboard', user.dashboard);//call for dashboard page after login

We have created routes and assigned dashboard method with this url, now define dashboard action method into user.js file.

exports.dashboard = function(req, res, next){
	
	var user =  req.session.user,
	userId = req.session.userId;
	
	if(userId == null){
		res.redirect("/home/login");
		return;
	}
	 
	 var sql="SELECT * FROM `login_details` WHERE `id`='"+userId+"'";
	 
	   db.query(sql, function(err, results){
		   
		   console.log(results);
		   
		   res.render('profile.ejs', {user:user});	  
		  
		});	 
};

We are rendering profile.ejs file when user is logged-in if not we are redirected to login view.We will create new views/profile.ejs file.

   <%- include header %>
   <!-- Main component for a primary marketing message or call to action -->

<div class="jumbotron">

<h1>Welcome</h1>

        
Hello Dashboard
        
          <a class="btn btn-lg btn-primary" href="#" role="button">Profile view »</a>
        
</div>

    <%- include footer %>

we have used header.ejs and footer.ejs partial file to add header and footer view file.

Conclusion:

We have created a simple nodejs application and installed dependency modules.We have also added user login and registration functionality. We have done authentication using mysql, and added partial file view render functionality with session management in node js application, to show messages we use a notification module to show the flash messages in the nodejs project.

You can download the source code and Demo from the below link.