Drizzle Team•7mo ago

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!

1 Reply

SpeileggOP•7mo 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 🙂

Gaming

Programming

Enum as primary key for roles (sqlite)