Add support for deriving enums from look-up tables

[sejr]

Problem

Right now, Prisma generates enums based on actual enum types supported in databases like Postgres.

For example, running this SQL statement

CREATE TYPE Gender AS ENUM ('Unspecified', 'Nonbinary', 'Male', 'Female');

and performing a Prisma introspection will generate the following schema:

enum gender {
  Female
  Male
  Nonbinary
  Unspecified
}

This works pretty well. However, this is a bit rigid and lacks SQLite support. One alternative, used in the Hasura GraphQL engine, is an "enum table" that Hasura converts into a GraphQL enum behind the scenes.

See their documentation for more information about the design of enum-compatible database tables. A SQL snippet for such a table is shown below:

CREATE TABLE user_role (
  value text PRIMARY KEY,
  comment text
);

INSERT INTO user_role (value, comment) VALUES
  ('user', 'Ordinary users'),
  ('moderator', 'Users with the privilege to ban users'),
  ('administrator', 'Users with the privilege to set users’ roles');

Essentially, these tables just have a unique identifier and (optionally) a description.

Suggested solution

It would be nice if a table structured this way was able to be identified and correctly parsed into a Prisma enum. Even if a special naming convention was required (e.g. _EnumUserRole), it would be really helpful especially to those who would like to use enums with SQLite.

Revisiting the Hasura example:

CREATE TABLE _EnumUserRole (
  value text PRIMARY KEY,
  comment text
);

INSERT INTO _EnumUserRole (value, comment) VALUES
  ('User', 'Ordinary users'),
  ('Moderator', 'Users with the privilege to ban users'),
  ('Administrator', 'Users with the privilege to set users’ roles');

would generate the following Prisma schema:

enum UserRole {
  User
  Moderator
  Administrator
}

[pantharshit00]

Right now we are not polyfilling any database features. Actually in Prisma 1 and in the very early versions of Prisma 2 we also used to create an enum table instead of a native type.

Here is some discussion on why we are not shimming database features for now: prisma/specs#330

This might coming in the future so I will keep this open as a feature request.

[arvindell]

This sounds like a cool idea, it would allow me to toggle between SQLite and MySQL for test and prod environments

[matthewmueller]

Would Support Enum in SQLite (via polyfill/emulation) solve the core issue here or would there be some other advantage to using a table?