Hi,
I’m using Postgres Node to Insert into a Table and it works fine, as my use case evolved I need to Upsert (Insert if missing or Updating if value already exists) and I’m having trouble with escaping fields.
To use Query operation it is necessary to interpolate strings, so if a name contains ' it will fail, also there is a special case when de value is null and should be replaced by NULL without quotes.
I’m writing a simple .replace() regex to deal with those cases, but seems error prone and hacky. Is there a better way to achieve this?
Example SQL statement:
INSERT INTO users (user_id, fullname, created_at)
VALUES('{{$json["user_id"]}}','{{$json["fullname"].replace(/'/g, '\'\'')}}', {{$json["created_at"]}})
ON CONFLICT (user_id)
DO NOTHING
RETURNING user_id;
A future feature request would be support pg-promise > ParameterizedQuery, so all escaping and type handling could be delegated to the driver.
A possible implementation using ParameterizedQuery will result on this SQL:
INSERT INTO users (user_id, fullname, created_at)
VALUES($1, $2, $3)
ON CONFLICT (user_id)
DO NOTHING
RETURNING user_id;
I’m leaving a workflow as reference:
Thanks!