PostgreSQL multi-row updates in Node.js

Question

As i already found here On Stackoverflow it is possible to update multiple rows in one query by doing something like this

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

special thanks to @Roman Pekar for the clear answer.

Now i'm trying to merge this way of updating with querying to a postgreSQL database in NodeJS.

Here is a snipped of my code:

var requestData = [
    {id: 1, value: 1234}
    {id: 2, value: 5678}
    {id: 3, value: 91011}
]


client.connect(function (err) {
    if (err) throw err;

client.query(buildStatement(requestData), function (err, result) {
    if (err) throw err;

    res.json(result.rows);

    client.end(function (err) {
        if (err) throw err;
    });
});
});


var buildStatement = function(requestData) {
var params = [];
var chunks = [];

for(var i = 0; i < requestData.length; i++) {

    var row = requestData[i];
    var valuesClause = [];

    params.push(row.id);
    valuesClause.push('$' + params.length);
    params.push(row.value);
    valuesClause.push('$' + params.length);

    chunks.push('(' + valuesClause.join(', ') + ')');

}

return {
    text: 'UPDATE fit_ratios as f set ratio_budget = c.ratio_budget from (VALUES ' +  chunks.join(', ') + ') as c(ratio_label, ratio_budget) WHERE c.ratio_label = f.ratio_label', values: params
        }
}

i don't get an error but it doesn't update my table, i don't really know what goes wrong here. Perhaps a syntax error in my query code? I just don't find any specific examples of multiple row querying when updating in NodeJS pg package

Trochaic

Answer 1

The example below is based on library pg-promise, and its method helpers.update:

// library initialization, usually placed in its own module:
const pgp = require('pg-promise')({
    capSQL: true // capitalize all generated SQL
});

const db = pgp(/*your connection details*/);

// records to be updated:
const updateData = [
    {id: 1, value: 1234},
    {id: 2, value: 5678},
    {id: 3, value: 91011}
];

// declare your ColumnSet once, and then reuse it:
const cs = new pgp.helpers.ColumnSet(['?id', 'value'], {table: 'fit_ratios'});

// generating the update query where it is needed:
const update = pgp.helpers.update(updateData, cs) + ' WHERE v.id = t.id';
//=> UPDATE "fit_ratios" AS t SET "value"=v."value"
//   FROM (VALUES(1,1234),(2,5678),(3,91011))
//   AS v("id","value") WHERE v.id = t.id

// executing the query:
await db.none(update);

This method of generating multi-row updates can be characterized as:

• very fast, as it relies on type ColumnSet that implements smart caching for query generation
• completely safe, as all data types are going through the library's query formatting engine to make sure everything is formatted and escaped correctly.
• very flexible, due to advanced ColumnConfig syntax supported for the columns definition.
• very easy to use, due to the simplified interface implemented by pg-promise.

---

Note that we use ? in front of column id to indicate that the column is part of the condition, but not to be updated. For complete column syntax see class Column and ColumnConfig structure.

---

Related question: Multi-row insert with pg-promise.

Jog

where is the v and t coming from in v.id = t.id? – Appel

@Appel From helpers.update documentation, if you just follow the links provided to you. – Jog

Answer 2

First of all, I did accept the answer from @vitaly-t, as he taught me to use a better and faster library pg-promise and it did solve my problem. (period)

But to answer my own question for the people who could end up with the same problem and want to stay with the library pg, here is where i made the mistake (it's just syntax)

In my original code i had this line at the end

return {
    text: 'UPDATE fit_ratios as f set ratio_budget = c.ratio_budget from (VALUES ' +  chunks.join(', ') + ') as c(ratio_label, ratio_budget) WHERE c.ratio_label = f.ratio_label', values: params
        }
}

The first time i saw this it was hard to understand so it was very easy to make some mistakes. changing this line of code to what you see underneath fixed my original problem to

return {
    text: 'UPDATE fit_ratios as f set ratio_budget = c.value from (VALUES ' +  chunks.join(', ') + ') as c(id, value) WHERE c.id = f.ratio_id',
                values: params
}

I just missed by using column names instead of using the keys of my object. (e.g. c.ratio_label to c.id, ...)

Trochaic

isnt this prone to SQLi? – Hanoverian