Drizzle Team•9mo ago

Is there a way to return the number of rows that were deleted?

I'm doing a batch delete of hundreds of thousands of records and I'm wondering if there's a way to return the amount of rows that were deleted to make sure that my expectations match reality. Currently I'm doing this:

      const res = await tx
        .delete(activity)
        .where(inArray(activity.id, numQuery))
        .returning({ id: activity.id })

      logger.info({ count: res.length }, 'delete dry run')

      const res = await tx
        .delete(activity)
        .where(inArray(activity.id, numQuery))
        .returning({ id: activity.id })

      logger.info({ count: res.length }, 'delete dry run')

But this is not ideal for large arrays.

1 Reply

Aaroned•9mo ago

@delvaze https://stackoverflow.com/questions/2251567/how-to-get-the-number-of-deleted-rows-in-postgresql seems to have the ideal solution. Which would translate to something like this in Drizzle

const deleted = tx.$with('deleted').as(tx.delete(activity).where(inArray(activity.id, numQuery)).returning({ id: activity.id }))

  const [count: deletedCount] = await tx.with(deleted).select({count: count()}).from(deleted)

const deleted = tx.$with('deleted').as(tx.delete(activity).where(inArray(activity.id, numQuery)).returning({ id: activity.id }))

  const [count: deletedCount] = await tx.with(deleted).select({count: count()}).from(deleted)

However $with doesn't seem to currently support DELETE ... RETURNING. 🥴 Maybe someone else might have another solution I've just added this to an existing feature request https://github.com/drizzle-team/drizzle-orm/issues/2078

Gaming

Programming

Is there a way to return the number of rows that were deleted?