Node-postgres: named parameters query (nodejs)

Question

I used to name my parameters in my SQL query when preparing it for practical reasons like in php with PDO.

So can I use named parameters with node-postgres module?

For now, I saw many examples and docs on internet showing queries like so:

client.query("SELECT * FROM foo WHERE id = $1 AND color = $2", [22, 'blue']);

But is this also correct?

client.query("SELECT * FROM foo WHERE id = :id AND color = :color", {id: 22, color: 'blue'});

or this

client.query("SELECT * FROM foo WHERE id = ? AND color = ?", [22, 'blue']);

I'm asking this because of the numbered parameter $n that doesn't help me in the case of queries built dynamically.

Answer 1

There is a library for what you are trying to do. Here's how:

var sql = require('yesql').pg

client.query(sql("SELECT * FROM foo WHERE id = :id AND color = :color")({id: 22, color: 'blue'}));

Answer 2

QueryConvert to the rescue. It will take a parameterized sql string and an object and converts it to pg conforming query config.

type QueryReducerArray = [string, any[], number];
export function queryConvert(parameterizedSql: string, params: Dict<any>) {
    const [text, values] = Object.entries(params).reduce(
        ([sql, array, index], [key, value]) => [sql.replace(`:${key}`, `$${index}`), [...array, value], index + 1] as QueryReducerArray,
        [parameterizedSql, [], 1] as QueryReducerArray
    );
    return { text, values };
}

Usage would be as follows:

client.query(queryConvert("SELECT * FROM foo WHERE id = :id AND color = :color", {id: 22, color: 'blue'}));

Answer 3

I have been working with nodejs and postgres. I usually execute queries like this:

client.query("DELETE FROM vehiculo WHERE vehiculo_id= $1", [id], function (err, result){ //Delete a record in de db
    if(err){
        client.end();//Close de data base conection
      //Error code here
    }
    else{
      client.end();
      //Some code here
    }
  });

Answer 4

Not exactly what the OP is asking for. But you could also use:

import SQL from 'sql-template-strings';

client.query(SQL`SELECT * FROM unicorn WHERE color = ${colorName}`)

It uses tag functions in combination with template literals to embed the values