Passing raw object to JSON field results in typescript error

[hanszoons]

I have a table with a column of type 'JSON' (plain, not JSONB). I have a js object that I want to store in the database.

I am using the following (simplified) function:

/**
 * @param {object} object_json
 * @returns {Promise<void>}
 */
export async function insert_json(object_json) {
  await sql`
    INSERT INTO 
      schema.table (field_json)
    VALUES (${object_json}::json)
  `;
}

This works, but vscode returns a typescript error that I can't make much of:

No overload matches this call.
  Overload 1 of 2, '(first: TemplateStringsArray, ...rest: never): PendingQuery<Row[]>', gave the following error.
    Argument of type 'object' is not assignable to parameter of type 'never'.
  Overload 2 of 2, '(template: TemplateStringsArray, ...parameters: readonly ParameterOrFragment<bigint>[]): PendingQuery<Row[]>', gave the following error.
    Argument of type 'object' is not assignable to parameter of type 'ParameterOrFragment<bigint>'.ts(2769)

I've tried all kinds of things to get rid of it, but I don't see the error. I found a similar comment here: #556 and here #415, but these were closed with no mention of solution.

Any idea what I am doing wrong?

Thanks!

[net]

I've had to revert to using the deprecated ${sql.json(object)}.

---

This query

sql`SELECT * FROM json_to_recordset(${object}::json) ...`

also gave me the error PostgresError: cannot call json_to_recordset on a scalar, while while sql.json did not.

[carycodes]

I can't comment on the json_to_recordset issue, but the first example in this thread is a typing problem and not a functionality problem, so you can also just tell TypeScript to stop caring via something like this:

export async function insert_json(object_json) {
  await sql`
    INSERT INTO 
      schema.table (field_json)
    VALUES (${object_json as any})
  `;
}

(I believe the ::json is unnecessary also, since it's going to get converted to json regardless to fit in a json column.)

This as any casting noise isn't ideal, but at least it avoids calling a deprecated function.

The only weirdness I've seen with this method (which is also an issue with sql.json) is that it interprets a JavaScript null as a SQL null, not a JSON null. This may or may not be what you want. If you specifically wanted a JSON null, you have to convert that to 'null' (a string) yourself before passing it in.

[hanszoons]

Yes, typing the object as 'any' works. (That 'as'-construction only works in actual TypeScript files though, I use plain javascript wit JSDoc typings.) Without the ::json the object get's stringified in stead of being stored as an actual json object in the db.

Still feels a bit wrong, but I'll cary on with any.

[carycodes]

Without the ::json the object get's stringified in stead of being stored as an actual json object in the db.

Interesting. I'm not sure what's different but that's not the behavior I'm seeing. I tried just now and when I do

await sql`INSERT INTO json_test VALUES (${obj})`

I get an actual JSON object in the database. I can query it using the JSON operators like ->.

To get a string there, I have to stringify it myself; something like

await sql`INSERT INTO json_test VALUES (${JSON.stringify(obj)})`

For reference I'm using v3.3.4 of this library and Postgres 15.3 via docker.

[porsager]

This seems related to #392 . Reopen if not.