CODEWITHSUNDEEP
javascriptmysqlnode.jssequelize.js
Gaurav Aggarwal
Gaurav Aggarwal

Reputation: 10187

nested array for multiple row insert is getting stringify with sequelize nodejs

As per some answers i am using this type of array to insert multiple rows in mysql

[ [ 252456, 1, 55, '0' ],
[ 357083, 1, 56, '0' ],
[ 316493, 1, 57, '0' ] ]

so after this expected result was 

INSERT INTO table (col1, col2, col3, col4) VALUES (252456,1,55,'0'), (357083,1,56,'0'), (316493,1,57,'0')

but actual query what is running is 

INSERT INTO table (col1, col2, col3, col4) VALUES 252456,1,55,'0',357083,1,56,'0',316493,1,57,'0'

Can anyone help with he problem?? below is my code snippet

let get_rows = await db.sequelize.query("select col1, col1, col3, '0' as col4 from table1 where (condition1 = '3' || condition2 = '4') and condition3 = '0';", {replacements: [], type: db.sequelize.QueryTypes.SELECT});

    if(get_rows && get_rows.length){
        let insert_rows = get_rows.map(x => Object.values(x));

        await db.sequelize.query("INSERT INTO table (col1, col2, col3, col4) VALUES ?;", {replacements: [insert_rows], type: db.sequelize.QueryTypes.INSERT});
    };

Upvotes: 0

Views: 3558

Answers (2)

Ratul Sharker
Ratul Sharker

Reputation: 8011

Why are you building query manually string by string, if you are using a ORM (Sequelize) for that ?

Try Model.bulkCreate

To use the bulkCreate first prepare your data as follows

const rawData = [
  [ 252456, 1, 55, '0' ],
  [ 357083, 1, 56, '0' ],
  [ 316493, 1, 57, '0' ] 
];

const convertedData = rawData.map(arrObj => {
  return {
    col1: arrObj[0],
    col2: arrObj[1],
    col3: arrObj[2],
    col4: arrObj[3]
  }
})

Then just feed it to your Sequelize model as follows

await Table1Model.bulkCreate(convertedData)

Upvotes: 2

Kristian
Kristian

Reputation: 3479

See this github issue

The feature has been implemented and merged in this pull request, perhaps you need to update your sequelize version?

If unable to update, check this post from same issue (copying for historical reasons):

let data = [ [ 252456, 1, 55, '0' ],
[ 357083, 1, 56, '0' ],
[ 316493, 1, 57, '0' ] ];

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

This will automatically create placeholders for all array items so they will be grouped correctly.

Upvotes: 0

Related Questions