arafays Posts - Answer Overflow

arafays

•Created by arafays on 12/28/2023 in #help

Union of schema with relations

This is code for my 2 tables where I am creating a new role and the adding its permissions

import { array, nativeEnum, string, union } from "zod";
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

export const roles = mysqlTable(
  "role",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    slug: varchar("slug", { length: 256 }).notNull().notNull(),
    name: varchar("name", { length: 256 }).unique().notNull(),
    updatedAt: timestamp("updatedAt", { mode: "date" }).defaultNow().onUpdateNow().notNull(),
  },
  (role) => ({
    roleNameIndex: index("name_idx").on(role.name),
    roleIdIdx: index("roleId_idx").on(role.id),
    roleSlug: index("roleSlug_idx").on(role.slug),
  })
);

export const rolesRelations = relations(roles, ({ one }) => ({
  permissions: one(rolePermissions, { fields: [roles.name], references: [rolePermissions.role] }),
}));

export const roleInsertSchema = createInsertSchema(roles);

export const rolePermissions = mysqlTable(
  "role-permission",
  {
    role: varchar("role", { length: 256 }).notNull(),
    permissions: varchar("permissions", { length: 256 }).$type<Permission[]>().notNull(),
  },
  (rolePermission) => ({
    compositeKey: primaryKey({ columns: [rolePermission.role, rolePermission.permissions] }),
  })
);

export const rolePermissionRelations = relations(rolePermissions, ({ one }) => ({
  role: one(roles, { fields: [rolePermissions.role], references: [roles.name] }),
}));

export const rolePermissionInsertSchema = createInsertSchema(rolePermissions, {
  permissions: array(nativeEnum(Permission)),
});
export const rolePermissionSelectSchema = createSelectSchema(rolePermissions, {
  permissions: array(nativeEnum(Permission)),
});

import { array, nativeEnum, string, union } from "zod";
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

export const roles = mysqlTable(
  "role",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    slug: varchar("slug", { length: 256 }).notNull().notNull(),
    name: varchar("name", { length: 256 }).unique().notNull(),
    updatedAt: timestamp("updatedAt", { mode: "date" }).defaultNow().onUpdateNow().notNull(),
  },
  (role) => ({
    roleNameIndex: index("name_idx").on(role.name),
    roleIdIdx: index("roleId_idx").on(role.id),
    roleSlug: index("roleSlug_idx").on(role.slug),
  })
);

export const rolesRelations = relations(roles, ({ one }) => ({
  permissions: one(rolePermissions, { fields: [roles.name], references: [rolePermissions.role] }),
}));

export const roleInsertSchema = createInsertSchema(roles);

export const rolePermissions = mysqlTable(
  "role-permission",
  {
    role: varchar("role", { length: 256 }).notNull(),
    permissions: varchar("permissions", { length: 256 }).$type<Permission[]>().notNull(),
  },
  (rolePermission) => ({
    compositeKey: primaryKey({ columns: [rolePermission.role, rolePermission.permissions] }),
  })
);

export const rolePermissionRelations = relations(rolePermissions, ({ one }) => ({
  role: one(roles, { fields: [rolePermissions.role], references: [roles.name] }),
}));

export const rolePermissionInsertSchema = createInsertSchema(rolePermissions, {
  permissions: array(nativeEnum(Permission)),
});
export const rolePermissionSelectSchema = createSelectSchema(rolePermissions, {
  permissions: array(nativeEnum(Permission)),
});

How would I make an insert schema for it so that the name from roles table is added to the insert schema and used as the role feild in rolePermissions

3 replies

DTDrizzle Team

•Created by arafays on 11/28/2023 in #help

SQL noob trying to make schema Drizzle ORM

My confusion comes from how to structure the database so that I don't save duplicate data and when I update I don't leave the data (like promoting agent to lead) I have watched a lot of tutorials but I just can't seem to get the mental model on how to do this or even what would be the best way to do this. where when a user logs in he is assigned a role

enum UserRole {
  SUPER = "super",
  ADMIN = "admin",
  HOD = "hod",
  MANAGER = "manager",
  LEAD = "lead",
  AGENT = "agent",
  USER = "user",
}

enum UserRole {
  SUPER = "super",
  ADMIN = "admin",
  HOD = "hod",
  MANAGER = "manager",
  LEAD = "lead",
  AGENT = "agent",
  USER = "user",
}

now those roles define what actions the user can perform. - agent has one lead - lead has one manager - lead is in charge of one brand - manager has one hod - manager is in charge of multiple brands The code is here https://stackoverflow.com/questions/77560683/sql-noob-trying-to-make-schema-drizzle-orm

10 replies

Gaming

Programming