Node.js Express MySQL Login Example

Node js express login example mysql; In this tutorial, i will guide you from scratch on how to build login authentication system in node js express with MySQL database.

One way to accept user input into your Node application is by using an HTML <form> element. To use the form data submitted by users we’ll need a Node. js server application to parse it. ExpressJS is a handy framework that simplifies the process of creating server applications.

In this login with mysql in node js express example; i will install some dependencies for building a login authentication system in node js epxress app. Then i’ll create login authentication html markup form. And create a method which will authenticate user from mysql database uisng node js express app.

Login in Node.js Express with MySQL

  • Step 1 – Create New Node Express JS
  • Step 2 – Create DB , Table and Connect App with DB
  • Step 3 – Install Flash,Session,Validator, MySQL Package
  • Step 4 – Import Installed Dependencies routes in app.js
  • Step 5 – Create Login Route
  • Step 6 – Create Login and Home View
  • Step 7 – Start Node js Express Login with MySQL App Server

Step 1 – Create New Node Express JS

Execute the following command on terminal to create loginAuthSystem directory:

express --view=ejs loginAuthSystem
cd loginAuthSystem

Step 2 – Create DB , Table and Connect App with DB

Execute the following sql query to your phpmyadmin and create a database and table:

CREATE DATABASE IF NOT EXISTS `nodelogin` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `nodelogin`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `users` (`id`, `name`, `password`, `email`) VALUES (1, 'test', 'test', '[email protected]');
ALTER TABLE `users` ADD PRIMARY KEY (`id`);
ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;

Then create a new file which name database.js; so go your app root directory and create it.

After that, add the MySQL connection code into your database.js file to connect your app to database:

 var mysql=require('mysql');
 var connection=mysql.createConnection({
   host:'localhost',
   user:'root',
   password:'your password',
   database:'nodelogin'
 });
connection.connect(function(error){
   if(!!error){
     console.log(error);
   }else{
     console.log('Connected!:)');
   }
 });  
module.exports = connection; 

Step 3 – Install Flash,Session,Validator, MySQL Package

Install flash message, session, validator, override, and mysql package by executing the following command on terminal:

 npm install    
 npm install express-flash --save
 npm install express-session --save
 npm install express-validator --save
 npm install method-override --save
 npm install mysql --save
express-flash

Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.
In this node js mysql crud tutorial express flash is used to display a warning, error and information message

express-session

Express-session is used to made a session as like in PHP. In this node js mysql crud tutorial, session is needed as the express requirement of express-flash.

express-validator

Express validator is used to validate form data it is easy to use. express-validator highly effective and efficient way to accelerate the creation of applications.

method-override

NPM is used to run a DELETE and PUT method from an HTML form. In several web browsers only support GET and POST methods.

MySQL

Driver to connect node.js with MySQL

Step 4 – Import Installed Dependencies routes in app.js

Visit your app root directory and open app.js file; then import above installed dependencies into your app.js file; as shown below:

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var expressValidator = require('express-validator');
var flash = require('express-flash');
var session = require('express-session');
var bodyParser = require('body-parser');
var mysql = require('mysql');
var connection  = require('./lib/db');
var indexRouter = require('./routes/index');
var authRouter = require('./routes/auth');
var app = express();
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use(session({ 
    secret: '123456cat',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 }
}))
app.use(flash());
app.use(expressValidator());
app.use('/', indexRouter);
app.use('/auth', authRouter);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});
// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};
  // render the error page
  res.status(err.status || 500);
  res.render('error');
});
module.exports = app;

Step 5 – Create Login Route

Visit your app root directory and open routes directory. Then inside route directory create one route file name auth.js:

var express = require('express');
var router = express.Router();
var connection  = require('../db');
//display login page
router.get('/', function(req, res, next){    
    // render to views/user/add.ejs
    res.render('auth/login', {
        title: 'Login',
        email: '',
        password: ''      
    })
})
//display login page
router.get('/login', function(req, res, next){    
    // render to views/user/add.ejs
    res.render('auth/login', {
        title: 'Login',
        email: '',
        password: ''     
    })
})
//authenticate user
router.post('/authentication', function(req, res, next) {
      
    var email = req.body.email;
    var password = req.body.password;
        connection.query('SELECT * FROM accounts WHERE email = ? AND password = ?', [email, password], function(err, rows, fields) {
            if(err) throw err
            
            // if user not found
            if (rows.length <= 0) {
                req.flash('error', 'Please correct enter email and Password!')
                res.redirect('/login')
            }
            else { // if user found
                // render to views/user/edit.ejs template file
                req.session.loggedin = true;
                req.session.name = name;
                res.redirect('/home');
            }            
        })
 
})
//display home page
router.get('/home', function(req, res, next) {
    if (req.session.loggedin) {
        
        res.render('auth/home', {
            title:"Dashboard",
            name: req.session.name,     
        });
    } else {
        req.flash('success', 'Please login first!');
        res.redirect('/login');
    }
});
// Logout user
router.get('/logout', function (req, res) {
  req.session.destroy();
  req.flash('success', 'Login Again Here');
  res.redirect('/login');
});
module.exports = router;

Step 6 – Create Login and Home View

Visit views directory in your app and create the Auth directory. And create two views file, one is login.ejs and home.ejs.

  • login.ejs
  • home.ejs

Add the following code in login.ejs file:

<!DOCTYPE html>
<html>
<head>
  <title><%= title %></title>
  <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js"></script>
  <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
</head>
<body>
  <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
  <% } %>
   
  <% if (messages.success) { %>
      <p style="color:green"><%- messages.success %></p>
  <% } %>
  <form action="/auth/authentication" method="post" name="form1">
    <div class="form-group">
      <label for="exampleInputEmail1">Email</label>
      <input type="email" name="email" class="form-control" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="">
    </div>
    <div class="form-group">
      <label for="exampleInputEmail1">Password</label>
      <input type="password" name="password" class="form-control" id="password" aria-describedby="emailHelp" placeholder="*********" value="">
    </div>
    <input type="submit" class="btn btn-primary" value="Add">
  </form>
</body>
</html>

Add the following code in home.ejs file:

<!DOCTYPE html>
<html>
<head>
  <title><%= title %></title>
  <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js"></script>
  <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
</head>
<body>
  <div class="container">
    
  <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
  <% } %>
   
  <% if (messages.success) { %>
      <p style="color:green"><%- messages.success %></p>
  <% } %>
  <div class="card">
    <div class="card-header">
     Dashboard <b><%= name %></b>
    </div>
    <div class="card-body">
      <h5 class="card-title">Welcome</h5>
      <p class="card-text">You have successfully login</p>
      <a href="auth/logout" class="btn btn-primary">Logout</a>
    </div>
  </div>
  </div>
</body>
</html>

Step 7 – Start Node js Express Login with MySQL App Server

Execute the following command on terminal to node js express login with mysql app server:

//run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/auth
OR
http://127.0.0.1:3000/auth/login

Conclusion

In this node express js login example with MySQL tutorial. You have learned how to create login system in node js using express js framework with MySQL db.

Recommended Node js Tutorials

Leave a Comment