[Feature Request] SQL Server Enum Support in ZenStack

[Val0429]

Problem

Prisma removed support for Enum in Sqlite and SqlServer back in 2020, leading to increased boilerplate code and a lack of a single source of truth. For instance, the data model, initially defined as:

enum UserRole {
    Admin
    User
}

model User {
    id Int @id @default(autoincrement())
    username String @unique
    role UserRole
}

For the SqlServer case, it becomes:

model User {
    id Int @id @default(autoincrement())
    username String @unique
    role String
}

This means that I lose all type definitions of UserRole in TypeScript and have to manually add them, resulting in more repetitive code and a lack of a single source of truth.

There's a lengthy discussion on this issue in the Prisma GitHub repository: Link, and it's been "open" for three years. This issue is also a roadblock for me in using Prisma until I discovered Zenstack. I believe the "custom attribute" feature in Zenstack could potentially resolve this problem.

What I've Tried

Upon delving into the ZenStack codebase, I noticed a limitation in customizing @core/zod to work seamlessly with my custom attribute, thereby restricting the flexibility I require.

While the option of forking the code and making my own modifications is available, it comes with the drawback of potentially losing out on future updates and improvements to this excellent codebase.

Suggested solution

I propose adding a way to define an "enum" type for a field, like the "role" field in the example above. For instance:

model User {
    id Int @id @default(autoincrement())
    username String @unique
    role String @enum("Admin | User")
}

This would generate TypeScript using z.infer<UserModel> as follows:

enum UserRole {
    Admin,
    User
}

interface UserModel {
    id: number;
    username: string;
    role: UserRole;
}

This approach would allow us to maintain a single source of truth (the zmodel file) and achieve strong type safety, for example, with Typia and Nestia. It will also position ZenStack as an essential and powerful superset of Prisma.

[ymc9]

Hey @Val0429 , thanks for bringing this up! I wasn't aware enum is missing for SQLSever too ... Really inconvenient for me at least working with sqlite.

Using an attribute to annotate is a great idea. How are you trying to tackle it today and what limitation did you run into with Zod? I'm thinking if the main thing you care about is the generated zod, maybe you can make a derivation to the generated zod schema and enforces enums there.

I'm even thinking, since ZenStack is already a superset of Prisma, maybe we should just polyfill and make enum work natively just like Postgres? At least from the TS/Zod typing point of view. In the db they're still strings.

[Val0429]

Hello @ymc9, appreciate your interest and attention to this request! Thank you! 😊

I'm even thinking, since ZenStack is already a superset of Prisma, maybe we should just polyfill and make enum work natively just like Postgres? At least from the TS/Zod typing point of view. In the db they're still strings.

It's also the best option for me. for example, in the schema.zmodel file:

datasource db {
    provider = "sqlserver"
    url = env("DATABASE_URL")
}

enum UserRole {
    Admin
    User
}

model User {
    role UserRole
}

If @core/prisma sees that the database provider is "sqlserver" or "sqlite," which don't support enums, it could automatically do a polyfill. So, in the schema.prisma file:

/// for sqlserver, zenstack polyfilled enum
/// enum UserRole {
///    Admin
///    User
/// }

model User {
    /// for sqlserver, zenstack polyfilled UserRole to String
    role String
}

And in the Zod interface (for TypeScript):

enum UserRole {
    Admin,
    User,
}
interface User {
    role:UserRole;
}

Else, If the database allows enum, everything stays as usual.

Most developers in this case are likely more interested in TypeScript type safety rather than whether the enum is stored inside the database. Introducing this polyfill concept in ZenStack would improve the way Prisma handles enums, making the overall experience smoother.

[Val0429]

I tried to breakdown the workload of this task,

1. Add Global Config for ZenStack

In Prisma, there's a global config in the package.json that provides "schema" and "seed" arguments.

"prisma": {
    "schema": "src/prisma.schema",
    "seed": "ts-node src/prisma/seed.ts"
}

link: https://www.prisma.io/docs/reference/api-reference/command-reference

To support this in ZenStack, we can have a similar setup in the package.json:

"zenstack": {
    "schema": "src/schema.zmodel",      /// so we won't need to run `zenstack generate --schema=...` every time
    "output": "src/prisma/schema.prisma",   /// it will be even better to just take `prisma.schema` as the output location
    "polyfill": {
        "enum": true    /// provide a way to turn off if developer perfer original prisma's behavior
                        /// reserve place for other kind of polyfill
    }
}

2. Modify ZenStack VSCode Plugin

Show the message "The current connector does not support enums" only if polyfill.enum is set to false.

3. Modify @core/prisma to Support Enum Polyfill

Adjust zenstack/packages/schema/src/plugins/prisma to translate enum types,

datasource db {
    provider = "sqlserver"
    url = env("DATABASE_URL")
}

enum UserRole {
    Admin
    User
}

model User {
    role UserRole
}

into:

/// for sqlserver, zenstack polyfilled enum
/// enum UserRole {
///    Admin
///    User
/// }

model User {
    /// for sqlserver, zenstack polyfilled UserRole to String
    role String
}

Only do this translation if the database connector doesn't natively support enums.

4. Modify @core/zod

Possibly, there might be a need to modify zenstack/packages/schema/src/plugins/zod.

[ymc9]

Thanks for the detailed breakdown! Really appreciate it.

One of the first things that needs to be done is to interfere with how Prisma generates its TS types (those under "node_modules/.prisma" folder). In previous versions of ZenStack we've been avoiding to do this to avoid extra complexities, but I'm aware that we can't go too far without doing that. Polyf-illing enum can be a good first try on that route.

[Val0429]

Hi @ymc9,

I believe there's a potential to make the initial attempt at the polyfill feature more effective by making some adjustments in the @core/prisma plugin.

In the schema-generator.ts file, specifically at this line link, where we identify an Enum on SqlServer or Sqlite, we can consider stopping the use of generateEnum() and instead, incorporate it as a built-in attribute like @enum(). This change would allow us to manage it consistently throughout the code.

I'm suggesting this as a potential improvement, although I haven't delved deeply into the code. It's just a thought I wanted to share.