NodeJS Passport login script with MySQL database

NodeJS Passport login with MySQL

User authentication is an important feature in today’s dynamic applications. This tutorial explores NodeJS passport login with MySQL. In a previous Nodejs mongodb tutorial you learned about performing Insert, Update, Delete and View records, but these tasks need to be performed by an authenticated user. This tutorial explores this important feature.

To do nodejs passport login with mysql, following tasks are performed.

1. Create a database db_users.

2. Create a database table for tbl_users and insert sample data into the table.

3. Generate a NodeJS, Express Application using Express Generator.

4. Install required modules like using NPM.

5. Create a NodeJS passport login script with MySQL.

nodejs passport login

Create database and a table for users

Open phpmyadmin, click on SQL tab and execute SQL statements below.

create database db_users;

use db_users;

CREATE TABLE `tbl_users` (
  `id` int(11) NOT NULL,
  `username` varchar(16) NOT NULL,
  `password` varchar(60) NOT NULL,
  `full_name` varchar(100) NOT NULL

INSERT INTO `tbl_users` (`id`, `username`, `password`, `full_name`) VALUES (NULL, 'john', SHA1('7fa73b47df808d36c5fe328546ddef8b9011b2c612345'), 'John Doe');

ALTER TABLE `tbl_users`

ALTER TABLE `tbl_users`

After creating database and table, sample data is inserted into table. Password is encrypted with a SALT and SHA1 algorithm.

nodejs passport login table structure

Generate a NodeJS application using Express

After creating database let us generate NodeJS, express application using express generator.

Install Express Generator

npm install express-generator -g

After express generator installation, type command below to generate application.

Generate nodejs-mysql-login express app

Command below generates an express application. Pug is used as view engine.

express --view=pug nodejs-mysql-login

nodejs passport login application generated app

Install dependencies and run application

After generation of app skeleton, change directory and install dependencies or required modules. These modules are mentioned in package.json

  "name": "nodejs-mysql-login",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "start": "node ./bin/www"

  "dependencies": {
    "body-parser": "~1.16.0",
    "cookie-parser": "~1.4.3",
    "debug": "~2.6.0",
    "express": "~4.14.1",
    "morgan": "~1.7.0",
    "pug": "~2.0.0-beta10",
    "serve-favicon": "~2.3.2"


Please type cd nodejs-mysql-login  and then npm install  To run the app, type command below.

DEBUG=nodejs-mysql-login:* npm start

Open browser and type following URL http://localhost:3000 to view running app.

Create NodeJS passport login script with MySQL

Open the the project folder in an IDE such as sublime text.

Create login view and add login form

Inside Views folder open Layout.pug, add bootstrap CSS and JS files. Add a new folder login and create a file named index.pug in it. Add following pug code.


        form.form-signin(method='post', action='/signin')

          h3.form-signin-heading Nodejs MySQL Login Script
          - if(message){
          p(style='color:red') #{message}
          - }
          input.form-control(type='text', name='username', placeholder='Username')
          input.form-control(type='password', name='password', placeholder='Password')
          button.btn.btn-lg.btn-primary.btn-block(type='submit') Login

Login form

nodejs passport login login form

After creating form, we have to install appropriate modules

MySQL database connection file

Add a folder called lib and create a file dbconn.js.

var mysql        = require('mysql');

var connection   = mysql.createConnection({

  supportBigNumbers: true,

  bigNumberStrings: true,

  host     : "localhost",

  user     : "root",

  password : "",

  database : "db_users"


module.exports = connection;

Change host name, username, password and database name according to your system settings.

Install passport and other modules

In order to perform nodejs passport login with mysql, you have to install Passport and other modules. Open command prompt, navigate to nodejs mysql login project directory and run commands given below.

npm install connect-flash

npm install passport

npm install passport-local

npm install express-session

npm install memory

npm install crypto

npm install mysql

Connect-flash module

Connect-flash module is a session area to store messages that are written to flash and are cleared after user has seen the messages.

Passport module

Passport module is a NodeJS module used as authentication middleware.

Passport-local module

This NodeJS module used to authenticate using a username and password.

Express-session and memory modules

This module is used to store session data.

Crypto module

Crypto module is JavaScript implementation of cryptographic algorithms.

MySQL module

This NodeJS module is driver for MySQL.

Add modules in app.js

Open app.js file and include these modules.

var flash             = require('connect-flash');

var crypto            = require('crypto');

var passport          = require('passport');

var LocalStrategy     = require('passport-local').Strategy;

var connection        = require('./lib/dbconn');

var sess              = require('express-session');

var Store             = require('express-session').Store;
var BetterMemoryStore = require(__dirname + '/memory');

Express session store and expiration

 var store = new BetterMemoryStore({ expires: 60 * 60 * 1000, debug: true });


    name: 'JSESSION',


    store:  store,

    resave: true,

    saveUninitialized: true


Code above sets expiration time for session in store variable. Then session name and session secret is set.

Passport module initialization

Flash module is mounted using app.use. After flash module passport and passport session module is initialized.




 PassportJS local strategy for Signin

Passport module LocalStrategy is used to authenticate user locally. Other then local strategy user can be authenticated using Twitter or Facebook etc. In LocalStrategyverify callback is required and credentials are passed to it. done method is called. If username and password are not entered an error message is displayed to client.

PassportJS login process

Passport LocalStrategy is called local, and username and password fields are specified. Request object req is passed to callback function. A SALT is defined and is concatenated with password. A query is passed to connection.query() method of mysql driver to fetch user information based on username entered by user.

If provided username is not correct then an error message is displayed to user, if username is correct then password concatenated with SALT and is hashed with sha1 algorithm using crypto module. If password is correct user information is returned.

passport.use('local', new LocalStrategy({

  usernameField: 'username',

  passwordField: 'password',

  passReqToCallback: true //passback entire req to call back
} , function (req, username, password, done){

      if(!username || !password ) { return done(null, false, req.flash('message','All fields are required.')); }

      var salt = '7fa73b47df808d36c5fe328546ddef8b9011b2c6';

      connection.query("select * from tbl_users where username = ?", [username], function(err, rows){

          console.log(err); console.log(rows);

        if (err) return done(req.flash('message',err));

        if(!rows.length){ return done(null, false, req.flash('message','Invalid username or password.')); }

        salt = salt+''+password;

        var encPassword = crypto.createHash('sha1').update(salt).digest('hex');

        var dbPassword  = rows[0].password;

        if(!(dbPassword == encPassword)){

            return done(null, false, req.flash('message','Invalid username or password.'));


        return done(null, rows[0]);




Serialize and deserialize user information

Passport serializes user information to store in session, deserialize function is used to deserialize the data.

passport.serializeUser(function(user, done){



passport.deserializeUser(function(id, done){

    connection.query("select * from tbl_users where id = "+ id, function (err, rows){

        done(err, rows[0]);



Signin route for GET method in app.js

Create a signin route inside app.js and render login/index.pug file.

app.get('/signin', function(req, res){

  res.render('login/index',{'message' :req.flash('message')});


Signin route for POST method to authenticate requests

When user fills in username and password in login form, data is posted to signin route in app.js."/signin", passport.authenticate('local', {

    successRedirect: '/profile',

    failureRedirect: '/signin',

    failureFlash: true

}), function(req, res, info){

    res.render('login/index',{'message' :req.flash('message')});


To authenticate requests using passport we have to use passport.authenticate and specify local strategy.

If user is signed in successfully, is redirected to profile page, if signin is failed, user is redirected back to signin page and appropriate message is displayed.

Protect routes with isAutehticated

Request object’s isAuthenticated method is used to validate whether user is logged in or not. So if  there are routes like profile that needs to be protected from public access and should only be visible to authorized user, isAuthenticated is added in route.

router.get('/', isAuthenticated, function(req, res, next) { ... }

The code below checks if user is authenticated then redirected to next page otherwise redirected back to login page.

function isAuthenticated(req, res, next) {

  if (req.isAuthenticated())

    return next();




In this tutorial you have learned about nodejs passport login with MySQL. We created a database and table. Created a form with bootstrap. Installed passport and other related modules, added a local strategy to perform signin and authenticated a route.

Stay tuned to for more great tutorials. To download the tutorial code visit following link. Please like us on twitter  to get informed about upcoming tutorials. Leave your comments and feedback below. thanks for reading.