Drizzle Role to Permissions

getPermission.js

const getUserDataWithPermission = async (res, token) => {

        const permissionList = await db.transaction(async (tx) => {
            const roleName = await tx.query.role.findFirst({ where: eq(role.id, getUserRoleID) });

            if (!roleName) {
                return res.status(401).json({ message: 'User Not Role' });
            }

            const rolePermissions = await tx.query.role_permission.findMany({
                where: eq(role_permission.role_id, roleName.id),
            });

            const permissionsmap = rolePermissions.map((item) => {
                return item.permission_id;
            });

            const permissions = await tx.query.permission.findMany({
                where: inArray(permission.id, permissionsmap),
            });

            return {
                permissions: permissions,
                role: roleName,
            };
        });

        const permissionData = permissionList.permissions.map((item) => {
            return item.name;
        });

        // decoded with come from jwt
        const user = {
            id: decoded.id,
            email: decoded.email,
            name: decoded.name,
            phone: decoded.phone,
            role_name: decoded.role_id ? permissionList.role.name : null,
            permissions: permissionData,
            avatar: decoded.avatar,
        };

        return user;

};

permission.js

import { relations, sql } from 'drizzle-orm';
import { pgTable, timestamp, uuid, varchar } from 'drizzle-orm/pg-core';
import { role_permission } from './role_permission';

export const permission = pgTable('permissions', {
    id: uuid('id')
        .default(sql`gen_random_uuid()`)
        .primaryKey(),
    name: varchar('name', { length: 255 }).unique().notNull(),
    display_name: varchar('display_name', { length: 255 }).notNull(),
    created_at: timestamp('created_at').notNull().defaultNow(),
    updated_at: timestamp('updated_at').defaultNow(),
});

export const permissionRelations = relations(permission, ({ many }) => ({
    roles: many(role_permission),
}));

role.js

import { relations, sql } from 'drizzle-orm';
import { boolean, pgTable, timestamp, uuid, varchar } from 'drizzle-orm/pg-core';
import { role_permission } from './role_permission';
import { user } from './user';

export const role = pgTable('roles', {
    id: uuid('id')
        .default(sql`gen_random_uuid()`)
        .primaryKey(),
    name: varchar('name', { length: 255 }).unique().notNull(),
    display_name: varchar('display_name', { length: 255 }).notNull(),
    can_deleted: boolean('can_deleted').notNull().default(true),
    created_at: timestamp('created_at').defaultNow().notNull(),
    updated_at: timestamp('updated_at').defaultNow(),
});

export const rolesRelations = relations(role, ({ many }) => ({
    users: many(user),
    permissions: many(role_permission),
}));

role_permission.js

import { relations, sql } from 'drizzle-orm';
import { pgTable, uuid } from 'drizzle-orm/pg-core';
import { role } from './role';
import { permission } from './permission';

export const role_permission = pgTable('role_permissions', {
    id: uuid('id')
        .default(sql`gen_random_uuid()`)
        .primaryKey(),
    role_id: uuid('role_id')
        .references(() => role.id)
        .notNull(),
    permission_id: uuid('permission_id')
        .references(() => permission.id)
        .notNull(),
});

export const role_permissionRelations = relations(role_permission, ({ one }) => ({
    role: one(role, {
        fields: [role_permission.role_id],
        references: [role.id],
    }),
    permission: one(permission, {
        fields: [role_permission.permission_id],
        references: [permission.id],
    }),
}));

user.js

import { relations, sql } from 'drizzle-orm';
import { boolean, integer, pgEnum, pgTable, timestamp, uuid, varchar } from 'drizzle-orm/pg-core';
import { role } from './role';

export const user = pgTable('users', {
    id: uuid('id')
        .default(sql`gen_random_uuid()`)
        .primaryKey(),
    role_id: uuid('role_id')
        .references(() => role.id)
        .notNull(),
    name: varchar('name', { length: 255 }).notNull(),
    avatar: varchar('avatar', { length: 255 }),
    email: varchar('email', { length: 255 }).unique().notNull(),
    phone: varchar('phone', { length: 255 }),
    password: varchar('password', { length: 255 }).notNull(),
    created_at: timestamp('created_at').notNull().defaultNow(),
    updated_at: timestamp('updated_at').defaultNow(),
});

export const userRelations = relations(user, ({ one, many }) => ({

    role: one(role, {
        fields: [user.role_id],
        references: [role.id],
    }),

}));