Delete records

delete

This method deletes one or more rows, based on other conditions specified in the query.

By default, delete will return a count of deleted records.

Place select, selectAll, or get before delete to specify returning columns.

Need to provide where, findBy, or find conditions before calling delete. To prevent accidental deletion of all records, deleting without effective where conditions will result in TypeScript and a runtime error.

Empty filters, and filters where every value is ignored as undefined, do not count as conditions for delete:

await db.table.where({}).delete(); // throws
await db.table.where({ id: undefined }).delete(); // throws

Use all() to delete ALL records without conditions:

await db.table.all().delete();
await db.table.all().where({ id: undefined }).delete();

// deletedCount is the number of deleted records
const deletedCount = await db.table.where(...conditions).delete();

// returns a single value, throws if not found
const id: number | undefined = await db.table
  .findBy(...conditions)
  .get('id')
  .delete();

// returns an array of records with specified columns
const deletedRecord = await db.table
  .select('id', 'name', 'age')
  .where(...conditions)
  .delete();

// returns an array of fully deleted records
const deletedUsersFull = await db.table
  .selectAll()
  .where(...conditions)
  .delete();

delete supports joining, under the hood the join is transformed to USING and WHERE statements:

// delete all users who have corresponding profile records:
db.table.join(db.profile, 'profile.userId', 'user.id').all().delete();

delete can be used in with expressions:

db.$qb
  // delete a record in one table
  .with('a', db.table.find(1).select('id').delete())
  // delete a record in other table using the first table record id
  .with('b', (q) =>
    db.otherTable.select('id').whereIn('aId', q.from('a').pluck('id')).delete(),
  )
  .from('b');

select relations for delete

You can load related data in delete methods by chaining .select() with relation queries.

Relation data is loaded in a CTE (Common Table Expression) before the table record(s) are deleted. This allows returning relation data even after the source row is gone.

// deleting a post,
// selecting both the post and its author.
db.post
  .find(postId)
  .delete()
  .select('id', 'text', {
    author: (q) => q.author.select('id', 'name'),
  });