Postgres Node: Effective way to Upsert (Insert or Update)

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:

{
 "nodes": [
   {
     "parameters": {},
     "name": "Start",
     "type": "n8n-nodes-base.start",
     "typeVersion": 1,
     "position": [
       250,
       300
     ]
   },
   {
     "parameters": {
       "functionCode": "const users = [\n  [9, \"Christopher 'Eccleston\"],\n  [10, \"David ''Tennant\"],\n  [11, \"Matt Smith\"],\n  [12, \"Peter\\\" Capaldi\"],\n  [13, \"Jodie, Whittaker\"],\n  [14, null],\n]\n\n// return user_id to test\nreturn users.map(function(u) {\n  return { json: {\n    fullname: u[1],\n    user_id: u[0],\n    created_at: new Date().getTime()\n  }}\n})\n"
     },
     "name": "Doctors",
     "type": "n8n-nodes-base.function",
     "typeVersion": 1,
     "position": [
       450,
       300
     ]
   },
   {
     "parameters": {
       "operation": "executeQuery",
       "query": "=INSERT INTO users (user_id, fullname, created_at)\nVALUES('{{$json[\"user_id\"]}}','{{$json[\"fullname\"].replace(/'/g, '\\'\\'')}}', {{$json[\"created_at\"]}}) \nON CONFLICT (user_id) \nDO NOTHING\nRETURNING user_id;"
     },
     "name": "Upsert DB",
     "type": "n8n-nodes-base.postgres",
     "typeVersion": 1,
     "position": [
       650,
       300
     ],
     "credentials": {
       "postgres": "n8n-pg"
     }
   }
 ],
 "connections": {
   "Start": {
     "main": [
       [
         {
           "node": "Doctors",
           "type": "main",
           "index": 0
         }
       ]
     ]
   },
   "Doctors": {
     "main": [
       [
         {
           "node": "Upsert DB",
           "type": "main",
           "index": 0
         }
       ]
     ]
   }
 }
}

Thanks!

If someone else, need to solve this, I created this functionItem which handles escaping most of scenarios I tried.

It copy each key and generate the escapes needed for psql, so if you have a key/field called name, it will generate a __name that can be used on your SQL syntax:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "const quoteValue = (v) => `$xyz$${v}$xyz$`\nconst newItem = item;\nfor (key in item) {\n  const pkey = `__${key}`\n  const value = item[key];\n  switch (typeof (value)) {\n    case 'string':\n      newItem[pkey] = quoteValue(value)\n      break;\n\n    case 'number':\n      newItem[pkey] = value\n      break;\n\n    case 'object':\n      if(value === null)  \n        newItem[pkey] = 'NULL'\n      else // considers it is a JSON field\n        newItem[pkey] = quoteValue(JSON.stringify(value))\n      break;\n  \n    default:\n      const errMessage = `type not found: ${typeof (item[key])} on key ${key}`\n      console.error(errMessage)\n      throw errMessage\n  }\n}\n\nreturn newItem;"
      },
      "name": "EscapeSQL",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        650,
        300
      ]
    }
  ],
  "connections": {}
}

Hi there!
I just created a PR implementing pg-promise > ParameterizedQuery solution.

Hope you’ll find it useful:

1 Like

Great, thanks a lot for the PR! We will check it out soon.

1 Like