Drizzle Team•2y ago

Simulate enums with SQLite `CHECK()`

I'm a heavy enum user in Postgres. Using SQLite now, wondering if anyone has come up with something clever to narrow a TEXT value to some values, perhaps based on CHECK() constraints, which kind of give you what ENUM does.

6 Replies

Andrii Sherman•2y ago

2 options Check only on type level

role: text('content').$type<'admin' | 'member'>().notNull(),

role: text('content').$type<'admin' | 'member'>().notNull(),

and to make runtime check available as well to can use custom types https://orm.drizzle.team/docs/custom-types

const someSortOfEnum = customType<{
  data: string;
  driverData: string;
}>({
  dataType() {
    return "text";
  },
  toDriver(value: string): string {
    if (!["admin", "user"].includes(value)) throw Error("roles can be only admin or member");
    return value;
  },
});

export const table = sqliteTable("table", {
  role: someSortOfEnum("content").$type<"admin" | "member">().notNull(),
});

const someSortOfEnum = customType<{
  data: string;
  driverData: string;
}>({
  dataType() {
    return "text";
  },
  toDriver(value: string): string {
    if (!["admin", "user"].includes(value)) throw Error("roles can be only admin or member");
    return value;
  },
});

export const table = sqliteTable("table", {
  role: someSortOfEnum("content").$type<"admin" | "member">().notNull(),
});

In this case you'll have both types and runtime check without db constraints just tested, worked as expected

Jökull SólbergOP•2y ago

Awesome! I guess I could add the CHECK in the migration if I want, for DB consistency too Or even dataType() { return "TEXT CHECK( role IN ('admin','user') )" } ?

Andrii Sherman•2y ago

best way would be to add it manually in sql migration

dataType() { return "TEXT CHECK( role IN ('admin','user') )" }

dataType() { return "TEXT CHECK( role IN ('admin','user') )" }

this approach won't be perfect it's better to have only type in dataType function

Jökull SólbergOP•2y ago

got it - thanks

saM69420•2y ago

sqlite has an enum option on the text type that does everything except adding the CHECK constraint. it's in the old docs just not the new ones https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/README.md#column-types but no runtime checks, but it plays nice with drizzle-zod

tiagobnobrega•11mo ago

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"}
*/

Gaming

Programming

Simulate enums with SQLite `CHECK()`