Soft Deletes API

Enabling soft delete

interface ColumnConfig {
  softDelete?: boolean;   // this column records deletion
}

Soft delete is switched on by a column, not by the model. Flag one column and that column becomes the model's deletion marker: a non-NULL value means the row is deleted, NULL means it is live.

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 255, required: true },
    deletedAt: { type: DataTypes.DATETIME, softDelete: true },
  },
});

What delete() actually runs

// with a soft-delete column
UPDATE users SET deletedAt = ? WHERE id = ?

// without one
DELETE FROM users WHERE id = ?

The marker is written as a timestamp — the current Date, sanitized for the dialect. The soft delete column is therefore a date/time column, not a boolean, and IS NULL is the “still live” test used everywhere in the library.

delete() does not fail on an already-deleted row and does not check the marker: it looks the row up by primary key, runs the beforeDelete and afterDelete hooks, and overwrites the marker with a fresh timestamp.

await userRepo.delete(1);      // sets deletedAt
await userRepo.delete(1);      // sets it again; not an error

await userRepo.bulkDelete([1, 2, 3]);   // same treatment per row
await userRepo.deleteBy({ status: "archived" });  // same

bulkDelete() and deleteBy() follow the same rule. deleteBy() also adds <marker> IS NULL to its own WHERE, so it never re-stamps a row that is already in the trash — the returned count is the number of rows it actually moved.

Do not mark the column required

A required: true column is created NOT NULL. Recovery works by setting the marker back to NULL, so a required marker column makes every row unrecoverable: the UPDATE is rejected by the database. Leave the soft-delete column optional.

// WRONG — recovery will fail at the database
deletedAt: { type: DataTypes.DATETIME, softDelete: true, required: true },

// RIGHT
deletedAt: { type: DataTypes.DATETIME, softDelete: true },

Recovering rows

async recover(id: number | string, _client?: DBClient): Promise<T>
async recoverAll(_client?: DBClient): Promise<number>
async restoreBy(conditions: Partial<T>, _client?: DBClient): Promise<number>

Parameters:

• id Primary key of the row to restore.
• conditions Column values to match. Entries that are undefined or null are skipped rather than matching NULL.
• _client Optional transactional client.

All three clear the marker. recover() restores one row and returns it; recoverAll() restores every trashed row and returns the count; restoreBy() restores the matching trashed rows and returns the count.

All three also throw when the model has no soft-delete column, with code RECOVER_ERROR and the message Soft delete not enabled (the single-row recover() words it Soft delete not enabled for this model).

recover() does not check that the row exists before clearing the marker: it runs the UPDATE, which affects nothing if the id is unknown, and then re-reads the row. A missing id therefore raises a second RECOVER_ERROR, messaged Failed to find recovered record. — not a DELETE_ERROR, and no other code.

const restored = await userRepo.recover(1);
const all = await userRepo.recoverAll();
const byStatus = await userRepo.restoreBy({ status: "archived" });

Querying the trash

findDeleted(): QueryBuilder<T>   // trashed rows only
withTrashed(): QueryBuilder<T>   // live AND trashed rows

Both return a QueryBuilder and are chainable and terminal in the usual way, via .execute(client). Unlike every other read path, the soft-delete filter is not applied to either of them.

findDeleted() requires the model to have a soft-delete column and throws code QUERY_ERROR with the message Soft delete not enabled when it does not. withTrashed() has no such guard and never throws — on a model with no marker column it is simply an unfiltered builder, which is the same set of rows find() returns. Neither attaches a relation loader, so .withRelations() on either does not eager-load; use find() when you need relations.

const trashed = await userRepo.findDeleted().execute(orm.client);
const everything = await userRepo.withTrashed().execute(orm.client);

const oldest = await userRepo
  .findDeleted()
  .orderBy("deletedAt", "ASC")
  .limit(10)
  .execute(orm.client);

Reads skip the trash automatically

When a model has a soft-delete column, the read paths add <marker> IS NOT NULL filtering for you. You do not opt in, and there is no flag to turn it off other than reaching for withTrashed().

find()            findOne()      findOneBy()    findBy()
findMany()        first()        last()         exists()
count()           countDistinct()  healthCheck()  paginate()
findAndCount()    findAndCountAll()  aggregate()
pluck()           selectColumns()  increment()   decrement()
toggle()          update()  updateBy()  delete()  deleteBy()

update() and updateBy() refuse to touch a trashed row: the marker filter is added to the WHERE, so a row in the trash simply matches nothing.

await userRepo.delete(1);

await userRepo.findOne(1);              // null
await userRepo.count();                 // excludes row 1
await userRepo.update(1, { name: "x" }); // matches no row

// Recover first, then update.
await userRepo.recover(1);
await userRepo.update(1, { name: "x" });

truncate() ignores all of it

async truncate(_client?: DBClient): Promise<void>
// DELETE FROM <table>

truncate() issues a plain unqualified DELETE FROM <table>. It does not check for a soft-delete column, does not run the delete hooks, and does not write version history — every row, trashed or live, is gone and recover() cannot bring any of it back. It needs no soft delete to be enabled and never throws for lack of one.

await userRepo.truncate();   // destructive and final

The marker is hidden from toJSON()

toJSON(entity: T): Record<string, any>

toJSON() copies the model's columns onto a plain object but omits the soft-delete column, so a serialized row never leaks its deletion timestamp to a client. Every other column is copied through, including ones whose value is undefined.

const user = await userRepo.findOne(1);
const payload = userRepo.toJSON(user);
// { id: 1, name: "Alice" }  — no deletedAt key at all

Because it is a mapping over the model's own columns, this is also the way to hide encrypted columns — the same method decrypts them on the way out.

Helpers on the repository

getSoftDeleteField(): string | null
MetadataStorage.getSoftDeleteField(model: Function): string | null

Returns the property name of the marker column, or null when the model has soft delete switched off. This is the property name ("deletedAt"), not the SQL column name — a column declared with name maps to that database column instead, and the static form reads the same metadata without a repository.