prisma
Support `enum` in SQLite (via polyfill)
Problem
Now, enum removed in sqlite
Solution
When develop time, It is very convenient to verify the schema.prisma with sqlite
Alternatives
Additional context
@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!
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.
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).
We sorely need this, as switching between sqlite and postgresql are common in our project lifecycle.
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.
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?
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.
What is the current project stance on this issue?
I could make a PR for it.
@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?
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.
having this would make our lives way easier for testing (switching between sqlite and postgres)
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
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.
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. 🤔
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 :)
@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?
@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.
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.
FWIW, it looks like SQLite does support enums: https://stackoverflow.com/a/17203007/96855
https://sqlite.org/forum/forumpost/f9d01271a397d9f5aee59b6f2e41eaf2898ac84888f373f10cb335e08450762b
That is a workaround that allows you to achieve close behavior, which we could use to emulate real enums.
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.
Yes, that is what defines a workaround. SQLite does not support enums directly, but you use check constraints to achieve the same functionality.
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.
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.