User Login and Registration with Node.js, Express & MySQL

In our previous tutorial, we have explained about User Authentication and Authorization with Node.js. In this tutorial we will learn how to implement user registration and login functionality with Node.js, Express & MySQL.

User login and registration is an important functionality of web application to allow users to create account and login. The functionality is created for user authentication to allow users to access different resources according to user role.

So if you’re working on web application wants to create user registration and login functionality, then you’re here at the right place.

We will implement the user registration and login functionality with user authentication. We will store the user information into session for authentication purpose.

Also, read:

• User Authentication and Authorization with Node.js

So let’s proceed to implement the user registration and login functionality with Node.js, Express & MySQL. The major files are:

• package.json: File to define the libraries or modules use in this project.
• app.js: File to create application server and routes url.
• routes/user.js: File to control all action of login and registration process.
• views/index.ejs: User login form.
• views/signup.ejs: User registration form.
• views/dashboard.ejs: User dashboard
• views/profile.ejs: User profile page.

Step1: Create Application

First we will create our application directory user_login_registration.

$ mkdir user_login_registration

we will go to the root directory of our application and type npm init to initialize our application with a package.json file.

$ cd user_login_registration
$ npm init

We will install the dependency modules for the our application. So we will install express, ejs, body-parser, req-flash, express-session and mysql modules. We will install ejs module for template engine in this application to create login and registration form.

$ npm install express ejs body-parser req-flash express-session mysql --save

The final package.json file will be created at the root of application directory.

{
  "name": "user_login_registration",
  "version": "1.0.0",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "ejs": "^3.0.1",
    "express": "^4.17.1",
    "express-session": "^1.17.0",
    "mysql": "^2.18.1",
    "req-flash": "0.0.3"
  },
  "devDependencies": {},
  "description": ""
}

Step2: Create MySQL Database and Table

We will create MySQL database coderszine_demo and create table users for storing user details. Here we will store password as varchar, you can change it to store as md5 any other secure format.

CREATE TABLE `users` (
  `id` int(5) NOT NULL,
  `first_name` text NOT NULL,
  `last_name` text NOT NULL,
  `mobile` int(11) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step3: Create Application File and Routing

We will create app.js file at the root of application. We will ad below code to instantiate all module and create connection with MySQL. We will also add routing for resources like login, logout, signup, dashboard, profile page etc. We will also mention the port 8080 to run the node server.

var express = require('express')
  , routes = require('./routes')
  , user = require('./routes/user')
  , http = require('http')
  , path = require('path');
var session = require('express-session');
var app = express();
var mysql      = require('mysql');
var bodyParser=require("body-parser");
var connection = mysql.createConnection({
              host     : 'localhost',
              user     : 'root',
              password : '',
              database : 'coderszine_demo'
            });
 
connection.connect();
 
global.db = connection;
 
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')));
app.use(session({
              secret: 'keyboard cat',
              resave: false,
              saveUninitialized: true,
              cookie: { maxAge: 60000 }
            }))
 
app.get('/', routes.index);
app.get('/signup', user.signup);
app.post('/signup', user.signup);
app.get('/login', routes.index);
app.post('/login', user.login);
app.get('/home/dashboard', user.dashboard);
app.get('/home/logout', user.logout);
app.get('/home/profile',user.profile);
app.listen(8080)

Step4: Implement User Login

We will create views/index.ejs file and create login form for user login.

<%- include('header'); -%>
<%- include('container'); -%>
<body>
	<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">User Sign In</div>
				</div>    
				<div style="padding-top:30px" class="panel-body" >
					<% if (message.length > 0) { %>
					<div class="alert alert-danger col-sm-12"><%= message %></div>
					<% } %>
					<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="username" 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 style="margin-top:10px" class="form-group">						
							<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>
<%- include('footer'); -%>

In routes/user.js file, we will implement user login functionality by using login form post values. We will store user details into SESSION variables for user authentication.

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

   if(req.method == "POST"){
      var post  = req.body;
      var username = post.username;
      var password= post.password;
     
      var sql="SELECT id, first_name, last_name, username FROM `users` WHERE `username`='"+username+"' and password = '"+password+"'";                           
      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 = 'You have entered invalid username or password.';
            res.render('index.ejs',{message: message});
         }
                 
      });
   } else {
      res.render('index.ejs',{message: message});
   }
           
};

Step5: Implement Signup Form

We will create views/signup.ejs file and user registration form.

<%- include('header'); -%>
<%- include('container'); -%>
<body>
	<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">User 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">
						<% if (message.length > 0) { %>
						<div class="alert alert-success col-sm-12"><%= message %></div>
						<% } %>
						<div id="signupalert" style="display:none" class="alert alert-danger">
							<p>Error:</p>
							<span></span>
						</div>
						<div class="form-group">
							<label for="username" class="col-md-3 control-label">Username</label>
							<div class="col-md-9">
								<input type="text" class="form-control" name="username" placeholder="username">
							</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">
							<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</label>
							<div class="col-md-9">
								<input type="number" class="form-control" name="mobile" placeholder="Mobile Number">
							</div>
						</div>						
						<div class="form-group">						            
							<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>   Sign Up</button>
							</div>
						</div>
					</form>
				</div>
			</div>
		</div>
	</div>
<%- include('footer'); -%>

In routes/user.js file, we will implement user registration functionality by using signup form post values. We will also display form validation messages.

exports.signup = function(req, res){
   message = '';
   if(req.method == "POST"){
      var post  = req.body;
      var username = post.username;
      var password = post.password;
      var fname= post.first_name;
      var lname= post.last_name;
      var mobile= post.mobile;
	  if(username !='' && password!='') {
		  var sql = "INSERT INTO `users`(`first_name`,`last_name`,`mobile`,`username`, `password`) VALUES ('" + fname + "','" + lname + "','" + mobile + "','" + username + "','" + password + "')";

		  var query = db.query(sql, function(err, result) {
			 message = "Your account has been created succesfully.";
			 res.render('signup.ejs',{message: message});
		  });
	  } else {
		  message = "Username and password is mandatory field.";
		  res.render('signup.ejs',{message: message});
	  }

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

Step6: Create Dashboard

We will template file views/dashboard.ejs and create HTML to display user dashboard with user profile link.

<%- include('header'); -%>
<%- include('container'); -%>
  <div class="jumbotron">
	<h1>Dashboard</h1>
	<p>Welcome here</p>
	<p>
	  <a href="/home/profile">View Profile</a>
	</p>
  </div>
<%- include('footer'); -%>

Step7: Implement User Profile Page

We will create template file views/profile.ejs and create HTML for user profile.

<%- include('header'); -%>
<%- include('container'); -%>
<div class="row">
    <div>
      <div class="panel panel-info">
        <div class="panel-heading">
          <span>Logged In: <%=data[0].username%></span>		  
		  <span class="pull-right"><a href="/home/logout">Logout</a></span>		  
        </div>
        <div class="panel-body">
          <div class="row">
            <div class="col-md-3 col-lg-3"> <img alt="User Pic" src="/images/user.jpg" class="img-circle img-responsive"> </div>
            <div class=" col-md-9 col-lg-9 "> 
              <table class="table table-user-information">
                <tbody>
				  <tr>
                    <td>Name:</td>
                    <td><%=data[0].first_name%> <%=data[0].last_name%></td>
                  </tr>
                  <tr>
                    <td>User Name:</td>
                    <td><%=data[0].username%></td>
                  </tr>
                  <tr>
                    <td>Mobile:</td>
                    <td><%=data[0].mobile%></td>
                  </tr>
                </tbody>
              </table>
            </div>
          </div>
        </div>
    </div>
<%- include('footer'); -%>

In routes/user.js file, we will implement user profile to get user details from MySQL database table. We will display user details on profile page.

exports.profile = function(req, res){

   var userId = req.session.userId;
   if(userId == null){
      res.redirect("/login");
      return;
   }

   var sql="SELECT * FROM `users` WHERE `id`='"+userId+"'";          
   db.query(sql, function(err, result){  
      res.render('profile.ejs',{data:result});
   });
};

Step8: Implement User Logout

We will implement user logout functionality in routes/user.js file.

exports.logout=function(req,res){
   req.session.destroy(function(err) {
      res.redirect("/login");
   })
};

Step9: Run Application

Now finally we will run the application by below command.

$ node app.js

Then we will open the http://localhost:8080 into browser run the application.

Conclusion

In this tutorial, We have explained how to implement user login and registration with Node.js, Express and MySQL. We will display dashboard after user login and display user profile page. You can download the code and customize to use in your application. You just need to create your Node.js application and keep downloaded files into your project to run this example code.

You may also like:

• How to Install Node.js on Windows
• How to Install Node.js on Ubuntu
• Amazon S3 File Upload using Node.js
• Upload Multiple Image File with Node.js
• Scrape Search Data with SERP API using Node.js
• Create REST API with Node.js, Express & MongoDB
• User Authentication and Authorization with Node.js
• Chat Application with Node.js, Express & MongoDB

You can download the script from the Download link below.
Download