How to insert JSONB values in Postgres database

[sharpoverride]

Hi,

I would like to leverage FastCRUD to also insert columns that have JSONB types in Postgres.

I've been scanning the source code, but I don't see an example of this. None of the issues seem to match either.

Is there a way to have a complex type automatically converted to a json value for insert/update, and vice-versa for retrieval?

Thank you.

[MoonStorm]

Can't you do the conversion prior to using it in the entity? Not sure how Dapper handles this type.

[sharpoverride]

Hi,

Dapper has a mechanism to handle it through SqlMapper.AddTypeHandler(). FastCrud however will consider it a Text field. Even if I convert it beforehand, I still need to have the serialized value converted to the proper column type e.g. '['serialized'::jsonb

It would be nice to be able to achieve a similar approach with FastCRUD by leveraging OrmConfiguration. At the moment my List Property is not even registered with the entity, which means I can't query for it.

I ended up using something like this.

 OrmConfiguration.GetDefaultEntityMapping<Workshop>()
                .SetProperty(p => p.Features, prop =>
                prop.SetDatabaseColumnName(nameof(Workshop.Features))
                );

And I created a sql statement

  InsertWorkshopSql = OrmConfiguration.GetSqlBuilder<Workshop>().Format(
                    $@"INSERT INTO {nameof(Workshop):T}({nameof(Workshop.Name):C},
                    {nameof(Workshop.Features):C})
                    values (@{nameof(Workshop.Name)}, @{nameof(Workshop.Features)})
                RETURNING *");

I have the proper type handler defined in Dapper, and so I can do a basic query.

 results = con.Query<Role>(InsertWorkshopSql , new Workshop
                    {
                        Name = workshopName,
                        Features = featuresList
                    });

The configuration that FastCRUD exposes should be extended to support custom types, I just don't have enough knoweldge of the code base to handle this.
Maybe if you can provide some direction on what would need to be done to support it?

[srbenjamin]

I struggled with this issue as well when trying to use PostgreSQL’s date-range capabilities. The trick I discovered was to implement Range<T> and DateRange SqlMapper handlers as you would for standard Dapper use and then manually register the members using the SetProperty method mentioned above. This approach seems to force the custom fuel types to register. Hopefully this saves someone else the time I spent deciphering this issue.