Accept JSON type

[rcourivaud]

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I want to be able to show JSON column in detail page.

Could not find field converter for column questions (<class 'sqlalchemy.dialects.postgresql.json.JSON'>).

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

[aminalaee]

Hey @rcourivaud ,

Thank you for reporting this. I think this needs two steps:

• First we need to add PostgreSQL tests to the Github actions so that we have coverage: Setting up Github Actions for other databases #30
• Then we can add the JSON converter which should not be much complicated (at least for the backend side).

Feel free to do any PRs for these.

[lovetoburnswhen]

What if we used sqlalchemy-utlis to automatically serialize the data based on the engine's dialect? https://sqlalchemy-utils.readthedocs.io/en/latest/data_types.html#module-sqlalchemy_utils.types.json

This could also be used for array types https://sqlalchemy-utils.readthedocs.io/en/latest/data_types.html#module-sqlalchemy_utils.types.scalar_list

[aminalaee]

Yeah at some point we should consider sqlalchemy-utils and geo-alchemy and other libraries. It's the same process as supporting SQLModel, basically needs to define WTForm converters for those fields.
But we need the sqlalchemy.dialects.postgresql.json.JSON support anyway as it can be used without the sqlalchemy-utils library.

So I think for this issue we can focus on JSON type in PostgreSQL dialect and in another issue track sqlalchemy-utils. What do you think?

[aminalaee]

So looks like the JSON column in SQLAlchemy would cover all JSON compatible columns in databases supporting JSON like PostgreSQL and the ones not supporting like SQLite.
This should be good for now.