text[] is being returned as string

[joshxyzhimself]

Hi everyone!

Long story short, I was using Kysely + Postgres.js using kysely-postgres-js, however the columns with text[] type are being returned as unparsed string array instead of a string array.

Returned value is '{example,example2}' instead of ['example', 'example2']

Related issue is here: https://github.com/igalklebanov/kysely-postgres-js/issues/8

The related issue contains a comparison example of outputs from kysely and postgres.js. The SELECT * FROM roles statement at postgres.js returns the correctly parsed output; kysely does not.

@igalklebanov said:

• Kysely, nor this dialect, perform any data transformation. The underlying driver (postgres.js) could be responsible.
• It could be that the reserved connections are behaving differently.

However I tried to test the reserved connection below:

const reserved = await sql.reserve();
const porsager_postgres_select_reserved = await reserved`SELECT * FROM private.roles`;
await reserved.release();
console.log(JSON.stringify({ porsager_postgres_select_reserved }, null, 2));

The output seems fine:

{
  "porsager_postgres_select_reserved": [
    {
      "id": "cb5c9d7a-15a6-4d9a-bd6d-dac958a3bd03",
      "name": "administrator",
      "scope": [
        "example",
        "example2"
      ]
    }
  ]
}

My question is, could there be other things that could cause the text[] column to not be parsed correctly?

Thank you!

[joshxyzhimself]

My instantiation code if it helps.

import postgres from "postgres";
import { Kysely } from "kysely";
import { PostgresJSDialect } from "kysely-postgres-js";
import type { KyselyDatabase } from "./types/index.mjs";

const POSTGRES_HOST = process.env["POSTGRES_HOST"] || "localhost";
const POSTGRES_PORT = 5432;
const POSTGRES_DB = process.env["POSTGRES_DB"] || "postgres";
const POSTGRES_USER = process.env["POSTGRES_USER"] || "postgres";
const POSTGRES_PASSWORD = process.env["POSTGRES_PASSWORD"] || "postgres";

export const pg = new Kysely<KyselyDatabase>({
  dialect: new PostgresJSDialect({
    postgres: postgres({
      database: POSTGRES_DB,
      host: POSTGRES_HOST,
      port: POSTGRES_PORT,
      user: POSTGRES_USER,
      password: POSTGRES_PASSWORD,
      max: 10,
      types: {
        date: {
          to: 1184,
          from: [1082, 1083, 1114, 1184],
          serialize: (x: string) => x, // TypeScript to PostgreSQL
          parse: (x: string) => x, // PostgreSQL to TypeScript
        },
      },
    }),
  }),
});

export default pg.withSchema("private");

[joshxyzhimself]

Hi just an update, I temporarily reverted into using node-postgres dialect to compare results and the results are below:

postgres.js

export const postgres_js_dialect = new PostgresJSDialect({
  postgres: postgres({
    database: POSTGRES_DB,
    host: POSTGRES_HOST,
    port: POSTGRES_PORT,
    user: POSTGRES_USER,
    password: POSTGRES_PASSWORD,
    max: 10,
    types: {
      date: {
        to: 1184,
        from: [1082, 1083, 1114, 1184],
        serialize: (x: string) => x, // TypeScript to PostgreSQL
        parse: (x: string) => x, // PostgreSQL to TypeScript
      },
    },
  }),
});

{
  user_role: {
    id: 'a45d7709-4159-46a5-a1c2-b41db62e7ff3',
    name: 'user',
    scope: '{account,search}'
  }
}

node-postgres

pg.types.setTypeParser(pg.types.builtins.TIMESTAMPTZ, (value) => {
  if (typeof value === "string") {
    return luxon.DateTime.fromSQL(value).toISO() as string;
  }
  return value;
});
export const pg_dialect = new PostgresDialect({
  pool: new pg.Pool({
    database: POSTGRES_DB,
    host: POSTGRES_HOST,
    port: POSTGRES_PORT,
    user: POSTGRES_USER,
    password: POSTGRES_PASSWORD,
    max: 10,
  }),
});

{
  user_role: {
    id: 'a45d7709-4159-46a5-a1c2-b41db62e7ff3',
    name: 'user',
    scope: [ 'account', 'search' ]
  }
}

references

• https://kysely.dev/docs/getting-started (usage instructions documenting node-postgres usage)

[porsager]

Thanks a lot for the description @joshxyzhimself ! There was indeed an issue with reserved connections not being properly initialized. I've pushed a fix, and I'll make a release now.

[joshxyzhimself]

It works now, thank you very much!