1 reply

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!

Enum as primary key for roles (sqlite)

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!

Enum as primary key for roles (sqlite)

Enum as primary key for roles (sqlite)

Similar Threads

Similar Threads

Similar Threads