How to bulk insert in psql using knex.js?

Question

I've searched a lot and this is deprecated question.

I'm trying to bulk insert in a table.

My approach was like this

knex('test_table').where({
  user: '[email protected]',
})
.then(result => {
  knex.transaction(trx => {
    Bluebird.map(result, data => {
      return trx('main_table')
        .insert(data.insert_row)
    }, { concurrency: 3 })
    .then(trx.commit);
  })
  .then(() => {
    console.log("done bulk insert")
  })
  .catch(err => console.error('bulk insert error: ', err))
})

this could work if the columns where text or numeric columns, but i have jsonb columns

But I got this error:

invalid input syntax for type json

How can I solve this problem?

Answer 1

Sounds like some json columns doesn't have data stringified when sent to DB.

Also that is pretty much the slowest way to insert multiple rows, because you are doing 1 query for each inserted row and using single connection for inserting.

That concurrency 3 only causes pg driver to buffer those 2 queries before they are sent to the DB through the same transaction that all the others.

Something like this should be pretty efficient (didn't test running the code, so there might be errors):

const rows = await knex('test_table').where({ user: '[email protected]' });
rows.forEach(row => {
  // make sure that json columns are actually json strings
  row.someColumnWithJson = JSON.stringify(row.someColumnWithJson);
});

await knex.transaction(async trx => {
  let i, j, temparray, chunk = 200;

  // insert rows in 200 row batches
  for (i = 0, j = rows.length; i < j; i += chunk) {
    rowsToInsert = rows.slice(i, i + chunk);
    await trx('main_table').insert(rowsToInsert);
  }
});

Also knex.batchInsert might work for you.