Support `enum` in SQLite (via polyfill)

[lglie]

Problem

Now, enum removed in sqlite

Solution

When develop time, It is very convenient to verify the schema.prisma with sqlite

Alternatives

Additional context

[pantharshit00]

Related: https://github.com/prisma/specs/issues/382

[tlonist-sang]

Has there been any update regarding this issue?

[pantharshit00]

@tlonist-sang Please see https://github.com/prisma/specs/issues/330 and https://github.com/prisma/specs/issues/382.

For now we have decided to not to polyfill database features. More details about the decision has been mentioned in the above issues. This might come in the future so stay tuned!

[devuxer]

This is a blocker for me. I can live with the database not enforcing enums (as it has no way of knowing about them), but it would be impractical to have to manually write a mapper for every model type that has an enum field just to get proper compile-time type checking and intellisense. The value of TypeScript is really compromised without this feature.

[beeman]

I would also really like to emphasize the importance of supporting this.

Having the ability to switch between SQLite and Postgres, without having to change the Prisma schema, is a very useful feature.

The main places I see it useful are testing (I'd love to be able to not spin up Postgres on CI) and starter projects (where you want to provide a working DB at start, and later suggest them to use Postgres).

[rizrmd]

We sorely need this, as switching between sqlite and postgresql are common in our project lifecycle.

[orefalo]

For me, it's all about principles - one ORM to rule them all

If the product starts adding/remove feature per database... will quickly become a nightmare to implement/maintain.

I run my tests in sqllite, prod in mysql - guess I will have to do enums outside the ORM.

Last point on this page, https://www.prisma.io/docs/concepts/more/comparisons To be fair to the other ORMs, it should reference the issue depicted above.

[eden-omb]

Why not even just use a simple string in place of an enum when using SQLite? SQLite is mostly just used for dev envs, and even if it wasn't, so long as people use prisma client enums are even enforceable. And even when they're not, why forbid it at the schema level just because of the datasource provider?

[Ali1Ammar]

any update?

[KorigamiK]

I would also really like to emphasize the importance of supporting this.

Having the ability to switch between SQLite and Postgres, without having to change the Prisma schema, is a very useful feature.

The main places I see it useful are testing (I'd love to be able to not spin up Postgres on CI) and starter projects (where you want to provide a working DB at start, and later suggest them to use Postgres).

I cannot agree more! I hope this issue surfaces again.

[john-schmitz]

What is the current project stance on this issue?

I could make a PR for it.

[ivan-kleshnin]

@lglie @john-schmitz I guess this issue would get a different reaction if you removed (via polyfill) from its title 🤔 .

Prisma devs: Can we just resolve enum to a simple text type for DBs that don't support enums. Or will it create introspection problems with diffs at migrations and elsewhere?

[homoky]

I see it as very important step towards better testing. We have right now about 6 000 test and for each we run in parralell the postgress db in docker and it is just because we need to use enums in production. This would simplify everything from the testing point.

I would not mind if it would be enabled with some flag so it is not default. But this is deal breaker for big teams from my point of view.

[alexferrari88]

having this would make our lives way easier for testing (switching between sqlite and postgres)

[juliusmarminge]

having this would make our lives way easier for testing (switching between sqlite and postgres)

This 👆👆👆 use sqlite in ci so much faster than waiting for a postgres container

[dzcpy]

Any updates? Will it be supported?

[richard-fairthorne]

I would be happy if I had to add an annotation to the schema to indicate that I am aware my target database may not enforce the enum. That way, developers can choose a schema that works everywhere, or a schema that is definitely enforced.

[ImLunaHey]

Hate to be that person but is there any update on this? From the comments everyone seems to agree this is needed one way or another. 🤔

[dorbn4]

Now that this issue is open for almost 3 years it would be nice to get some kind of update on this. Supporting enums would be amazing as you can simply use e.g. postgres for production and sqlite for local development and in CI pipelines for testing.

In the prisma documentation about enums is written "Enums are implemented and enforced at Prisma level in MongoDB", the same approach would be good enough for enum's in sqlite. If this is unacceptable for you then atleast put it behind a feature flag 🙏

On a personal note, I do think that adding enum's to sqlite on a prisma level would increase prisma's adoption and developer happiness :)

[NyllRE]

@dorbn4 well said, I think the reason people use ORM's are to not bother too much about the features of a database since an ORM's job is to do the hardwork for me. If I can't use a simple feature like enums in different databases it would be such a productivity bottleneck.

does anyone have some sort of workaround or replacement?

[richard-fairthorne]

@dorbn4 well said, I think the reason people use ORM's are to not bother too much about the features of a database since an ORM's job is to do the hardwork for me.

Yup. I use an ORM to abstract away the underlying database specific issues.

The reason I would switch to prisma is because of the wide variety of type safe language generators.

If prisma doesn't do both of these things, at least for simple cases, I can stick accessing databases directly. At least, in rust, type safety can be provided by many db connector packages which query the schema and validate SQL at compile time.

[GandelXIV]

Any updates on this?

[homoky]

Why would you not use the mocked prisma for tests?

[richard-fairthorne]

Why would you not use the mocked prisma for tests?

This does not solve the problem of requiring different schemas for different database engines, while using basic features.

[olalonde]

FWIW, it looks like SQLite does support enums: https://stackoverflow.com/a/17203007/96855

https://sqlite.org/forum/forumpost/f9d01271a397d9f5aee59b6f2e41eaf2898ac84888f373f10cb335e08450762b

[janpio]

That is a workaround that allows you to achieve close behavior, which we could use to emulate real enums.

[olalonde]

That is a workaround that allows you to achieve close behavior, which we could use to emulate real enums.

How is it different from PostgreSQL enums? Can't really tell the difference other than it's anonymous / not named.

[janpio]

Yes, that is what defines a workaround. SQLite does not support enums directly, but you use check constraints to achieve the same functionality.

[olalonde]

I don't want to argue too much about semantics but it could be argued that check (col1 in ('value1','value2','value3,'value4')) is just an enum type with an unusual syntax. Regardless, my point was that there is no need to enforce/verify the constraint client side in JavaScript via emulation/polyfill. We just need to tell the SQLite engine how to create enum columns using this syntax. I'm not familiar with Prisma architecture, maybe people meant something else by polyfill.

[janpio]

From our perspective a polyfill is, in this case, pretending that an enum exists as a construct for SQLite, so you can use enum Foo { .... } in Prisma Schema Language.

If you "just" want to have a field that behaves like an enum, that would require us to support the check constraint - which we unfortunately also do not, for any database: https://github.com/prisma/prisma/issues/3388 (That is also a reason why we can not easily just use the check constraint to emulate an enum - because we will add support for check constraints sooner or later, which will then mean we have a conflict how to present some check constraints from SQLite in PSL).

It's messy, I know.

[ladybunne]

Well, it's almost been three years since the original proposal and this still isn't implemented.

It is a very weird new user experience, to start building out a Prisma schema, see enums in the documentation, think "ooh, this would be perfect for X use case!", then have my editor complain that the connector does not, in fact, support them.

Prisma's a great library that offers a lot of benefits, but this is a bit silly. Is there an ETA on an implementation for this, preferably more specific than "sooner or later"?

[janpio]

We know that this feature would be very nice for some of our users, which is why the feature request is still open after 3 years and has not been closed. But we did not and do not have the capacity to build this right now, so it will have to wait a bit longer until we do. Sorry.

[ladybunne]

We know that this feature would be very nice for some of our users, which is why the feature request is still open after 3 years and has not been closed. But we did not and do not have the capacity to build this right now, so it will have to wait a bit longer until we do. Sorry.

Noted. Not to beat a dead horse, but...

Is there an ETA on an implementation for this, preferably more specific than "sooner or later"?

In an ideal situation the enhancement is implemented and everyone's happy. Failing that, the next best thing to provide is a considered timeline on when we can expect this, so we can make our own development decisions accordingly. "sooner or later" and "wait a bit longer" are not helpful answers in this regard.

So... can you give us a proper answer of when we can expect this to be prioritised? A few months, a year, a few years? Honesty and realism are more important than timeliness here.

[devuxer]

@ladybunne,

I'd like to know the same thing.

Taking a look at the Prisma Roadmap, I note that this feature is not on it, so the best case scenario is probably "when everything else on the roadmap is done". That said, the roadmap doesn't list estimated completion dates, so it's hard to put a number to this. If I had to hazard a guess, I'd say "at least 6 months". It would be nice if @janpio could offer something like, we are in line behind X major features, y medium features, and z small features, but I don't know how Prisma's backlog is organized.

[dzcpy]

Or, as an alternative, is supporting pg-mem easier?

[janpio]

We can not give an ETA. We do not know. It might happen next, or it might happen never. We have not prioritized this yet, and do not plan to. It is not how we work right now. Sorry.

[mattwebhub]

Ehrm.. how something apparently so small can be this big of a pain point for everyone

[Aeolun]

We have not prioritized this yet, and do not plan to.

Honestly, if after 3 years of development it has never made it close to even being considered for the roadmap I think it might as well be closed. When was the last time you went back to a 3 year old feature request without being poked?

I mean, I of course don't have any insight into the other things on the roadmap, but aside from being able to count included relations I've never really felt like Prisma missed any features. However, I've arrived at this page multiple times over the past 3 years whenever I get the dreaded But the current connector does not support enums. message.

The most glaring lack in Prisma right now is the inability to easily switch between sqlite and everything else. No reasonable person switches from MySQL to Postgres, or Postgres to Mongo while hoping to keep the same schema. But basically everyone, regardless of what other database they use, would be very happy to do the same thing with sqlite.

It's not really possible for anyone to submit a PR either, since the last we heard Prisma has decided to not polyfill features at all. Of course, that discussion is now 4 years out of date, so has anything changed in that regard?

[dzcpy]

At least there are many new ORMs comming out like orchid orm, stay alart Prisma 😂

[janpio]

If we built this, how would you expect the enum values to be stored in the database? Any concrete examples on how other ORMs and database clients handle this?

[vighnesh153]

One way would be to mark the type as string and do the enum validations at prisma level before/during insertion.

[SagnikPradhan]

Seems like both mikro-orm and typeorm use CHECK constraints

[absolutelysimon]

Agreed that it would be extremely useful to smoothly switch between SQLite and Postgres.

We would love this.

[olalonde]

As a workaround, I have resorted to using "enum tables" which are tables with a single id column and which use a seed script to populate the allowed values. It's a lot of boilerplate but it guarantees data consistency.

e.g.

// stores allowed values for user roles
model UserRole {
  id String @id
  users User[]
}

model User {
  id  Int @id @default(autoincrement())
  roleId String @default("user")
  role UserRole @relation(fields: [roleId], references: [id])
}

[andriemc]

Any updates on this?

[Andrew-Pynch]

Any updates on this? my company would love to use prisma but we NEED this feature! :-)

[andriemc]

The reason I don't prisma is because this is missing

[yanisurbis]

Is there any monetary value that we can attach to this issue? :)

[Andrew-Pynch]

Is there any monetary value that we can attach to this issue? :)

1 billion dollars

[SeanMcGoff]

I'm not even 2 days into a new project and ran into this issue. It's rather silly that this is not a feature of prisma, even as a optional config