Search code examples
node.jspostgresqlpg-promise

UPDATE prepared statement with Object

I have an Object that maps column names to values. The columns to be updated are not known beforehand and are decided at run-time.

e.g. map = {col1: "value1", col2: "value2"}.

I want to execute an UPDATE query, updating a table with those columns to the corresponding values. Can I do the following? If not, is there an elegant way of doing it without building the query manually?

db.none('UPDATE mytable SET $1 WHERE id = 99', map)
Solution

  • is there an elegant way of doing it without building the query manually?

    Yes, there is, by using the helpers for SQL generation.

    You can pre-declare a static object like this:

    const cs = new pgp.helpers.ColumnSet(['col1', 'col2'], {table: 'mytable'});

    And then use it like this, via helpers.update:

    const sql = pgp.helpers.update(data, cs) + /* WHERE clause with the condition */;
// and then execute it:
db.none(sql).then(data => {}).catch(error => {})

    This approach will work with both a single object and an array of objects, and you will just append the update condition accordingly.

    See also: PostgreSQL multi-row updates in Node.js

    What if the column names are not known beforehand?

    For that see: Dynamic named parameters in pg-promise, and note that a proper answer would depend on how you intend to cast types of such columns.