Update of an installment form query

Question

Good afternoon, today I had a little doubt. Somewhere in my code I have a query to do an update. The function is as follows:

function updateUser(user_id, params) {
    const query = `UPDATE users 
                   SET user_name = $1,
                       user_brithday = $2,
                       user_active = $3
                   WHERE user_id = $4`;
    const queryParams = [
        params.user_name,
        params.user_brithday,
        params.user_ative,
        user_id
    ];

    return pg.query(query, queryParams);
}

What I intended was for example to send Object params without some keys, not to update to Undefined but to preserve what is already there. I have seen that it is possible to generate the query dynamically but what I wanted to know is if there is any way in the query to do this.

Answer 1

If what you want to do is ride a query dynamically with only the columns informed in the object, do the following:

Set a variable to store the amount of parameters, and the object to store the query values:

let total = 1;
let queryParams = [];

Set the variable query with the initial value:

let query = `UPDATE users SET `;

Tie for to traverse the object params, within it, check whether the value of total is greater than 1, if larger, each loop concatenates a comma and the key in the query.

if (total > 1) { query += `, `; }
query += `${key} = $${total}`;

Then add the value of the key to the object queryParams;

queryParams.push(params[key]);

Increment in the variable total:

total++;

The complete loop:

for (key in params){
  if (total > 1) { query += `, `; }
  query += `${key} = $${total}`;
  queryParams.push(params[key]);
  total++;
}

Finally, concatenate the clause WHERE in query and add the parameter user_id in the object queryParams:

query += ` WHERE user_id = $${total}`;
queryParams.push(user_id);

Working example

function updateUser(user_id, params) {
  let total = 1;
  let queryParams = [];
  let query = `UPDATE users SET `;
  for (key in params){
    if (total > 1) { query += `, `; }
    query += `${key} = $${total}`;
    queryParams.push(params[key]);
    total++;
  }
  query += ` WHERE user_id = $${total}`;
  queryParams.push(user_id);
  console.log(query);
  console.log(queryParams);
  // return pg.query(query, queryParams);
}

updateUser(1, { user_name: 'Usuário 1', user_brithday: '1960-05-30' });
updateUser(2, { user_brithday: '1990-11-12' });