NodeJS MySQL prepared statements

Hello! I have the following code (implementation of mysql2 npm package:

Items.insertItem = (
  room,
  supplier,
  name,
  invoiceNumber,
  purchaseDate,
  inUse
) => {
  return new Promise((resolve, reject) => {
    connection.query(
      "INSERT INTO Items(room_id,supplier_id,name,invoice_number,purchase_date,in_use) VALUES (?,?,?,?,?,?);",
      [room, supplier, name, invoiceNumber, purchaseDate, inUse],
      (err, results) => {
        if (err) return reject(err);
        return resolve(results[0]);
      }
    );
  });
};

I would like to implement the same thing with UPDATE (for a PUT), but I’m having a hard time figuring out how to prepare such a statement, since in an update one could not touch all fields, so I don’t know beforehand how many placeholders to put. Anybody knows a workaround?

Hello!

If the problem is just the placeholders, simply count the arguments passed to the function (before the promise):

// Count arguments:
const argLen = arguments.length

// Then, inside the promise:
const placeHolders = Array(argLen).fill('?').join(','); // Would be: ?, ?, ? if argLen is 3.

Thank you for your answer!
So you mean something like

Items.updateItem = () => {
  const argLen = arguments.length;
  const placeHolders = Array(argLen).fill('?').join(',');
  return new Promise((resolve, reject) => {
    connection.query(
      "UPDATE Items(room_id,supplier_id,name,invoice_number,purchase_date,in_use) VALUES (?,?,?,?,?,?);",
      arguments == '' ? null : arguments,
      (err, results) => {
        if (err) return reject(err);
        return resolve(results[0]);
      }
    );
  });
};

? (probably all wrong hehe)

Sorry for the late response, I forgot to subscribe to the thread :sweat_smile:.

In case you haven’t solved it yet, yes, something like that. I mean, the first part is correct (argLen and placeHolders), but you didn’t use it in the end.

Besides that, I’m pretty sure that’s not the correct syntax for the SQL UPDATE.

You should be using UPDATE table_name SET column = ?, column1 = ? WHERE condition.

However, I didn’t think about this before, but what I wrote will not work for this update. If you don’t know how many columns there will be, you would need something like this:

function dynamicUpdate(update) {
  let ps = 'UPDATE table SET ';
  const columns = [];
  const values = []

  for (let prop in update) {
    // _ is lodash: npm i --save lodash
    // const _ = require('lodash');
    // https://lodash.com/docs/4.17.15#snakeCase
    const snake = _.snakeCase(prop);
    columns.push(`${snake} = ?`);
    values.push(update[prop]);
  }

  return [
    `UPDATE table SET ${columns.join(',')}`,
    values
  ]
}

const query = dynamicUpdate({
	room: 1,
  supplier: 1,
  name: 'a name',
  another: 'value'
});

Thank you for the response!
Can you please explain the code? I never used lodash module before :sweat_smile:

In regards to lodash, it’s a collection of utility functions and here I’m only using it to convert the object properties to snake case: _.snakeCase(string). It would covert aProperty to a_property, which is required for mapping the JavaScript properties to the name of the columns on the database.

For instance, if the update parameter was this:

const update = {
  roomId: 1,
  supplierId: 1,
  name: "A name",
  purchaseDate: "a date"
};

And you pass it to dynamicUpdate, you will receive an array with two values:

[
  'UPDATE table SET room_id = ?, supplier_id = ?, name = ?, purchase_date = ?`,
  1, 1, "a name", "a date"
]

Which you can then add to your prepared statement:

const result = dynamicUpdate(updateObject);
connection.execute(result[0], result[1])

Aaaaaah okay.
Still, one thing is not quite clear. How does the code know which parameters have been touched?

Do you mean that you need to update only the values that have changed? I assumed you would send them to the function from another place (i.e., the front end and already processed/validated). If you need to compare them, then you would need to:

  • Query the database to check what’s been modified or
  • Pass another parameter to the function and compare the values.

None of which were taken into account :stuck_out_tongue:, so you would have to implement them :slight_smile:.

Well, yes, this was my problem haha
If I knew that every field gets updated every time, I would just do the same thing as I did with the post method. So I give the fields to the controller, taken by destructuring req.body and that’s what happens inside insertItem. With update and put it’s not simple because the query doesn’t know which fields will get changed, hence doesn’t know which fields to put in the update query and how many question mark to put :sweat_smile:

You could, on the front end, compare the submitted data and only send the values/fields that differ:

// https://jsfiddle.net/skaparate/rpz7n83f/

function diff(a, b, skip = []) {
	const result = {};
  
   for (let prop in a) {
      if (b.hasOwnProperty(prop) && !skip.includes(prop) && a[prop] !== b[prop]) {
         result[prop] = b[prop];
      }
   }
  
   return result;
}

const a = {
	roomId: 1,
  supplierId: 1,
	name: "a name",
  publishDate: "2020-01-19",
  _version: 9
};

const b = {
	roomId: 1,
  supplierId: 1,
	name: "modified name",
  publishDate: "2020-03-19",
  _version: 10
};

diff(a, b, ['_version']);

The result of that call would be:

{
  "name": "modified name",
  "publishDate": "2020-03-19"
}

And this would work with the backend code you suggested before?

Not directly :stuck_out_tongue:, you would need to adapt it :slight_smile:.

What if, instead, inside the update I changed every value, then stored all the values in an array with a SELECT within the put method of the current entry, and somehow told the query to update the value only if it received an empty string in the update?

I didn’t understand :stuck_out_tongue:. How would you store the values with a SELECT? Like a SELECT INTO or a string in JS (SELECT ${values})?

Maybe we’re making this too complex xD. Do you know about sequelize? It may save you a lot of time :slight_smile:.

Yes, but I prefer to generate my own queries, at least I know how they work XD
Besides, many sites based on SQL allow users to change data with PUT methods and UPDATE, there must be a painless way to do this like it would be deleting, posting or getting :sweat_smile:

Anyhoo, I was talking about something like

  • When a PUT request gets issued on the endpoint, put current fields into an array
  • UPDATE Items(field,field,field,other_field) SET (?,?,?,?)
  • In the array to pass to the query, if the passed value (which is different from the current) is an empty string, use the corresponding item in the currentValues array

Yeah, I get that :stuck_out_tongue:, and I thinks it’s a good idea to increase your knowledge/skill with SQL.

You’ll basically end up building your own ORM (a different version of sequelize), :laughing:. You need to generalize your code and queries to adapt to your needs, which ends up being the same.

What will happen when you need to limit (paginate) the results? What about the WHERE clauses? ORDERs, GROUP BYs? It’s a lot of work :stuck_out_tongue:.

That’s similar (if not the same) to what I did with the dynamicUpdate and the diff :stuck_out_tongue:. On the front end you’ll have both, just send (PUT) the update object with only the changed values.

I don’t know why I didn’t ask this before, but why do you want to do this? I mean, why not just pass all the values regardless if they have changed?

Uhm…this is a little embarassing but…can you do that? :sweat_smile:
I mean if in the CLI I do something like UPDATE Items SET (?,?,?,?), how do I pass the unchanged values? I mean, I could end up modifying the untouched fields with empty strings or null values

Yes, you could actually end up saving the data in an invalid state, but you should validate the data before storing it, either with JavaScript or with database constraints (not null, triggers, primary/foreign keys, etc.).

And what if the user actually wants to remove a field/leave it empty (if it’s possible, of course :stuck_out_tongue:)?

Exactly! All issues that make me wonder, how do professional developers do this? :sweat_smile: