template strings in insert and update?

[zoobot]

hi, I have been struggling to make template strings work in insert and update queries and wondering if I am just doing it wrong or is it not supported? THANKS for any input.

https://dev.mysql.com/doc/refman/8.0/en/insert.html

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

Here's what I was attempting but could not get to work:

let query = `INSERT INTO myTAble SET
        myID = ${id},
        name = ${customer_name},
     email = ${customer_email},
        ON DUPLICATE KEY UPDATE
        name = ${customer_name},
        email = ${customer_email}`;

Also tried these to no avail:

query = `INSERT INTO myTAble SET
        myID = ${id},
        name = ${customer_name},
     email = ${customer_email}`;

query = `UPDATE myTable SET name = ${chargebackData.customer_name}`;

[dougwilson]

You need to escape the values you are inserting into the SQL or you'll have SQL syntax errors. See escaping section.

[dougwilson]

When i get to a computer i can provide an example too

[dougwilson]

Example:

let query = `INSERT INTO myTAble SET
        myID = ${mysql.escape(id)},
        name = ${mysql.escape(customer_name)},
     email = ${mysql.escape(customer_email)},
        ON DUPLICATE KEY UPDATE
        name = ${mysql.escape(customer_name)},
        email = ${mysql.escape(customer_email)}`;

Of course you can use the placeholder syntax instead:

let query = mysqk.format(`INSERT INTO myTAble SET
        myID = ?,
        name = ?,
     email = ?,
        ON DUPLICATE KEY UPDATE
        name = ?,
        email = ?`, [id, customer_name, customer_email, customer_name, customer_email]);

Or named placeholders:

var toUnnamed = require('named-placeholders')();
let [query, values] = toUnnamed(`INSERT INTO myTAble SET
        myID = :id,
        name = :customer_name,
     email = :customer_email,
        ON DUPLICATE KEY UPDATE
        name = :customer_name,
        email = :customer_email`, { id, customer_name, customer_email });

[zoobot]

Thanks @dougwilson !! mysql.escape is a bit verbose... named placeholders and placeholders looks a bit cleaner and more readable though still have to worry about order with placeholder. I had given up on string templates and used placeholder set as an options array populated by my object. Wish I could just do string templates without escapes, that way I wouldn't have to worry about order or messing up which fields I have in the dataobject.

[sidorares]

or if you are too lazy to type mysql.escape @zoobot you can automate it like this:

const escape = (strings, ...values) =>
  strings
    .map((s, index) => (index < values.length ? `${s}${mysql.escape(values[index])}` : s))
    .join("");

let query = escape`INSERT INTO myTAble SET
        myID = ${id},
        name = ${customer_name},
     email = ${customer_email},
        ON DUPLICATE KEY UPDATE
        name = ${customer_name},
        email = ${customer_email}`;

see https://medium.freecodecamp.org/es6-tagged-template-literals-48a70ef3ed4d for example

[zoobot]

I am definitely too lazy to type mysql.escape. This looks cool thanks @sidorares , that looks like it can be used for a lot of things