Support JSONB columns in sql.insert's type definition

[canastro]

What version of Effect is running?

3.8.4

What steps can reproduce the bug?

const SaveItem = SqlSchema.void({
  Request: S.Struct({ jsonColumn: S.Struct({ data: S.String }) }),
  execute: (value) => sql`INSERT INTO table ${sql.insert(value)} `,
});

What is the expected behavior?

TS allows complex types in sql.insert (to be inserted into JSONB columns in postgres)

What do you see instead?

The insert actually works as expected, is just the Typechecking that fails with the following error:

No overload matches this call.
  Overload 1 of 2, '(value: readonly Record<string, Primitive | Fragment | undefined>[]): RecordInsertHelper', gave the following error.
    Argument of type '{ readonly jsonColumn: { readonly data: string; }; }' is not assignable to parameter of type 'readonly Record<string, Primitive | Fragment | undefined>[]'.
      Type '{ readonly jsonColumn: { readonly data: string; }; }' is missing the following properties from type 'readonly Record<string, Primitive | Fragment | undefined>[]': length, concat, join, slice, and 20 more.
  Overload 2 of 2, '(value: Record<string, Primitive | Fragment | undefined>): RecordInsertHelper', gave the following error.
    Argument of type '{ readonly jsonColumn: { readonly data: string; }; }' is not assignable to parameter of type 'Record<string, Primitive | Fragment | undefined>'.
      Property 'jsonColumn' is incompatible with index signature.
        Type '{ readonly data: string; }' is not assignable to type 'Primitive | Fragment | undefined'.ts(2769)

Additional information

No response

[tim-smart]

I don't think it will work for every client though. Which one are you using?

[canastro]

Yeah, I assume that the fact that we use sql.insert from the generic SQL package will make it hard so support this specific requirement from Postgres.

This is how I create the client and add it to my Runtime:

import { PgClient } from '@effect/sql-pg';

const SqlLive = PgClient.layer({
	url: Config.redacted('DB_CONNECTION_STRING'),
	transformQueryNames: Config.succeed(STR.camelToSnake),
	transformResultNames: Config.succeed(STR.snakeToCamel),
});

But then I just use the generic SQLClient interface

import { SqlClient } from '@effect/sql';

export const makeAssetRepository = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;

  const SaveItem = SqlSchema.void({
    ...

[ueueuoaix]

As a workaround, I use the following for postgresql:

sql`'${sql.unsafe(JSON.stringify(v))}'::jsonb`

But maybe we could pass the client DBMS used to onDialect or another similar method, and resolve it independently for each database? For PostgreSQL, it would be sql.json.