In this tutorial, I'll show you how to build an application that allows users to apply for grants. This post will demonstrate concepts such as connecting to a MySQL database, SQL association, password hashing, and code base modularization.
Stack used: MySQL, NodeJS, Express, Nodemailer
Folder structure for this project
Project setup
Initiate a npm project and install the following packages
Setup your script in the package.json file
//...
"scripts": {
"dev": "nodemon index.js",
"start": "node index.js"
},
//...
:white_check_mark: Create config folder and db.config.js file
// config/db.config.js
const dotenv = require('dotenv');
dotenv.config();
module.exports = {
HOST: process.env.DB_HOST,
USER: process.env.DB_USER,
PASSWORD: process.env.DB_PASSWORD,
DB: process.env.DB_NAME,
PORT: process.env.DB_PORT,
};
:white_check_mark: Create a database in mysql
mysql -u root -p
Enter password: *******
CREATE DATABASE db_name;
:computer: Create user and grant model
In models/user.model.sql
CREATE TABLE IF NOT EXISTS users (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(255) NOT NULL,
gender enum('Male', 'Female') DEFAULT 'Male' NOT NULL,
dob DATE NOT NULL,
role enum('Admin', 'User') DEFAULT 'User',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
In models/grant.model.sql
CREATE TABLE IF NOT EXISTS grants (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT(11) UNSIGNED NOT NULL,
grant_name VARCHAR(255) NOT NULL,
grant_type VARCHAR(255) NOT NULL,
grant_amount VARCHAR(255) NOT NULL,
grant_description VARCHAR(255) NOT NULL,
grant_status enum('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
:loudspeaker: In the mysql shell, you can run the two models to create the user and grants table
:low_brightness: Application logic for authentication
In the controllers folders, create user.controller.js file
exports.createNewUser = async (req, res, next) => {
try {
const { email, username, phone, gender, password, dob } = req.body;
// check if email already exists
const [row] = await db.query('SELECT * FROM users WHERE email = ?', [
email,
]);
if (row.length > 0) {
return res.status(409).json({
status: 400,
message: 'Email already exists',
});
}
// hash password
const hashedPassword = await passwordHash(password);
const user = await db.query(
'INSERT INTO users (email, username, phone,password, gender, dob) VALUES (?,?,?,?,?,?)',
[email, username, phone, hashedPassword, gender, dob]
);
let data = {
id: user[0].insertId,
email,
username,
phone,
};
const token = await jwtSign(data);
// send email
const subject = 'Welcome to AB Code';
const text = `Hi ${username}, welcome to AB Code. We are glad to have you on board.`;
await sendEmail({ email, subject, text });
return responseHandler(res, 200, 'User created successfully', {
token,
data,
});
} catch (error) {
next(error);
}
};
:low_brightness: Login logic
exports.loginUser = async (req, res, next) => {
try {
const { email, password } = req.body;
const [user] = await db.query('SELECT * FROM users WHERE email = ?', [
email,
]);
if (user.length === 0) {
return res.status(401).json({
status: 401,
message: 'Invalid email or password',
});
}
const isPasswordValid = await passwordCompare(password, user[0].password);
if (!isPasswordValid) {
return res.status(401).json({
status: 401,
message: 'Invalid email or password',
});
}
let data = {
id: user[0].id,
email: user[0].email,
username: user[0].username,
phone: user[0].phone,
role: user[0].role,
};
const token = await jwtSign(data);
return responseHandler(res, 200, 'Login successful', {
token,
data,
});
} catch (error) {
next(error);
}
};
:closed_lock_with_key: Create an authentication middleware to validate user token
Create a new file middleware/auth.js
const { jwtVerify } = require('../lib/jwt');
const customError = require('../utils/customError');
const validateUserToken = async (req, res, next) => {
try {
// console.log(req.headers.authorization);
const token = req.headers.authorization.split(' ')[1];
if (!token) return res.status(401).json({ message: 'Unauthorized' });
const decoded = await jwtVerify(token);
if (!decoded) {
throw new Error('Invalid token');
}
req.user = decoded;
console.log('===req.user');
console.log(req.user);
console.log('===req.user');
next();
} catch (e) {
return res.status(401).json({ message: 'Unauthorized...' });
}
};
const validateAdmin = (req, res, next) => {
try {
if (req.user.role !== 'Admin') {
// use custom error statusCode, message, data
throw new customError(
401,
'You are not authorized to perform this action',
[]
);
}
next();
} catch (e) {
next(e);
}
};
module.exports = { validateUserToken, validateAdmin };
:moneybag: Now let's create application logic to apply for a grant
// Apply for grant
exports.applyForGrant = async (req, res, next) => {
try {
const {
grant_name,
grant_type,
grant_amount,
grant_description,
grant_status,
} = req.body;
const { id } = req.user;
// check if grant already exists
const [row] = await db.query(
'SELECT * FROM grants WHERE grant_name = ? AND user_id = ?',
[grant_name, id]
);
if (row.length > 0) {
return res.status(409).json({
status: 400,
message: 'Grant already exists',
});
}
const grant = await db.query(
'INSERT INTO grants (grant_name, grant_type, grant_amount, grant_description, grant_status, user_id) VALUES (?,?,?,?,?,?)',
[
grant_name,
grant_type,
grant_amount,
grant_description,
grant_status,
id,
]
);
let data = {
id: grant[0].insertId,
grant_name,
grant_type,
grant_amount,
grant_description,
grant_status: 'Pending',
};
// send email
const subject = 'Grant Application';
const text = `Hi ${req.user.username}, your grant application has been received. We will get back to you shortly.`;
await sendEmail({ email: req.user.email, subject, text });
return responseHandler(res, 200, 'Grant created successfully', {
data,
});
} catch (error) {
next(error);
}
};
:door: Route handler
//...
const { validateUserToken, validateAdmin } = require('../middleware/auth');
const router = express.Router();
router.post('/signup', createNewUser);
router.post('/login', loginUser);
router.post('/grant', validateUserToken, applyForGrant);
//...
Finally, we mount our route handler in our main entry file, index.js
const express = require('express');
const cors = require('cors');
const connection = require('./database/db');
const dotenv = require('dotenv');
dotenv.config();
const userRoutes = require('./routes/user.routes.js');
const app = express();
const corsOptions = {
origin: 'http://localhost:7878',
optionsSuccessStatus: 200,
};
app.use(cors(corsOptions));
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.get('/', (req, res) => {
res.send('Hello World');
});
app.use('/api/users', userRoutes);
const port = process.env.PORT || 7878;
// 404 global error handler
app.use((req, res, next) => {
const error = new Error('Not Found');
error.status = 404;
next(error);
});
// global error handler
app.use((error, req, res, next) => {
res.status(error.status || 500);
res.json({
error: {
message: error.message,
},
});
});
app.listen(port, async () => {
console.log(`Server is running on port ${port}`);
});
Conclusion
I hope this post was useful in demonstrating concepts such as connecting to a MySQL database, associating different models, folder structure, and so on.
Get the source code here: source code
:blush: Thank you for reading