Preface

For backend management systems, features like access control and personalized user interfaces are essential. For instance, a super administrator can view all pages, regular users can access pages A and B, and VIP users can view pages A, B, C, and D. The logic behind these functionalities is based on the design of three key concepts:

• User: The basic unit, such as Alice, Bob, Charlie.
• Role: A user can have one or more roles. For example, Alice may have both the roles of a regular user and a VIP.
• Permission: A role is associated with multiple permissions. For example, the VIP role might have permissions to view, edit, and add, while the super administrator can view, edit, add, and delete.

The relationship can be illustrated with the following diagram:

Next, we’ll use Nest to implement the foundation of such a system from scratch — the permission design.

Creating the Database

First, we need to create the database. We’ll use the MySQL database and execute the following command to create it:

CREATE DATABASE `nest-database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Project Initialization

We’ll start a new Nest project by running the following command:

nest new nest-project

Then, install the necessary database dependencies, primarily typeorm and mysql2:

npm install --save @nestjs/typeorm typeorm mysql2

Next, configure typeorm in app.module.ts:

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'password',
      database: 'nest-database',
      synchronize: true,
      logging: true,
      entities: [__dirname + '/**/*.entity{.ts,.js}'],
      poolSize: 10,
      connectorPackage: 'mysql2',
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Table Design

Typically, an RBAC (Role-Based Access Control) system will have 5 tables as follows:

• User table (user): Stores basic user information like username, password, and email.
• Role table (role): Stores role details like role name and role code.
• Permission table (permission): Stores permission details like permission name and permission code.
• User-role relation table (user_role_relation): Tracks the relationship between users and roles.
• Role-permission relation table (role_permission_relation): Tracks the relationship between roles and permissions.

The domain model can be visualized as follows:

Next, we’ll create three non-relation tables in Nest and define their relationships.

user.entity.ts:

import {
  Column,
  CreateDateColumn,
  Entity,
  JoinTable,
  ManyToMany,
  PrimaryGeneratedColumn,
  UpdateDateColumn,
} from 'typeorm';

import { Role } from './role.entity';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({
    length: 50,
  })
  username: string;

  @Column({
    length: 50,
  })
  password: string;

  @CreateDateColumn()
  createTime: Date;

  @UpdateDateColumn()
  updateTime: Date;

  @ManyToMany(() => Role)
  @JoinTable({
    name: 'user_role_relation',
    joinColumn: {
      name: 'userId',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'roleId',
      referencedColumnName: 'id',
    },
  })
  roles: Role[];
}

In the User table, the roles field is defined to connect with the user_role_relation table. The relationship logic is: user.id === userRoleRelation.userId and role.id === userRoleRelation.roleId. Matching Role records are automatically linked to User.

role.entity.ts:

import {
  Column,
  CreateDateColumn,
  Entity,
  JoinTable,
  ManyToMany,
  PrimaryGeneratedColumn,
  UpdateDateColumn,
} from 'typeorm';

import { Permission } from './permission.entity';

@Entity()
export class Role {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({
    length: 20,
  })
  name: string;

  @CreateDateColumn()
  createTime: Date;

  @UpdateDateColumn()
  updateTime: Date;

  @ManyToMany(() => Permission)
  @JoinTable({
    name: 'role_permission_relation',
    joinColumn: {
      name: 'roleId',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'permissionId',
      referencedColumnName: 'id',
    },
  })
  permissions: Permission[];
}

The permissions field in the Role table works similarly. It connects with the role_permission_relation table using the logic: role.id === rolePermissionRelation.roleId and permission.id === rolePermissionRelation.permissionId.

permission.entity.ts:

import {
  Column,
  CreateDateColumn,
  Entity,
  PrimaryGeneratedColumn,
  UpdateDateColumn,
} from 'typeorm';

@Entity()
export class Permission {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({
    length: 50,
  })
  name: string;

  @Column({
    length: 100,
    nullable: true,
  })
  desc: string;

  @CreateDateColumn()
  createTime: Date;

  @UpdateDateColumn()
  updateTime: Date;
}

The Permission table doesn’t have relationships; it simply records available permissions.

Data Initialization

Here’s a service to initialize some test data:

async function initData() {
  const user1 = new User();
  user1.username = 'Alice';
  user1.password = 'aaaaaa';

  const user2 = new User();
  user2.username = 'Bob';
  user2.password = 'bbbbbb';

  const user3 = new User();
  user3.username = 'Charlie';
  user3.password = 'cccccc';

  const role1 = new Role();
  role1.name = 'Administrator';

  const role2 = new Role();
  role2.name = 'Regular User';

  const permission1 = new Permission();
  permission1.name = 'Add resource_a';

  const permission2 = new Permission();
  permission2.name = 'Edit resource_a';

  const permission3 = new Permission();
  permission3.name = 'Delete resource_a';

  const permission4 = new Permission();
  permission4.name = 'Query resource_a';

  const permission5 = new Permission();
  permission5.name = 'Add resource_b';

  const permission6 = new Permission();
  permission6.name = 'Edit resource_b';

  const permission7 = new Permission();
  permission7.name = 'Delete resource_b';

  const permission8 = new Permission();
  permission8.name = 'Query resource_b';

  role1.permissions = [
    permission1,
    permission2,
    permission3,
    permission4,
    permission5,
    permission6,
    permission7,
    permission8,
  ];

  role2.permissions = [permission1, permission2, permission3, permission4];

  user1.roles = [role1];

  user2.roles = [role2];

  await this.entityManager.save(Permission, [
    permission1,
    permission2,
    permission3,
    permission4,
    permission5,
    permission6,
    permission7,
    permission8,
  ]);

  await this.entityManager.save(Role, [role1, role2]);

  await this.entityManager.save(User, [user1, user2]);
}

Run the initData service via a browser or Postman, and the data will populate the database.

---

With the basic permission structure set up, you can now implement features like registration, login, and JWT-based authentication.

Now it's your turn!

---

We are Leapcell, your top choice for deploying NestJS projects to the cloud.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with JavaScript, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ