Raw query replacements in MySQL INSERT query

[Kirill89]

db.query('INSERT INTO product (a, b) VALUES ?;', {
        replacements: [[['a', 'b'], ['c', 'd']]],
        type: Sequelize.QueryTypes.INSERT
});

What do you expect to happen?

INSERT INTO product (a, b) VALUES ('a', 'b'), ('c', 'd');

What is actually happening?

INSERT INTO product (a, b) VALUES 'a','b','c','d';

Dialect: mysql
Sequelize version: 4.32.3

So, the question is, how to insert non-determined number of items in one query?

[sushantdhiman]

Have you tried

db.query('INSERT INTO product (a, b) VALUES (?), (?);', {
        replacements: [['a', 'b'], ['c', 'd']],
        type: Sequelize.QueryTypes.INSERT
});

Might be possible, but not sure

[Kirill89]

@sushantdhiman yes, it will work. But how to do that if I don't know number of items?

[sushantdhiman]

Well you can repeat, (?) to length of your array

db.query(
  `INSERT INTO product (a, b) VALUES ${data.map(a => '(?)').join(',')};`,
  {
     replacements: data,
     type: Sequelize.QueryTypes.INSERT
  }
);

[Kirill89]

@sushantdhiman, but most of MySQL drivers can do it from the box.

I probably need to describe my case. In my project we are doing a migration from plain queries to ORM. As a first step we decide to use sequelize library to perform all SQL requests. And it was bad news for me that it's not compatible with usual placeholder syntax (like here).

[sushantdhiman]

We are using mysql2, I dont think it support array replacement

[sushantdhiman]

Can you test if mysql2 can handle that sort of replacement, if it can I am willing to tag this as a feature request

[Kirill89]

@sushantdhiman, in mysql2 it works.

const mysql = require('mysql2');
const connection = mysql.createConnection({host: 'localhost', user: 'root', database: 'test', debug: true});

connection.query(
    'INSERT INTO product (a, b) VALUES ?;',
    [[['a', 'b'], ['c', 'd']]],
    (...args) => console.log(args)
);

In debug log: Sending query command: INSERT INTO product (a, b) VALUES ('a', 'b'), ('c', 'd');

[Kirill89]

@sushantdhiman, can you please review the pull request I did. It's almost copy from SqlString library, which is used in mysql2. If it's looks suitable for you, I will add tests and update documentation.

[Kirill89]

Thank you!