tiagobnobrega Comments - Answer Overflow

tiagobnobrega

•Created by Jökull Sólberg on 5/25/2023 in #help

Simulate enums with SQLite `CHECK()`

Here's a generic solution with runtime checks based on @Andrew Sherman's solution:

const textEnum = <V extends Record<string, string>, RV = V[keyof V]>(
  columnName: string,
  enumObj: V,
  message?: string,
) => {
  const colFn = customType<{
    data: string;
    driverData: string;
  }>({
    dataType() {
      return "text";
    },
    toDriver(value: string): string {
      const values = Object.values(enumObj);
      if (!values.includes(value))
        throw Error(
          message ??
            `Invalid value for column ${columnName}. Expected:${values.join(
              ",",
            )} | Found:${value}`,
        );
      return value;
    },
  });
  return colFn(columnName).$type<RV>();
};

const textEnum = <V extends Record<string, string>, RV = V[keyof V]>(
  columnName: string,
  enumObj: V,
  message?: string,
) => {
  const colFn = customType<{
    data: string;
    driverData: string;
  }>({
    dataType() {
      return "text";
    },
    toDriver(value: string): string {
      const values = Object.values(enumObj);
      if (!values.includes(value))
        throw Error(
          message ??
            `Invalid value for column ${columnName}. Expected:${values.join(
              ",",
            )} | Found:${value}`,
        );
      return value;
    },
  });
  return colFn(columnName).$type<RV>();
};

The idea is the following usage:

export const userRoleEnum = {
  ADMIN: "ADMIN",
  USER: "USER",
} as const;
export const users = sqliteTable(
  "users",
  {
    role: textEnum("role", userRoleEnum).notNull(),
  }
);
export type UserSelect = typeof users.$inferSelect;
/* type of UserSelect:
{role: "ADMIN" | "USER"}
*/

export const userRoleEnum = {
  ADMIN: "ADMIN",
  USER: "USER",
} as const;
export const users = sqliteTable(
  "users",
  {
    role: textEnum("role", userRoleEnum).notNull(),
  }
);
export type UserSelect = typeof users.$inferSelect;
/* type of UserSelect:
{role: "ADMIN" | "USER"}
*/

12 replies

Gaming

Programming