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.

In [pg-promise](https://github.com/vitaly-t/pg-promise) there is very flexible [Named Parameter formatting](https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#named-parameters), even with [Raw-Text formatting](https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#raw-text), plus [Custom-Type formatting](https://github.com/vitaly-t/pg-promise#custom-type-formatting). — vitaly-t, Sep 16 '15 at 18:26
Thanks for the link, I heard about this module but didn't go further. It's what I'm looking for! — AnomalySmith, Sep 16 '15 at 21:07

score 9 · Accepted Answer · answered Nov 16 '16 at 20:42

9

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'}));

answered Nov 16 '16 at 20:42

pihvi

309
2
5

At least, seems to be a fresh solution one year later! I'll give it a try asap and let you know.Thanks :) – AnomalySmith Nov 17 '16 at 09:38
Great lib, but I prefer answers to SO answers that are based on first principles (don't require libraries), with a mention of the lib for reference in case OP wants that. – Marc H. Weiner Jan 31 '22 at 20:18

score 7 · Answer 2 · answered Nov 19 '21 at 18:46

7

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'}));

answered Nov 19 '21 at 18:46

velop

3,102
1
27
30

1

Thank you! Good util function, no need to use library for this – Artem Kolodko Jan 27 '22 at 20:17
@ArtemKolodko please also see my latest answer below. Which I think is even neater :) – velop Jan 29 '22 at 08:42
I get: `Cannot find name 'Dict'.` What imports are necessary for this to be recognized? – andymel Dec 04 '22 at 13:43
1

Hi @andymel it is a custom type I find to be handy: `interface Dict { [Key: string]: T }` – velop Dec 05 '22 at 15:37

score 1 · Answer 3 · answered Sep 16 '15 at 13:45

1

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
    }
  });

answered Sep 16 '15 at 13:45

Kevin Sanchez

2,215
2
11
19

Yeah, I omit callback but there is still the problem. – AnomalySmith Sep 16 '15 at 13:54

score 0 · Answer 4 · answered Jan 29 '22 at 08:41

0

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

answered Jan 29 '22 at 08:41

velop

3,102
1
27
30

2

will this prevent sql injection? – Novellizator Mar 14 '22 at 11:38
1

Yes, that is the purpose of sql-template-strings – velop Mar 30 '22 at 07:56
3

Feel dangerous as hell, injecting raw values like this and sanitizing it on the library level. As least fi I understood correctly. – Novellizator Mar 31 '22 at 13:06
1

dunno, you always have to trust functions at some point of time. But of course it is easier for an junior to remove SQL from the beginning of the string and missing it in a code review. Than probably my top voted function is a better fit for you. – velop Apr 01 '22 at 14:08
It's not sanitized at the library level, the library just converts this to `{ text: 'SELECT * FROM unicorn WHERE color = $1', values: [colorName] }` – Jan Aug 19 '22 at 09:27

Node-postgres: named parameters query (nodejs)

4 Answers4