12

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.

AnomalySmith
  • 427
  • 1
  • 5
  • 15
  • 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

2 Answers2

7

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'}));
pihvi
  • 166
  • 1
  • 4
2

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
    }
  });
Kevin Sanchez
  • 1,922
  • 2
  • 8
  • 18