User authentication is a vital part of many web applications, and building a secure login and registration API is a common requirement. In this step-by-step tutorial guide, you will learn how to create a Node.js API for user registration and login using MySQL as the database system.
Table of Contents #
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.
Prerequisites to Create this Project
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
Step 1: Project Setup
Create a new project folder and initialize it with npm. The project name is totally up to you and I called this folder nodejs-mysql-auth-api.
mkdir nodejs-mysql-auth-api
cd nodejs-mysql-auth-apiNext, initialize a new Node.js project:
npm init -yThis command creates a package.json file with default settings.
Step 2: Install the Necessary Node.js Packages
npm install express express-validator mysql2 dotenv jsonwebtoken bcryptexpress: is used for building the API.mysql2: MySQL client for Node.jsexpress-validator: is used for data validation.dotenv: It loads environment variables from a.envfile intoprocess.env.jsonwebtoken: An implementation of JSON Web Tokens.bcrypt: A library to help you hash passwords.
Step 3: Enable ES6 Import
In this project, we will use es6 import, so you have to add "type": "module" in your package.json like the following image.
Step 4: Setting Up MySQL Database and Its Tables
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;
Step 5: 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:
1. 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 here2. “dbConnection.js”: Database Connection
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();3. “tokenHandler.js” Token-Based Authentication
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.
// Importing required modules
import jwt from 'jsonwebtoken';
import { config } from 'dotenv';
// Loading environment variables from .env file
config();
// Function to generate JWT token
export const generateToken = (data, access = true) => {
// Selecting secret and expiry based on the type of token (access or refresh)
const secret = access
? process.env.ACCESS_TOKEN_SECRET
: process.env.REFRESH_TOKEN_SECRET;
const expiry = access
? process.env.ACCESS_TOKEN_EXPIRY
: process.env.REFRESH_TOKEN_EXPIRY;
// Generating token with provided data, secret, and expiry
return jwt.sign(data, secret, { expiresIn: parseInt(expiry) });
};
// Function to verify JWT token
export const verifyToken = (token, access = true) => {
// Selecting secret based on the type of token (access or refresh)
const secret = access
? process.env.ACCESS_TOKEN_SECRET
: process.env.REFRESH_TOKEN_SECRET;
try {
// Verifying token using the selected secret
return jwt.verify(token, secret);
} catch (err) {
// Handling token verification errors
return {
status: 401,
message: `Unauthorized: ${err.message}`,
};
}
};
4. “routes.js” Creating API Endpoints
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';
// Initialize router
const routes = Router({ strict: true });
// Token Validation Rule
const tokenValidation = (isRefresh = false) => {
// Define token type based on the flag
let refreshText = isRefresh ? 'Refresh' : 'Authorization';
return [
// Validate the presence and format of the token
header('Authorization', `Please provide your ${refreshText} token`)
.exists()
.not()
.isEmpty()
.custom((value, { req }) => {
// Check if token starts with 'Bearer' and has content
if (!value.startsWith('Bearer') || !value.split(' ')[1]) {
throw new Error(`Invalid ${refreshText} token`);
}
// Store token in request headers based on its type
if (isRefresh) {
req.headers.refresh_token = value.split(' ')[1];
return true;
}
req.headers.access_token = value.split(' ')[1];
return true;
}),
];
};
// Route for user registration
routes.post(
'/signup',
[
// Validation for user name
body('name')
.trim()
.not()
.isEmpty()
.withMessage('Name must not be empty.')
.isLength({ min: 3 })
.withMessage('Name must be at least 3 characters long')
.escape(),
// Validation for email and check if it's already registered
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;
}),
// Validation for password length
body('password')
.trim()
.isLength({ min: 4 })
.withMessage('Password must be at least 4 characters long'),
],
validate, // Middleware to handle validation errors
controller.signup // Controller function for signup
);
// Route for user login
routes.post(
'/login',
[
// Validation for email and check if it's registered
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;
}),
// Validation for password length
body('password', 'Incorrect Password').trim().isLength({ min: 4 }),
],
validate, // Middleware to handle validation errors
controller.login // Controller function for login
);
// Route to get user data by providing the access token
routes.get('/profile', tokenValidation(), validate, controller.getUser);
// Route to get new access and refresh token by providing the refresh token
routes.get(
'/refresh',
tokenValidation(true), // Validate refresh token
validate, // Middleware to handle validation errors
controller.refreshToken // Controller function for refreshing token
);
export default routes;
5. “controller.js” Routes Callbacks
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';
// Function to customize validation result formatter
const validation_result = validationResult.withDefaults({
formatter: (error) => error.msg,
});
// Middleware to handle validation errors
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();
};
// Function to fetch user data by email or ID
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 {
// Controller function for user signup
signup: async (req, res, next) => {
try {
const { name, email, password } = matchedData(req);
// Generate salt and hash the password
const saltRounds = 10;
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);
}
},
// Controller function for user login
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);
}
},
// Controller function to get user data by providing the access token
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);
// Fetch user by ID
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);
}
},
// Controller function to refresh access and refresh tokens
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);
// Convert refresh token to md5 format
const md5Refresh = createHash('md5')
.update(refreshToken)
.digest('hex');
// Find 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.',
});
}
// Generate new access and refresh tokens
const access_token = generateToken({ id: data.id });
const refresh_token = generateToken({ id: data.id }, false);
const newMd5Refresh = createHash('md5')
.update(refresh_token)
.digest('hex');
// Replace the old refresh token with the new one
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);
}
},
};
6. “app.js” Creating main application file
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}`);
});
Step 6: Testing the API
You can now start the application:
node app.jsYour 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.
1. User Sign Up (Registration) Testing:
POST - http://localhost:3000/api/signup
Payload:
{
"name":"User name",
"email":"[email protected]",
"password":"*****"
}
2. User Login Testing:
POST - http://localhost:3000/api/login
Payload:
{
"email":"[email protected]",
"password":"*****"
}
Response:
{
"status": 200,
"access_token": "eyJhbGciOiJIUzI1N****",
"refresh_token": "eyJhbGciOiJI****"
}
3. Get the User by Providing Access Token:
GET - http://localhost:3000/api/profile
Header - Authorization: "Bearer access_token"
4. Refresh the Token Testing:
GET - http://localhost:3000/api/refresh
Header - Authorization: "Bearer refresh_token"
Response: (New access and refresh token)
{
"status": 200,
"access_token": "eyJhbGciOiJIUzI1NiI****",
"refresh_token": "eyJhbGciOiJIUzI1NiI****"
}