CODEWITHSUNDEEP
mysqlnode.jsknex.jsobjection.js
Johncy Binoy
Johncy Binoy

Reputation: 179

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

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?

Upvotes: 5

Views: 10142

Answers (5)

Akash Kumar Verma
Akash Kumar Verma

Reputation: 3318

In latest version of Objection insertWithRelated Deprecated!

Instead you need to use insertGraph

samplemodel.query().insertGraph(newData);

or

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

Upvotes: 4

Ahmed Gaber
Ahmed Gaber

Reputation: 188

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

Upvotes: 3

user3662456
user3662456

Reputation: 265

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!

Upvotes: 1

Sami Koskimäki
Sami Koskimäki

Reputation: 250

You can call

samplemodel.query().insertWithRelated( newData );

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

Upvotes: 7

Borjante
Borjante

Reputation: 10497

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.

Upvotes: 8

Related Questions