Batch insert of array data into mysql database using objection.js

Question

I have an array

newData = [{ sId: 'XXXXXX', itemlName: 'LSｽﾄﾚｯ', iCode: 'XXXXXX', iType: '', skus: 'XXXXXXX', iLevel: 'L2', cCode: '88', cName: 'Other', sCode: '999', sName: 'No Control', pLengthCode: '988', core: 'N', sCode: '1', dCode: 'XX', gDeptCode: 'XX', gDeptName: 'Women\\\'s Items', rCode: 'jqs' },{ sId: 'XXXXXX', itemlName: 'LSｽﾄﾚｯ', iCode: 'XXXXXX', iType: '', skus: 'XXXXXXX', iLevel: 'L2', cCode: '88', cName: 'Other', sCode: '999', sName: 'No Control', pLengthCode: '988', core: 'N', sCode: '1', dCode: 'XX', gDeptCode: 'XX', gDeptName: 'Women\\\'s Items', rCode: 'jqs' }]

I wants to insert newData in to mysql database using objection.js. But when I run my node application , I got the error saying:

Error: batch insert only works with Postgresql

My insert code is -:

samplemodel.query().insert( newData );

How can I perform the batch insertion of array data in mysql data base using objection.js?

score 8 · Accepted Answer · edited Jun 13 '16 at 10:54

8

Error: batch insert only works with Postgresql

Well, that means that you need Postgres's instead of Mysql to make it work.

Edit1:

From the docs:

If you are using Postgres the inserts are done in batches for maximum performance. On other databases the rows need to be inserted one at a time. This is because postgresql is the only database engine that returns the identifiers of all inserted rows and not just the first or the last one.

That means that if you are using mysql you should do

samplemodel.query().insert(data);

For each element in the "newData" array.

edited Jun 13 '16 at 10:54

19greg96

2,592
5
41
55

answered Jun 13 '16 at 10:06

Borjante

9,767
6
35
61

Sorry, but this is not an answer to the question. – Philip Enc Jul 27 '20 at 13:59
1

Why not? It seemed to help the OP – Borjante Jul 27 '20 at 15:44
Because your answer doesn't solve the problem, which is: Insert in batch USING MySQL. The OP specified Mysql as a tag. – Philip Enc Jul 27 '20 at 16:39
4

The docs state that its NOT posible to insert batch using MySQL, so the only solution is to insert them one by one. – Borjante Jul 27 '20 at 18:53

Sami Koskimäki · Answer 2 · 2016-07-24T08:09:11.590

7

You can call

samplemodel.query().insertWithRelated( newData );

which uses multiple queries if needed and works with all databases.

edited Jul 24 '16 at 08:09

answered Jul 18 '16 at 19:26

Sami Koskimäki

250
2
7

score 4 · Answer 3 · answered Jul 15 '21 at 09:58

4

In latest version of Objection `insertWithRelated` Deprecated!

Instead you need to use insertGraph

samplemodel.query().insertGraph(newData);

or

samplemodel.query().insertGraph(newData, { allowRefs: true });

answered Jul 15 '21 at 09:58

Akash Kumar Verma

3,185
2
16
32

score 3 · Answer 4 · answered Oct 10 '20 at 09:25

Hence there is no way to reduce number of queries, due to mysql limitations.

I would loop over it insert it into array of promises and execute it throw Promise.all

...
let insertPromises = []
for(let item of newData){
    insertPromises.push(samplemode.query().insert(item))
}

Promise.all(insertPromises).then(() => { ... })
...

I would suggest using transactions if I'm using this approach with $query or $relatedQuery to ensure data integerity

could you give an example of transaction with promise.all? – CloudWave May 21 '21 at 08:40 — CloudWave, May 21 '21 at 08:40

score 1 · Answer 5 · answered Dec 18 '19 at 00:15

The trick is to make the convert the insert statement into a string and execute the raw sql.

Something like

let insertQuery = samplemodel.query().insert( newData ); let insertRes = await knex.raw(insertQuery.toString())

PS: if this is not good practice, I'd appreciate you letting me know!

Batch insert of array data into mysql database using objection.js

5 Answers5

In latest version of Objection insertWithRelated Deprecated!

In latest version of Objection `insertWithRelated` Deprecated!