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

Question

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

Most likely the data should be an array of objects, like [ { col1: '1', col2: '2', ... }, ... ] — Kristian
– Kristian, Commented Nov 27, 2019 at 6:49
@Kristian Earlier i was trying array of objects only but found some answers on SO to do it like this. Also adding my code in question. — Gaurav Aggarwal
– Gaurav Aggarwal, Commented Nov 27, 2019 at 6:51
Oh, the issue might also be that you are double nesting insert_rows. Try using await db.sequelize.query("INSERT INTO table (col1, col2, col3, col4) VALUES ?;", {replacements: insert_rows, type: db.sequelize.QueryTypes.INSERT}); — Kristian
– Kristian, Commented Nov 27, 2019 at 7:47

Ratul Sharker · Accepted Answer · 2019-11-27 14:48:44Z

2

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)

answered Nov 27, 2019 at 14:48

Ratul Sharker

8,0974 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kristian · Accepted Answer · 2019-11-27 07:44:13Z

0

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.

edited Nov 27, 2019 at 7:44

answered Nov 27, 2019 at 7:00

Kristian

3,5113 gold badges31 silver badges53 bronze badges

5 Comments

Gaurav Aggarwal Over a year ago

Do i need to upgrade sequelize for above solution also as after your solution this query is running

INSERT INTO table (col1, col2, col3, col4) VALUES ${data.map(a=> '(252456, 1, 55, '0', 357083, 1, 56, '0', 316493, 1, 57, '0')').join(',')}

Kristian Over a year ago

Updated my answer, you don't need to update for that solution

Gaurav Aggarwal Over a year ago

But query executing after this solution is not correct as i mentioned in above comment.

pakut2 Over a year ago

@GauravAggarwal did you manage to find the solution?

Gaurav Aggarwal Over a year ago

@pakut2 just started using models with this array

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related