Native enum support for postgres

[B4nan]

Is your feature request related to a problem? Please describe.

Currently we use text columns with check constraints instead of native enums.

Describe the solution you'd like

While using native enums on runtime should be generally supported, we don't allow using them in schema reflection/diffing or entity generator.

• add option to EnumOptions and EntityProperty to distinguish native enums (maybe also allow switching this globally)
• implement schema generator support (create/update/drop)
• implement entity generator support

[ghost]

We use mikro orm and postgres massively at work. So when I discovered this issue I spoke to my superior wether it is also interesting for us. There is a general interest there so I would like to try to implement a working solution for this problem.

If I understand it correctly, The solution has to at least fullfill these conditions:

1. If postgres is not use, nothing should change.
2. If postgres is used a enum property is not mapped to string, but to the native postgres enum type.
3. All normal Operations should still be possible and typesafety is still working
4. Enum changes have to be reflected in the migration
5. The support for native enums should be switchable via global configuration

Which points do I miss?

I have to work on it during my off hours so it might not be very fast, but if that is ok, I would start during this week (after thursday because its my mothers 60. birthday and I will be beaten up if I work during that ordeal) and try to implement a solution.

[B4nan]

Great to hear that!

If postgres is not use, nothing should change. If postgres is used a enum property is not mapped to string, but to the native postgres enum type.

Nothing should change even with postgres driver, this should be optional. We could switch the default in v6 (it will be a breaking change). But I would like to first see general community interest in that, personally as a postgres user, I am fine with current approach, but it's been some time since I worked on any web app, so my optionion is not really important :]

All normal Operations should still be possible and typesafety is still working Enum changes have to be reflected in the migration

Yes. Note that you don't have to care about migrations, but rather about schema generator support.

The support for native enums should be switchable via global configuration

I would do both global and local (in enum decorator options). I can imagine people will want to use native enums in existing apps for new things, but keep the old way so they dont have to migrate existing data.

[ghost]

Sry for the silence. Due to some work I could not start with the implementation. But the good news is, that I am able to implement during work time now, because we need this feature soon...

Today I only forked the project and got it to run the tests successfully. The reason for my hard time was that I tried to use my windows build environment, which does not work with your build. For a long time I tried to fix it so that it runs (I was somewhat successful), in the end i discarded my repo.

Instead I use wsl2 with ubuntu 20.4 and docker-desktop. With that it took me about 5 Minutes to get everything to run smoothly and all tests to work. So tomorrow I will start with the implementation.

Maybe its a good idea to add a paragraph in CONTRIBUTING.md to use wsl in windows enviroments...

PS: We rebuild our fulltext search at work, based on postgres fulltext search and hope to contribute some of our results here in the near future, if you are interested.

[vincentwinkel]

Cant wait for this enhancement!

[AzSiAz]

Should be an option, using enum in postgres come with some downside with array and index, which require custom index operator sometimes

[B4nan]

Indeed, this will be opt in, I want to keep the default as is.

[rostow]

Any news regarding this feature? Happy to help to get it done if needed

[B4nan]

@goeddek any updates, do you need help with something? Are you stuck somewhere or just lack of time?

[jbmikk]

This is also very useful when adding mikro-orm on top of databases that already exist and have been modeled with native enums (which is my case). It should definitely be configurable as different databases are going to have different needs.

[thisisomar]

Looks like they deleted their account - a bit interested in this feature Any leads on this that we can follow?

[B4nan]

Mmmm that's a shame, apparently that guy had some WIP we could at least build upon...

I guess there will need to be someone else pioneering this feature.

[B4nan]

So this is happening for v6, see initial implementation in #4296. Few notes:

• no global config toggle, as native enum needs to have a name, so instead there is a new nativeEnumName option we use to distinguish them
• postgres apparently allows only adding new items to existing enum, so missing items are ignored (won't cause a schema diff)
• postgres apparently supports only string labels, so the ORM will normalize numeric enums to string ones automatically
• no entity generator support for now - this could be quite easy, maybe someone would like to contribute that?