Node js Express MySQL Login MySQL Example

In this tutorial guide, you will learn how to create a login system in node js express with MySQL database.

How to Create Login System in Node.js Express with MySQL

Steps to develop a login form that allows users to authenticate with MySQL:

Step 1: Install Node Express App JS

Run the following command on the cmd to install the express js app:

express --view=ejs loginSystem

To install some required packages:

 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
 npm install http-errors-enhanced --save

Step 2: Connect Application to Database

Create table into your database by using the following SQL query:

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;

Create one folder named lib and create a new file name db.js in it, and add the MySQL connection code into your db.js file:

 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: Import Packages and routes in app.js

Edit app.js file, and import all installed packages in it:

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');
});
// port must be set to 3000 because incoming http requests are routed from port 80 to port 8080
app.listen(3000, function () {
    console.log('Node app is running on port 3000');
});
module.exports = app;

Step 4: Create Route

Create one file name auth.js inside the routes folder, and add login and authentication routes into it:

var express = require('express');
var router = express.Router();
var connection  = require('../lib/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 users 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 5: Create views

Create folder name Auth, and inside it, create two views files:

• login.ejs
• home.ejs

Application-folder/viwes/auth/home.js

Now, open your login.ejs file and update the following code into your 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>

This login.ejs file contains login form.

Application-folder/viwes/auth/home.js

Next, open your home.ejs file and update the following code into your 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 6: Test Application

You can use the following command to run the development server:

//run the below command

npm start

Open browser and use the following URLs:

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

Conclusion

Node js express 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