How to Login REST API using Node js Express MySQL?

Hello Friends,

In this tutorial, you will learn how to create Node js Login and Registration RESTful API using MySQL DB. The purpose of this tutorial is to give an idea to create this type of authentication-based API application using Node JS.

This article is the first part of a two-part series to create a complete login system with Node.js and Vue.js. For this, we use Node.js as the backend and Vue.js as front end. you will learn MySQL. You need some steps to build a rest API with node.js.

Step 1: Create Database And Table

Execute the following command on the terminal to create a database and table:

CREATE DATABASE node-app
 
CREATE TABLE users (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    email varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    password varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY email (email)
) 

ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Step 2: Create Node Express js App

Execute the following command on the terminal to create the node js app:

mkdir nodejs-auth-rest-api-mysql

cd nodejs-auth-rest-api-mysql

npm init -y

Step 3: Connect App to Database

Create dbConnection.js file into your app root directory add the following code into it to connect your node js express app to the database:

var mysql = require('mysql');

var conn = mysql.createConnection({
    host: 'localhost', // Replace with your host name
    user: 'root',      // Replace with your database username
    password: '',      // Replace with your database password
    database: 'my-node' // Replace with your database Name
}); 
 
conn.connect(function(err) {
    if (err) throw err;
    console.log('Database is connected successfully !');
});

module.exports = conn;

Step 4: Install express and required Modules

Execute the following command on the terminal to install express express-validator MySQL body-parser JSON web token bcryptjs cors into your node js express app:

npm install express express-validator mysql body-parser jsonwebtoken bcryptjs cors --save

Step 5: Create Server.js File

Create server.js file and import express express-validator MySQL body-parser JSON web token bcryptjs cors into your server.js file; as shown below:

const createError = require('http-errors');
const express = require('express');
const path = require('path');
const bodyParser = require('body-parser');
const cors = require('cors');
const indexRouter = require('./router.js');
 
const app = express();
 
app.use(express.json());
 
app.use(bodyParser.json());
 
app.use(bodyParser.urlencoded({
    extended: true
}));
 
app.use(cors());
 
app.use('/api', indexRouter);
 
// Handling Errors
app.use((err, req, res, next) => {
    // console.log(err);
    err.statusCode = err.statusCode || 500;
    err.message = err.message || "Internal Server Error";
    res.status(err.statusCode).json({
        message: err.message,
    });
});
 
app.listen(3000,() => console.log('Server is running on port 3000'));

Step 6: Create Validation.js, Router.js, and dbConnection.js

Create validation.js and router.js. So visit your app root directory and create these files.

Then add the following code into your validation.js file:

const { check } = require('express-validator');
 
exports.loginValidation = [
    check('email', 'Please include a valid email').isEmail().normalizeEmail({ gmail_remove_dots: true }),
    check('password', 'Password must be 6 or more characters').isLength({ min: 6 })
]

Then add the following code to your router.js file:

const express = require('express');
const router = express.Router();
const db  = require('./dbConnection');
const { signupValidation, loginValidation } = require('./validation');
const { validationResult } = require('express-validator');
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
 
 
router.post('/login', loginValidation, (req, res, next) => {
    db.query(
        `SELECT * FROM users WHERE email = ${db.escape(req.body.email)};`,
        (err, result) => {
            
            // user does not exists
            if (err) {
                throw err;
                return res.status(400).send({
                    msg: err
                });
            }
            if (!result.length) {
                return res.status(401).send({
                    msg: 'Email or password is incorrect!'
                });
            }
      
            // check password
            bcrypt.compare(
                req.body.password,
                result[0]['password'],
                (bErr, bResult) => {
                    
                    // wrong password
                    if (bErr) {
                        throw bErr;
                        return res.status(401).send({
                            msg: 'Email or password is incorrect!'
                        });
                    }
                    if (bResult) {
                        const token = jwt.sign({id:result[0].id},'the-super-strong-secrect',{ expiresIn: '1h' });
                        db.query(
                            `UPDATE users SET last_login = now() WHERE id = '${result[0].id}'`
                        );
                        return res.status(200).send({
                            msg: 'Logged in!',
                            token,
                            user: result[0]
                        });
                    }
                    return res.status(401).send({
                        msg: 'Username or password is incorrect!'
                    });
                }
            );
        }
    );
});
 
module.exports = router;

login route – When you call this route on the postman app with email and password; it will return a jwt token. Which is used to call the get-user method.

Step 7: Start Node Express Js App Server

Execute the following command on the terminal to start the node express js server:

//run the below command

nodemon server.js

After running this command open your browser and hit

http://127.0.0.1:3000/api/login

Step 8: Test Rest Apis with PostMan App

Test node js experss + mysql user login api with Postman app:

Test node js experss + mysql user login api with Postman app:

POST - http://localhost:3000/api/login

I hope it can help you...