SQLite's support on batch inserts changed since version 2012-03-20 (3.7.11), before that, batch inserts need to be done with a workaround like:
INSERT INTO 'tablename'
SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1' AS 'column1', 'data2' AS 'column2'
In the new versions since 2012, its batch inserts use the same syntax as other dbms:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
However, it seems mikro or knex is still using the old syntax, I have the following code:
const q = em.createQueryBuilder(MyModel);
q.insert([{data1, data2}, ...]);
await q.execute('run', false);
Is there a way to have mikro or knex compiling SQL into the modern way? I ask because SQLite has a limitation on compound select statements which is 500, see SQLITE_MAX_COMPOUND_SELECT, it means currently I am not able to insert more than 500 items in batch.
Did I miss any configurable options? Any help would be appreciated!
CodePudding user response：
it means currently I am not able to insert more than 500 items in batch.
Inserts via UoW are batched by default and use the new syntax already. You can control the batch size, so if you reach this limit, you can just make the batches smaller via batchSize option in the ORM config. Note that this is an Unit of Work setting, so it wont affect QB usage. But there is no reason to use QB here, its gonna be only better via UoW as you will get the batching for free - you can insert tens of thousands of records, and UoW will batch them into smaller pieces and insert 300 records in a single query (that's the batchSize option you can adjust).
const entities = [{data1, data2}, ...].map(row => em.create(MyModel, row));
await em.persist(entities).flush();
To fix this on QB level we would need to fix it in knex directly, not much we can do about this (unless we would ditch knex as a QB and keep it only as query runner).