MySql2 - Nodejs - How to build a complex query?

user_1775598 0
Viewed 231

I'm using mysql2 package on a node server. I've got an array of values over which I iterate. Inside the loop I build my query. I want the end result to look something like:

INSERT INTO table (url, caption, username, type, timestamp)
  VALUES ("https://www.url.com/123", "my caption1", "user1", "image", "12423423"),
         ("https://www.url.com/124", "my caption2", "user2", "image", "12423567"),
         ("https://www.url.com/125", "my caption3", "user3", "image", "12423890")
ON DUPLICATE KEY UPDATE 
   username=VALUES("user1", "user2", "user3")

At the moment I'm building the Insert values as a string and passing in a ? to replace the entire line. See below:

   //Use this as a query shell
   let queryPlaceholder = `INSERT INTO table (url, caption, username, type, timestamp)
         VALUES ?
         ON DUPLICATE KEY UPDATE username=VALUES(?)`

   data.foreach(item => {
      //Long string - to replace the first `?` in the queryPlaceholder
      valuesToInsert += "('" + item.url + "', '" + item.caption + "','" + item.username + "','" + item.type + "','" + item.timestamp + "'), ";
      
      //Replace the second `?` in the queryPlaceholder
      usernamesToUpdate += "'" + item.username + "', ";
   }
   //Get rid of trailing commas and space
   valuesToInsert = valuesToInsert.slice(0, -2);
   usernamesToUpdate = valuesToInsert.slice(0, -2);

   conn.query(queryPlaceholder, [valuesToInsert, usernamesToUpdate], function........)

I realise this is looks messy and I'm not using mysql2 correctly. In addition the quotes all get escaped even though they're meant to be part of the query itself.

How should I be building such a query instead of what i'm currently doing - also so that the quotes from caption's and other fields are escaped properly?

query-builder node.js mysql
1 Answers

Instead of trying to build one complex statement, send a simple one multiple times.

const sql = `
    INSERT INTO table (url, caption, username, type, timestamp)
    VALUES (?, ?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE;
`;

data.foreach(item => {
  conn.query(sql, [item.url, item.caption, item.username, item.type, item.timestamp], callback);
});

If you want to be treat the updates as one block, use a transaction:

function executeAll(conn, sql, data, callback) {
  var abort = false;

  conn.beginTransaction((err) => {
    if (err) return callback(err);
    data.foreach((params, i) => {
      if (abort) return;
      conn.query(sql, params, (err) => {
        if (err) {
          abort = true;
          return conn.rollback(() => callback(err));
        }
        if (i < params.length) return;
        conn.commit((err) => {
          if (err) return conn.rollback(() => callback(err));
          callback();
        });
      });
    });
  });
}

Usage:

const sql = `
    INSERT INTO table (url, caption, username, type, timestamp)
    VALUES (?, ?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE;
`;

const data = [
  ["https://www.url.com/123", "my caption1", "user1", "image", "12423423"],
  ["https://www.url.com/124", "my caption2", "user2", "image", "12423567"],
  ["https://www.url.com/125", "my caption3", "user3", "image", "12423890"]
];

executeAll(conn, sql, data, (err) => {
  if (err) return console.log(err);
  console.log("All done.");
});
Tomalak_187710
Related