How should JSONB values be passed for typescript support

[RyanHirsch]

I'm attempting to insert an optional config value into my database that is a JSONB field. The library handles this just fine and the correct values end up in my database but the typescript types get very upset. The function is below

async function sendPayment(
  from: string,
  to: string,
  amount: number,
  id: string = uuidv4(),
  config: Record<string, string|number> | null = null
) {
  await sql`INSERT INTO payment ("id", "from", "to", "amount", "reason", "status", "config")
    VALUES (${id}, ${from}, ${to}, ${amount}, 'refund', 'PENDING', ${config})
    ON CONFLICT ("id")
    DO
      UPDATE SET "status" = 'PENDING', "from" = ${from};`;
}

Is this a known limitation of the current types or am I doing something wrong?

[csjh]

I believe you're supposed to stringify the object prior to passing it? Not 100% sure right now

[porsager]

No, Postgres.js will get the types and JSON.stringify it for you ;)

[Minigugus]

Is this a known limitation of the current types or am I doing something wrong?

Sorry but yes it is: since parameters types are inferred at runtime, static types have to allow all possible parameters types, but if we add JSON parameters types, we lost almost all advantages ({ [k: string]: any } may allow objects into number columns for instance). Maybe this can change in the future, if it's too constraining for instance.

The workaround is to use a helper: sql.json(config), but I don't know if it works with JSONB thought. Another possible workaround is to use customs types, especially since typings now adds custom types types to parameters types. The last possible workaround is to simply add as any after config, in order to disable types checking for that parameter only.