Fragment not supported in insert helper?

[Pyrolistical]

I have a Buffer which I want to cast to a UUID during insert, and this works.

sql`
  INSERT INTO "some_table"(id, name)
  VALUES
    (CAST(ENCODE(${id}, 'hex') AS UUID), ${name})
`

I need to do that cast a lot, so I extracted as as_uuid:

const as_uuid = (id: Buffer) => sql`CAST(ENCODE(${id}, 'hex') AS UUID)`
sql`
  INSERT INTO "some_table"(id, name)
  VALUES
    (${as_uuid(id)}, ${name})
`

But but when I convert that to use the insert helper, I get a type error.

const as_uuid = (id: Buffer) => sql`CAST(ENCODE(${id}, 'hex') AS UUID)`
sql`
  INSERT INTO "some_table"
    ${sql({
      id: as_uuid(id), // errors with Index signature for type 'string' is missing in type 'PendingQuery<any>'
      name
    })}
`

Is this a missing feature or a type bug?

[porsager]

You can't put fragments as parameter values, so that's why.

I think you should look into custom types instead as a way to solve it, by converting the Buffer you have to a hex string in the serialize function.

[oneHoopoe]

Hi! I don't know if anything changed in the meanwhile, or I misunderstood something, but it seems that fragments used as parameter values inside insert helpers can be used, and they do seem to work.

import postgres from "postgres";

const sql = postgres(/* Some connection values */);

// await sql`
//  CREATE TABLE test_table (
//      id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
//      user_name TEXT NOT NULL
//  )
// `

console.log(
  await sql`
    INSERT INTO test_table ${sql({
        id: sql`DEFAULT`,
        user_name: "test"
    })};
  `.describe()
); /* Prints out:
{
  string: '\n\t  INSERT INTO test_table ("id","user_name")values(DEFAULT,$1);\n\t',
  types: [ 25 ],
  name: 'jpf8ugf8dl2',
  columns: []
}
*/

await sql`
  INSERT INTO test_table ${sql({
    id: sql`DEFAULT`,
    user_name: "test"
})};
` // Succesfully inserts a row

sql.close();

However, it seems TypeScript types won't allow it and will throw an error mentioned in the issue. For now, I have been able to mitigate it by adding Fragment type to ParameterOrJSON.

 type ParameterOrJSON<T> =
   | SerializableParameter<T>
   | JSONValue
+  | Fragment

I haven't spotted any problems with this solution, though I currently don't use any other methods using this type, such as unsafe or file.

While I was looking for a fix in these past days, I found that one of the tests also seems to check for this use case.

https://github.com/porsager/postgres/blob/f58cd4f3affd3e8ce8f53e42799672d86cd2c70b/tests/index.js#L2420-L2435

Pasted over to TypeScript, this example exhibits the same error.

import postgres from "postgres";

const sql = postgres();

await sql`insert into test ${sql({
    a: 2,
    b: sql`exists(select 1 from test where b = ${true})`,
})}`; // A long error stack ending with: Index signature for type 'string' is missing in type 'PendingQuery<Row[]>'.

TypeScript Playground link

PS: Thank you for mantaining this amazing library!