CRUD Rest API with Express, TypeOrm and Jest for Testing — part 1

Let’s take a note along the way

Today I am going to share how to create an Express API using Typescript and TypeOrm as our mysql ORM. We will also add integration test using Jest and supertest.

Just a disclaimer, this is not a professional tutorial. Just some notes and steps some random nobody took during their process learning this topic. So this might not be the best or most efficient article on whole Medium and the codes will often get scrapped and replaced. But I do hope I can share my process of learning this to the public out there!

Yeah, this might be how it looks when we are learning something

Setting up the project

Let’s start by creating the directory for our project, we will start with making directory and initializing the npm project. Let’s create a directory called TypeOrm-Express then we will initialize the npm project with npm init.

initializing our first npm project

Next we will start by installing the dependencies and development dependencies that we will need

npm i express mysql2 typeorm dotenv reflect-metadata

npm i -D @types/express @types/node nodemon ts-node typescript

Our package.json should pretty much became like this now

{
  "name": "typeorm-express",
  "version": "1.0.0",
  "description": "this is a simple rest api using typeorm and express",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "andre",
  "license": "ISC",
  "dependencies": {
    "dotenv": "^16.3.1",
    "express": "^4.18.2",
    "mysql2": "^3.6.2",
    "reflect-metadata": "^0.1.13",
    "typeorm": "^0.3.17"
  },
  "devDependencies": {
    "@types/express": "^4.17.20",
    "@types/node": "^20.8.10",
    "nodemon": "^3.0.1",
    "ts-node": "^10.9.1",
    "typescript": "^5.2.2"
  }
}

Next we will create tsconfig.json file which will mount the configurations for our typescript application.

{
  "compilerOptions": {
    "module": "commonjs",
    "esModuleInterop": true,
    "target": "es6",
    "moduleResolution": "node",
    "outDir": "dist",
    "baseUrl": ".",
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
  },
  "include": ["src"],
  "exclude": ["node_modules"]
}

We will be using nodemon for live reload so we should create nodemon.json configuration.

{
  "watch": ["src"],
  "ext": "ts",
  "exec": "ts-node src/index.ts"
}

Then we will add the script to run nodemon to our package.json

"scripts": {
    "dev": "nodemon",
    "test": "echo \"Error: no test specified\" && exit 1"
  },

Now let’s create src directory and we will create app.ts and index.ts. app.ts will be where our application resides while index.ts will serve as the runner.

app.ts

import express, { Express } from "express";

// Create Express app
const app: Express = express();

// Middleware
app.use(express.json());

// Routes
app.get("/", async (req, res) => {
  res.send("It works, congratulations");
});

export default app;

index.ts

import app from "./app";

// Read the port from environment variables or use a default port
const port = process.env.PORT || 3000;

// Start the server
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

Now you should see this message

[nodemon] restarting due to changes…

[nodemon] starting `ts-node src/index.ts`

Server is running on port 3000

Go ahead and access http://localhost:3000/ and you should see the message “It works, congratulations“ being displayed. Congratulations you have created your first express server application using typescript.

Now we will start configuring typeorm, let’s create databaseManager.ts for our typeOrm instance

import "reflect-metadata";
import { DataSource, DataSourceOptions } from "typeorm";

class DatabaseManager {
  private datasourceOptions: DataSourceOptions = null;
  private dataSource: DataSource = null;

  constructor(private config: DataSourceOptions) {
    this.datasourceOptions = config;
  }

  getDataSource() {
    return this.dataSource;
  }

  async initializeDataSource() {
    this.dataSource = new DataSource({
      ...this.datasourceOptions,
      entities: [],
    });

    await this.dataSource
      .initialize()
      .then(async () => {
        console.log("Data source initialized");
      })
      .catch((error) => console.log(error));
  }
}

export default DatabaseManager;

Lets import and use those in our index.ts

import { DataSourceOptions } from "typeorm";
import app from "./app";
import DatabaseManager from "./databaseManager";

// Read the port from environment variables or use a default port
const port = process.env.PORT || 3000;

// Initialize data source option
const datasourceOptions: DataSourceOptions = {
  type: "mysql",
  host: "localhost",
  port: 3306,
  username: "root",
  password: "root",
  database: "typeormdb",
  synchronize: true, // only set to true in development
  logging: false,
};

// Initialize databaseManager object and initialize data source
const dbManager: DatabaseManager = new DatabaseManager(datasourceOptions);
dbManager.initializeDataSource();

// Start the server
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

We will run our app in development mode by running this command

npm run dev

Now as you can see if we have not created the database yet we will face this exception

Server is running on port 3000

Error: Unknown database ‘typeormdb’

at Packet.asError (D:\TypeOrm-Express\node_modules\mysql2\lib\packets\packet.js:728:17)

at ClientHandshake.execute (D:\TypeOrm-Express\node_modules\mysql2\lib\commands\command.js:29:26)

at PoolConnection.handlePacket (D:\TypeOrm-Express\node_modules\mysql2\lib\connection.js:478:34)

at PacketParser.onPacket (D:\TypeOrm-Express\node_modules\mysql2\lib\connection.js:97:12)

at PacketParser.executeStart (D:\TypeOrm-Express\node_modules\mysql2\lib\packet_parser.js:75:16)

at Socket.<anonymous> (D:\TypeOrm-Express\node_modules\mysql2\lib\connection.js:104:25)

at Socket.emit (node:events:390:28)

at Socket.emit (node:domain:475:12)

at addChunk (node:internal/streams/readable:315:12)

at readableAddChunk (node:internal/streams/readable:289:9) {

code: ‘ER_BAD_DB_ERROR’,

errno: 1049,

sqlState: ‘42000’,

sqlMessage: “Unknown database ‘typeormdb’”,

sql: undefined

}

Let’s handle that, we will create new method in our databaseManager.ts. We will utilize mysql2 to create the database if it does not exists.

public async initializeDataSource() {
    this.dataSource = new DataSource({
      ...this.datasourceOptions,
      entities: [],
    });

    await this.dataSource
      .initialize()
      .then(async () => {
        console.log("Data source initialized");
      })
      .catch(async (error) => {
        if (error.code === "ER_BAD_DB_ERROR") {
          await this.createDatabaseIfNotExists();
          await this.initializeDataSource();
        } else {
          console.error("Error creating database:", error);
          throw error;
        }
      });
  }

  private async createDatabaseIfNotExists() {
    console.log("DATABASE DOES NOT EXISTS, CREATING DATABASE");
    const access: ConnectionOptions = {
      user: "root",
      password: "root",
      database: "mysql",
    };

    const conn = await mysql.createConnection(access);
    await conn.query(
      `CREATE DATABASE IF NOT EXISTS ${this.datasourceOptions.database}`
    ); 
    await conn.end();
  }

After this change, our databaseManager.ts will try to create the database then reinitialize the connection if the earlier initialization fails.

Let’s also change the code a bit to use local environment variables instead of hardcoded value. While we’re at it let’s also change the DataSourceOptions to also use environment variables values.

First we’ll create .env file like this

PORT=3000

ADMIN_USER=root
ADMIN_PASSWORD=root
ADMIN_DEFAULT_DB=mysql

DB_HOST=localhost
DB_PORT=3306
DB_USERNAME=root
DB_PASSWORD=root
DB_NAME=typeormdb
DB_SYNCHRONIZE=true
DB_LOGGING=false

Then we’ll modify createDatabaseIfNotExists() method inside databaseManager.ts to use those environment variables.

private async createDatabaseIfNotExists() {
    console.log("DATABASE DOES NOT EXISTS, CREATING DATABASE");
    const access: ConnectionOptions = {
      user: process.env.ADMIN_USER,
      password: process.env.ADMIN_PASSWORD,
      database: process.env.ADMIN_DEFAULT_DB,
    };

    const conn = await mysql.createConnection(access);
    await conn.query(
      `CREATE DATABASE IF NOT EXISTS ${this.datasourceOptions.database}`
    );
    await conn.end();
  }

We will also modify index.ts to use environment variables instead of using hardcodes.

import dotenv from 'dotenv';
// Load environment variables from .env file
dotenv.config();

...
// Rest of the code
...

const datasourceOptions: DataSourceOptions = {
  type: "mysql",
  host: process.env.DB_HOST,
  port: process.env.DB_PORT ? parseInt(process.env.DB_PORT, 10) : 3306,
  username: process.env.DB_HOST,
  password: process.env.DB_HOST,
  database: process.env.DB_NAME,
  synchronize:
    process.env.DB_SYNCHRONIZE &&
    process.env.DB_SYNCHRONIZE.toLowerCase() === "true",
  logging:
    process.env.DB_LOGGING && process.env.DB_LOGGING.toLowerCase() === "true",
};

Structuring Our Express App

We will structure our project like this

│   app.ts
│   databaseManager.ts
│   index.ts
│
└───modules
    │   models.ts
    │
    ├───expense
    │       expense.controller.ts
    │       expense.model.ts
    │       expense.routes.ts
    │
    └───user
            user.controller.ts
            user.model.ts
            user.routes.ts

Of course you can also structure your working directory and group it by functionalities like this.

│   app.ts
│   databaseManager.ts
│   index.ts
│
└───controllers
│       expense.controller.ts
│       user.controller.ts
└───models
│       expense.model.ts
│       models.ts
│       user.model.ts
└───routes
        expense.routes.ts
        user.routes.ts

Feel free to adopt whichever that works for you. But for this article I will be using the former which is just my personal preferences. But should you adopt the latter then don’t forget to modify accordingly, especially the import paths.

Creating TypeORM Data Models

Now let’s create our entities. We will be using Active Record model approach using our typeOrm. You can also use DataMapper approach since TypeOrm support both.

// user.model.ts
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  OneToMany,
  BaseEntity,
} from "typeorm";

import Expense from "../expense/expense.model";

export interface IUser {
  id: number;
  name: string;
  created_at: Date;
  updated_at: Date;
  expense: Expense[];
}

@Entity()
export default class User extends BaseEntity implements IUser {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @CreateDateColumn()
  created_at: Date;

  @UpdateDateColumn()
  updated_at: Date;

  @OneToMany(() => Expense, (expense) => expense.user)
  expense: Expense[];
}

// expense.model.ts
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  ManyToOne,
  BaseEntity,
} from "typeorm";

import User from "../user/user.model";

export enum ExpenseType {
  EXPENSE = "expense",
  INCOME = "income",
}

export interface IExpense {
  id?: number;
  amount: number;
  note: string | null;
  type: ExpenseType;
  created_at?: Date;
  updated_at?: Date;
  user?: User;
  userId?: number;
}

@Entity()
export default class Expense extends BaseEntity {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  amount: number;

  @Column({ nullable: true })
  note: string;

  @Column({ type: "enum", enum: ExpenseType, default: ExpenseType.EXPENSE })
  type: ExpenseType;

  @CreateDateColumn()
  created_at: Date;

  @UpdateDateColumn()
  updated_at: Date;

  @Column({ nullable: true })
  userId: number;

  @ManyToOne(() => User, (user) => user.expense)
  @JoinColumn({ name: "userId" })
  user: User;
}

Now let’s create another file named models.ts inside src/modules

// models.ts
export * from "./user/user.model";
export * from "./expense/expense.model";

As you can see, we have defined our models in User and Expense class then we will use a centralized models.ts that imports and re-export them for ease of access.

We also tried using two different types of @PrimaryGeneratedColumn, in User model we will just use auto incremented primary key while we used UUID approach for expense model.

Now let’s register our entities to databaseManager.ts

// import the entities from models.ts
import { User, Expense } from "./modules/models";

// Rest of your codes

async initializeDataSource() {
    this.dataSource = new DataSource({
      ...this.datasourceOptions,
      entities: [User, Expense], // Add the entities here
    });

    await this.dataSource
      .initialize()
      .then(async () => {
        console.log("Data source initialized");
      })
      .catch(async (error) => {
        if (error.code === "ER_BAD_DB_ERROR") {
          await this.createDatabaseIfNotExists();
          await this.initializeDataSource();
        } else {
          console.error("Error creating database:", error);
          throw error;
        }
      });
  }

Let’s run npm run dev again and now you should already see that we have two tables created for our application.

Dynamic Importing Models

Let’s upgrade our code, up until this point you might be wondering. If I added more models to the modules, will I have to manually register each and every one of them to models.ts and databaseManager.ts?. If you are still using this implementation then unfortunately the answer is yes. But worry not, we will fix it by updating our code to use dynamic imports.

Let’s modify models.ts

// models.ts
import fs from "fs/promises";
import path from "path";

const baseDirectory = __dirname;

export async function importModels(
  directoryPath?: string
): Promise<Function[]> {
  try {
    if (!directoryPath) {
      directoryPath = baseDirectory;
    }
    const entries = await fs.readdir(directoryPath);
    const entities: Function[] = [];

    for (const entry of entries) {
      const entryPath = path.join(directoryPath, entry);
      const entryStat = await fs.stat(entryPath);
      if (entryStat.isFile() && entry.endsWith(".model.ts")) {
        const moduleExport = await import(entryPath);
        if (
          moduleExport.default &&
          typeof moduleExport.default === "function"
        ) {
          entities.push(moduleExport.default);
        }
      } else if (entryStat.isDirectory()) {
        const subEntities = await importModels(entryPath);
        entities.push(...subEntities);
      }
    }

    return entities;
  } catch (error) {
    console.error("Error importing models:", error);
    throw error;
  }
}

When called without any arguments, this code will iterate over our current directory in which it resides in (which is in modules directory). If said entity name ends with .model.ts then we will import the entity (if it have default export) and push the imported entity into our entities list. If the currently iterated entity is a directory we will iterate inside it by calling it’s own function (importModels) on said directory. After all directory is iterated then we will return the entity list. Thus, building a list of Model that we can use in our code.

Now let’s modify our databaseManager.ts to use that.

import "reflect-metadata";
import { DataSource, DataSourceOptions } from "typeorm";

import { ConnectionOptions } from "mysql2";
import * as mysql from "mysql2/promise";

import { importModels } from "./modules/models"; // import models

class DatabaseManager {
  private datasourceOptions: DataSourceOptions = null;
  private dataSource: DataSource = null;

  constructor(private config: DataSourceOptions) {
    this.datasourceOptions = config;
  }

  getDataSource() {
    return this.dataSource;
  }

  public async initializeDataSource() {
    const entities = await importModels(); //use dynamic imports here

    this.dataSource = new DataSource({
      ...this.datasourceOptions,
      entities,
    });

    await this.dataSource
      .initialize()
      .then(async () => {
        console.log("Data source initialized");
      })
      .catch(async (error) => {
        if (error.code === "ER_BAD_DB_ERROR") {
          await this.createDatabaseIfNotExists();
          await this.initializeDataSource();
        } else {
          console.error("Error creating database:", error);
          throw error;
        }
      });
  }

  private async createDatabaseIfNotExists() {
    console.log("DATABASE DOES NOT EXISTS, CREATING DATABASE");
    const access: ConnectionOptions = {
      user: process.env.ADMIN_USER,
      password: process.env.ADMIN_PASSWORD,
      database: process.env.ADMIN_DEFAULT_DB,
    };

    const conn = await mysql.createConnection(access);
    await conn.query(
      `CREATE DATABASE IF NOT EXISTS ${this.datasourceOptions.database}`
    );
    await conn.end();
  }
}

export default DatabaseManager;

Try dropping the database and then start the project again or just save and let nodemon reloads and you will see that the database will be created accordingly. If you renamed one of the module to .mod.ts you will see that it will not be imported. So make sure if you add more models later to follow the standard *.model.ts name format.

Adding Routes And Controller

We’ll start adding our routes and controllers now

// user.controller.ts
import { Request, Response } from "express";
import User from "./user.model";

export default class UserController {
  public async createUser(req: Request, res: Response) {
    try {
      const user: User = new User();
      user.name = req.body.name;
      await user.save();

      res.status(201).json({
        status: "ok",
        message: "created",
        data: user,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async getSingleUser(req: Request, res: Response) {
    try {
      const user = await User.findOneBy({ id: parseInt(req.params.id) });
      res.status(200).json({
        status: "ok",
        message: "created",
        data: user,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async getAllUser(req: Request, res: Response) {
    try {
      const users = await User.find();
      res.status(200).json({
        status: "ok",
        message: "created",
        data: users,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async deleteUser(req: Request, res: Response) {
    try {
      const user = await User.findOneBy({ id: parseInt(req.params.id) });
      await user.remove();
      res.status(200).json({
        status: "ok",
        message: "deleted",
        data: user,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async updateUser(req: Request, res: Response) {
    try {
      const user = await User.findOneBy({ id: parseInt(req.params.id) });
      user.name = req.body.name;
      await user.save();

      res.status(200).json({
        status: "ok",
        message: "updated",
        data: user,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }
}

// user.routes.ts
import express from "express";
import Controller from "./user.controller";

const router = express.Router();
const UserController = new Controller();

router.post("/", UserController.createUser.bind(UserController));

router.get("/", UserController.getAllUser.bind(UserController));

router.get("/:id", UserController.getSingleUser.bind(UserController));

router.put("/:id", UserController.updateUser.bind(UserController));

router.delete("/:id", UserController.deleteUser.bind(UserController));

export default router;

// expense.controller.ts
import { Request, Response } from "express";
import Expense from "./expense.model";

export default class ExpenseController {
  public async createExpense(req: Request, res: Response) {
    try {
      const expense: Expense = new Expense();
      expense.amount = req.body.amount;
      expense.note = req.body.note;
      expense.type = req.body.type;
      expense.user = req.body.userId;
      await expense.save();

      res.status(201).json({
        status: "ok",
        message: "created",
        data: expense,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async getSingleExpense(req: Request, res: Response) {
    try {
      const expense = await Expense.findOneBy({ id: req.params.id });
      res.status(200).json({
        status: "ok",
        message: "created",
        data: expense,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async getAllExpense(req: Request, res: Response) {
    try {
      const expenses = await Expense.find();
      res.status(200).json({
        status: "ok",
        message: "created",
        data: expenses,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async deleteExpense(req: Request, res: Response) {
    try {
      const expense = await Expense.findOneBy({ id: req.params.id });
      await expense.remove();
      res.status(200).json({
        status: "ok",
        message: "deleted",
        data: expense,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async updateExpense(req: Request, res: Response) {
    try {
      const expense = await Expense.findOneBy({ id: req.params.id });
      expense.amount = req.body.amount;
      expense.note = req.body.note;
      expense.type = req.body.type;
      await expense.save();

      res.status(200).json({
        status: "ok",
        message: "updated",
        data: expense,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }
}

// expense.routes.ts
import express from "express";
import Controller from "./expense.controller";

const router = express.Router();
const ExpenseController = new Controller();

router.post("/", ExpenseController.createExpense.bind(ExpenseController));

router.get("/", ExpenseController.getAllExpense.bind(ExpenseController));

router.get("/:id", ExpenseController.getSingleExpense.bind(ExpenseController));

router.put("/:id", ExpenseController.updateExpense.bind(ExpenseController));

router.delete("/:id", ExpenseController.deleteExpense.bind(ExpenseController));

export default router;

Next we will group those routes in single typescript file. Start by creating routes.ts inside modules directory

// routes.ts
import express from "express";

import userRoutes from "./user/user.routes";
import expenseRoutes from "./expense/expense.routes";

const router = express.Router();

router.use("/users", userRoutes);
router.use("/expenses", expenseRoutes);

export default router;

Let’s import the routes and use it in our app.ts

// app.ts
import express, { Express } from "express";

import apiRoutes from "./modules/routes";

// Create Express app
const app: Express = express();

// Middleware
app.use(express.json());

app.use("/api/v1", apiRoutes);

// Routes
app.get("/", async (req, res) => {
  res.send("It works, congratulations");
});

export default app;

Now let’s try hitting our endpoint to create and test the functionality. You can write request to localhost:3000 (or whatever port you defined in .env) on your own or you can import this postman collection to ease the testing process.

Currently you might have noticed now that our getAllUser() and getAllExpense() are returning all the data presents. As the data gets added it will slow down due to fetching large amount of data and also possibly making the api response bloated. Hence we will now implement pagination. We will also implement data filtering and ordering in getAllExpense when we wanted to find expenses list for only one user and sort all the results according to the created date descendingly.

// user.controller.ts
public async getAllUser(req: Request, res: Response) {
    let page: number = Number(req.query.page) || 1;
    let itemPerPage: number = Number(req.query.itemPerPage) || 10;

    try {
      const usersAndCount: [User[], number] = await User.findAndCount({
        skip: (page - 1) * itemPerPage,
        take: itemPerPage,
      });
      console.log;
      res.status(200).json({
        status: "ok",
        message: "created",
        data: usersAndCount[0],
        paging: {
          page: page,
          itemPerPage: itemPerPage,
          itemCount: usersAndCount[1],
          lastPage: Math.ceil(usersAndCount[1] / itemPerPage),
        },
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

// expense.controller.ts
public async getAllExpense(req: Request, res: Response) {
    let page: number = Number(req.query.page) || 1;
    let itemPerPage: number = Number(req.query.itemPerPage) || 10;

    let searchCondition = req.query.userId
      ? {
          user: { id: Number(req.query.userId) },
        }
      : {};

    try {
      const expensesAndCount: [Expense[], number] = await Expense.findAndCount({
        where: searchCondition,
        skip: (page - 1) * itemPerPage,
        take: itemPerPage,
        order: {
          created_at: "DESC",
        },
      });
      res.status(200).json({
        status: "ok",
        message: "created",
        data: expensesAndCount[0],
        paging: {
          page: page,
          itemPerPage: itemPerPage,
          itemCount: expensesAndCount[1],
          lastPage: Math.ceil(expensesAndCount[1] / itemPerPage),
        },
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

At this point you might have noticed that our user.controller.ts and expense.controller.ts implementations are fairly similar. You can also observe that since both User and Expense extends BaseEntity, it can act as generic for us. Now let’s try to abstract our controller.

We will begin by moving our database instance from index.ts to app.ts

// app.ts
import express, { Express } from "express";
import apiRoutes from "./modules/routes";
import { DataSourceOptions } from "typeorm";
import DatabaseManager from "./databaseManager";

export let dbManager: DatabaseManager;

export const connectToDb = async (datasourceOptions: DataSourceOptions) => {
  dbManager = new DatabaseManager(datasourceOptions);
  await dbManager.initializeDataSource();
};

// Create Express app
const app: Express = express();

// Middleware
app.use(express.json());

app.use("/api/v1", apiRoutes);

// Routes
app.get("/", async (req, res) => {
  res.send("It works, congratulations");
});

export default app;

This way index.ts is only used to pass connection options data to app.ts

import dotenv from "dotenv";
// Load environment variables from .env file
dotenv.config();

import { DataSourceOptions } from "typeorm";
import app, { connectToDb } from "./app";

// Read the port from environment variables or use a default port
const port = process.env.PORT || 3000;

// Initialize data source option
const datasourceOptions: DataSourceOptions = {
  type: "mysql",
  host: process.env.DB_HOST,
  port: process.env.DB_PORT ? parseInt(process.env.DB_PORT, 10) : 3306,
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  synchronize:
    process.env.DB_SYNCHRONIZE &&
    process.env.DB_SYNCHRONIZE.toLowerCase() === "true",
  logging:
    process.env.DB_LOGGING && process.env.DB_LOGGING.toLowerCase() === "true",
};

connectToDb(datasourceOptions);

// Start the server
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

Then we’ll create baseController.ts inside modules directory.

// baseController.ts
import { Request, Response } from "express";
import { BaseEntity, EntityTarget, FindOneOptions, Repository } from "typeorm";

// Here we will import the dbManager instance from app.ts
import { dbManager } from "../app";

export default abstract class BaseController<MODEL extends BaseEntity> {
  protected entityRepository: Repository<MODEL>;
  protected entity: MODEL;

  constructor(entity: MODEL) {
    this.entity = entity;
  }

  protected getRepository() {
    if (!this.entityRepository) {
      this.entityRepository = dbManager
        .getDataSource()
        .getRepository(this.entity.constructor as EntityTarget<MODEL>);
    }
    return this.entityRepository;
  }

  public async create(req: Request, res: Response, entity: MODEL) {
    try {
      await this.getRepository().save(entity);
      res.status(201).json({
        status: "ok",
        message: "created",
        data: entity,
      });
    } catch (error) {
      console.error("Error happened:", error);
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async getSingle(
    req: Request,
    res: Response,
    options: FindOneOptions<MODEL>
  ) {
    try {
      const entity = await this.getRepository().findOne(options);
      res.status(200).json({
        status: "ok",
        message: "retrieved",
        data: entity,
      });
    } catch (error) {
      console.error("Error happened:", error);
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async getAll(
    req: Request,
    res: Response,
    options: FindOneOptions<MODEL>
  ) {
    let page: number = Number(req.query.page) || 1;
    let itemPerPage: number = Number(req.query.itemPerPage) || 10;

    try {
      const [entities, totalCount] = await this.getRepository().findAndCount({
        ...options,
        skip: (page - 1) * itemPerPage,
        take: itemPerPage,
      });

      res.status(200).json({
        status: "ok",
        message: "retrieved",
        data: entities,
        paging: {
          page: page,
          itemPerPage: itemPerPage,
          itemCount: totalCount,
          lastPage: Math.ceil(totalCount / itemPerPage),
        },
      });
    } catch (error) {
      console.error("Error happened:", error);
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async delete(
    req: Request,
    res: Response,
    options: FindOneOptions<MODEL>
  ) {
    try {
      const entity = await this.getRepository().findOne(options);
      if (!entity) {
        res.status(404).json({
          status: "not_found",
          message: "Entity not found",
        });
        return;
      }
      await entity.remove();
      res.status(200).json({
        status: "ok",
        message: "deleted",
        data: entity,
      });
    } catch (error) {
      console.error("Error happened:", error);
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

  public async update(
    req: Request,
    res: Response,
    options: FindOneOptions<MODEL>
  ) {
    try {
      const entity = await this.getRepository().findOne(options);
      if (!entity) {
        res.status(404).json({
          status: "not_found",
          message: "Entity not found",
        });
        return;
      }
      Object.assign(entity, req.body);
      await entity.save();

      res.status(200).json({
        status: "ok",
        message: "updated",
        data: entity,
      });
    } catch (error) {
      console.error("Error happened:", error);
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }
}

Then we can modify our user controller and expense controller like this

// user.controller.ts
import { Request, Response } from "express";
import { FindOneOptions } from "typeorm";
import BaseController from "../baseController";
import User from "./user.model";

export default class UserController extends BaseController<User> {
  constructor() {
    super(new User());
  }

  public async createUser(req: Request, res: Response) {
    const user: User = new User();
    user.name = req.body.name;
    return this.create(req, res, user);
  }

  public async getSingleUser(req: Request, res: Response) {
    const options: FindOneOptions<User> = {
      where: { id: parseInt(req.params.id) },
    };
    return this.getSingle(req, res, options);
  }

  public async getAllUser(req: Request, res: Response) {
    const options: FindOneOptions<User> = {};
    return this.getAll(req, res, options);
  }

  public async deleteUser(req: Request, res: Response) {
    const options: FindOneOptions<User> = {
      where: { id: parseInt(req.params.id) },
    };
    return this.delete(req, res, options);
  }

  public async updateUser(req: Request, res: Response) {
    const options: FindOneOptions<User> = {
      where: { id: parseInt(req.params.id) },
    };
    return this.update(req, res, options);
  }
}

// expense.controller.ts
import { Request, Response } from "express";
import { FindOneOptions } from "typeorm";
import BaseController from "../baseController";
import Expense from "./expense.model";

export default class ExpenseController extends BaseController<Expense> {
  constructor() {
    super(new Expense());
  }

  public async createExpense(req: Request, res: Response) {
    const expense: Expense = new Expense();
    expense.amount = req.body.amount;
    expense.note = req.body.note;
    expense.type = req.body.type;
    expense.user = req.body.userId;
    return this.create(req, res, expense);
  }

  public async getSingleExpense(req: Request, res: Response) {
    const options: FindOneOptions<Expense> = {
      where: { id: req.params.id },
    };
    return this.getSingle(req, res, options);
  }

  public async getAllExpense(req: Request, res: Response) {
    let searchCondition = req.query.userId
      ? {
          user: { id: Number(req.query.userId) },
        }
      : {};

    const options: FindOneOptions<Expense> = {
      where: searchCondition,
      order: {
        created_at: "DESC",
      },
    };
    return this.getAll(req, res, options);
  }

  public async deleteExpense(req: Request, res: Response) {
    const options: FindOneOptions<Expense> = {
      where: { id: req.params.id },
    };
    return this.delete(req, res, options);
  }

  public async updateExpense(req: Request, res: Response) {
    const options: FindOneOptions<Expense> = {
      where: { id: req.params.id },
    };
    return this.update(req, res, options);
  }
}

Fixing Our Deletion Issues

Let’s fix a small issue that we might have faced. When we tried to delete user that have expenses recorded we will receive this error:

{
    "status": "failed",
    "message": "internal_server_error",
    "errors": {
        "query": "DELETE FROM `user` WHERE `id` = ?",
        "parameters": [
            4
        ],
        "driverError": {
            "code": "ER_ROW_IS_REFERENCED_2",
            "errno": 1451,
            "sqlState": "23000",
            "sqlMessage": "Cannot delete or update a parent row: a foreign key constraint fails (`typeormdb`.`expense`, CONSTRAINT `FK_06e076479515578ab1933ab4375` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))",
            "sql": "DELETE FROM `user` WHERE `id` = 4"
        },
        "code": "ER_ROW_IS_REFERENCED_2",
        "errno": 1451,
        "sqlState": "23000",
        "sqlMessage": "Cannot delete or update a parent row: a foreign key constraint fails (`typeormdb`.`expense`, CONSTRAINT `FK_06e076479515578ab1933ab4375` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))",
        "sql": "DELETE FROM `user` WHERE `id` = 4"
    }
}

While when we are trying to add expense with non existent user id we will be greeted by this lengthy error.

{
    "status": "failed",
    "message": "internal_server_error",
    "errors": {
        "query": "INSERT INTO `expense`(`id`, `amount`, `note`, `type`, `created_at`, `updated_at`, `userId`) VALUES (?, ?, ?, ?, DEFAULT, DEFAULT, ?)",
        "parameters": [
            "26c733be-4e37-48d6-ba86-3679c04e8f98",
            10,
            "testing notes",
            "EXPENSE",
            1
        ],
        "driverError": {
            "code": "ER_NO_REFERENCED_ROW_2",
            "errno": 1452,
            "sqlState": "23000",
            "sqlMessage": "Cannot add or update a child row: a foreign key constraint fails (`typeormdb`.`expense`, CONSTRAINT `FK_06e076479515578ab1933ab4375` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))",
            "sql": "INSERT INTO `expense`(`id`, `amount`, `note`, `type`, `created_at`, `updated_at`, `userId`) VALUES ('26c733be-4e37-48d6-ba86-3679c04e8f98', 10, 'testing notes', 'EXPENSE', DEFAULT, DEFAULT, 1)"
        },
        "code": "ER_NO_REFERENCED_ROW_2",
        "errno": 1452,
        "sqlState": "23000",
        "sqlMessage": "Cannot add or update a child row: a foreign key constraint fails (`typeormdb`.`expense`, CONSTRAINT `FK_06e076479515578ab1933ab4375` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))",
        "sql": "INSERT INTO `expense`(`id`, `amount`, `note`, `type`, `created_at`, `updated_at`, `userId`) VALUES ('26c733be-4e37-48d6-ba86-3679c04e8f98', 10, 'testing notes', 'EXPENSE', DEFAULT, DEFAULT, 1)"
    }
}

Let’s talk about the former issue, when we read the sqlMessage we can deduce that this user cannot be deleted since there are still entries in expenses that refers to said user. There are two alternative ways we can fix this issue depending on how we want to delete the data. We’’ll talk about soft delete and hard delete. In short, hard delete is hard to recover once deleted. Once an entry is hard deleted it will be removed and gone forever from said database with the only way to recover it is by restoring using backup. Soft delete are easier to recover since they are not permanently removed from database, instead data have a flag which marks if a data is deleted or not. So recovering said data is only as easy as changing the flag from deleted back to not deleted.

When we want to hard delete the user, we can implement cascading delete. Which works by removing the user along with all affected expenses. So when we have a user with ten expenses, the user along with those ten expenses will be removed altogether.

Implementing hard delete is done by adding { onDelete: “CASCADE” }) tag to the relationship of Expense entity.

@Entity()
export default class Expense extends BaseEntity {
  // rest of the properties
  @ManyToOne(() => User, (user) => user.expense, { onDelete: "CASCADE" })
  user: User;
}

While for soft delete we can just import DeleteDateColumn and add the column like this to our user.model.ts

// user.model.ts
@Entity()
export default class User extends BaseEntity implements IUser {
  ....

  @DeleteDateColumn({ type: "timestamp", default: null, nullable: true })
  deletedAt: Date;

  ....
}

We can then update our deleteUser method to utilize softRemove() instead of the regular remove().

public async deleteUser(req: Request, res: Response) {
    try {
      const entity = await User.findOneBy({ id: parseInt(req.params.id) });
      if (!entity) {
        res.status(404).json({
          status: "not_found",
          message: "Entity not found",
        });
        return;
      }
      await entity.softRemove();
      res.status(200).json({
        status: "ok",
        message: "deleted",
        data: entity,
      });
    } catch (error) {
      res.status(500).json({
        status: "failed",
        message: "internal_server_error",
        errors: error,
      });
    }
  }

Or we can also make use of our update abstraction like this

public async deleteUser(req: Request, res: Response) {
    const options: FindOneOptions<User> = {
      where: { id: parseInt(req.params.id) },
    };
    req.body.deletedAt = new Date();
    return this.update(req, res, options);
  }

All those approaches are fine, but for the sake of convenience we will go with soft delete and using the update abstraction for now.

Next we will implement validator to validate the user id being passed to expenses. We are going to implement out validator using Joi. Lets begin by installing joi using

npm install joi

Now we are going to create expense.validator.ts inside our expense module. We are using external to validate from User schema to check whether User exists or not.

import Joi, { ObjectSchema, ValidationErrorItem } from "joi";
import User from "../user/user.model";
import { ExpenseType } from "./expense.model";

const validatorSchema: any = {
  userId: Joi.number()
    .external(async (value: any, helpers: any) => {
      if (!(await User.findOneBy({ id: parseInt(value) }))) {
        const errorDetails: ValidationErrorItem[] = [
          {
            message: "user is not valid",
            path: ["user"],
            type: "any.invalid",
          },
        ];
        const error: Joi.ValidationError = new Joi.ValidationError(
          "user is not valid",
          errorDetails,
          value
        );
        throw error;
      }
      return value;
    })
    .required()
    .messages({
      "any.invalid": "user is not valid",
      "any.required": "is required",
    }),
  amount: Joi.number(),
  note: Joi.string().optional(),
  type: Joi.string()
    .valid(...Object.values(ExpenseType))
    .required()
    .messages({
      "any.invalid": "expense type invalid",
      "any.required": "is required",
    }),
};

export const createExpenseValidator: ObjectSchema<any> = Joi.object(
  validatorSchema
).options({ stripUnknown: true });

Joi returns a specifically shaped error message so let’s also modify baseController and and add following methods and abstraction. We will use formatValidationErrors method to group the errors to a more UI friendly format.

private static formatValidationErrors(errors: ValidationErrorItem[]): {
    [field: string]: string[];
  } {
    const something: { field: string; message: string }[] = errors.map(
      (error) => ({
        field: error.context?.label || error.path.join("."),
        message: error.message.replace(/["]/g, ""),
      })
    );

    const groupedErrors: {
      [field: string]: string[];
    } = something.reduce((acc, error) => {
      const { field, message } = error;
      if (!acc[field]) {
        acc[field] = [message];
      } else {
        acc[field].push(message);
      }
      return acc;
    }, {});

    return groupedErrors;
  }

  public static sendValidationError(
    res: Response,
    errors: ValidationErrorItem[] | { [x: string]: string[] }
  ): void {
    const status = 400;
    const validationErrors: { [x: string]: string[] } = Array.isArray(errors)
      ? this.formatValidationErrors(errors)
      : errors;
    res.status(status).json({
      status,
      error: validationErrors,
      message: "Validation failed",
    });
  }

  public static sendISE(res: Response, error: any) {
    console.error("Error happened:", error);
    res.status(500).json({
      status: "failed",
      message: "internal_server_error",
      errors: error,
    });
  }

public async createWithValidator(
    req: Request,
    res: Response,
    validateFunction: () => Promise<void>,
    entity: MODEL
  ) {
    try {
      await validateFunction();
      return this.create(req, res, entity);
    } catch (error) {
      if (error.name === "ValidationError") {
        return BaseController.sendValidationError(res, error.details);
      } else {
        return BaseController.sendISE(res, error);
      }
    }
  }

Now let’s call our validator from controller, here we added abortEarly options so all the schema validation will be returned all at once and not one by one at a time.

// expense.controller.ts
public async createExpense(req: Request, res: Response) {
    const validationFunction = async (): Promise<void> => {
      return createExpenseValidator.validateAsync(
        { ...req.body, userId: user.id },
        {
          abortEarly: false,
        }
      );
    };

    const expense: Expense = new Expense();
    expense.amount = req.body.amount;
    expense.note = req.body.note;
    expense.type = req.body.type;
    expense.user = req.body.userId;
    return this.createWithValidator(req, res, validationFunction, expense);
  }

Now let’s try hitting update expense endpoint with wrong type of data to see if the validation works.

I am sure some of you might have noticed that external validator are executed only when schema validation passed. This is expected actually. Since as of the version of Joi I used, following this documentation: https://joi.dev/api/?v=17.9.1

It is quoted that

“If schema validation failed, no external validation rules are called.”

So there are currently no way for us to return all errors along with the external user validation on single form submission.

To keep this documentation from getting too long, I have split it into two parts. Here is the link to the second part of the story.

CRUD Rest API with Express, TypeOrm and Jest for testing — part 2