JWT Authentication with NodeJS and MySQL
Rahul Chaudhary7 min read
Table of contents
Concept: Using Refresh Token-based Authentication with JWT
Folder Structure
First Step: Environment Steup install required software (NodeJs, MySql or MongoDB, npm, git etc.
Create a folder and inside the folder initialize the npm to create a node js project(cmd: npm init).
Copy and Paste the following code into the package.json file it has all the information about all packages we are going to use. After that using cmd (npm install ) inside your project folder will install all the packages and dependencies.
database Schema
-- User Table Schema CREATE TABLE `authdb`.`users` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(250) NOT NULL, `email` VARCHAR(250) NOT NULL, `role` ENUM('user', 'admin', 'super-user') NULL DEFAULT user, UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE, PRIMARY KEY (`id`)); -- userToken Table CREATE TABLE `authdb`.`usertoken` ( `id` INT NOT NULL AUTO_INCREMENT, `userId` INT NULL, `token` VARCHAR(500) NULL, `createdAt` TIMESTAMP(2) NULL, PRIMARY KEY (`id`), INDEX `userId_idx` (`userId` ASC) VISIBLE, CONSTRAINT `userId` FOREIGN KEY (`userId`) REFERENCES `authdb`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE);
{
"name": "backend",
"version": "1.0.0",
"description": "",
"main": "app.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": "nodemon app.js"
},
"author": "",
"license": "ISC",
"dependencies": {
"bcryptjs": "^2.4.3",
"cors": "^2.8.5",
"dotenv": "^16.0.3",
"express": "^4.18.2",
"jsonwebtoken": "^9.0.0",
"morgan": "^1.10.0",
"mysql2": "^3.1.2",
"nodemon": "^2.0.20"
}
}
Create a main file (app.js) file to create your and setup express server and Database connection and create routes.
"use strict";
require("dotenv").config();
const express = require("express");
const cors = require('cors')
const pool = require("./config/db");
const app = express();
const PORT = process.env.PORT || 5000;
// Middlewares to parse json data into express server
app.use(express.json());
// Middlewares to accept cross origin requests express server
const corsOptions = {
origin: "*",
credentials: true,
optionSuccessStatus: 200,
};
app.use(cors(corsOptions));
// Connect Database
pool.getConnection(function (err, conn) {
// Do something with the connection
if (err) throw err;
console.log("Database Connected");
});
// Routes
app.use('/api/user', require('./routes/userRoutes'))
// Test Route
app.get("/test", (req, res) => {
res.json({
message: "Api is running.",
});
});
app.listen(PORT, () => console.log(`Server is running on ${PORT}`));
Create a db.js file for database connection inside the config folder
const mysql = require('mysql2');
// Create the connection pool. The pool-specific settings are the defaults
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
waitForConnections: true,
connectionLimit: 10,
maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
queueLimit: 0
});
module.exports = pool;
Create Your userRoutes.js file inside the routes folder to create different endpoints for handling HTTPS/HTTP requests
const express = require('express');
const router = express.Router()
const { userRegister, userLogin, userLogout,getNewAcessToken, addPost } = require('../controllers/userController');
const {authMiddleWare} = require('../services/authServices')
router.post('/register', userRegister)
router.post('/login', userLogin)
router.post('/getNewToken', getNewAcessToken)
router.get('/getPost', authMiddleWare, addPost)
router.delete('/logout', userLogout)
module.exports = router;
Create a userController.js file to write an implementation of your logic in the controller's folder
const pool = require("../config/db");
const bcrypt = require("bcryptjs");
const { findUser } = require("../services/userServices");
const {
genrateToken,
verifyRefreshToken,
} = require("../services/authServices");
let posts = [
{
id: 1,
userId: 1,
text: "Post 1",
role:"admin"
},
{
id: 2,
userId: 1,
text: "Post 2",
role:"admin"
},
{
id: 3,
userId: 2,
text: "Post 3",
role:"user"
},
{
id: 4,
userId: 1,
text: "Post 4",
role:"super-admin"
},
];
const userController = {
userRegister: async (req, res) => {
try {
// find user already exists or not
let user = await findUser({
email: req.body.email,
});
if (user) {
return res.status(403).json({
message: "User Already Exists.",
});
}
// Not exist then create a recoerd in to user
const { name, email, password, role } = req.body;
let insetUserQuery =
"INSERT INTO users (`name`, `email`, `password`, `role`) VALUES (?)";
const salt = bcrypt.genSaltSync(10);
const hashPassword = bcrypt.hashSync(password, salt);
let t_role = !role ? "user" : role;
let values = [name, email, hashPassword, t_role];
pool.query(insetUserQuery, [values], (err, data) => {
if (err) res.json(err);
console.log("created");
return res.status(200).json({
message: "User Created Successfully",
data: data,
});
});
} catch (err) {
return res.status(500).json({
message: "Server Error",
data: err,
});
}
},
userLogin: async (req, res) => {
try {
//Find User
let findUserQuery = "SELECT * FROM users WHERE email = ?";
pool.query(findUserQuery, [req.body.email], (err, data) => {
if (err) {
res.json(err);
}
// User found
if (data.length <= 0) {
return res.status(409).json({ message: "User does not Exists" });
}
const checkPassword = bcrypt.compareSync(
req.body.password,
data[0].password
);
if (!checkPassword) {
return res.status(400).json({
message: "Worng Username and Password",
});
}
// genrate tokens
const user = {
id: data[0].id,
role: data[0].role,
};
genrateToken(user).then((data) => {
return res.status(200).json({
tokens: data,
message: "User Login Sucessfull",
});
});
});
} catch (err) {
return res.status(500).json({
message: "Server Error",
data: err,
});
}
},
getNewAcessToken: async (req, res) => {
try {
verifyRefreshToken(req.body.refreshToken).then((data) => {
return res.status(200).json({
message: "Token Refreshed",
data: data,
});
});
} catch (err) {
return res.status(500).json({
message: "Server Error",
data: err,
});
}
},
userLogout: async (req, res) => {
try {
// Find Token And Remove It from Database
let delTokenQuery = "DELETE FROM userToken WHERE token = ?";
pool.query(delTokenQuery, [req.body.refreshToken], (err, data) => {
if (err) throw err;
return res.status(200).json({
message: "User Logout Sucessfully",
});
});
} catch (err) {
return res.status(500).json({
message: "Server Error",
data: err,
});
}
},
addPost: async(req, res) =>{
try {
let filtedPost = posts.filter(item => item.role === req.user.role);
if(filtedPost)
return res.status(200).json(filtedPost);
} catch (err) {
return res.status(500).json({
message: "Server Error",
data: err,
});
}
}
};
module.exports = userController;
Create a services folder for necessary operations (database queries) it is not necessary you can do it inside your controller but I recommend it do it because it looks a lot cleaner and is reusable.
const jwt = require("jsonwebtoken");
const pool = require("../config/db");
const authServices = {
genrateToken: async (user) => {
try {
let tokens = null;
const { id, role } = user;
const payload = {
id: id,
role: role,
};
const accessToken = jwt.sign(payload, process.env.ACCESS_TOKEN_SECRET, {
expiresIn: "60m",
});
const refreshToken = jwt.sign(payload, process.env.REFRESH_TOKEN_SECRET, {
expiresIn: "14d",
});
// check user alredy has refresh token saved
let findTokenQuery = "SELECT * FROM userToken WHERE userId = ?";
pool.query(findTokenQuery, [user.id], (err, data) => {
if (err) return res.json(err);
// find any refresh token conatined with given
if (data.length > 0) {
// remove existing token
let delTokenQuery = "DELETE FROM userToken WHERE userId = ?";
pool.query(delTokenQuery, [data[0].userId], (err, data1) => {
if (err) throw err;
console.log("Existing token removed");
});
}
// Add new Token to database
let insertTokenQuery =
"INSERT INTO userToken (`userId`, `token`) VALUES (?)";
let values = [user.id, refreshToken];
pool.query(insertTokenQuery, [values], (err, data) => {
if (err) throw err;
console.log("new Token Added");
});
});
tokens = {
accessToken: accessToken,
refreshToken: refreshToken,
};
console.log(tokens);
return tokens;
} catch (err) {
console.log(err);
}
},
verifyRefreshToken: async (refreshToken) => {
try {
// Find refresh token exist or not
let data = null;
let findTokenQuery = "SELECT * FROM userToken WHERE token = ?";
pool.query(findTokenQuery, [refreshToken], (err, data) => {
if (err) throw err;
console.log("found");
});
data = jwt.verify(refreshToken, process.env.REFRESH_TOKEN_SECRET);
console.log(data);
if (!data) {
throw "Dteails not found";
}
console.log(data);
return data;
} catch (err) {
console.log(err);
}
},
authMiddleWare: async (req, res, next) => {
const authHeader = req.headers['authorization'];
const token = authHeader && authHeader.split(' ')[1];
console.log("middle",token)
if (token === null || !token)
return res.status(401).json({
message: "There is no token in header",
});
const user = jwt.verify(token, process.env.ACCESS_TOKEN_SECRET);
if (!user) {
return res.status(401).json({
message: "Token is Expired Login Again.",
});
}
req.user = user;
console.log(user)
next();
},
};
module.exports = authServices;
userServices.js
const pool = require("../config/db");
const userServices = {
findUser: async ({ email }) => {
let resObj = false;
let findUserQuery = "SELECT * FROM users WHERE email = ?";
pool.query(findUserQuery, [email], (err, data) => {
if (err) {
resObj = false;
throw err;
}
if (data.length > 0) {
resObj = true;
}
});
return resObj;
},
};
module.exports = userServices;
NOW WE CAN ACCESS LOGIN AND REGISTRATION ROUTES AND VERIFY TOKEN ROUTES.
Advantage: Our access token will expire in 1 hour but we do not want to log in again and again so we provide a refresh token which will give again a new access token after every hour If the refresh token expires then the user has to login again to generate new refresh token.
2
Subscribe to my newsletter
Read articles from Rahul Chaudhary directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by