Postgres jsonb array objects support

[lovis-ff]

Hi, I am using kysely with postgres like this

const db = new Kysely<Database>({
    dialect: new PostgresDialect({
      pool: new pg.Pool(),
    }),
    log: ["error", "query"],
    plugins: [new CamelCasePlugin()],
  });

and jsonb columns in postgres.

Having a column with a json object works fine so far, but I now encountered problems with putting javascript arrays in json columns. I ran into the following issues:

db.updateTable("table")
        .set({
          jsonArrayColumn: []
        }).where("id", "=", "1");

results in a json object ( ‘{}’ ) being inserted into the column instead of an array (‘[]’).

db.updateTable("table")
        .set({
          jsonArrayColumn: [{ value: "someValue" }],
        })
        .where("id", "=", "1");

gives me the following error

kysely:error:` error: invalid input syntax for type json from the database.

Inspecting qb.compile() gives the correct parameters but the underlying issue seems to be with the pg client being not able to interpret the array correctly here.

Same is true for insert queries.

Are there any plans or possibilities on being able to support this out of the box? Meaning, for both, a column of postgres array type and a column of jsonb type inputting a javascript array will yield the correct result?

[igalklebanov]

https://github.com/brianc/node-postgres/issues/2680

For jsonb javascript array input, simply JSON.stringifying your input works. You'd have to change your column type to something like ColumnType<MyType[], string, string> to not get typescript errors in your insert/update queries.

It's possible to write your own PostgresCompiler and override appendValue(parameter) to stringify parameter if it's a javascript array. It's also possible to write a plugin that transforms value nodes.

[lovis-ff]

How does this behave then when it comes to plain postgres arrays? Are these considered or would the downside of #138 be that plain postgres arrays would have to be parsed like

arrayColumn = `{${(arrayColumnValue as ComlexValue[]).join(",")}}`

?

[finaloop-igal]

The downside of the current implementation (of #138 ) is when your database has a mix of sql array columns and json[b] array columns - is it really a thing?

If that's not the case.. a. all arrays are sql arrays in db, you could write a custom serializer that does a .join(',') when it receives a js array. b. all arrays are json[b] in db, you could use the default serializer.

If that's the case, some arrays are sql arrays, and some are json[b]... you're forced to serialize on your own some of the time. Was thinking about a way to pass table and column name to serializer and caster so you could decide.. not sure about it.

[igalklebanov]

This won't be added to Kysely.

Refer to #138 for an example implementation of a plugin that achieves this, and also check the comment section for more ideas.