Returning Postgres result in nodejs with parameterized query

Question

How can I return results with parameterized query in Nodejs?

Query runs fine if I remove RETURNING*

Right now, server returns this error

error: syntax error at or near "RETURNING"

server.js

const text = "UPDATE users SET info = JSONB_SET(info, '{geometry,coordinates}', '"+coords+"') WHERE id=$1 RETURNING*";
const values = [id];

pool.query(text, values, (err, res) => {

if (err) {

//log errors
console.log(err.stack);

//return error to client

} else {

//success
//console.log(res.rows);


}
});

All the queries I've seen that use RETURNING have a space after RETURNING followed by either a * to return all fields or a list of fields to return. If there's no space after it will be seen as RETURNING*. — norie
– norie, Commented May 27, 2021 at 1:39

Grogu · Accepted Answer · 2021-05-29 16:19:56Z

To use postgres parameterized queries with node, the proper way is to use promise syntax. A similar issue was raised on Github here and here.

It seems that the problem with the syntax I'm using in my question is that that pg is reading the $1 as part of the literal string instead of a placeholder because it is wrapped in quotes.

Promise syntax seems to be fixing this issue. Well, works for me.

UPDATE Query

const queryOpts = {
text: "UPDATE users SET info = jsonb_set(info, '{geometry,coordinates}', $1) WHERE id = $2",
values: [coords, userid]
}

pool.query(queryOpts).then(response => {
console.log(response.rows)
}).catch(error => {
console.log(error)
})

SELECT Query

var email = JSON.stringify(logins.email);

const queryOpts = {
text: "SELECT * FROM users WHERE info -> 'email'=$1",
values: [email]
}

pool.query(queryOpts).then(response => {
console.log(response.rows)
}).catch(error => {
console.log(error)
})

Collectives™ on Stack Overflow

Returning Postgres result in nodejs with parameterized query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related