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
Andrii Sherman13mo 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
Jökull Sólberg13mo 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
Andrii Sherman13mo 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
Jökull Sólberg13mo ago
got it - thanks
saM69420
saM6942013mo 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
tiagobnobrega6mo 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"}
*/