PG prepared statement

pg npm package is one of the good packages to work with postgres database in nodejs. It supports prepared statement. So you can use it in your code.

For security reason, you should use prepared statement when querying to your database with parameters. This is a great approach to fight against sql injection.

Today I was struggling to figure out how to pass array type parameters.

Had to do a lot of trial and error after not finding direct helpful documentation or materials.

So this article is to help others (including me :smile:) in future.

Here I will show some examples on how to use that. This example is from the official documentation.

// Using parameters with query
const query = {
  text: 'SELECT $1::text as first_name, $2::text as last_name',
  values: ['Brian', 'Carlson'],
  rowMode: 'array',
}
 
const res = await client.query(query)
console.log(res.fields.map(field => field.name)) // ['first_name', 'last_name']
console.log(res.rows[0]) // ['Brian', 'Carlson']

This shows how to pass parameters to prepared statement.

The documentation is not clear about how to pass array type parameters.

Here is one example,

const query = {
  text: 'SELECT * FROM "users" WHERE id = ANY ($1::uuid[]),
  values: [['id1', 'id2']],
  rowMode: 'array',
}
 
const res = await client.query(query)
console.log(res.fields.map(field => field.name)) // ['first_name', 'last_name']
console.log(res.rows[0]) // ['Brian', 'Carlson']

Let me know if this was helpful. Also don’t forget to share your thoughts and suggestions in the comment box.