Create a Grant Application
Tags: mysql, node, javascript, tutorial
Published: 2022-10-18 17:52:23

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_

&nbsp;

#### Folder structure for this project

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1fhtrwe4l74lxcxqmygn.PNG)

&nbsp;

#### Project setup
Initiate a npm project and install the following packages

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o4sz3ut9lpjvp9i55z9i.png)

&nbsp;

#### Setup your script in the `package.json` file
```javascript
//...
 "scripts": {
    "dev": "nodemon index.js",
    "start": "node index.js"
  },
//...
```

&nbsp;

#### :white_check_mark: Create config folder and db.config.js file

```javascript
// 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,
};

```

&nbsp;

#### :white_check_mark: Create a database in mysql
```javascript
mysql -u root -p
Enter password: *******
CREATE DATABASE db_name;
```

&nbsp;

#### :computer: Create user and grant model
In models/user.model.sql
```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
```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
```javascript

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);
  }
};
```
&nbsp;

#### :low_brightness: Login logic
```javascript
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);
  }
};
```
&nbsp;

#### :closed_lock_with_key: Create an authentication middleware to validate user token
Create a new file middleware/auth.js

```javascript
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 };
```

&nbsp;

#### :moneybag: Now let's create application logic to apply for a grant
```javascript

// 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);
  }
};

```

&nbsp;

#### :door: Route handler
```js
//...
const { validateUserToken, validateAdmin } = require('../middleware/auth');

const router = express.Router();

router.post('/signup', createNewUser);
router.post('/login', loginUser);
router.post('/grant', validateUserToken, applyForGrant);

//...

```

&nbsp;

#### Finally, we mount our route handler in our main entry file, `index.js`

```javascript
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}`);
});
```

&nbsp;

#### 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](https://github.com/drsimplegraffiti/apply-for-grant)

:blush: Thank you for reading