I'm trying to insert a lot of entries in my Postgres database (60,000 rows), and how I've set it up is that I load up all the rows into a long array, and then use knex.batchInsert to insert them. However, this error gets thrown:
TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
According to the knex documentation, batchInsert is already wrapped in a transaction, so it shouldn't be a problem, if I'm not mistaken. I also tried to write it as a forEach loop with transactions inserting the rows, but that also didn't work (but it could be my fault; I haven't used transactions before). Only 5,000 rows get entered before the app throws the error.
Here's the code I used with batchInsert:
await knex.batchInsert('timelines', rows, 1000)
.then(console.log('success'))
.catch(function(error) {
console.log(error);
});
And here's the code I used with transactions in a forEach loop:
await rows.forEach(async function (row) {
try {
knex.transaction(function(trx) {
knex('timelines').transacting(trx).insert(row)
.then(trx.commit)
.catch(trx.rollback);
})
.then(function(resp) {
console.log(resp);
console.log('Transaction complete.');
})
.catch(function(err) {
console.error(err);
});
}
catch (e) {
console.error(e);
}
So when I run the code, I only get to 5,000 entries before the error arises. Is there a way I can get around this so I can enter all 60,000?