Node, TypeScript, Postgres setup for fitness app

Introduction

Welcome to part 2 of our series on building a fitness app backend. If you missed the first part, check it out here. We have already covered the project prerequisites, project structure, user authorization, and authentication.

In this article, we will get our hands dirty by learning how to:

Implement Role-Based Access Control (RBAC).
Seed the database with predefined data.
Run migrations to track entity changes.

Role-Based Access Control (RBAC)

Role-Based Access Control, also known as RBAC, is a backend functionality that allows certain permissions to be assigned to a superuser, such as an administrator. In this section, we will implement RBAC to give the admin access to some routes. We want the admin to be able to perform CRUD (Create, Read, Update, Delete) functionality on the exercise table.

In the user entity, add the Role field.

import { Role } from "../types/user.ts";
@Column({ type: "enum", enum: Role, default: Role.USER})
  role?: Role;

The code defines the user entity, whether an account belongs to a user or an admin. It uses the TypeScript enum type to describe the values and further sets the default value to user.

Here is how we defined the enum.

export enum Role {
  USER = "User",
  ADMIN = "Admin",
}

Next, we need a middleware that checks if a logged-in user is an admin.

// src/middlewares/adminMiddleware.ts
import { Request, Response, NextFunction } from "express";
import { AppDataSource } from "../data-source";
import { User } from "../entities/user";
import { AppError } from "../utils/appError";

export const adminMiddleware = async (
  req: Request,
  res: Response,
  next: NextFunction
) => {
  try {
    const userRepo = AppDataSource.getRepository(User);
    if (!req.user)
      throw new AppError("Unauthorized", 401, true, "Unauthorized");

    const isUser = await userRepo.findOneBy({ id: req.user.id });
    if (isUser && isUser.role === "Admin") {
      next();
    } else {
      throw new AppError("Access denied", 403, true, "Forbidden");
    }
  } catch (error) {
    throw error;
  }
};

This middleware function starts by fetching the Userrepository and checking if the user is logged in. Next, it checks if the logged-in user is an admin. It passes to the next middleware function if the logged-in user is an admin. It throws an error if the user is not an admin.

Database seeding

Data seeding is a process of running a script that fills the database with predefined data.

In this section, we will implement data seeding functionality. We want to write a script that loads our database with predefined exercises.

Firstly, let's create the exercise entity.

// src/entities/exercises.ts
import {
  Column,
  Entity,
  PrimaryGeneratedColumn,
  CreateDateColumn,
  UpdateDateColumn,
  OneToMany,
  Relation,
} from "typeorm";
import { WorkoutExercise } from "./workoutExercises.ts";
import { Difficulty } from "../types/exercises.ts";

@Entity()
export class Exercise {
  @PrimaryGeneratedColumn("uuid")
  id!: string;

  @Column({ type: "varchar", nullable: false })
  name!: string;

  @Column({ type: "varchar", nullable: false })
  description!: string;

  @Column({ type: "varchar", nullable: false })
  category!: string;

  @Column({ type: "enum", enum: Difficulty, default: Difficulty.BEGINNER })
  difficulty?: Difficulty;

  @Column({ type: "int", nullable: true })
  duration?: number;

  @Column({ type: "int", nullable: true })
  calorie_burned?: number;

  @Column({ type: "varchar", nullable: true })
  media_url?: string;

  @OneToMany(
    () => WorkoutExercise,
    (workoutExercise) => workoutExercise.exercise
  )
  workoutExercise?: Relation<WorkoutExercise[]>;
  @CreateDateColumn()
  createdAt!: Date;

  @UpdateDateColumn()
  updatedAt?: Date;
}

The code above defines the various fields in the exercise table and decorates them with TypeORM decorators. It also describes relationships between the exercise table and the workoutExercise table by establishing a OnetoMany relationship. This means one exercise entity can exist in many workoutExercise entities.

The difficulty field has an enum type, defined by a TypeScript enum type called Difficulty

// src/types/exercises.ts
export enum Difficulty {
  BEGINNER = "Beginner",
  INTERMEDIATE = "Intermediate",
  ADVANCED = "Advanced",
}

Next, we need to create our predefined exercise data to be inserted into the exercise table.

// src/seeders/exercises.seed.sql
INSERT INTO "exercise" (
  id, name, description, category, difficulty, duration,
  calorie_burned, media_url, "createdAt", "updatedAt"
) VALUES
(uuid_generate_v4(), 'Push-Up', 'Targets the chest, shoulders, and triceps using body weight.', 'Strength', 'Beginner', 30, 8, 'https://cdn.fitnessapp.com/media/pushup.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'Jumping Jacks', 'Full-body cardio warm-up that improves circulation and endurance.', 'Cardio', 'Beginner', 45, 10, 'https://cdn.fitnessapp.com/media/jumping-jacks.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'Plank', 'Improves core stability and posture through static endurance.', 'Core', 'Intermediate', 60, 12, 'https://cdn.fitnessapp.com/media/plank.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'Burpees', 'Combines a squat, jump, and push-up to increase strength and cardio.', 'Cardio', 'Advanced', 40, 15, 'https://cdn.fitnessapp.com/media/burpees.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'Mountain Climbers', 'High-intensity core and cardio workout that raises heart rate fast.', 'Cardio', 'Intermediate', 30, 14, 'https://cdn.fitnessapp.com/media/mountain-climbers.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'Bodyweight Squats', 'Builds lower body strength using just body weight.', 'Strength', 'Beginner', 60, 10, 'https://cdn.fitnessapp.com/media/squats.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'Lunges', 'Strengthens glutes, hamstrings, and improves balance.', 'Strength', 'Intermediate', 45, 11, 'https://cdn.fitnessapp.com/media/lunges.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'Bicycle Crunches', 'Targets obliques and abs with controlled twisting motions.', 'Core', 'Intermediate', 60, 13, 'https://cdn.fitnessapp.com/media/bicycle-crunches.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'High Knees', 'Fast-paced movement that increases lower-body endurance.', 'Cardio', 'Beginner', 30, 9, 'https://cdn.fitnessapp.com/media/high-knees.mp4', NOW(), NOW()),
(uuid_generate_v4(), 'Russian Twists', 'Core exercise that targets the obliques and helps with torso rotation.', 'Core', 'Advanced', 45, 10, 'https://cdn.fitnessapp.com/media/russian-twists.mp4', NOW(), NOW());

The code above is written in SQL. It inserts the data into the exercise table according to the column descriptions. This is suitable for testing in the development environment.

Next, we write the script that will automatically load the data into the database when the script is executed. Before going further, install the package pg using npm.

// src/seeders/seed.ts
import pkg from "pg";
const { Pool } = pkg;
import * as fs from "fs";
import * as dotenv from "dotenv";
import { dirname } from "path";
import { fileURLToPath } from "url";
dotenv.config();

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const databaseUrl = process.env.DB_URL;
const pool = new Pool({
  connectionString: databaseUrl,
});

if (process.env.NODE_ENV === "development") {
  const seedQuery = fs.readFileSync(__dirname + "/exercises.seed.sql", {
    encoding: "utf8",
  });
  pool.query(seedQuery, (err: Error, res: any) => {
    console.log(err, res);
    console.log("Seeding Completed!");
    pool.end();
  });
}

This code imports the pg module and uses the Pool class to handle database connections. Next, it creates the current file path and directory. The import.meta.url contains the current module file URL.

The database connection is created by passing the database URL into the Pool constructor. The script only runs when the application is running in development.

In the package.json file, add this script:"seed": "node --loader ts-node/esm src/seeders/seed.ts"

Open your terminal and run npm run seed command. Confirm if it has been added to the database.

Running database migrations

Migration is a method for tracking changes in the database. Anytime there is a change in the database, a new migration file is generated that tracks that change. This is done by executing certain commands.

Open thepackage.json file and add these two commands to the script.

"scripts":{
  "gen-migration": "node --loader ts-node/esm ./node_modules/typeorm/cli.js migration:generate ./src/migrations/Initial -d ./src/data-source.ts",
  "run-migration": "node --loader ts-node/esm ./node_modules/typeorm/cli.js migration:run -d ./src/data-source.ts"
 }

The gen-migration command generates a new migration file. It runs Node.js with ts-node, executes the TypeORM generate migration command, and points to the datasource configuration file.

The run-migration command differs a bit. It runs all migrations that haven't been executed.

After running both commands, we should have something like this below:

import { MigrationInterface, QueryRunner } from "typeorm";

export class Initial1745980718070 implements MigrationInterface {
    name = 'Initial1745980718070'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE TYPE "public"."exercise_difficulty_enum" AS ENUM('Beginner', 'Intermediate', 'Advanced')`);
        await queryRunner.query(`CREATE TABLE "exercise" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "name" character varying NOT NULL, "description" character varying NOT NULL, "category" character varying NOT NULL, "difficulty" "public"."exercise_difficulty_enum" NOT NULL DEFAULT 'Beginner', "duration" integer, "calorie_burned" integer, "media_url" character varying, "createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), CONSTRAINT "PK_a0f107e3a2ef2742c1e91d97c14" PRIMARY KEY ("id"))`);
        await queryRunner.query(`CREATE TABLE "workout_exercise" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "sets" integer NOT NULL, "reps" integer NOT NULL, "duration" integer, "notes" character varying, "createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), "exerciseId" uuid, "workoutPlansId" uuid, CONSTRAINT "PK_9598996a913c5f5114f9e6403b6" PRIMARY KEY ("id"))`);
        await queryRunner.query(`CREATE TYPE "public"."workout_plans_difficulty_enum" AS ENUM('Beginner', 'Intermediate', 'Advanced')`);
        await queryRunner.query(`CREATE TABLE "workout_plans" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "name" character varying NOT NULL, "description" character varying NOT NULL, "goal" character varying, "duration_in_weeks" integer, "difficulty" "public"."workout_plans_difficulty_enum" NOT NULL DEFAULT 'Beginner', "is_public" boolean DEFAULT false, "createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), "userId" uuid, CONSTRAINT "PK_9ae1bdd02db446a7541e2e5b161" PRIMARY KEY ("id"))`);
        await queryRunner.query(`CREATE TABLE "user" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "firstname" character varying NOT NULL, "lastname" character varying NOT NULL, "email" character varying NOT NULL, "password" character varying NOT NULL, "isActive" boolean NOT NULL DEFAULT true, "refreshToken" character varying array, "phone" character varying NOT NULL, "country" character varying NOT NULL, "createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"))`);
        await queryRunner.query(`ALTER TABLE "workout_exercise" ADD CONSTRAINT "FK_a2ac7d92eeb9bd5fc2bb9896611" FOREIGN KEY ("exerciseId") REFERENCES "exercise"("id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE "workout_exercise" ADD CONSTRAINT "FK_8a4dad402e75fb8496cef056e60" FOREIGN KEY ("workoutPlansId") REFERENCES "workout_plans"("id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE "workout_plans" ADD CONSTRAINT "FK_ff2ee5d107dfa46fbafa59d316e" FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "workout_plans" DROP CONSTRAINT "FK_ff2ee5d107dfa46fbafa59d316e"`);
        await queryRunner.query(`ALTER TABLE "workout_exercise" DROP CONSTRAINT "FK_8a4dad402e75fb8496cef056e60"`);
        await queryRunner.query(`ALTER TABLE "workout_exercise" DROP CONSTRAINT "FK_a2ac7d92eeb9bd5fc2bb9896611"`);
        await queryRunner.query(`DROP TABLE "user"`);
        await queryRunner.query(`DROP TABLE "workout_plans"`);
        await queryRunner.query(`DROP TYPE "public"."workout_plans_difficulty_enum"`);
        await queryRunner.query(`DROP TABLE "workout_exercise"`);
        await queryRunner.query(`DROP TABLE "exercise"`);
        await queryRunner.query(`DROP TYPE "public"."exercise_difficulty_enum"`);
    }

}

To confirm the migration was successful, you can check the database for the tables created.

Conclusion

In this article, we have set up Role-Based Access Control functionality that ensures only the admin can access some routes. We have learnt how to set up middleware to handle this functionality.

We have learnt how to seed the database with predefined data. We have seen how to create the data in SQL. Before we seeded the database, we saw how to create an entity that matches the predefined data.

Lastly, we saw how useful migrations can be in helping us track changes in the TypeORM entity. We also have the commands that execute these migrations for us.

Next steps

In the coming series, we will explore:

How to protect routes with middleware.
How to implement CRUD functionality.

Thanks for reading.

Building a Fitness App Backend with Node, TypeScript, Postgres, and TypeORM (Part 2)