# Soft delete

> Turn delete() into a recoverable state change, filter deleted rows by default, and add restore helpers.

URL: https://better-drizzle.com/docs/plugins/soft-delete

`better-drizzle/soft-delete` extends the built-in methods with typed soft-delete controls, default visibility filtering, and restore helpers.

## Install

**npm**

```bash
npm install better-drizzle
```

**pnpm**

```bash
pnpm add better-drizzle
```

**yarn**

```bash
yarn add better-drizzle
```

**bun**

```bash
bun add better-drizzle
```

## Usage

```ts
import { better } from 'better-drizzle';
import { softDelete } from 'better-drizzle/soft-delete';

const client = better(db, {
	plugins: [
		softDelete({
			column: 'deletedAt',
			deletedByColumn: 'deletedById',
			defaults: {
				mode: 'soft',
				visibility: 'without',
			},
		}),
	],
});
```

## Options

Every option is optional. Column names are the property keys in your Drizzle table definition, not the database column names.

| Option                | Default         | Description                                                                           |
| --------------------- | --------------- | ------------------------------------------------------------------------------------- |
| `column`              | `'deletedAt'`   | column that marks a row as deleted; tables without it are ignored                     |
| `deletedByColumn`     | `'deletedById'` | column written from the `deletedBy` arg and cleared on restore, when the table has it |
| `defaults.mode`       | `'soft'`        | `delete()` mode when the call passes no `mode`                                        |
| `defaults.visibility` | `'without'`     | read visibility when the call passes no `deleted`: `'without'` or `'with'`            |

## What is filtered

(Added in 0.4.0)

| Operation                                                                                                        | Behavior                                                                         |
| ---------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------- |
| `findMany`, `findFirst`, `findOne`, `findUnique`, `count`, `exists`                                              | deleted rows hidden                                                              |
| `paginate`, `cursor`                                                                                             | deleted rows hidden from `data`, `total`, and the navigation flags               |
| `update`, `updateMany`, `updateEach`                                                                             | deleted rows are not updated                                                     |
| `delete`, `deleteMany`                                                                                           | soft by default: set `column` on live rows instead of deleting                   |
| `upsert`, `upsertMany`                                                                                           | not filtered: hiding a deleted row would turn the update into a duplicate insert |
| `include`, relation `select`, relation filters (`some`, `every`, `none`, `is`, `isNot`), `count`, relation sorts | deleted rows always hidden                                                       |
| `connect`, `disconnect`, `set`                                                                                   | deleted rows cannot be linked or unlinked                                        |
| `$where()`                                                                                                       | deleted rows hidden: the condition includes the visibility filter                |

The reads, updates, and deletes in the first rows accept `deleted: 'without' | 'with' | 'only'`. It controls the root rows only; nested reads always hide deleted rows. A write whose `where` is empty stays a no-op: the plugin never turns `deleteMany({})` into "soft delete everything".

```ts
await client.users.findMany(); // visible rows only
await client.users.findMany({ deleted: 'with' }); // include deleted
await client.users.findMany({ deleted: 'only' }); // only deleted
await client.users.count({ deleted: 'with' });
```

> **Changed in 0.4.0:** The plugin now filters through core [model
> scopes](/docs/plugins/plugin-scopes#model-scopes). Before 0.4.0 it only
> filtered the root query, so deleted rows appeared in `include`, relation
> `select`, relation filters, `count`, and relation sorts. They are hidden
> there now, and `connect`/`set` no longer reach deleted rows. Read deleted
> relations from the root with `deleted: 'with'`, or through
> `$withoutPlugins()`.

## Protected columns

Ordinary writes ignore caller-provided `deletedAt` and `deletedById` values, or the configured `column` and `deletedByColumn`. This applies to `create`, `createMany`, `update`, `updateMany`, `updateEach`, `upsert`, and `upsertMany`. The plugin removes those fields from `updateEach` resolvers and from every `upsertMany` update shape: `'all'`, a column list, an object, or a callback result. Expanding `'all'` also preserves the conflict target.

Upserts can update other fields on a deleted row, but keep its deletion timestamp and author. Use `delete()` to mark a row as deleted and `restore()` to clear those fields. For trusted imports that need to set them directly, use `$withoutPlugins()`.

## Deleting and restoring

```ts
// Soft delete (default)
await client.users.delete({ where: { id: 1 } });

// Record who deleted it (when deletedByColumn exists)
await client.users.delete({ where: { id: 1 }, deletedBy: 'admin_42' });

// Soft delete many rows; already deleted rows keep their timestamp
await client.users.deleteMany({ where: { active: false } });

// Force a real, physical delete
await client.users.delete({ where: { id: 1 }, mode: 'hard' });

// Purge rows that were soft deleted
await client.users.deleteMany({ where: { id: { in: ids } }, mode: 'hard', deleted: 'only' });

// Restore
await client.users.restore({ where: { id: 1 } });
await client.users.restoreById(1);
```

A soft `delete()` runs an `update` of the same `where` limited to live rows, then returns the updated row (or `null`), so it still has `.throw()`. A soft `deleteMany()` runs an `updateMany` and returns `{ count, data? }`: PostgreSQL and SQLite include the marked rows (or scalar `select` fields), while MySQL returns only the count. Empty results omit `data`. Deleting an already deleted row finds nothing and keeps the original timestamp.

## Behavior details

* The timestamp is an ISO 8601 string (`new Date().toISOString()`) when the column's Drizzle data type is `string`, such as a SQLite `text` column. Otherwise it is a `Date`.
* `deletedBy` is written only when the call passes it and the table has `deletedByColumn`.
* `restore()` and `restoreById()` exist only on tables with `column`. They set `column` (and `deletedByColumn`, when present) to `null` through the normal update pipeline with `deleted: 'with'`. Cache invalidation, timestamps, rules, and client `beforeUpdate` / `afterUpdate` hooks run. Both helpers accept `select`, `include`, and `meta`.
* `restoreById(id)` matches the table's single primary key column, regardless of its name. It throws `INVALID_ARGS` for tables with composite keys or no primary key; use `restore({ where })` for those tables.
* A restore keeps model scopes, so a [tenant](/docs/plugins/tenant)-scoped client only restores that tenant's rows. A soft delete uses an inner `$withoutPlugins({ keepScopes: true }).update(...)`, which keeps scopes and client update hooks but skips other plugins; its update hooks run before the delete hooks.
* `$withoutPlugins()` (without `keepScopes`) drops the soft-delete filter with every other scope, so it reads deleted rows at every level and deletes physically.
* `mode: 'hard'` falls back to the built-in physical delete. It matches deleted and live rows alike unless the call passes `deleted`.

> **Need a one-off hard delete elsewhere?:** Any delegate can bypass plugins for a single call with
> [`$withoutPlugins()`](/docs/plugins/writing-plugins#bypassing-plugins) - handy
> for repair scripts that must delete beneath the soft-delete layer.

## Where it fits best

* audit-sensitive systems
* admin tools where deleted rows must remain inspectable
* apps where "delete" should default to reversible