How to implement Email Verification using Node js, Express and MySQL?

Hello Friends,

In this example, I will show you how to implement email verification using node js express and MySQL. I would like to share with you how to implement an email verification feature in your nodejs app using express. Here you will learn email authentication and verification using node.js and MySQL. Step-by-step explain node email verification script.

This tutorial is going to give you a high-level guide on how to get users of your NodeJS/Express web application to get their email verified for possible foreseeable future occurrences where users can manage their accounts or recover their passwords and also make sure they are not robots with fake emails.

Step 1: Install Node Express App JS

Execute the following command on the terminal to install the express js app:

express --view=ejs emailVerification

Then open emailVerification setup with any text editor. And use the following command to enter your emailVerification app directories, So open your cmd and run the following command:

cd emailVerification

Your node express js + mysql email verification app structure looks like this:

Next, you need to install some required packages, so open your cmd again and run the following commands:

 npm install express-flash --save
 npm install express-session --save
 npm install method-override --save
 npm install mysql --save
 npm install rand-token --save
 npm install body-parser --save
 npm install 

Express-flash – Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.

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

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.

MySQL – Driver to connect node.js with MySQL.

rand-token – Generate random tokens from your choice of randomness.

body-parser – Body-parser is the Node. js body parsing middleware. It is responsible for parsing the incoming request bodies in a middleware before you handle it.

Nodemailer – Nodemailer is a module for Node.js applications to allow easy as cake email sending. The project started in 2010 when there was no sane option to send email messages, today it is the solution most Node. js users turn to by default.

If you want to send mail from localhost or without SSL certificate, you can execute the following command on your terminal also:

npm config set strict-ssl false --global

set NODE_TLS_REJECT_UNAUTHORIZED=0

Step 2: Connect Node Express JS App with DB

Before connecting DB to your node js email verification application, create a table in your database by using the following SQL query:

-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 10, 2021 at 02:12 PM
-- Server version: 10.4.14-MariaDB
-- PHP Version: 7.4.9
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
 
--
-- Database: `my-node`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `verifications`
--
 
CREATE TABLE `verifications` (
    `id` int(11) NOT NULL,
    `email` varchar(250) NOT NULL,
    `token` varchar(250) DEFAULT NULL,
    `verify` varchar(100) DEFAULT '0',
    `created_at` date NOT NULL DEFAULT current_timestamp(),
    `updated_at` date NOT NULL DEFAULT current_timestamp()
) 

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
--
-- Indexes for dumped tables
--
 
--
-- Indexes for table `verifications`
--
ALTER TABLE `verifications`
  ADD PRIMARY KEY (`id`);
 
--
-- AUTO_INCREMENT for dumped tables
--
 
--
-- AUTO_INCREMENT for table `verifications`
--
ALTER TABLE `verifications`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

Then visit your app root directory and create one file name database.js. Then add the following code to your database.js file:

var mysql=require('mysql');

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

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

module.exports = connection; 

Note that This file is used to connect your node express js app to MySQL DB.

Step 3: Import Packages in app.js

Open your app.js file and import all the packages above installed in app.js file.

So go to 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 flash = require('express-flash');
var session = require('express-session');
var bodyParser = require('body-parser');
 
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
 
var app = express();
 
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
 
app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
 
app.use(session({ 
    secret: '123458cat',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 }
}))
 
app.use(flash());
 
app.use('/', indexRouter);
app.use('/users', usersRouter);
 
// 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');
});
 
app.listen(3000, function () {
    console.log('Node app is running on port 3000');
});
 
module.exports = app;

Step 4: Create Email Send And Verification Route

Create email sending and verification route; so visit routes directory and open index.js route file and create routes as shown below:

Email Send Function with Gmail nodemailer configuration

//send email
function sendEmail(email, token) {
 
    var email = email;
    var token = token;
 
    var mail = nodemailer.createTransport({
        service: 'gmail',
        auth: {
            user: '', // Your email id
            pass: '' // Your password
        }
    });
 
    var mailOptions = {
        from: 'nicesnippets@gmail.com',
        to: email,
        subject: 'Email verification - Nicesnippets.com',
        html: '
You requested for email verification, kindly use this link to verify your email address
'
    };
 
    mail.sendMail(mailOptions, function(error, info) {
        if (error) {
            return 1
        } else {
            return 0
        }
    });
}

Now open the link https://myaccount.google.com/lesssecureapps to Allow less secure apps: ON. It will send the email using Gmail account.

Email sending route with Verification Link

/* send verification link */
router.post('/send-email', function(req, res, next) {
 
    var email = req.body.email;
 
    //console.log(sendEmail(email, fullUrl));
 
    connection.query('SELECT * FROM verifications WHERE email ="' + email + '"', function(err, result) {
        if (err) throw err;
         
        var type = 'success'
        var msg = 'Email already verified'
   
        console.log(result[0]);
     
        if (result.length > 0) {
            var token = randtoken.generate(20);
 
            if(result[0].verify == 0 ){
                var sent = sendEmail(email, token);
                if (sent != '0') {
                    var data = {
                        token: token
                    }
                    connection.query('UPDATE verifications SET ? WHERE email ="' + email + '"', data, function(err, result) {
                        if(err) throw err
                    })
                    type = 'success';
                    msg = 'The verification link has been sent to your email address';
                } 
                else {
                    type = 'error';
                    msg = 'Something goes to wrong. Please try again';
                }
            }
        } 
        else {
            console.log('2');
            type = 'error';
            msg = 'The Email is not registered with us';
        }
        req.flash(type, msg);
        res.redirect('/');
    });
})

Email Verification route

/* send verification link */
router.get('/verify-email', function(req, res, next) {
 
    connection.query('SELECT * FROM verifications WHERE token ="' + req.query.token + '"', function(err, result) {
        if (err) throw err;
 
        var type
        var msg
 
        console.log(result[0].verify);
 
        if(result[0].verify == 0){
            if (result.length > 0) {
                var data = {
                    verify: 1
                }
                connection.query('UPDATE verifications SET ? WHERE email ="' + result[0].email + '"', data, function(err, result) {
                    if(err) throw err
                })
                type = 'success';
                msg = 'Your email has been verified';
            } 
            else {
                console.log('2');
                type = 'success';
                msg = 'The email has already verified';
            }
        }
        else{
            type = 'error';
            msg = 'The email has been already verified';
        }
        req.flash(type, msg);
        res.redirect('/');
    });
})

Complete source code of index.js route:

var express = require('express');
var router = express.Router();
var connection = require('../database.js');
var nodemailer = require('nodemailer');
var randtoken = require('rand-token');

//send email
function sendEmail(email, token) {
    var email = email;
    var token = token;
    var mail = nodemailer.createTransport({
        service: 'gmail',
        auth: {
            user: '', // Your email id
            pass: '' // Your password
        }
    });
    var mailOptions = {
        from: 'nicesnippets@gmail.com',
        to: email,
        subject: 'Email verification - Nicesnippets.com',
        html: '
You requested for email verification, kindly use this link to verify your email address
'
    };
    mail.sendMail(mailOptions, function(error, info) {
        if (error) {
            return 1
        } else {
            return 0
        }
    });
}

/* home page */
router.get('/', function(req, res, next) {
    res.render('index', {
        title: 'Express'
    });
});

/* send verification link */
router.post('/send-email', function(req, res, next) {
    var email = req.body.email;

    //console.log(sendEmail(email, fullUrl));
    connection.query('SELECT * FROM verifications WHERE email ="' + email + '"', function(err, result) {
        if (err) throw err;
        
        var type = 'success'
        var msg = 'Email already verified'
    
        console.log(result[0]);
        
        if (result.length > 0) {
            var token = randtoken.generate(20);
            if(result[0].verify == 0 ){
                var sent = sendEmail(email, token);
                if (sent != '0') {
                    var data = {
                        token: token
                    }
                    connection.query('UPDATE verifications SET ? WHERE email ="' + email + '"', data, function(err, result) {
                        if(err) throw err
                    })
                    type = 'success';
                    msg = 'The verification link has been sent to your email address';
                } 
                else {
                    type = 'error';
                    msg = 'Something goes to wrong. Please try again';
                }
            }
        } 
        else {
            console.log('2');
            type = 'error';
            msg = 'The Email is not registered with us';
        }
        req.flash(type, msg);
        res.redirect('/');
    });
})

/* verification email link */
router.get('/verify-email', function(req, res, next) {
    connection.query('SELECT * FROM verifications WHERE token ="' + req.query.token + '"', function(err, result) {
        if (err) throw err;
        
        var type
        var msg
        
        console.log(result[0].verify);
        
        if(result[0].verify == 0){
            if (result.length > 0) {
                var data = {
                    verify: 1
                }
                connection.query('UPDATE verifications SET ? WHERE email ="' + result[0].email + '"', data, function(err, result) {
                    if(err) throw err
                })
                type = 'success';
                msg = 'Your email has been verified';
            } 
            else {
                console.log('2');
                type = 'success';
                msg = 'The email has already verified';
            }
        }
        else{
            type = 'error';
            msg = 'The email has been already verified';
        }
        req.flash(type, msg);
        res.redirect('/');
    });
})

module.exports = router;

Step 5: Create Sending view

Create email sending view; so visit your app root directory and find the views directory. Then inside this directory create one file index.ejs. And add the following code into it:

<!DOCTYPE html>
<html>
    <head>
        <title>How to implement Email Verification using Node js, Express and MySQL?</title>
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
    </head>
    <body class="container mt-3">
        <% if (messages.error) { %>
        <p style="color:red"><%- messages.error %></p>
        <% } %>
        <% if (messages.success) { %>
        <p style="color:green"><%- messages.success %></p>
        <% } %>
        <h1 class="mb-3"><%= title %></h1>
        <form action="/send-email" 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>
            <input type="submit" class="btn btn-primary" value="Send Verification Link">
        </form>
    </body>
</html>

Step 6: Run Development Server

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

//run the below command
npm start

After running this command open your browser and hit

http://127.0.0.1:3000/

I hope it can help you...