Node.js Expressjs MySQL login with Example

Let’s see how to create express login functionality with MySQL in node.js. I will show examples of Node.js Express Login with MySQL As well as you will learn how to handle sessions in node express js, body-parser to handle form data, and how to contact DB to node express js app.

Follow the step by step process to create a login system in node js using express js framework with MySQL database.

Steps:

Step 1: Install Node Express JS Project Setup
Step 2: Connect Application with Database
Step 3: Include Dependencies/Packages and routes in app.js
Step 4: Create and Update Routes
Step 5: Create and update views
Step 6: Run Application

Step 1: Install Node Express JS Project Setup

Using the following command install the new express js project setup, and learn the details about the express js and project setup using the express application generator

//Create a new project called codeamend_login
express --view=ejs codeamend_login
//Move to the directory - codeamend_login
cd codeamend_login
//Install default dependencies
npm install

After that, we need to install some more required packages for the login authentication, so in your cmd, run the following commands.

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 Login tutorial express flash is used to display a warning, error and information message. View More

express-session

Express-session is used to make a session like in PHP. In this node.js express login example, the session is needed as the express requirement of express-flash. View More

express-validator

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

method-override

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

MySQL

Driver to connect node.js with MySQL, for the NodeJs MySQL CRUD functionalities. View More

Step 2: Connect Application with Database

Refer – https://codeamend.com/blog/connecting-to-the-mysql-database-using-nodejs/

Before connecting DB to your NodeJs MySQL CRUD application, create a new database and table by using the following SQL query:

CREATE DATABASE IF NOT EXISTS `codeamend_login` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `codeamend_login`;
 
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
 
INSERT INTO `users` (`id`, `username`, `email`, `password`) VALUES (1, 'user', 'user@test.com', 'testuser');
 
ALTER TABLE `users` ADD PRIMARY KEY (`id`);
ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;

Then, we need to create a folder named config and create a new file name database.js inside the config folder. This file is used to connect your node express js app to the MySQL database.

config/database.js

Update the below MySQL connection code in the database.js file.

var mysql=require('mysql');

var connection=mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'',
    database:'codeamend_login'
});

connection.connect(function(error){
    if(!!error){
        console.log(error);
    } else {
        console.log('Connected!:)');
    }
});  

module.exports = connection;

Step 3: Include Dependencies/Packages and routes in app.js

In this step, we need to include all packages in the app.js file, above installed and also initialize the session in this file.

Open the app.js file and update the following code:

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('./config/database');
 
var indexRouter = require('./routes/index');
var userRouter = require('./routes/user');
 
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('/user', userRouter);
 
// 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 4: Create and Update Routes

In this step, we need to create the route file name user.js inside the routes folder for the nodejs authentication and update the below codes,

var express = require('express');
var router = express.Router();
var connection  = require('../config/database');

//Display login page
router.get('/', function(req, res, next){    
    // render to views/user/login.ejs
    res.render('user/login', { title: 'Login', email: '', password: '' })
});
 
//Display login page
router.get('/login', function(req, res, next){    
    // render to views/user/login.ejs
    res.render('user/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 enter correct email and Password!')
            res.redirect('/user/login')
        } else { 
            // if user found
            req.session.loggedin = true;
            req.session.name = rows[0].username;
            res.redirect('/user/home');
        }            
    });
});
 
//Display home page
router.get('/home', function(req, res, next) {
    if (req.session.loggedin) {
        // render to views/user/home.ejs template file
        res.render('user/home', { title:"Dashboard", name: req.session.name });
    } else {
        req.flash('success', 'Please login first!');
        res.redirect('/user/login');
    }
});
 
// Logout user
router.get('/logout', function (req, res) {
    req.flash('success', 'Login Again');
    req.session.destroy();
    res.redirect('/user/login');
});
 
module.exports = router;

Step 5: Create and update views

In this step, we need to create one folder named user. Go to the views folder in your app and create the User folder.

Inside the User folder, create two views files (login.ejs and home.ejs)

Open your /views/user/login.ejs file and update the following code

<html>
    <head>
        <title><%= title %></title>
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
    </head>
    <body>
        <div class="container">
            
            <div class="text-center mt-3">
                <% if (messages.error) { %>
                <p style="color:red"><%- messages.error %></p>
                <% } %>

                <% if (messages.success) { %>
                <p style="color:green"><%- messages.success %></p>
                <% } %>
            </div>
            
            <div class="row justify-content-center">
                <div class="col-5 mt-2">
                    <div class="card">
                        <div class="card-header text-center">
                            <h3>Codeamend Nodejs Login Example Using ExpressJs and MySQL</h3>
                        </div>
                        <div class="card-body">
                            <form action="/user/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>

                                <button type="submit" class="btn btn-primary">Login</button>
                            </form>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>

Open your /views/user/home.ejs file and update the following code

<html>
    <head>
        <title><%= title %></title>
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
    </head>
    <body>
        <div class="container">
            
            <div class="text-center mt-3">
                <% if (messages.error) { %>
                <p style="color:red"><%- messages.error %></p>
                <% } %>

                <% if (messages.success) { %>
                <p style="color:green"><%- messages.success %></p>
                <% } %>
            </div>
            
            <div class="row justify-content-center">
                <div class="col-5 mt-2">
                    <div class="card">
                        <div class="card-header text-center">
                            <h3>Codeamend Nodejs Login Example Using ExpressJs and MySQL</h3>
                        </div>
                        <div class="card-body">
                            <h5 class="card-title">Dashboard</h5>
                            <h5 class="card-title">Welcome <b><%= name %></b></h5>
                            <p class="card-text">You have successfully login</p>
                            <a href="/user/logout" class="btn btn-primary">Logout</a>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>

Step 6: Run Application

You can use the following command to run development server:

//run the below command
npm start

After running this command open your browser and hit

http://127.0.0.1:3000/user OR http://localhost:3000/user

http://127.0.0.1:3000/user/login OR http://localhost:3000/user/login

Note if you get "TypeError: expressValidator is not a function"

Resolve TypeError: expressValidator is not a function issue by reinstall the express validator NPM package and run the server again.

npm uninstall express-validator
npm install express-validator@5.3.0

Node.js Login authentication using ExpressJs and MySQL with Example

Steps:

Step 1: Install Node Express JS Project Setup

Step 2: Connect Application with Database

Step 3: Include Dependencies/Packages and routes in app.js

Step 4: Create and Update Routes

Step 5: Create and update views

Open your /views/user/login.ejs file and update the following code

Open your /views/user/home.ejs file and update the following code

Step 6: Run Application

Note if you get "TypeError: expressValidator is not a function"

Popular Posts

You can also read...

Codeamend

Tags

Categories