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.

[sjrulandch]

Hello!

First time Dapper & Dapper.FastCrud user. I will need to use PostgreSQL jsonb columns. I've search around a bunch, but haven't seen a clear explanation of a solution. Here is mine, in case it helps someone.

First, postgres has two json database type json & jsonb. In order to get things working, I had to create a couple of generics so I could support both/either when defining a dto.

public class DbJsonb<T> {
        public DbJsonb() => JsonObj = default(T);
        public DbJsonb(T val) => JsonObj = val;
        public T JsonObj { get; set; }
}

public class DbJson<T> {
        public DbJson() => JsonObj = default(T);
        public DbJson(T val) => JsonObj = val;
        public T JsonObj { get; set; }
}

With these types defined, a Dapper.SqlMapper.TypeHandler could be created:

public class HandleAsJson<T> : Dapper.SqlMapper.TypeHandler<DbJsonb<T>>
{
  public override DbJsonb<T> Parse(object value)
  {
    return (value?.ToString() ?? "{}").JsonToObject<DbJsonb<T>>();
  }

  public override void SetValue(IDbDataParameter parameter, DbJsonb<T> value)
  {
    NpgsqlParameter npgsqlParam = parameter as NpgsqlParameter ;
    parameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    parameter.NpgsqlValue = value.JsonObj.ToJson();
  }
}

From here all that was left is to define the dto and it's jsonb column

public class DtoWidgit 
{
  public int MyId  { get; set; }
  public DbJsonb<MyObj> MyJsonb { get; set; }
}

public record MyObj(string Str1, string Str2);

Finally, I needed to register my handler for the record. Since I was using Fluent mapping, I did the following when defining the EntityMapping<DtoWidgit>

Dapper.SqlMapper.AddTypeHandler(typeof(DbJsonb<MyObj>), new TypeHandlers.HandleAsJson<MyObj>());

This helped. I still need to explore the source generators tip.

NOTE: This code has been truncated and freehanded. Please excuse any compiler issues.
Lastly, for completeness, the extensions used above:

public static T JsonToObject<T>(this string jsonToObj, JsonSerializerOptions options = null)
{
  return JsonSerializer.Deserialize<T>(jsonToObj, options ?? MyConstants.DefaultOptions);
}

public static string ToJson<T>(this T objToJson, JsonSerializerOptions settings = null)
{
  if (objToJson is string json) return json;
  return JsonSerializer.Serialize(objToJson, settings ?? MyConstants.DefaultOptions);
}

Dapper 2.0.123
Dapper.FastCrud 3.0.46
Npgsql 6.0.3