ExpressJs

⌘K
  2. Home
  4. Docs
  6. ExpressJs
  8. Role And Permission
  10. Entities Definition

Entities Definition

Step 1: Create Base Tables

We’ll start by creating the fundamental tables without any relationships. These tables are:

  • Permissions
  • Roles
  • UserGroups
  • Users

1.1 Create permissions Table

const { EntitySchema } = require('typeorm');

module.exports = new EntitySchema({
    name: 'Permission',
    tableName: 'permissions',
    columns: {
        id: {
            type: 'int',
            primary: true,
            generated: true,
        },
        name: {
            type: 'varchar',
            unique: true,
            nullable: false,
        },
        model: {
            type: 'varchar',
            nullable: false,
        },
        action: {
            type: 'varchar',
            nullable: false,
        },
        createdAt: {
            type: 'timestamp',
            createDate: true,
        },
        updatedAt: {
            type: 'timestamp',
            updateDate: true,
        },
    },
});

Explanation of Columns:

  • id: Auto-incremented primary key.
  • name: Name of the permission (e.g., Manage Users, Edit Posts). It’s set as UNIQUE to ensure no duplicate permission names.
  • model: The model or entity this permission applies to (e.g., User, Post).
  • action: The specific action allowed for this permission (e.g., create, edit, delete).
  • createdAt: Timestamp of when the permission was created.
  • updatedAt: Timestamp of when the permission was last updated.

1.2 Role Entity

Create a file named Role.js:

const { EntitySchema } = require('typeorm');

module.exports = new EntitySchema({
    name: 'Role',
    tableName: 'roles',
    columns: {
        id: {
            type: 'int',
            primary: true,
            generated: true,
        },
        name: {
            type: 'varchar',
            unique: true,
            nullable: false,
        },
        createdAt: {
            type: 'timestamp',
            createDate: true,
        },
        updatedAt: {
            type: 'timestamp',
            updateDate: true,
        },
    },
});

Explanation:

  • id: Unique identifier for each role.
  • name: A unique name for the role (e.g., “Admin”, “Editor”).
  • description: Optional detailed description of the role’s responsibilities.

1.3 UserGroup Entity

Create a file named UserGroup.js:

const { EntitySchema } = require('typeorm');

module.exports = new EntitySchema({
    name: 'UserGroup',
    tableName: 'user_groups',
    columns: {
        id: {
            type: 'int',
            primary: true,
            generated: true,
        },
        name: {
            type: 'varchar',
            unique: true,
            nullable: false,
        },
        createdAt: {
            type: 'timestamp',
            createDate: true,
        },
        updatedAt: {
            type: 'timestamp',
            updateDate: true,
        },
    },
});

Explanation:

  • id: Unique identifier for each user group.
  • name: A unique name for the user group (e.g., “Marketing Team”, “Developers”).
  • description: Optional detailed description of the user group.

1.4 User Entity

Create a file named User.js:

const { EntitySchema } = require('typeorm');

module.exports = new EntitySchema({
    name: 'User',
    tableName: 'users',
    columns: {
        id: {
            type: 'int',
            primary: true,
            generated: true,
        },
        username: {
            type: 'varchar',
            unique: true,
            nullable: false,
        },
        email: {
            type: 'varchar',
            unique: true,
            nullable: false,
        },
        password: {
            type: 'varchar',
            nullable: false,
        },
        createdAt: {
            name: 'created_at',
            type: 'timestamp',
            createDate: true,
        },
        updatedAt: {
            name: 'updated_at',
            type: 'timestamp',
            updateDate: true,
        },
    },
    relations: {
        role: {
            type: 'many-to-one',
            target: 'Role',
            joinColumn: { name: 'role_id' },
            nullable: true,
            onDelete: 'SET NULL',
        },
        usergroup: {
            type: 'many-to-one',
            target: 'UserGroup',
            joinColumn: { name: 'usergroup_id' },
            nullable: true,
            onDelete: 'SET NULL',
        },
    },
});

Explanation:

Columns:

  • id: This is the primary key, auto-incremented.
  • username: A unique and non-nullable string.
  • email: A unique and non-nullable string.
  • password: A non-nullable string for storing the hashed password.
  • createdAt: Automatically set to the current timestamp when a new record is created.
  • updatedAt: Automatically updated to the current timestamp whenever the record is updated.

Relations:

  • role: A many-to-one relationship with the Role entity. The foreign key is role_id, and if the referenced role is deleted, this relationship will be set to NULL.
  • usergroup: A many-to-one relationship with the UserGroup entity. The foreign key is usergroup_id, and if the referenced user group is deleted, this relationship will be set to NULL.

Note: Assigning role_id and usergroup_id directly in the users table establishes a Many-to-One relationship:

  • User to Role: Many users can have the same role.
  • User to UserGroup: Many users can belong to the same user group.

Step 2: Establish Relationships

Now that we have our base tables, we’ll establish the necessary Many-to-Many relationships using join tables.

2.1 Assigning Permissions to Roles

Relationship: A role can have multiple permissions, and a permission can be assigned to multiple roles.

Implementation: Create a join table role_permissions.

2.1.1 Create role_permissions Join Table

const { EntitySchema } = require('typeorm');

module.exports = new EntitySchema({
    name: 'RolePermission',
    tableName: 'role_permissions',
    columns: {
        roleId: {
            name: 'role_id',
            type: 'int',
            primary: true,
        },
        permissionId: {
            name: 'permission_id',
            type: 'int',
            primary: true,
        },
    },
    relations: {
        role: {
            type: 'many-to-one',
            target: 'Role',
            joinColumn: { name: 'role_id' },
            onDelete: 'CASCADE',
        },
        permission: {
            type: 'many-to-one',
            target: 'Permission',
            joinColumn: { name: 'permission_id' },
            onDelete: 'CASCADE',
        },
    },
});

Explanation:

  • role_id: References a role in the roles table.
  • permission_id: References a permission in the permissions table.
  • Composite Primary Key: Combines role_id and permission_id to ensure each pair is unique.
  • ON DELETE CASCADE: If a role or permission is deleted, related entries in this table are also deleted automatically.

Usage Example:

-- Assign 'CREATE_USER' permission to 'Admin' role
INSERT INTO role_permissions (role_id, permission_id)
VALUES (1, 2);

2.2 Assigning Permissions to UserGroups

Relationship: A user group can have multiple permissions, and a permission can be assigned to multiple user groups.

Implementation: Create a join table usergroup_permissions.

2.2.1 Create usergroup_permissions Join Table

const { EntitySchema } = require('typeorm');

module.exports = new EntitySchema({
    name: 'UserGroupPermission',
    tableName: 'usergroup_permissions',
    columns: {
        usergroupId: {
            name: 'usergroup_id',
            type: 'int',
            primary: true,
        },
        permissionId: {
            name: 'permission_id',
            type: 'int',
            primary: true,
        },
    },
    relations: {
        usergroup: {
            // Many-to-Many relationship (part of the join table definition)
            // This field references the 'usergroups' table via the foreign key 'usergroup_id'.
            type: 'many-to-one',
            target: 'UserGroup', // Refers to the UserGroup entity
            joinColumn: { name: 'usergroup_id' }, // The foreign key in the 'usergroup_permissions' table
            onDelete: 'CASCADE', // If a usergroup is deleted, remove the related entries in the join table
        },
        permission: {
            // Many-to-Many relationship (part of the join table definition)
            // This field references the 'permissions' table via the foreign key 'permission_id'.
            type: 'many-to-one',
            target: 'Permission', // Refers to the Permission entity
            joinColumn: { name: 'permission_id' }, // The foreign key in the 'usergroup_permissions' table
            onDelete: 'CASCADE', // If a permission is deleted, remove the related entries in the join table
        },
    },
});

Explanation:

  • usergroup_id: References a user group in the usergroups table.
  • permission_id: References a permission in the permissions table.
  • Composite Primary Key: Ensures each user group and permission pair is unique.
  • ON DELETE CASCADE: Maintains referential integrity by deleting related entries upon deletion of a user group or permission.

Usage Example:

-- Assign 'EDIT_POST' permission to 'Editors' user group
INSERT INTO usergroup_permissions (usergroup_id, permission_id)
VALUES (3, 4);

2.3 Assigning Permissions Directly to Users

Relationship: A user can have multiple permissions directly assigned, and a permission can be assigned to multiple users.

Implementation: Create a join table user_permissions.

2.3.1 Create user_permissions Join Table

const { EntitySchema } = require('typeorm');

module.exports = new EntitySchema({
    name: 'UserPermission',
    tableName: 'user_permissions',
    columns: {
        userId: {
            name: 'user_id',
            type: 'int',
            primary: true,
        },
        permissionId: {
            name: 'permission_id',
            type: 'int',
            primary: true,
        },
    },
    relations: {
        user: {
            // Many-to-Many relationship (part of the join table definition)
            // This field references the 'users' table via the foreign key 'user_id'.
            type: 'many-to-one',
            target: 'User', // Refers to the User entity
            joinColumn: { name: 'user_id' }, // The foreign key in the 'user_permissions' table
            onDelete: 'CASCADE', // If a user is deleted, remove the related entries in the join table
        },
        permission: {
            // Many-to-Many relationship (part of the join table definition)
            // This field references the 'permissions' table via the foreign key 'permission_id'.
            type: 'many-to-one',
            target: 'Permission', // Refers to the Permission entity
            joinColumn: { name: 'permission_id' }, // The foreign key in the 'user_permissions' table
            onDelete: 'CASCADE', // If a permission is deleted, remove the related entries in the join table
        },
    },
});

Explanation:

  • user_id: References a user in the users table.
  • permission_id: References a permission in the permissions table.
  • Composite Primary Key: Ensures each user and permission pair is unique.
  • ON DELETE CASCADE: Deletes related entries if a user or permission is removed.

Usage Example:

-- Assign 'DELETE_POST' permission directly to user with id 5
INSERT INTO user_permissions (user_id, permission_id)
VALUES (5, 6);

Step 3: Summary and Diagram

3.1 Summary of Relationships

  • Users
    • Belongs to one Role (role_id in users table).
    • Belongs to one UserGroup (usergroup_id in users table).
    • Has many Permissions through user_permissions.
  • Roles
    • Has many Permissions through role_permissions.
    • Has many Users.
  • UserGroups
    • Has many Permissions through usergroup_permissions.
    • Has many Users.
  • Permissions
    • Assigned to many Roles, UserGroups, and Users.

3.2 Visual Diagram

Here’s a simplified representation of the relationships:

[USERS]
   |        \
   |         \
[ROLES]    [USERGROUPS]
   |             |
   |             |
[ROLE_PERMISSIONS]   [USERGROUP_PERMISSIONS]
          \             /
           \           /
            [PERMISSIONS]
                |
                |
          [USER_PERMISSIONS]
                |
              [USERS]

Explanation:

Direct user permissions: Assign specific permissions directly to users when needed.

Users are connected directly to Roles and UserGroups via foreign keys.

Permissions are connected to Roles, UserGroups, and Users via respective join tables.

This setup allows for flexible and granular access control:

Role-based permissions: Assign common permissions to roles and then assign roles to users.

Group-based permissions: Assign permissions to user groups and assign users to these groups.

Articles

How can we help?