User login and registration using nodejs and mysql with example

Posted 7 years ago By Ajay Gupta 675439

User login and registration using nodejs and mysql with example

In this tutorial, I am going to create simple email and password login authentication and register a user using nodejs and mysql.

I will save simple password in mysql database but this is not good practice for security reason so in next tutorial, you will know the use of BCrypt module of Node.js to encrypt passwords.

This tutorial will explain only how to save a record in mysql table and how to check email exist or not in the table with given password.

To handle post parameters of Http request in Node.js, we use Body-Parser module.

Step1: Table and directory structure

In first step, create a "users" table in the database by running following command in phpmyadmin or in mysql shell :

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

Now i will have to setup directory structure in following way :

├── controllers
│ └── authenticate-controller.js
│ └── register-controller.js
├── node_modules
├── config.js
├── index.js
└── package.json

├── controllers
│ └── authenticate-controller.js
│ └── register-controller.js
├── node_modules
├── config.js
├── index.js
└── package.json

In Node.js package.json file is used to install all the dependencies.

{
  "name": "login",
  "version": "1.0.0",
  "description": "login authentication",
  "main": "index.js",
  "dependencies": {
    "body-parser": "^1.17.1",
    "express": "^4.14.1",
    "jsonwebtoken": "^7.3.0",
    "mysql": "^2.13.0"
  },
  "devDependencies": {},
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC"
}

Step 2: Config.js to configure database connectivity

In this step, i will create mysql connection to work with database.

config.js

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'test'
});
connection.connect(function(err){
if(!err) {
    console.log("Database is connected");
} else {
    console.log("Error while connecting with database");
}
});
module.exports = connection;

var mysql      = require('mysql');

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'test'
});
connection.connect(function(err){
if(!err) {
    console.log("Database is connected");
} else {
    console.log("Error while connecting with database");
}
});
module.exports = connection;

Step 3: index.js

Now start with index.js file that is entry point of the application.

index.js

var express=require("express");
var bodyParser=require('body-parser');
var app = express();
var authenticateController=require('./controllers/authenticate-controller');
var registerController=require('./controllers/register-controller');
app.use(bodyParser.urlencoded({extended:true}));
app.use(bodyParser.json());
/* route to handle login and registration */
app.post('/api/register',registerController.register);
app.post('/api/authenticate',authenticateController.authenticate);
app.listen(8012);

var express=require("express");
var bodyParser=require('body-parser');

var app = express();

var authenticateController=require('./controllers/authenticate-controller');
var registerController=require('./controllers/register-controller');

app.use(bodyParser.urlencoded({extended:true}));
app.use(bodyParser.json());
/* route to handle login and registration */
app.post('/api/register',registerController.register);
app.post('/api/authenticate',authenticateController.authenticate);

app.listen(8012);

Step 4: Create Register Controller

In this step, I will register a user in database so that i can login in the application with register user.

controller/register-controller.js

var connection = require('./../config');
module.exports.register=function(req,res){
    var today = new Date();
    var users={
        "name":req.body.name,
        "email":req.body.email,
        "password":req.body.password,
        "created_at":today,
        "updated_at":today
    }
    connection.query('INSERT INTO users SET ?',users, function (error, results, fields) {
      if (error) {
        res.json({
            status:false,
            message:'there are some error with query'
        })
      }else{
          res.json({
            status:true,
            data:results,
            message:'user registered sucessfully'
        })
      }
    });
}

var connection = require('./../config');

module.exports.register=function(req,res){
	var today = new Date();
	var users={
	    "name":req.body.name,
	    "email":req.body.email,
	    "password":req.body.password,
	    "created_at":today,
	    "updated_at":today
	}
	connection.query('INSERT INTO users SET ?',users, function (error, results, fields) {
	  if (error) {
		res.json({
			status:false,
			message:'there are some error with query'
		})
	  }else{
	  	res.json({
			status:true,
			data:results,
			message:'user registered sucessfully'
		})
	  }
	});
}

In above code, i run a simple insert query of mysql to save user details in database with created date using date function.

Step 5: Create Authenticate Controller

In this step, i will validate user credentials.

controllers/authenticate-controller.js

var connection = require('./../config');
module.exports.authenticate=function(req,res){
    var email=req.body.email;
    var password=req.body.password;
    connection.query('SELECT * FROM users WHERE email = ?',[email], function (error, results, fields) {
      if (error) {
          res.json({
            status:false,
            message:'there are some error with query'
            })
      }else{
        if(results.length >0){
            if(password==results[0].password){
                res.json({
                    status:true,
                    message:'successfully authenticated'
                })
            }else{
                res.json({
                  status:false,
                  message:"Email and password does not match"
                 });
            }
         
        }
        else{
          res.json({
              status:false,    
            message:"Email does not exits"
          });
        }
      }
    });
}

var connection = require('./../config');

module.exports.authenticate=function(req,res){
	var email=req.body.email;
	var password=req.body.password;
	connection.query('SELECT * FROM users WHERE email = ?',[email], function (error, results, fields) {
	  if (error) {
		  res.json({
			status:false,
			message:'there are some error with query'
			})
	  }else{
	    if(results.length >0){
	    	if(password==results[0].password){
				res.json({
					status:true,
					message:'successfully authenticated'
				})
	    	}else{
	    		res.json({
				  status:false,
		          message:"Email and password does not match"
		         });
	    	}
	     
	    }
	    else{
	      res.json({
	      	status:false,	
	        message:"Email does not exits"
          });
	    }
	  }
	});
}

In above code, i check first if email exists in the database and then check password.

Before going with testing with the help of postman or other tools, start server from the command prompt first :

node index.js

Click here to know how to store encrypted password using bcrypt module in Node.js

Hashing passwords with Bcrypt and node.js

In our next tutorial, you will learn login system with registering a JWT token and verify token with each request after authentication.

User login and registration using nodejs and mysql with example

User login and registration using nodejs and mysql with example

Tags :

Related Posts

Node.js Tutorial: A Beginner’s Guide

How to Install Node.js on Window ?

How to create first application in Node.js in Windows Environment

How to connect mysql database in node js with crud query example?

Nodemon reload the server on code change automatically

Generate JWT token after login and verify with Node.js API

Hashing passwords with Bcrypt and node.js

Node.js calculate datetime difference in hours minutes and seconds moment.js

Route Middleware to Check if a User has active subscription in Node.js

How To send emails using Node.js with Nodemailer npm

What is difference between Callback and Promise in Javascript with example