Nicholas Kyriakides Nicholas Kyriakides - 6 months ago 19
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).




tl;dr

Assume I have a table
User
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);
database.end();
});


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);
database.end();
});

Answer

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:

squel.insert()
    .into("User")
    .setFieldsRows([
      { idUser: 2, fullName: "John Doe" }
    ])
    .toParam()

// => { text: 'INSERT INTO User (idUser, fullName) VALUES (?, ?)',
//      values: [ 2, 'John Doe' ] }