Postgresql, nodejs and parameterized queries

Once we've install pg (npm i pg) we can import the Pool object and pass it a connectionString.

const {Pool} = require('pg')
const connectionString = `postgresql://postgres:YOUR_PASSWORD@localhost:4000/YOUR_DB"
const pool = new Pool({ connectionString, ssl: false })

Then we pass pool an sql string and a list of parameters, thereby avoid sql injection attacks. Finally we wait for a promise to resolve with a list of our sql rows as javascript objects.

pool.query("select * from cats where id = $1;", [100])
.then(result => {
  console.log(result.rows)
})
.catch(err => {
  console.log(err)
})

pg allows us to escape SQL literal. If you want to escape SQL identifiers then pg-format (npm i pg-format) with its %I and %L format strings will help:

const pgFormat = require('pg-format')
var sql = pgFormat("select * from %I where id = %L", "cat", 100)
javascript postgresql sql nodejs

Edit on github
comments powered by Disqus
Click me