Lab-2-5

(2% of the course mark)

MySQL MVC Express Lab

• In this lab, students will refactor the Express MVC app that was built in Lab 1-3. The lab will focus on integrating the mysql2 package into the Express MVC app.

Lab objectives

• Learn the technique of properly in integrating the mysql2 package into the Express MVC app.

• Use the factory / singleton pattern to build and share database connection and querying capabilites.

• Use connection pooling effectively.

Build Schema

1. Open MySQL Workbench and run the following SQL commands:

-- Drop the database if it exists
DROP DATABASE IF EXISTS BACKEND_DB;

-- Create the database
CREATE DATABASE BACKEND_DB;

-- Use the database
USE BACKEND_DB;

-- Create the USERS table
CREATE TABLE USERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    USER_NAME VARCHAR(50) UNIQUE NOT NULL,
    EMAIL VARCHAR(50) UNIQUE NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a row into the USERS table
INSERT INTO
USERS (USER_NAME, EMAIL)
VALUES
('bill.gates', 'bill.gates@microsoft.com'),
('elon.musk', 'elon.musk@tesla.com');

-- Verify
SELECT * FROM USERS;

Database Objects

Ensure that all the database objects are created successfully before proceeding to the next step.

Create Node.js Express app

1. Open VSCode and create a folder named MySql-Express-App.

2. Open the terminal and change the directory to MySql-Express-App.

3. Initialize the app by running the following command:

npm init -y

4. Install typescript by running the following commands:

npm install typescript -D
npm install @types/node -D

5. Install mysql2 by running the following command:

npm install mysql2

6. Initialize tsconfig.json by running the following command:

npx tsc --init

7. Modify tsconfig.json and uncomment outDir.

8. Modify tsconfig.json and add the following property:

"removeComments": true

9. Install nodemon by running the following command:

npm install nodemon -D

10. Install dotenv by running the following command:

npm install dotenv

11. Create an .env file in the root of your project and enter the following.

PORT=3000
SQL_HOST=localhost
SQL_USER=root
SQL_PASSWORD=password
SQL_DB_NAME=BACKEND_DB
SQL_CONNECTION_LIMIT=10

12. Install express by running the following commands:

npm install express
npm install @types/express -D

13. Configure package.json by adding a new entry to the scripts property.

"build": "tsc",
"start": "nodemon ./dist/index",

14. Configure package.json by adding a new property.

"type": "module"

15. Create a file named index.ts and add the following code:

// Developer:
// Purpose:

import * as dotenv from "dotenv";
dotenv.config();

import express, { type Request, type Response } from "express";

const PORT: number = process.env.PORT ? parseInt(process.env.PORT) : 3000;
const app = express();

app.all("/{*catchAll}", (req: Request, res: Response) => {
  res.status(404).json({
    message: `Invalid route (${req.url}) or HTTP method (${req.method}).`,
  });
});

app.listen(PORT, () => {
  console.log(`Server is running at http://localhost:${PORT}`);
  process.on("SIGTERM", shutdown);
  process.on("SIGINT", shutdown);
});

function shutdown() {
  console.log(`Server is shutting down.`);
  process.exit(0);
}

16. Build the app by running the following command:

npm run build

17. Start the app by running the following command:

npm run start

Express app status

Verify that your Express app starts without errors before proceeding to the next step.

Express app MVC

1. In the root folder of the app, create a folder named lib.

2. In the lib folder, create a file named DBConnectionFields.ts and add the following code:

// Developer:
// Purpose:

export class DBConnectionFields {
  host: string = "";
  user: string = "";
  password: string = "";
  database: string = "";
  connectionLimit: number = 0;

  constructor(
    host: string,
    user: string,
    password: string,
    database: string,
    connectionLimit: number
  ) {
    this.host = host;
    this.user = user;
    this.password = password;
    this.database = database;
    this.connectionLimit = connectionLimit;
  }
}

3. In the lib folder, create a file named MySqlDB.ts and add the following code:

// Developer:
// Purpose:

import mysql, { type Pool } from "mysql2/promise";
import { DBConnectionFields } from "./DBConnectionFields.js";

export class MySqlDB {
  dbConnectionFields: DBConnectionFields | null = null;
  pool: Pool | null = null;

  constructor() {}

  setDbConnectionFields(dbConnectionFields: DBConnectionFields) {
    this.dbConnectionFields = dbConnectionFields;
  }

  async connect() {
    let connectResponse = {
      isConnected: false,
      message: "",
    };

    try {
      if (this.dbConnectionFields != null) {
        this.pool = mysql.createPool({
          host: this.dbConnectionFields.host,
          user: this.dbConnectionFields.user,
          password: this.dbConnectionFields.password,
          database: this.dbConnectionFields.database,
          waitForConnections: true,
          connectionLimit: this.dbConnectionFields.connectionLimit,
        });

        connectResponse.isConnected = true;
        connectResponse.message = `Connected to: ${this.dbConnectionFields.database}.`;
      }
    } catch (error: any) {
      connectResponse.message = `Could not connect to: ${this.dbConnectionFields?.database}.`;
    } finally {
      return connectResponse;
    }
  }

  async query(sqlStatement: string, options: [] = []) {
    let queryResult: any;

    try {
      if (this.pool != null) {
        queryResult = await this.pool.execute(sqlStatement, options);
      }
    } catch (error: any) {
      console.log("code:", error.code);
      console.log("errno:", error.errno);
      console.log("sqlState:", error.sqlState);
      console.log("sqlMessage:", error.sqlMessage);
      console.log("sql:", error.sql);
    } finally {
      return queryResult;
    }
  }

  async disConnect() {
    let disConnectResponse = {
      isDisconnected: false,
      message: "",
    };

    try {
      if (this.pool != null) {
        await this.pool.end();
      }

      disConnectResponse.isDisconnected = true;
      disConnectResponse.message = `Disconnected to: ${this.dbConnectionFields?.database}.`;
    } catch (error) {
      disConnectResponse.message = `Could not disconnect to: ${this.dbConnectionFields?.database}.`;
    } finally {
      return disConnectResponse;
    }
  }
}

// This cached by node and returns the same instance all the time, acts like a singleton pattern
export const mySqlDB = new MySqlDB();

4. In the root folder of the app, create a folder named models.

5. In the models folder, create a file named userModel.ts and add the following code:

// Developer:
// Purpose:

import { mySqlDB } from "../lib/MySqlDB.js";

export interface User {
  id: number;
  userName: string;
  email: string;
  createdAt: Date;
}

export const UserModel = {
  findAll: async (): Promise<User[]> => {
    const queryResult = await mySqlDB.query("SELECT * FROM USERS");

    if (queryResult) {
      if (queryResult[0]) {
        const mapResult = queryResult[0].map((user: any) => ({
          id: user.ID,
          userName: user.USER_NAME,
          email: user.EMAIL,
          createdAt: new Date(user.CREATED_AT),
        }));

        return mapResult;
      }
    }

    return [];
  },

  findById: async (id: number): Promise<User | undefined> => {
    const options = [id];
    const queryResult = await mySqlDB.query(
      "SELECT * FROM USERS WHERE ID = ?",
      options as any
    );

    if (queryResult) {
      if (queryResult[0]) {
        const mapResult = queryResult[0].map((user: any) => ({
          id: user.ID,
          userName: user.USER_NAME,
          email: user.EMAIL,
          createdAt: new Date(user.CREATED_AT),
        }));

        return mapResult[0];
      }
    }

    return undefined;
  },

  findByUserName: async (userName: string): Promise<User | undefined> => {
    const options = [userName];
    const queryResult = await mySqlDB.query(
      "SELECT * FROM USERS WHERE USER_NAME = ?",
      options as any
    );

    if (queryResult) {
      if (queryResult[0]) {
        const mapResult = queryResult[0].map((user: any) => ({
          id: user.ID,
          userName: user.USER_NAME,
          email: user.EMAIL,
          createdAt: new Date(user.CREATED_AT),
        }));

        return mapResult[0];
      }
    }

    return undefined;
  },

  create: async (userName: string, email: string): Promise<boolean> => {
    const options = [userName, email];
    const queryResult = await mySqlDB.query(
      "INSERT INTO USERS (USER_NAME, EMAIL) VALUES(?, ?)",
      options as any
    );

    if (queryResult) {
      if (queryResult[0]?.affectedRows) {
        return true;
      }
    }

    return false;
  },

  update: async (
    id: number,
    userName: string,
    email: string
  ): Promise<boolean> => {
    const options = [userName, email, id];
    const queryResult = await mySqlDB.query(
      "UPDATE USERS SET USER_NAME = ?, EMAIL = ? WHERE ID = ?",
      options as any
    );

    if (queryResult) {
      if (queryResult[0]?.affectedRows) {
        return true;
      }
    }

    return false;
  },

  delete: async (id: number): Promise<boolean> => {
    const options = [id];

    const queryResult = await mySqlDB.query(
      "DELETE FROM USERS WHERE ID = ?",
      options as any
    );

    if (queryResult) {
      if (queryResult[0]?.affectedRows) {
        return true;
      }
    }

    return false;
  },
};

6. In the root folder of the app, create a folder named services.

7. In the services folder, create a file named userService.ts and add the following code:

// Developer:
// Purpose:

import { UserModel } from "../models/userModel.js";

export const UserService = {
  getAll: async () => await UserModel.findAll(),
  getById: async (id: number) => await UserModel.findById(id),
  getByUserName: async (userName: string) =>
    await UserModel.findByUserName(userName),
  create: async (userName: string, email: string) =>
    await UserModel.create(userName, email),
  update: async (id: number, userName: string, email: string) =>
    await UserModel.update(id, userName, email),
  delete: async (id: number) => await UserModel.delete(id),
};

8. In the root folder of the app, create a folder named controllers.

9. In the controllers folder, create a file named userController.ts and add the following code:

// Developer:
// Purpose:

import { type Request, type Response } from "express";
import { UserService } from "../services/userService.js";

export const UserController = {
  getAll: async (req: Request, res: Response): Promise<void> => {
    res.json(await UserService.getAll());
  },

  getById: async (req: Request, res: Response): Promise<void> => {
    const user = await UserService.getById(Number(req.params.id));
    user ? res.json(user) : res.status(404).json({ message: "User not found" });
  },

  getByUserName: async (req: Request, res: Response): Promise<void> => {
    const user = await UserService.getByUserName(String(req.params.userName));
    user ? res.json(user) : res.status(404).json({ message: "User not found" });
  },

  create: async (req: Request, res: Response): Promise<void> => {
    const { userName, email } = req.body;
    if (!userName) {
      res.status(400).json({ message: "userName is required" });
      return;
    }

    if (!email) {
      res.status(400).json({ message: "email is required" });
      return;
    }

    (await UserService.create(userName, email))
      ? res.status(201).send()
      : res.status(500).json({
          message: "Internal Server Error, contact the system administrator",
        });
  },

  update: async (req: Request, res: Response): Promise<void> => {
    const { userName, email } = req.body;
    (await UserService.update(Number(req.params.id), userName, email))
      ? res.status(204).send()
      : res.status(404).json({ message: "User not found" });
  },

  delete: async (req: Request, res: Response): Promise<void> => {
    (await UserService.delete(Number(req.params.id)))
      ? res.status(204).send()
      : res.status(404).json({ message: "User not found" });
  },
};

10. In the root folder of the app, create a folder named routes.

11. In the routes folder, create a file named userRouter.ts and add the following code:

// Developer:
// Purpose:

import { Router } from "express";
import { UserController } from "../controllers/userController.js";

const router = Router();

router.get("/", UserController.getAll);
router.get("/:id", UserController.getById);
router.get("/username/:userName", UserController.getByUserName);
router.post("/", UserController.create);
router.put("/:id", UserController.update);
router.delete("/:id", UserController.delete);

export default router;

12. Update index.ts and add the following code:

// Developer:
// Purpose:

import * as dotenv from "dotenv";
dotenv.config();

import express, { type Request, type Response } from "express";
const PORT: number = process.env.PORT ? parseInt(process.env.PORT) : 3000;

import { mySqlDB } from "./lib/MySqlDB.js";
import { DBConnectionFields } from "./lib/DBConnectionFields.js";
import userRouter from "./routes/userRouter.js";

const app = express();
const dBConnectionFields = new DBConnectionFields(
  String(process.env.SQL_HOST),
  String(process.env.SQL_USER),
  String(process.env.SQL_PASSWORD),
  String(process.env.SQL_DB_NAME),
  Number(process.env.SQL_CONNECTION_LIMIT)
);

mySqlDB.setDbConnectionFields(dBConnectionFields);
const connectResponse = await mySqlDB.connect();
console.log(connectResponse);

app.use(express.json());
app.use("/api/users", userRouter);

app.all("/{*catchAll}", (req: Request, res: Response) => {
  res.status(404).json({
    message: `Invalid route (${req.url}) or HTTP method (${req.method}).`,
  });
});

app.listen(PORT, () => {
  console.log(`Server is running at http://localhost:${PORT}`);
  process.on("SIGTERM", shutdown);
  process.on("SIGINT", shutdown);
});

async function shutdown() {
  console.log(`Server is shutting down.`);
  const disConnectResponse = await mySqlDB.disConnect();
  console.log(disConnectResponse);
  process.exit(0);
}

13. Build the app by running the following command:

npm run build

Postman testing

1. Use Postman to perform the following testing scenarios:

Method	Endpoint	Screenshot
GET	/api/users	get-all.png
GET	/api/users/valid-user-id	get-one01.png
GET	/api/users/invalid-user-id	get-one02.png
GET	/api/users/userName/valid-user-name	get-one03.png
GET	/api/users/userName/invalid-user-name	get-one04.png
POST	/api/users	post.png
PUT	/api/users/valid-user-id	put01.png
PUT	/api/users/invalid-user-id	put02.png
DELETE	/api/users/valid-user-id	delete.png

Sample request body for POST and PUT

{
  "userName": "firstname.lastname",
  "email": "firstname.lastname@email.com"
}

Implement Lab-2-3 Library app (MySql-Express-App)

1. Modify the .env file of the MySql-Express-App and update the value of SQL_DB_NAME to the name of the library app schema.

SQL_DB_NAME=NAME_OF_LIBRARY_APP_SCHEMA

2. Refactor MySql-Express-App and create new routes, controllers, services, and models:

• Allow patrons to borrow and return books.

• View current book inventory levels.

3. Build and run the modified app and use Postman to test the scenarios above and name the screenshots as borrow.png, return.png and inventory.png.

Submission

1. Create a folder named submit.

Delete node_modules

Delete the node_modules folder on any app that you are submitting.

2. Copy the MySql-Express-App folder and all the screenshots (get-all.png, get-one01.png, get-one02.png, get-one03.png, get-one04.png, post.png, put01.png, put02.png, delete.png, borrow.png, return.png and inventory.png) to the submit folder.

3. Create a zip file of the submit folder.

4. Navigate back to where the lab was originally downloaded, there should be a Submissions section (see below) where the zip file can be uploaded.