Update multiple columns and values node postgres

Question

Using node-postgres I want to update columns in my user model, at present i have this

async function update_user_by_email(value, column, email){
  const sql = format('UPDATE users SET %I = $1,  WHERE email = $2', column);
  await pool.query(sql, [value, email]);
}

So I can do this

await  update_user_by_email(value, column_name, email_address);

However if I want to update multiple columns and values I am doing something very inefficient at the moment and calling that method x amount of times (i.e for each query)

await update_user_by_email(value, column_name, email_address);
await update_user_by_email(value_2, column_name_2, email_address);
await update_user_by_email(value_3, column_name_3, email_address);

How can I generate this with just one call to the database.

Thanks

Check out [PostgreSQL multi-row updates in Node.js](https://stackoverflow.com/questions/39119922/postgresql-multi-row-updates-in-node-js) — vitaly-t, Jun 29 '18 at 19:17
Hey there, what library is the format() function you're using from? — Ulsting, Aug 29 '19 at 02:02

score 2 · Answer 1 · answered Jun 22 '18 at 23:52

You have a few options here:

node-postgres allows you to create queries based on prepared statements. (This builds on the native pg-sql prepared statements).

These are recommended by postgres for populating a table as a secondary option to using their copy command. You would end up doing more SQL statements (probably one per line), but the advantages of prepared statements are supposed to offset this somewhat.

You can also combine this with transactions, also mentioned in the postgres "populate" link above.
Another option is the approach taken by another library called pg-promise (specifically helpers). The pg-promise helpers library literally builds sql statements (as a string) for the bulk insert/update statements. That way you can have a single statement to update/insert thousands of rows at a time.
It's also possible (and relatively easy) to custom-build your own sql helpers, or to supplement pg-promise, by pulling structure data directly from information_schema tables and columns tables.

One of the more tedious things about pg-promise is having to give it all the column names (and sometimes definitions, default values, etc), and if you're working with dozens or hundreds of separate tables, auto-generating this info directly from the database itself is probably simpler and more robust (you don't have to update arrays of column names every time you change your database)

NOTE: You don't need to use pg-promise to submit queries generated by their helpers library. Personally, I like node-postgres better for actual db communications, and typically only use the pg-promise helpers library for building those bulk SQL statements.

NOTE2: It's worth noting that pg-promise wrote their own SQL injection protection (by escaping single-quotes in values and double-quotes in table/column names). The same would need to be done in the third option. Whereas the prepared statements are natively protected from sql injection by the database server itself.

Update multiple columns and values node postgres

1 Answers1