Drizzle Team•15mo 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•15mo 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ólberg•15mo 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•15mo 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ólberg•15mo ago

got it - thanks

saM69420•14mo 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•7mo 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()`