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.

2 options

Check only on type level

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(),
});

In this case you'll have both types and runtime check without db constraints

just tested, worked as expected

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') )" } ?

best way would be to add it manually in sql migration

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

this approach won't be perfect

it's better to have only type in dataType function

got it - thanks

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