Bulk Insert Error using Knex.js: bind message supplies X parameters, but prepared statement "" requires Y

Question

When using Knex.js to do a large INSERT to PostgreSQL 11.2,

await knex(myTable).insert(largeArray);

we get the error

bind message supplies 47078 parameters, but prepared statement "" requires 9353190

Question: Is there a way to do large inserts in Knex.js without the use of binding? Or is there a parameter in PostgreSQL that we can change to allow large inserts like these?

I believe I have used Sequelize.js ORM to perform similarly sized inserts (1/2 the size of this) without such errors, but prefer not to use an ORM for our current purposes.

Using knex 0.20.9, Node.js 12.14.1, PostgreSQL 11.2, TimescaleDB 1.6.0, Ubuntu 18.04.3

Answer 1

You need to add the data in batches. There is utility function called batchInsert() in knex for helping to do it. Databases has limited statement size / number of bindings. Sequelize probably does batching internally, since it cannot override the limits casted by database server.

If you prefer not to use ORM you could try objection.js it is more like an advanced helper library on top of knex for commonly needed stuff instead of real ORM.