Omiting column names / inserting objects directly into node-postgres

Question

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 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 1

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' ] }