Nicholas Kyriakides Nicholas Kyriakides - 1 year ago 99
Node.js Question

Ommiting column names/passing directly dicitonaries in node-postgres

I'd like to pass dictionaries with column names as keys, thus avoiding declaring the column names within the query itself (typing them directly).


Assume I have a table
with 2 column names:

  • idUser(INT)

  • fullName(VARCHAR)

To create a record using node-postgres, I'll need to declare within the query the column names like so:

var idUser = 2;
var fullName = "John Doe";
var query = 'INSERT INTO User(idUser, age) VALUES ($1, $2)';

database.query(query, [idUser, fullName], function(error, result) {
callback(error, result.rows);

I'd prefer if there was a way to just pass a dictionary & have it infer the column names from the keys - If there's an easy trick I'd like to hear it.

E.g something like this:

var values = {
idUser : 2,
fullName: "John Doe"
var query = 'INSERT INTO User VALUES ($1)';

database.query(query, [values], function(error, result) {
callback(error, result.rows);

Answer Source

There's no support for key-value values in the insert statement, so it can not be done with native sql.

However, the node-postgres extras page mentions multiple sql generation tools, and for example Squel.js parameters can be used to construct sql in a way very close like what you're looking for:

      { idUser: 2, fullName: "John Doe" }

// => { text: 'INSERT INTO User (idUser, fullName) VALUES (?, ?)',
//      values: [ 2, 'John Doe' ] }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download