How to INSERT data to Postgres with node-postgres?

[nktamanakov]

Hi!
I’m complete begginer at node.js and js. Trying to insert data to Postgres but getting errors:
> at Connection.null (/opt/ee/node_modules/pg/lib/client.js:132:36)

at Object.onceWrapper (events.js:519:28)
at Connection.emit (events.js:412:35)
at Socket.null (/opt/ee/node_modules/pg/lib/connection.js:57:12)
at Socket.emit (events.js:400:28)
at TCP.null (net.js:686:12)

Here my steps.postgresql:

async(event,steps,auths) => {
const { Client } = require('pg')

const { host, user, password, port, database } = auths.postgresql

const client = new Client({
  host,
  database,
  user,
  password,
  port,
})

const text = 'INSERT INTO ss_subs(userid, fullname, currentdate) VALUES($1, $2, $3)'
const values = Object.values(event.body)

await client.connect()

await client.query(text, values, (err, res) => {
  if (err) {
    console.log(err.stack)
  } else {
    console.log(res.rows[0])
     
  }
 })

await client.end()
}

[pierce]

Hi @nktamanakov welcome to the Pipedream community! Happy to have you here.

Thanks for the error message & code example, very helpful.

The error message sounds like it is saying the connection wasn’t made from your workflow action to your Postgres database.

Can you double check that a postgresql account is linked under “Accounts” on the left hand side of the navigation in Pipedream?

If so, and the credentials are correct, perhaps Postgres is bound to a specific IP address (usually 127.0.01.). This means it’s only accepting connections to the local host, not outside connections.

Which service (AWS, etc) is hosting this Postgres database?

[nktamanakov]

Hi @pierce!
Glad to meet you and thank you for answering!

Checked connected accounts: postgress in ON

[nktamanakov]

Also checked simple Select-query and its works

[nktamanakov]

But Insert-query at the next step of the same workflow still giving me errors:

[pierce]

Thanks @nktamanakov, screenshots speak a thousand words.

I was able to reproduce the issue on a brand new action. It appears that the output from the postgres insert statement always produces an error, like the one you shared.

However, it’s odd because for me it actually inserts the data into the database, just falsely errors in the workflow.

We’re investigating and I’ll get back to you when it’s fixed.

[pierce]

@nktamanakov could you please share that workflow with me as well? We may have found something, but just want to confirm it’s the same setup on your end.

You can add me (pierce@pipedream.com) or make it public and share the link here.

[pierce]

@nktamanakov Think we may have a solution for you, I tested it on my own workflow and it seems to solve the problem.

Instead of using the callbacks version of the PD query, try using the await query()way instead.

It may be that the workflow step is closing because the callback way doesn’t stop the workflow from “finishing”. This causes a race condition between the query & the workflow step.

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', 'brian.m.carlson@gmail.com']

// async/await
try {
  const res = await client.query(text, values)
  console.log(res.rows[0])
  // { name: 'brianc', email: 'brian.m.carlson@gmail.com' }
} catch (err) {
  console.log(err.stack)
}

[nktamanakov]

@pierce with yours last solution and some dances with a tambourine it’s finally works!

Thank you so much for a great support!

[pierce]

feels good when things just jam together

Awesome, happy for you @nktamanakov let us know if we can help you with anything else.

If you have a minute, could you leave us a review on Capterra?

https://reviews.capterra.com/new/242217