DT
Drizzle TeamSpeilegg

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
Speilegg
Speilegg•18d 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 🙂
Want results from more Discord servers?
Add your server
More Posts
How can I get a better structured response when querying a table (many to many)?Hi, I am new to sql. This might be obvious, but I'm not sure how to go about this. Below is a simplisql where column md5Hey, how can I achieve the following statement in drizzle? ```sql -- mysql SELECT * FROM podcasts_Error on Query Filter: Types of property 'table' are incompatible.I'm currently trying to filter a query as [explained in on the docs](https://orm.drizzle.team/docs/rHow to do Computed Fields / Manipulate data at Query Level?I'm looking for something like this, on prisma we can validate fields when querying the model. I cou