Node js + Express + MySQL + Email Verification Tutorial

Node js + Express + MySQL + Email Verification Tutorial

Email verification in Node js + Express + MySQL+ nodemailer; Through this tutorial, you will learn how to send email verification link in e-mail in node js + express js + MySQL database. And verify email by link.

If you are registering the user in your node jas app from the registration form and you want to verify the user’s email. So for this, you have to send the verification link to the user on his given email. And the user will verify his email.

In this tutorial, verification mail will be sent to the user by using Gmail SMTP + nodemailer. And you will also explain how to configure Gmail SMTP connection with your node js app.

Email Verification in Node js + Express + MySQL

  • Step 1: Install Node Express App JS
  • Step 2: Connect Node Express JS App with DB
  • Step 3: Import Packages in app.js
  • Step 4: Create Email Sending And Verification Route
    • Email Send Function with Gmail nodemailer configuration
    • Email sending route with Verification Link
    • Email Verification route
  • Step 5: Create Email Sending view
  • Step 6: Run Development Server

Step 1: Install Node Express App JS

Execute the following command on terminal to install 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 pacakges, so open again your cmd 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 made a session as like in PHP. In this node js mysql crud tutorial, 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. In 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 got started back 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 table into 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 into 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: '[email protected]',
        to: email,
        subject: 'Email verification - Tutsmake.com',
        html: '<p>You requested for email verification, kindly use this <a href="http://localhost:3000/verify-email?token=' + token + '">link</a> to verify your email address</p>'

    };

    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: '[email protected]',
to: email,
subject: 'Email verification - Tutsmake.com',
html: '<p>You requested for email verification, kindly use this <a href="http://localhost:3000/verify-email?token=' + token + '">link</a> to verify your email address</p>'
};
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 views directory. Then inside this directory create one file index.ejs. And add the following code into it:

<!DOCTYPE html>
<html>
<head>
<title><%= title %></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 run this command open your browser and hit 
http://127.0.0.1:3000/

If you found any error, while sending email with Gmail SMTP; So you can follow the these two steps and resolve it:

  • You have to enable the Gmail service to use it in third-party apps. In case you miss to do so, we may face such error. To resolve this error just login in Gmail account and enable less secure apps using this link https://myaccount.google.com/lesssecureapps
  • self signed certificate in certificate chain.
    • Execute this command on termianl:
      • npm config set strict-ssl false –globalset
      • NODE_TLS_REJECT_UNAUTHORIZED=0

Conclusion

Email verification in Node js + Express + MySQL; Through this tutorial, you will learn how to send email verification link in e-mail in node js + express js + MySQL database. And verify email by link.

Recommended Node js Tutorials

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *