Enum type

[roitman-g]

I wonder if it is possible to create enum types with Kysely? I have not found anything in docs

[koskimas]

Well, you can create anything with raw SQL using the sql template tag. You can run raw sql in migrations. On the typescript side enums work just fine. Just create an enum and use if for a field.

No enum support has been added yet to the schema builder because all database engines have different implementation for enums. Also people mean different things when they talk about enums. On postgres there's a way to create new enum types and use those as types for columns. AFAIK no other engine has something like that. Knex creates a check constraint for enums by default on postgres.

Kysely doesn't have an abstraction layer on top of SQL. Therefore there's never going to be an API like .addColumn('foo', 'text', col => col.enum(['a', 'b', 'c']) that would create different SQL on different engines.

There are already methods for creating check constraints, but you need to write the check code using raw sql.

What kind of support would you like see in kysely? And what do you mean by enum types?

[koskimas]

I can add a CREATE TYPE "my_type" AS ENUM ('a', 'b', 'c') builder for postgres. That's definitely a useful addition.

[koskimas]

On mysql enums are simple:

.addColumn('some_col', sql`enum('a', 'b', 'c', 'd', 'e')`)

You can even create a helper like this:

function enum(...args: string[]) {
  return sql`enum(${sql.join(args.map(sql.literal))})`
}

.addColumn('some_col', enum('a', 'b', 'c', 'd', 'e'))

[koskimas]

@roitman-g Any thoughts?

[vladstavarache]

For postgres you can do:

await db.schema
    .createType("status")
    .asEnum(["failed", "success"])
    .execute();

and then reference the status type:

.addColumn("status", sql`status`)

[bhgames]

When I do this, it throws a ts error saying that is not assignable to DataTypeExpression

[igalklebanov]

@vladstavarache fixed your example with sql template tag usage in .addColumn.

@bhgames check the example again.

[leleofg]

the example above worked for me! I'm using postgres
thanks @vladstavarache

I created this pr to add enum in doc: #504

[anoopsimelabs]

@vladstavarache fixed your example with sql template tag usage in .addColumn.

@bhgames check the example again.

@igalklebanov , For me the following solution worked, when I want to use different name(UserStatus) for type.

await db.schema
    .createType("UserStatus")
    .asEnum(["failed", "success"])
    .execute();

and then reference the UserStatus type:

.addColumn("status", sql`"UserStatus"`)

[0xPT]

@anoopsimelabs @vladshcherbin how would you do migrations if you need to change the type? It doesn't seem like db.schema has any way to change the type. Is it better to just do raw SQL for this?