IDocumentOperations.QueueSqlCommand to insert into jsonb-column

[gfoidl]

E.g.

// Setup
Table myTable = new("my_table");
myTable.AddColumn("data", "jsonb");

options.Storage.ExtendedSchemaObjects.Add(myTable);

Creates a table with a jsonb-column.

internal class MyProjection : IProjection
{
    public void Apply(IDocumentOperations operations, IReadOnlyList<StreamAction> streams)
    {
        foreach (IEvent @event in streams.SelectMany(static s => s.Events))
        {
            const string Sql = "insert into my_table (data) values (?);";

            // Use the object as parameter directly
            operations.QueueSqlCommand(Sql, @event.Data);
        }
    }
}

fails with

System.InvalidCastException: Can't write CLR type XYZ with handler type TextHandler
         at Npgsql.Internal.TypeHandlers.TextHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
         at Npgsql.NpgsqlParameter.ValidateAndGetLength()
         at Npgsql.NpgsqlParameterCollection.ProcessParameters(TypeMapper typeMapper, Boolean validateValues, CommandType commandType)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Marten.Internal.Sessions.QuerySession.ExecuteReaderAsync(NpgsqlCommand command, CancellationToken token)

Trying to insert the JSON as string like

internal class MyProjection : IProjection
{
    public void Apply(IDocumentOperations operations, IReadOnlyList<StreamAction> streams)
    {
        foreach (IEvent @event in streams.SelectMany(static s => s.Events))
        {
            const string Sql = "insert into my_table (data) values (?);";

            string json = JsonSerializer.Serialize(@event.Data);
            operations.QueueSqlCommand(Sql, json);
        }
    }
}

fails with

 Npgsql.PostgresException (0x80004005): 42804: column "data" is of type jsonb but expression is of type text

Is there any way to insert JSON into a jsonb-column via IDocumentOperations?

If not, then I believe the problem results from

marten/src/Marten/Internal/Operations/ExecuteSqlStorageOperation.cs

Line 40 in 56fb16e

var dbType = PostgresqlProvider.Instance.TryGetDbType(_parameterValues[i].GetType());

(at for the "insert json as string"-case), as the type is string, so it tries to insert via TextHandler.
If this gets changed to

                var dbType = PostgresqlProvider.Instance.TryGetDbType(_parameterValues[i].GetType());

                parameters[i].NpgsqlDbType = dbType != null
                    ? dbType.Value
                    : NpgsqlTypes.NpgsqlDbType.Jsonb;

then the object could be used a parameter directly, and NpgSql would care about serialization and the insert to the jsonb-column.

Could this have any downsides? Is there possibly a better way by getting the actual underlying db-column-type and set the NpgsqlDbType based on that?

A different approach could be to pass the DB-types as arguments to QueueSqlCommand (additional overload).

[mysticmind]

Use const string Sql = "insert into my_table (data) values (?::jsonb);"; to convert the json string value to jsonb using ::jsonb

Your code changes as below:

const string Sql = "insert into my_table (data) values (?::jsonb);";
string json = JsonSerializer.Serialize(@event.Data);
operations.QueueSqlCommand(Sql, json);

[gfoidl]

Fantastic 👍🏻 Thank you!

In Execute custom SQL in session a hint could be added that Postgres casting operator can be appended to parameters and that these casts aren't changed / removed by Marten. Then it would be ideal.

Something like

"?" placeholders can be used to denote parameter values. Postgres type casts :: can applied to the parameter if needed.

If that sounds good, I can do a PR for this.

[mysticmind]

@gfoidl Sure, go ahead, we are happy to take a PR!

[mysticmind]

@gfoidl Quick note, the use case which you outlined is not a typical one which users would use especially a custom table with jsonb column (we haven't seen such usage so far).

[gfoidl]

Yep, I know. Normally the document store features are sufficient. I came accross this issue while playing around with the outbox pattern (and wanted to have the jsonb column, not a plain string / text column).

[mysticmind]

Got you!