How to batch delete in Knex?

Question

I'd like to perform batch deletion using knex.js. We have batchInsert as an API method , but nothing as far as batchDelete is concerned.

I tried async iteration and delete each row separately. But it's not efficient ,because we have lot of server to DB calls. I'm looking into a possibility, if DB have 100 records the batch of 25 records should be delete every time

Any ideas welcome!!

What do you mean by batch delete? what would be the sql statement? — felixmosh, Dec 10 '19 at 07:50
delete from table1 where id=%id%. I've millions of record to delete every time. — User412387, Dec 10 '19 at 08:02

score 3 · Accepted Answer · answered Dec 10 '19 at 08:11

3

Given ids of the items that you need to delete, you can use the In SQL statement.

It should look like:

Delete from tableName Where id In (1,2,3,45,636,52);

In order to build this query using knex

db('tableName')
  .delete()
  .whereIn('id', [1, 2, 3, 45, 636, 52]);

answered Dec 10 '19 at 08:11

felixmosh

32,615
9
69
88

I have the same logic. But I'm looking for a possibility to delete id in parallel mode. – User412387 Dec 10 '19 at 08:32
How do u know that the db engine is not doing it in parallel? – felixmosh Dec 10 '19 at 08:34
I'm looking into a possibility, if DB have 100 records the batch of 25 records should be delete every time. – User412387 Dec 10 '19 at 12:24
1

so, given a full list of ids to delete, chunk it into 25 items (can be done with lodash.chunk), iterate over it, and for each invoke the whereIn that will contain 25 ids... – felixmosh Dec 10 '19 at 12:40
QQ: Is it possible to chunk the query for `db('tableName') .delete() .whereIn('id', query);` – User412387 Dec 10 '19 at 13:22
What do you mean by "to chunk the query for"? – felixmosh Dec 10 '19 at 15:46

How to batch delete in Knex?

1 Answers1