DT
Drizzle Team•10mo ago
Speilegg

Enum as primary key for roles (sqlite)

I'm pretty new to Drizzle and database schemas in general so bear with me if this is stupid. I'm creating a crud application with some users with a role table. The roles are quite defined so I thought I can use a enum with the role "slug" as the ID: 
const ROLES = ["manager", "user"] as const;

export const roleTable = sqliteTable("role", {
  id: text("id", { enum: ROLES }).primaryKey(),
  name: text("name").notNull().unique(),
  description: text("description"),
});
const ROLES = ["manager", "user"] as const;

export const roleTable = sqliteTable("role", {
  id: text("id", { enum: ROLES }).primaryKey(),
  name: text("name").notNull().unique(),
  description: text("description"),
});
This does seem to work (is it a good idea though?) but the inferred roleId when querying is a string. Only when I join it is a string literal/enum: 
export const userIsAdminOfOrg = async ({ userId, orgId }: UserOrgParams) => {
  const user = await db.query.userTable.findFirst({
    where: eq(userTable.id, userId),
    with: {
      organizationUsers: {
        with: {
          role: true,
        },
      },
    },
  });

  if (!user) {
    throw new Error("User not found");
  }

  const hasAccess = user.organizationUsers.some(
    (orgUser) => orgUser.organizationId === orgId && orgUser.role.id === "manager",
  );

  if (!hasAccess) {
    throw new Error("User is not admin of this organization");
  }
};
export const userIsAdminOfOrg = async ({ userId, orgId }: UserOrgParams) => {
  const user = await db.query.userTable.findFirst({
    where: eq(userTable.id, userId),
    with: {
      organizationUsers: {
        with: {
          role: true,
        },
      },
    },
  });

  if (!user) {
    throw new Error("User not found");
  }

  const hasAccess = user.organizationUsers.some(
    (orgUser) => orgUser.organizationId === orgId && orgUser.role.id === "manager",
  );

  if (!hasAccess) {
    throw new Error("User is not admin of this organization");
  }
};
Is this intended? Is this a bad pattern? Any feedback would be greatly appreciated!
No description
No description
1 Reply
Speilegg
SpeileggOP•10mo ago
I guess rubber ducking this helped me with the inferring as I can specify the enum on the organizationUsers table: 
export const userOrganizationTable = sqliteTable("user_organizations", {
  id: text("id").primaryKey(),
  roleId: text("role_id", { enum: ROLES })
    .notNull()
    .references(() => roleTable.id, { onDelete: "restrict" }), // Using restrict to prevent deletion of roles that are in use
  userId: text("user_id")
    .notNull()
    .references(() => userTable.id, { onDelete: "cascade" }),
  organizationId: text("organization_id")
    .notNull()
    .references(() => organizationTable.id, { onDelete: "cascade" }),
});
export const userOrganizationTable = sqliteTable("user_organizations", {
  id: text("id").primaryKey(),
  roleId: text("role_id", { enum: ROLES })
    .notNull()
    .references(() => roleTable.id, { onDelete: "restrict" }), // Using restrict to prevent deletion of roles that are in use
  userId: text("user_id")
    .notNull()
    .references(() => userTable.id, { onDelete: "cascade" }),
  organizationId: text("organization_id")
    .notNull()
    .references(() => organizationTable.id, { onDelete: "cascade" }),
});
Still would love to hear any thoughts on this pattern in general 🙂

Did you find this page helpful?