1

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)
Code
  • 6,041
  • 4
  • 35
  • 75
  • 1
    You need provide an id. – Indent Nov 15 '17 at 11:29
  • `The columns to be updated are not known beforehand and are decided at run-time.` You forgot to specify how you intend to cast their SQL types in this case. – vitaly-t Nov 15 '17 at 12:22
  • @vitaly-t I'm not sure what you mean by cast the type. Could you explain? – Code Nov 15 '17 at 12:30
  • @Code You have a specific SQL type for your columns in the database, to which you need to adhere when updating. And you mention nothing about it, which means it is unknown how your JavaScript properties in the object are supposed to be properly escaped. i.e. what is the SQL type for `col1`, `col2`, etc...? And what is their JavaScript type? – vitaly-t Nov 15 '17 at 12:32
  • @vitaly-t The type of each column is known and the value inserted will be of the appropriate type that the database accepts. Any escaping will be automatically done by the library based on the JavaScript type of the value itself, no? – Code Nov 15 '17 at 12:47
  • @Code that's a very long assumption. Without the specifics the answer won't be very useful. Although you can start with the answer I gave, and see if you run into any type casting issues. – vitaly-t Nov 15 '17 at 12:49
  • @vitaly-t I still don't understand. Say col1 is of type integer, col2 of type text. My program start with an empty {}, then either adds a property {'col1': 123} or {'col2': 'abc'}, then runs the query with the object. What can go wrong here? Where does the casting occur? – Code Nov 15 '17 at 12:59
  • @Code in that simple example it should just work, but specifics are everything in this case ;) – vitaly-t Nov 15 '17 at 13:04

2 Answers2

1

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.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
0

Something like this :

map = {col1: "value1", col2: "value2",id:"existingId"}.

db.none("UPDATE mytable SET col1=${col1}, col2=${col2} where id=${id}", map)
Indent
  • 4,675
  • 1
  • 19
  • 35