Example of UPDATE prepared statements?

[GRawhideMart]

Hello! I am fairly new to this package, so I was wondering if somebody could give me an example of usage. I successfully did GET and POST, like so:

const connection = require("../db");

let Items = {};

Items.getAll = () => {
  return new Promise((resolve, reject) => {
    connection.query("SELECT * FROM Items", (err, results) => {
      if (err) return reject(err);
      return resolve(results);
    });
  });
};

Items.getOne = (id) => {
  return new Promise((resolve, reject) => {
    connection.query("SELECT * FROM Items WHERE id=?", [id], (err, results) => {
      if (err) return reject(err);
      return resolve(results[0]);
    });
  });
};

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]);
      }
    );
  });
};

module.exports = Items;

How can I implement PUT with prepared statements, considering I don't know beforehand which fields will be touched?

[alete89]

What about something like this?

Items.updateItem = (room, supplier, name, invoiceNumber, purchaseDate, inUse) => {
  return new Promise((resolve, reject) => {
    connection.query(
      `UPDATE Items
      SET supplier_id = ?,
      name = ?,
      invoice_number = ?,
      purchase_date = ?,
      in_use = ?
      WHERE room_id = ?
      ;`,
      [supplier, name, invoiceNumber, purchaseDate, inUse, room].map((elem) => elem || null),
      (err, results) => {
        if (err) return reject(err);
        return resolve(results[0]);
      }
    );
  });
};

Honestly I didn't try it, but I think this might work, or at least may give you a good idea.

rationale: if you are PUTting an element, you already know its ID (should always be present), so you can use it to select it. Also for PUT semantics, you will always want to overwrite every field/column.

For PATCH, it could get a little bit trickier, I came up with this, I didn't try it either.

Items.updateItem = (room, supplier, name, invoiceNumber, purchaseDate, inUse) => {
    return new Promise((resolve, reject) => {
      connection.query(
        `UPDATE Items
        SET room_id = ?
        ${supplier ? ",supplier_id = ?" : ""}
        ${name ? ",name = ?" : ""}
        ${invoiceNumber ? ",invoice_number = ?" : ""}
        ${purchaseDate ? ",purchase_date = ?" : ""}
        ${inUse ? ",in_use = ?" : ""}
        WHERE room_id = ?
        ;`,
        [room, supplier, name, invoiceNumber, purchaseDate, inUse, room].filter((elem) => elem),
        (err, results) => {
          if (err) return reject(err);
          return resolve(results[0]);
        }
      );
    });
  };

[GRawhideMart]

Hello, and thank you for your answer. I am using the PUT method, so no PATCH (I don't even know actually what's the difference lol). Unfortunately though, the first method (which I didn't try either) wouldn't work, because it would set the fields I didn't pass in the request to null, which is not what I want; I want fields not passed to be left untouched, which is why I'm struggling 😅

[alete89]

because it would set the fields I didn't pass in the request to null, which is not what I want; I want fields not passed to be left untouched, which is why I'm struggling

That's exactly the difference between PUT and PATCH (I encourage you to read about it). In PUT you should always pass the full object and it would be replaced. If you don't pass a certain property it should be set to null. PATCH instead, will only change provided properties.

Without knowing it, I think that you are trying to implement a PATCH. Anyway, REST verbs don't matter here. Check the second example I provided, it might help.

[GRawhideMart]

Thank you! I notice you used the $ notation, isn't it dangerous in terms of possible SQL Injection?

[sidorares]

@GRawhideMart local "direct" string interpolation ( "... ${} ..." ) is used to build another template string ( with ? as a template placeholders ) that is later consumed by .format() which tries to escape values and build correct resulting sql

[GRawhideMart]

Check the second example I provided, it might help.

@alete89 This doesn't work btw, from what I understand, it updates every object with the same room_id

[alete89]

Check the second example I provided, it might help.

@alete89 This doesn't work btw, from what I understand, it updates every object with the same room_id

It should, isn't it? if not, how would you univocally identify the row you are trying to update? If room_id is not, you should be using a unique id (a primary key, perhaps?)

[GRawhideMart]

Well, yes. Because when the end user modifies an item, it shouldn't apply the change also to every other object in the room. room_id (and also supplier_id) are foreign keys, every item has its own id as a primary key. When I tried changing your example to id and passing it to the function as req.params.id, it didn't work

[alete89]

you will have to receive the actual id of the row (id)

Items.updateItem = (id, room, supplier, name, invoiceNumber, purchaseDate, inUse) => {
    return new Promise((resolve, reject) => {
      connection.query(
        `UPDATE Items
        SET id = ?
        ${room ? ",room_id = ?" : ""}
        ${supplier ? ",supplier_id = ?" : ""}
        ${name ? ",name = ?" : ""}
        ${invoiceNumber ? ",invoice_number = ?" : ""}
        ${purchaseDate ? ",purchase_date = ?" : ""}
        ${inUse ? ",in_use = ?" : ""}
        WHERE id = ?
        ;`,
        [id, room, supplier, name, invoiceNumber, purchaseDate, inUse, id].filter((elem) => elem),
        (err, results) => {
          if (err) return reject(err);
          return resolve(results[0]);
        }
      );
    });
  };

note (again) that id is repeated in the array, because it's used two times, at the very beggining and end of the query. The first time is just a workaround to be able to conditionally add ,column later. The last is for the where.