How to Build Node.js Login and Registration API with MySQL DB

User authentication is a vital part of many web applications, and building a secure login and registration API is a common requirement. In this guide, we will create a Node.js API for user registration and login using MySQL as the database system.

Table of Contents #

1. Introduction
2. Prerequisites
3. Project Setup
4. Install the necessary packages
5. Enable ES6 Import
6. Setting Up MySQL Database
7. Writing the code for Login and Registration API
   • Let’s start by creating the “.env” file
   • Database Connection “dbConnection.js”
   • Token-Based Authentication “tokenHandler.js”
   • Creating API Endpoints “routes.js”
   • Routes Callbacks “controller.js”
   • Creating main application file “app.js”
8. Testing the API
9. Download the source code

1. Introduction

User authentication is the process of verifying the identity of a user, which is crucial for web applications that require user-specific information or data protection.

We will create a Node.js API that facilitates user registration and login, utilizing MySQL to store user information and JSON Web Tokens (JWT) for secure authentication.

2. Prerequisites

Before you begin, ensure you have the following prerequisites in place:

• Node.js and npm installed
• A MySQL server set up and running
• A code editor (e.g., Visual Studio Code)
• Basic knowledge of JavaScript and Node.js

3. Project Setup

Create a new project folder and initialize it with npm:

mkdir nodejs-mysql-auth-api
cd nodejs-mysql-auth-api

Next, initialize a new Node.js project:

npm init -y

This command creates a package.json file with default settings.

4. Install the necessary packages

npm install express express-validator mysql2 dotenv jsonwebtoken bcrypt

• express: is used for building the API.
• mysql2: MySQL client for Node.js
• express-validator: is used for data validation.
• dotenv: It loads environment variables from a .env file into process.env.
• jsonwebtoken: An implementation of JSON Web Tokens.
• bcrypt: A library to help you hash passwords.

5. Enable ES6 Import

In this project, we will use es6 import, so you have to add "type": "module" in the package.json.

6. Setting Up MySQL Database

First, create a database with the name you want, I named it node_auth_api.

In the database we need to create two tables users and refresh_tokens.

• users: Storing the users information like name, email, password, etc.
• refresh_tokens: Storing refresh tokens that will be considered as whitelisted refresh tokens.

Now use the following SQL code to create the “users” table and the “refresh_tokens” table with their structure and relations.

Hey, If you don’t know how to use the following SQL code to create tables, then see this – How to Run SQL code on a Specific Database using phpMyAdmin?

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `refresh_tokens` (
  `user_id` int(11) NOT NULL,
  `token` varchar(35) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `email` varchar(40) NOT NULL,
  `password` varchar(70) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `refresh_tokens`
  ADD KEY `user_id` (`user_id`),
  ADD KEY `token` (`token`);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `refresh_tokens`
  ADD CONSTRAINT `refresh_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

7. Writing the code for Login and Registration API

After successfully setup your database, It’s time to write codes to build this Node.js Login API.

Before starting, let’s take a look on the node-mysql-auth-api folder structure, so you will know what files we need to create:

Let’s start by creating the “.env” file

The .env file contains all the secret information that we do not want to reveal in the code, like database information, secret keys, etc.

In Node, we will use the dotenv package to load this information to our code.

ACCESS_TOKEN_SECRET=avSjekkd4526Dkeo586Dhjdsf52ba
ACCESS_TOKEN_EXPIRY=1200 # In seconds (1200 = 20 minutes)
REF_TOKEN_SECRET=j82dDJKE3643LIEJ253DjEL35223dHek52ed
REF_TOKEN_EXPIRY=86400 # In seconds (86400 = 24 hr | 1 Day)

DB_HOST=localhost
DB_NAME=node_api # Replace with your DB name
DB_USER=root # Replace with your DB user
DB_PASSWORD= #add your DB password here

Database Connection “dbConnection.js”

Learn: How to make MySQL database connection with Node.js?

import mysql from 'mysql2';
import { config } from 'dotenv';

const connection = () => {
    config();
    const { DB_HOST, DB_NAME, DB_USER, DB_PASSWORD } = process.env;
    return mysql.createPool({
        host: DB_HOST,
        user: DB_USER,
        password: DB_PASSWORD,
        database: DB_NAME,
    });
};

export default connection().promise();

Token-Based Authentication “tokenHandler.js”

Implement token-based authentication to secure specific routes, ensuring that only authenticated users can access them. In this project we will use JSON Web Tokens (JWT) for this purpose.

The tokenHandler.js is responsible for creating new tokens (access and refresh), and verifying the tokens.

import jwt from 'jsonwebtoken';
import { config } from 'dotenv';
config();

export const generateToken = (data, access = true) => {
    const secret = access
        ? process.env.ACCESS_TOKEN_SECRET
        : process.env.REF_TOKEN_SECRET;
    const expiry = access
        ? process.env.ACCESS_TOKEN_EXPIRY
        : process.env.REF_TOKEN_EXPIRY;
    return jwt.sign(data, secret, { expiresIn: parseInt(expiry) });
};

export const verifyToken = (token, access = true) => {
    const secret = access
        ? process.env.ACCESS_TOKEN_SECRET
        : process.env.REF_TOKEN_SECRET;
    try {
        return jwt.verify(token, secret);
    } catch (err) {
        return {
            status: 401,
            message: `Unauthorized: ${err.message}`,
        };
    }
};

Creating API Endpoints “routes.js”

The routes.js files contains all endpoints (/signup, /login, /profile, /refresh) with validations.

import { Router } from 'express';
import { body, header } from 'express-validator';
import controller, { validate, fetchUserByEmailOrID } from './controller.js';

const routes = Router({ strict: true });

// Token Validation Rule
const tokenValidation = (isRefresh = false) => {
    let refreshText = isRefresh ? 'Refresh' : 'Authorization';

    return [
        header('Authorization', `Please provide your ${refreshText} token`)
            .exists()
            .not()
            .isEmpty()
            .custom((value, { req }) => {
                if (!value.startsWith('Bearer') || !value.split(' ')[1]) {
                    throw new Error(`Invalid ${refreshText} token`);
                }
                if (isRefresh) {
                    req.headers.refresh_token = value.split(' ')[1];
                    return true;
                }
                req.headers.access_token = value.split(' ')[1];
                return true;
            }),
    ];
};

// Register a new User
routes.post(
    '/signup',
    [
        body('name')
            .trim()
            .not()
            .isEmpty()
            .withMessage('Name must not be empty.')
            .isLength({ min: 3 })
            .withMessage('Name must be at least 3 characters long')
            .escape(),
        body('email', 'Invalid email address.')
            .trim()
            .isEmail()
            .custom(async (email) => {
                const isExist = await fetchUserByEmailOrID(email);
                if (isExist.length)
                    throw new Error(
                        'A user already exists with this e-mail address'
                    );
                return true;
            }),
        body('password')
            .trim()
            .isLength({ min: 4 })
            .withMessage('Password must be at least 4 characters long'),
    ],
    validate,
    controller.signup
);

// Login user through email and password
routes.post(
    '/login',
    [
        body('email', 'Invalid email address.')
            .trim()
            .isEmail()
            .custom(async (email, { req }) => {
                const isExist = await fetchUserByEmailOrID(email);
                if (isExist.length === 0)
                    throw new Error('Your email is not registered.');
                req.body.user = isExist[0];
                return true;
            }),
        body('password', 'Incorrect Password').trim().isLength({ min: 4 }),
    ],
    validate,
    controller.login
);

// Get the user data by providing the access token
routes.get('/profile', tokenValidation(), validate, controller.getUser);

// Get new access and refresh token by providing the refresh token
routes.get(
    '/refresh',
    tokenValidation(true),
    validate,
    controller.refreshToken
);

export default routes;

Routes Callbacks “controller.js”

Now we need to create controller.js that contains all the callbacks (that handle all the requests) defined in the routes.js.

import bcrypt from 'bcrypt';
import { createHash } from 'crypto';
import { validationResult, matchedData } from 'express-validator';
import { generateToken, verifyToken } from './tokenHandler.js';
import DB from './dbConnection.js';

const validation_result = validationResult.withDefaults({
    formatter: (error) => error.msg,
});

export const validate = (req, res, next) => {
    const errors = validation_result(req).mapped();
    if (Object.keys(errors).length) {
        return res.status(422).json({
            status: 422,
            errors,
        });
    }
    next();
};

// If email already exists in database
export const fetchUserByEmailOrID = async (data, isEmail = true) => {
    let sql = 'SELECT * FROM `users` WHERE `email`=?';
    if (!isEmail)
        sql = 'SELECT `id` ,`name`, `email` FROM `users` WHERE `id`=?';
    const [row] = await DB.execute(sql, [data]);
    return row;
};

export default {
    signup: async (req, res, next) => {
        try {
            const { name, email, password } = matchedData(req);

            const saltRounds = 10;
            // Hash the password
            const hashPassword = await bcrypt.hash(password, saltRounds);

            // Store user data in the database
            const [result] = await DB.execute(
                'INSERT INTO `users` (`name`,`email`,`password`) VALUES (?,?,?)',
                [name, email, hashPassword]
            );
            res.status(201).json({
                status: 201,
                message: 'You have been successfully registered.',
                user_id: result.insertId,
            });
        } catch (err) {
            next(err);
        }
    },

    login: async (req, res, next) => {
        try {
            const { user, password } = req.body;
            const verifyPassword = await bcrypt.compare(
                password,
                user.password
            );
            if (!verifyPassword) {
                return res.status(422).json({
                    status: 422,
                    message: 'Incorrect password!',
                });
            }

            // Generating Access and Refresh Token
            const access_token = generateToken({ id: user.id });
            const refresh_token = generateToken({ id: user.id }, false);

            const md5Refresh = createHash('md5')
                .update(refresh_token)
                .digest('hex');

            // Storing refresh token in MD5 format
            const [result] = await DB.execute(
                'INSERT INTO `refresh_tokens` (`user_id`,`token`) VALUES (?,?)',
                [user.id, md5Refresh]
            );

            if (!result.affectedRows) {
                throw new Error('Failed to whitelist the refresh token.');
            }
            res.json({
                status: 200,
                access_token,
                refresh_token,
            });
        } catch (err) {
            next(err);
        }
    },

    getUser: async (req, res, next) => {
        try {
            // Verify the access token
            const data = verifyToken(req.headers.access_token);
            if (data?.status) return res.status(data.status).json(data);
            // fetching user by the `id` (column)
            const user = await fetchUserByEmailOrID(data.id, false);
            if (user.length !== 1) {
                return res.status(404).json({
                    status: 404,
                    message: 'User not found',
                });
            }
            res.json({
                status: 200,
                user: user[0],
            });
        } catch (err) {
            next(err);
        }
    },

    refreshToken: async (req, res, next) => {
        try {
            const refreshToken = req.headers.refresh_token;
            // Verify the refresh token
            const data = verifyToken(refreshToken, false);
            if (data?.status) return res.status(data.status).json(data);

            // Converting refresh token to md5 format
            const md5Refresh = createHash('md5')
                .update(refreshToken)
                .digest('hex');

            // Finding the refresh token in the database
            const [refTokenRow] = await DB.execute(
                'SELECT * from `refresh_tokens` WHERE token=?',
                [md5Refresh]
            );

            if (refTokenRow.length !== 1) {
                return res.json({
                    status: 401,
                    message: 'Unauthorized: Invalid Refresh Token.',
                });
            }

            // Generating new access and refresh token
            const access_token = generateToken({ id: data.id });
            const refresh_token = generateToken({ id: data.id }, false);

            const newMd5Refresh = createHash('md5')
                .update(refresh_token)
                .digest('hex');

            // Replacing the old refresh token to new refresh token
            const [result] = await DB.execute(
                'UPDATE `refresh_tokens` SET `token`=? WHERE `token`=?',
                [newMd5Refresh, md5Refresh]
            );

            if (!result.affectedRows) {
                throw new Error('Failed to whitelist the Refresh token.');
            }

            res.json({
                status: 200,
                access_token,
                refresh_token,
            });
        } catch (err) {
            next(err);
        }
    },
};

Creating main application file “app.js”

app.js: The root of the app where the execution of the application will start.

import express from 'express';
import dbConnection from './dbConnection.js';
import routes from './routes.js';
const app = express();
const port = process.env.PORT || 3000;

// Middleware to parse JSON requests
app.use(express.json());

app.get('/', (req, res) => {
    res.send('Hello World!');
});
app.use('/api', routes);
app.use((err, req, res, next) => {
    err.statusCode = err.statusCode || 500;
    err.message = err.message || 'Internal Server Error';
    res.status(err.statusCode).json({
        message: err.message,
    });
});

// If database is connected successfully, then run the server
dbConnection
    .getConnection()
    .then(() => {
        app.listen(port, () => {
            console.log(`Server is running on port ${port}`);
        });
    })
    .catch((err) => {
        console.log(`Failed to connect to the database: ${err.message}`);
    });

8. Testing the API

You can now start the application:

node app.js

Your Login API is now running on http://localhost:3000. You can use tools like Postman or Thunder Client VS Code extension to interact with the API.

9. Download the source code