How to use custom postgres types in a softr<>postgrs/supabase integration?

Data Sources
,
1

Let’s say I have a softr project setup with supabase/postgres where I have a custom type that looks something like this:

CREATE TYPE allowable_colors AS ENUM ('Green', 'Red', 'Blue');

I then have a field called color_choice in a user_colors table that looks something like:

color_choice allowable_colors NOT NULL

then, I have a form that uses allowable_colors. Is it possible to set it up in such a way where Softr will understand that custom type, and allow a “sync with source” to pre-populate valid single-select options for that field?

So far I have had no luck with that – I have had to un-type my fields in postgres back to plain old text or varchar and then add in the ENUM types manually in softr as input choices. I attempted to still keep the allowed_colors type in postgres (assuming it would accept them if softr sent over an allowed typed value), but it doesn’t look like softr can actually sync with custom typed fields – it throws an error (casting-related, which makes sense – e.g. the connector would have to know to do something like INSERT ... 'Green'::allowable_colors ... ).

Anyone else had experience with a setup like this? I am thinking I will have to fall back to not using custom types and either:

  • approximate types with a lookup table for each type populated by the desired enum values, then for tables that have a field of that type, add a relationship that softr can then use to reference fk-wise to sync/populate form choices – basically just using the db relationship instead of a custom type

  • find some way to efficiently templatize out forms in such a way that I don’t need to continuously/redundantly copypasta enum values as form field choices

2

I also had trouble with that. I am using airtable and wanted to migrate to either postgresql or supabase for performance improvement. However, it was not easy to get all the tailored data types you get in airtable. I wasted a lot of time and could not get much done.

3

Hey folks, ENUMs should supported if you face issues pls share details via support chat and we will check

4

@artur – re: ENUMs, are you saying they should work in the context that I mentioned above? TLDR> if a postgres field has a custom type, that softr should allow you to “sync with source” such that the ENUM values associated with the custom type can appear as desired choices if that field was mapped in a form to, say, a multiselect?

We couldn’t get that to work, so we had to fall back to a table-based approach where we create a table for a given type and add in rows that represent that type’s ENUM values. Sure would have been nicer if the first approach had worked though :wink:

cc: @gabrielco