Sql method does not correctly handle strings with parentheses

[RadotJar]

Thank you for this project! I was very glad to stumble upon it.

I've got a use case where I need to dynamically create database columns from a json file. This json file contains the names and types of each column. E.g:

[
  { "name": "TEMP", "type": "uint8" }, 
  { "name":  "VOLTAGE", "type": "flt16" }
] 

I then map these types into data types supported by Postgres. E.g. uint8 becomes int2 and flt16 becomes real.

After this, I feed those names and mapped data types into an await sql`ALTER TABLE X ADD COLUMN ${ sql( name ) } ${ sql(mappedType) }` command using postgres.js.

The issue is that one of my types maps to the postgres type bit(4), but postgres.js does not seem to parse this correctly and I get a postgres error: code 42704: type: ''bit(4)'' does not exist. I am suspicious about the double quoting going on here and assume that the sql method does not like to parse strings with parentheses. Escaping the parentheses did not help.

I appreciate any help or thoughts on the issue.

[boromisp]

Based on the documentation sql(string) is for identifiers (e.g. table or column names). You might have to use sql.unsafe(string) here.

[RadotJar]

Thanks for the response @boromisp . That does appear to work. Why does the other syntax work for other data types such as 'real' and 'int4' though? I don't believe those are identifiers, but I find the postgres term 'identifier' a little confusing.

[boromisp]

The name of the type is bit, and the 4 is a type modifier.

I've found this error message when messing with the query syntax:

ERROR: type modifiers must be simple constants or identifiers

Based on that a relevant quote I've found in the documentation (https://www.postgresql.org/docs/17/sql-createtype.html#id-1.9.3.94.5.8):

PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers.

If you want to use safe interpolation you will have to separate the type modifier into its own value and use it like this:

const columns = [
  { name: "ColumnName", type: "bit", mod: "4" },
  { name: "simple_col", type: "int8" },
  { name: "num_col", type: "numeric", mod: ["10", "3"] },
  { name: "invalid col", type: "bit(4)" },
];

for (const { name, type, mod } of columns) {
  await sql`ALTER TABLE x ADD COLUMN ${sql(name)} ${sql(type)}${mod ? sql`(${sql(mod)})` : sql``}`
}

This will build queries like this:

ALTER TABLE x ADD COLUMN "ColumnName" "bit"("4")
ALTER TABLE x ADD COLUMN "simple_col" "int8"
ALTER TABLE x ADD COLUMN "num_col" "numeric"("10","3")
ALTER TABLE x ADD COLUMN "invalid col" "bit(4)" <- this is invalid

[RadotJar]

Ah that's interesting, thank you. Do you think it is worth updating documentation to warn others of this? I don't know how common it is for people to want to pass type modifiers like this.