How to delete many rows typeorm - postgresql & node.js(typescript)

Question

Hi friends this is my function, it gets an array of ids I want to erase the rows in one stroke and not run in the loop, and can't find a solution to that. Would appreciate help.

async remove(ids: DeleteEmployeeAnswerDTO): Promise<boolean> {
        if (ids.employeeAnswersIds.length) {
            for (const id of ids.employeeAnswersIds) {
                await EmployeeAnswers.delete(id.id);
            }
        }
        return true;
    }

Solution

If your table has a single ID column then you should be able to pass an array of IDs:

await EmployeeAnswers.delete(ids.employeeAnswersIds);

You could also specify multiple IDs in your where clause using In:

await EmployeeAnswers.delete({ id: In(ids.employeeAnswersIds) });

However if you deal with a table that has a composite primary key, like in my case, the following example can be the solution for you. I'm not crazy about this answer, but here is how I overcame this problem using DeleteQueryBuilder (docs):

async remove(ids: DeleteEmployeeAnswerDTO): Promise<boolean> {
  if (ids.employeeAnswersIds.length) {
    const deleteQueryBuilder = EmployeeAnswer.createQueryBuilder().delete()

    const idClauses = ids.map((_, index) => `ID = :id${index}`)
    const idClauseVariables = ids.reduce((value: any, id, index) => {
      value[`id${index}`] = id
      return value
    }, {})

    await deleteQueryBuilder.where(idClauses.join(' OR '), idClauseVariables).execute()
  }
  return true;
}

Comments

[csakbalint]

This can work, however the process contains an unnecessary query. If there are a tons of ids, the first query can be very slow.

[csakbalint]

Since the example provided by the questioner made it clear, that his entity has only one primary key, I would highlight the last example you provided.

[Sergio Arrighi]

Yes but in my experience remove or delete fails catastrophically if the provided id doesn't exist in the table. Server just crashes... it's insane if you ask me. How would you circumvent this problem in a single query? Regards