Handling user login and registration using nodejs and mysql

Image source: Pixabay

Nodejs has seen a dramatic increase in adoption in the backend side since it allows javascript usage in the backend along with frontend. It is usually advisable to use NoSQL databases like MongoDB, Couchbase, and aerospike but there might be a need to use MySQL databases with nodejs until you successfully migrate your MySQL to NoSQL stack.

In today’s tutorial, we are going to create a basic email login authentication system using nodejs and MySQL.

If you need to set up MySQL server on ubuntu or mac and create a connection between nodejs and MySQL then you can follow the basic tutorial here.

The steps are as follows:

First, we need to create a new table in the database for registering new users. Drop into MySQL shell from the terminal using the following command:

mysql -u root -p
mysql> show databases
 -> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cloudprint |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> use cloudprint
Database changed

Now that we have selected required database lets create users table in the table by running following command in MySQL shell:

CREATE TABLE `users` (
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Exit the MySQL shell using the following command:

mysql> \c
mysql> exit

In the last tutorial, we had set up a basic connection with MySQL in server.js file which we are going to use in this tutorial.First, you need to set up a directory structure like this in the project’s folder:

.
├── node_modules
├── package.json
├── routes
│ └── loginroutes.js
└── server.js

Here routes folder will contain all route handlers for api requests and loginroutes will contain handlers specific to login and registration tasks.

Here is package.json for reference and installing all the dependencies:

{
  "name": "loginbackend",
  "version": "1.0.0",
  "description": "A simple server for handling user login",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "node.js"
  ],
  "author": "Saurabh Mhatre",
  "license": "ISC",
  "dependencies": {
    "bcrypt": "4.0.1",
    "body-parser": "1.19.0",
    "cors": "2.8.5",
    "dotenv": "8.2.0",
    "express": "4.17.1",
    "jsonfile": "6.0.1",
    "mysql": "2.18.1"
  }
}

Let's start with server.js code which is as follows:

var express    = require("express");
var login = require('./routes/loginroutes');
var bodyParser = require('body-parser');
var app = express();
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
    next();
});
var router = express.Router();
// test route
router.get('/', function(req, res) {
    res.json({ message: 'welcome to our upload module apis' });
});
//route to handle user registration
router.post('/register',login.register);
router.post('/login',login.login)
app.use('/api', router);
app.listen(4000);

We are using express framework and bodyparser as middleware on line 6 for parsing incoming requests as JSON. The access controls allow origin headers on line 9 are essential for our server to serve cross-domain requests. We are using a built-in router from express for handling api requests. I have defined two apis for register and login on lines 22 and 23.

Now, let's start with handler functions in loginroutes.js:

First, we create a MySQL connection using specifying the database of choice:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'your-password',
  database : 'mockUsers'
});
connection.connect(function(err){
if(!err) {
    console.log("Database is connected ... nn");
} else {
    console.log("Error connecting database ... nn");
}
});

Then we create a handler for user registration:

exports.register = async function(req,res){
  const password = req.body.password;
  const encryptedPassword = await bcrypt.hash(password, saltRounds)
  var users={
     "email":req.body.email,
     "password":encryptedPassword
   }
  
  connection.query('INSERT INTO users SET ?',users, function (error, results, fields) {
    if (error) {
      res.send({
        "code":400,
        "failed":"error ocurred"
      })
    } else {
      res.send({
        "code":200,
        "success":"user registered sucessfully"
          });
      }
  });
}

Here we run a simple insertion query into MySQL for adding new users to the database. I have used bcrypt npm module for encrypting user passwords.

Next is a handler for user login and validating user credentials:

exports.login = async function(req,res){
  var email= req.body.email;
  var password = req.body.password;
  connection.query('SELECT * FROM users WHERE email = ?',[email], async function (error, results, fields) {
    if (error) {
      res.send({
        "code":400,
        "failed":"error ocurred"
      })
    }else{
      if(results.length >0){
        const comparision = await bcrypt.compare(password, results[0].password)
        if(comparision){
            res.send({
              "code":200,
              "success":"login sucessfull"
            })
        }
        else{
          res.send({
               "code":204,
               "success":"Email and password does not match"
          })
        }
      }
      else{
        res.send({
          "code":206,
          "success":"Email does not exits"
            });
      }
    }
    });
}

First, we check if the email exists in the database and then check the password with bcrypt hash for allowing successful login.

You can use tools like postman from chrome apps for sending post requests to server after starting it using the command:

node server.js

Here is postman request format for user registration and login:

Registration:
url:localhost:4000/api/register
payload:{
"email":"sm2@example.com",
"password":"pass123"
  }
payload type:raw (Application/json)
Login:
url:localhost:4000/api/login
payload:{
	"email":"sm2@example.com",
	"password":"pass123"
}
payload type:raw (Application/json)

We still have to cover user session management but I will cover it a different article since it requires coverage of some more concepts. For time being can find an excellent article for session management with express-session npm module here:- Article

Bonus Tip:

MySql Workbench

Mysql workbench is a great tool for managing MySQL databases in ubuntu. In order to install and run MySQL workbench run the following commands:

sudo apt-get install mysql-workbench
mysql-workbench &

dotenv

dotenv is an npm package to store critical project credentials out of the code and in an external .env file which is not uploaded in online repos. I have made use of such .env file in my project to move database-related credentials out of project’s source code.

You can find the complete source code for the project in my GitHub repo here:- Codeclassifiers GitHub

Connect Deeper

Kindly mention your feedback in the comments and check out my other tutorials here: Technoetics or follow me on twitter