@kysera/soft-delete

Soft delete plugin for Kysera - Mark records as deleted without permanently removing them from the database.

Installation

npm install @kysera/soft-delete

Overview

Metric	Value
Bundle Size	~4 KB (minified)
Dependencies	@kysera/core
Peer Dependencies	@kysera/executor, kysely >=0.29.0, zod (optional)

Exports

// Main plugin
export { softDeletePlugin } from './index'

// Types
export type { SoftDeleteOptions, SoftDeleteMethods, SoftDeleteRepository }

// Zod schema (optional, requires Zod) - only via the /schema subpath
export { SoftDeleteOptionsSchema, type SoftDeleteOptionsSchemaType } from './schema'

:::info Separate Export SoftDeleteOptionsSchema and SoftDeleteOptionsSchemaType are not exported from the package root. Import them from @kysera/soft-delete/schema — this keeps Zod an optional dependency. :::

Architecture

The soft-delete plugin uses the Unified Execution Layer (@kysera/executor) to work with both Repository and DAL patterns:

Plugin Interface: Implements the standard Plugin interface from @kysera/executor
Query Interception: Uses interceptQuery() hook to filter soft-deleted records from SELECT queries and narrow the row scope of UPDATE/DELETE statements
Repository Extensions: Uses extendRepository() hook to add soft-delete methods to repositories
Scoped Opt-Out: Uses the withPluginMetadata() channel from executor when a query must see soft-deleted rows, keeping all other plugins active

How It Works

The executor wraps Kysely with a Proxy that intercepts .selectFrom(), .updateTable(), and .deleteFrom() calls
When a query is built, the plugin's interceptQuery() is called
The plugin adds WHERE deleted_at IS NULL to the query builder — filtering SELECT results and narrowing UPDATE/DELETE row scope
The filtered query is executed
This works in both Repository and DAL patterns automatically

softDeletePlugin

Creates a soft delete plugin instance.

function softDeletePlugin(options?: SoftDeleteOptions): Plugin

SoftDeleteOptions

interface SoftDeleteOptions {
  /**
   * Column name for soft delete timestamp.
   * @default 'deleted_at'
   */
  deletedAtColumn?: string

  /**
   * Include soft-deleted records by default in queries.
   * When false, soft-deleted records are automatically filtered out.
   * @default false
   */
  includeDeleted?: boolean

  /**
   * List of tables that support soft delete.
   * If not provided, all tables are assumed to support it.
   * Takes precedence over excludeTables when both are provided.
   * @example ['users', 'posts', 'comments']
   */
  tables?: string[]

  /**
   * Tables that should be excluded from soft delete.
   * Ignored if the `tables` whitelist is provided.
   * @example ['migrations', 'sessions']
   */
  excludeTables?: string[]

  /**
   * Primary key column name used for identifying records.
   * Tables with different primary key names can be configured.
   * @default 'id'
   * @example 'uuid', 'user_id', 'post_id'
   */
  primaryKeyColumn?: string

  /**
   * Logger for plugin operations.
   * Uses KyseraLogger interface from @kysera/core.
   * @default silentLogger
   */
  logger?: KyseraLogger
}

Configuration Examples

import { softDeletePlugin } from '@kysera/soft-delete'

// Default configuration
const plugin = softDeletePlugin()

// Custom column name
const plugin = softDeletePlugin({
  deletedAtColumn: 'removed_at'
})

// UUID primary key
const plugin = softDeletePlugin({
  primaryKeyColumn: 'uuid'
})

// Only specific tables
const plugin = softDeletePlugin({
  tables: ['users', 'posts', 'comments']
})

// Include deleted by default (not recommended)
const plugin = softDeletePlugin({
  includeDeleted: true
})

// Custom logger
import { consoleLogger, createPrefixedLogger } from '@kysera/core'
const plugin = softDeletePlugin({
  logger: createPrefixedLogger('soft-delete', consoleLogger)
})

Repository Pattern

When used with @kysera/repository, the plugin extends repositories with additional methods.

Setup with Repository

import { createORM } from '@kysera/repository'
import { softDeletePlugin } from '@kysera/soft-delete'

const orm = await createORM(db, [
  softDeletePlugin({
    deletedAtColumn: 'deleted_at',
    tables: ['users', 'posts']
  })
])

const userRepo = orm.createRepository(createUserRepository)

// Automatic filtering - findAll excludes soft-deleted
const activeUsers = await userRepo.findAll()

// Soft delete operations
await userRepo.softDelete(userId)
await userRepo.restore(userId)

SoftDeleteMethods Interface

Repositories are extended with these methods:

interface SoftDeleteMethods<T> {
  // Single record operations
  softDelete(id: number | string): Promise<T>
  restore(id: number | string): Promise<T>
  hardDelete(id: number | string): Promise<void>
  findWithDeleted(id: number | string): Promise<T | null>

  // Query methods
  findAllWithDeleted(): Promise<T[]>
  findDeleted(): Promise<T[]>

  // Bulk operations
  softDeleteMany(ids: (number | string)[]): Promise<T[]>
  restoreMany(ids: (number | string)[]): Promise<T[]>
  hardDeleteMany(ids: (number | string)[]): Promise<void>
}

softDelete

Soft delete a record by setting the deleted_at timestamp.

async softDelete(id: number | string): Promise<T>

Parameters:

id - Primary key of the record

Returns: The soft-deleted record with deleted_at set

Throws: NotFoundError if record doesn't exist

Example:

const deletedUser = await userRepo.softDelete(userId)
console.log(deletedUser.deleted_at) // Date timestamp

restore

Restore a soft-deleted record by clearing the deleted_at timestamp.

async restore(id: number | string): Promise<T>

Parameters:

id - Primary key of the record

Returns: The restored record with deleted_at set to null

Throws:

NotFoundError if the record doesn't exist
RecordNotDeletedError if the record exists but is not soft-deleted
SoftDeleteError if the record disappears mid-restore (race condition)

All three error classes are exported from @kysera/core.

Example:

const restoredUser = await userRepo.restore(userId)
console.log(restoredUser.deleted_at) // null

hardDelete

Permanently delete a record from the database (bypasses soft delete).

async hardDelete(id: number | string): Promise<void>

Parameters:

id - Primary key of the record

Example:

await userRepo.hardDelete(userId)
// Record is permanently removed from database

findWithDeleted

Find a record by ID including soft-deleted records.

async findWithDeleted(id: number | string): Promise<T | null>

Parameters:

id - Primary key of the record

Returns: The record or null if not found

Example:

// Regular findById excludes soft-deleted
const user = await userRepo.findById(userId) // null if soft-deleted

// findWithDeleted includes soft-deleted
const user = await userRepo.findWithDeleted(userId) // Returns even if soft-deleted

findAllWithDeleted

Find all records including soft-deleted ones.

async findAllWithDeleted(): Promise<T[]>

Returns: Array of all records including soft-deleted

Example:

// Regular findAll excludes soft-deleted
const activeUsers = await userRepo.findAll()

// findAllWithDeleted includes soft-deleted
const allUsers = await userRepo.findAllWithDeleted()

findDeleted

Find only soft-deleted records.

async findDeleted(): Promise<T[]>

Returns: Array of soft-deleted records only

Example:

const deletedUsers = await userRepo.findDeleted()
console.log(`${deletedUsers.length} users in trash`)

softDeleteMany

Soft delete multiple records in a single query (bulk operation).

async softDeleteMany(ids: (number | string)[]): Promise<T[]>

Parameters:

ids - Array of primary keys

Returns: Array of soft-deleted records

Note: If some IDs are not found, a warning is logged and partial results are returned. The operation does not throw for missing records -- callers can check the returned array length to detect missing records.

Example:

const deleted = await userRepo.softDeleteMany([1, 2, 3, 4, 5])
console.log(`Soft-deleted ${deleted.length} users`)

// Check for missing records
if (deleted.length < 5) {
  console.warn('Some records were not found')
}

restoreMany

Restore multiple soft-deleted records in a single query (bulk operation).

async restoreMany(ids: (number | string)[]): Promise<T[]>

Parameters:

ids - Array of primary keys

Returns: Array of restored records

Example:

const restored = await userRepo.restoreMany([1, 2, 3])
console.log(`Restored ${restored.length} users`)

hardDeleteMany

Permanently delete multiple records in a single query (bulk operation).

async hardDeleteMany(ids: (number | string)[]): Promise<void>

Parameters:

ids - Array of primary keys

Example:

await userRepo.hardDeleteMany([1, 2, 3])
// Records are permanently removed from database

DAL Pattern

The plugin works seamlessly with @kysera/dal through the unified executor layer.

Setup with DAL

import { createQuery, createContext, withTransaction } from '@kysera/dal'
import { createExecutor } from '@kysera/executor'
import { softDeletePlugin } from '@kysera/soft-delete'

// Create executor with plugins
const executor = await createExecutor(db, [softDeletePlugin({ deletedAtColumn: 'deleted_at' })])

// DAL queries automatically get soft-delete filters
const getUser = createQuery((ctx: DbContext<Database>, id: string) =>
  ctx.db.selectFrom('users').where('id', '=', id).selectAll().executeTakeFirst()
)

// Create context from executor
const ctx = createContext(executor)

// Query automatically excludes soft-deleted records
const user = await getUser(ctx, '1')

Transaction Support (DAL)

Plugins are automatically propagated to transactions:

await withTransaction(executor, async txCtx => {
  // All queries in transaction have soft-delete filter applied
  const user = await getUser(txCtx, userId)
  const posts = await getUserPosts(txCtx, userId)

  // Both queries automatically filter soft-deleted records
})

Bypassing Filters (DAL)

To include soft-deleted rows in DAL queries, derive a metadata-scoped executor with withPluginMetadata() — only the soft-delete filter is switched off, and every other plugin (RLS, timestamps, ...) stays active:

import { withPluginMetadata } from '@kysera/executor'

const withDeleted = withPluginMetadata(executor, { includeDeleted: true })

const getAllUsers = createQuery((ctx: DbContext<Database>) =>
  // This query includes soft-deleted records
  ctx.db.selectFrom('users').selectAll().execute()
)

const allUsers = await getAllUsers(withDeleted)

:::warning Avoid getRawDb() for this getRawDb() bypasses all plugin interceptors, not just soft-delete. With an RLS plugin installed it removes tenant filtering too — a cross-tenant data leak. Use withPluginMetadata() for scoped opt-outs. :::

CQRS-lite Pattern

Combine Repository (writes) and DAL (reads) with shared plugins:

import { createORM } from '@kysera/repository'
import { createQuery, createContext } from '@kysera/dal'
import { softDeletePlugin } from '@kysera/soft-delete'

const orm = await createORM(db, [softDeletePlugin()])

// Repository for writes
const userRepo = orm.createRepository(createUserRepository)

// DAL for complex reads
const getDashboardStats = createQuery((ctx: DbContext<Database>, userId: string) =>
  ctx.db
    .selectFrom('users')
    .leftJoin('posts', 'users.id', 'posts.user_id')
    .where('users.id', '=', userId)
    .select(['users.id', 'users.name', sql<number>`count(posts.id)`.as('post_count')])
    .groupBy('users.id')
    .executeTakeFirst()
)

// Use together in transaction (same plugins!)
await orm.transaction(async ctx => {
  // Repository write
  const user = await userRepo.create({ name: 'Alice' })

  // DAL read - both respect soft-delete plugin
  const stats = await getDashboardStats(ctx, user.id)
})

Automatic Query Filtering

The plugin automatically filters out soft-deleted records from SELECT queries:

// This query automatically adds WHERE deleted_at IS NULL
const users = await userRepo.findAll()

// Equivalent SQL:
// SELECT * FROM users WHERE deleted_at IS NULL

// To include soft-deleted records:
const allUsers = await userRepo.findAllWithDeleted()
// SELECT * FROM users

Query Interception Implementation

The plugin uses interceptQuery() from the @kysera/executor Plugin interface:

// Simplified plugin implementation
{
  name: '@kysera/soft-delete',
  version: '0.9.0', // injected from package.json at build time

  interceptQuery<QB>(qb: QB, context: QueryBuilderContext): QB {
    // Filter SELECT/UPDATE/DELETE when not explicitly including deleted
    if (
      ['select', 'update', 'delete'].includes(context.operation) &&
      !context.metadata['includeDeleted'] &&
      !includeDeleted
    ) {
      return qb.where(`${context.table}.${deletedAtColumn}`, 'is', null)
    }
    return qb
  },

  extendRepository<T>(repo: T): T {
    // Add softDelete, restore, hardDelete methods...
  }
}

How it works:

createExecutor wraps Kysely with a Proxy
When .selectFrom(), .updateTable(), or .deleteFrom() is called, the plugin's interceptQuery() hook is invoked
The filter WHERE deleted_at IS NULL is applied to the query builder — filtering SELECT results and narrowing UPDATE/DELETE row scope
The modified query is executed
Works in both Repository and DAL patterns automatically

Method Override Pattern

IMPORTANT: Soft deleting itself uses the Method Override pattern — a DELETE is never silently converted:

✅ SELECT queries are automatically filtered to exclude soft-deleted records
✅ UPDATE/DELETE statements are narrowed with deleted_at IS NULL (soft-deleted rows are untouchable)
❌ DELETE operations are NOT automatically converted to soft deletes
✅ Use softDelete() method explicitly instead of delete()
✅ Use hardDelete() method to bypass soft delete and perform a real DELETE

This design is intentional for simplicity and explicitness.

// ❌ WRONG - delete() does NOT soft delete
await userRepo.delete(userId) // Permanently deletes!

// ✅ CORRECT - use softDelete()
await userRepo.softDelete(userId) // Sets deleted_at

// ✅ CORRECT - use hardDelete() for permanent deletion
await userRepo.hardDelete(userId) // Permanently deletes

Transaction Support

Soft delete operations respect ACID properties and work correctly with transactions.

Repository Pattern Transactions

await db.transaction().execute(async trx => {
  const txORM = await createORM(trx, [softDeletePlugin()])
  const txRepo = txORM.createRepository(createUserRepository)

  await txRepo.softDelete(1)
  await txRepo.softDeleteMany([2, 3, 4])

  // Both operations commit or roll back together
})

DAL Pattern Transactions

await withTransaction(executor, async txCtx => {
  // All queries in transaction have soft-delete filter applied
  const user = await getUser(txCtx, userId)

  // Soft-delete filter is automatically applied
  const posts = await getUserPosts(txCtx, userId)
})

Cascade Soft Delete Pattern

For related entities, manually implement cascade soft delete:

await db.transaction().execute(async trx => {
  const repos = createRepositories(trx)
  const userId = 123

  // First, soft delete child records
  const userPosts = await repos.posts.findBy({ user_id: userId })
  await repos.posts.softDeleteMany(userPosts.map(p => p.id))

  // Then, soft delete parent
  await repos.users.softDelete(userId)
})

withPluginMetadata() - Scoped Opt-Out

Extension methods that must reach soft-deleted rows (restore(), findAllWithDeleted(), findDeleted(), ...) derive a metadata-scoped executor with withPluginMetadata() from @kysera/executor. Only the soft-delete filter reacts to the metadata; every other plugin stays active:

import { withPluginMetadata } from '@kysera/executor'

// In repository extension methods (actual plugin implementation)
const withDeletedDb = withPluginMetadata(baseRepo.executor, { includeDeleted: true })

const extendedRepo = {
  async findAllWithDeleted(): Promise<T[]> {
    return await withDeletedDb.selectFrom(baseRepo.tableName).selectAll().execute()
  }
}

// In DAL queries
const withDeleted = withPluginMetadata(executor, { includeDeleted: true })
const getAllUsersIncludingDeleted = createQuery((ctx: DbContext<Database>) =>
  ctx.db.selectFrom('users').selectAll().execute()
)
const allUsers = await getAllUsersIncludingDeleted(withDeleted)

:::warning Why not getRawDb()? The previous getRawDb() escape bypassed all plugins — combined with RLS, findAllWithDeleted() leaked other tenants' rows. getRawDb() remains available for genuinely plugin-free internals, but scoped opt-outs should use withPluginMetadata(). :::

Database Schema

Ensure your tables have the deleted_at column:

-- PostgreSQL
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP DEFAULT NULL;
CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;

-- MySQL
ALTER TABLE users ADD COLUMN deleted_at DATETIME DEFAULT NULL;
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

-- SQLite
ALTER TABLE users ADD COLUMN deleted_at TEXT DEFAULT NULL;
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

TypeScript Types

SoftDeleteRepository

type SoftDeleteRepository<
  Entity,
  BaseRepo extends object = Record<string, never>
> = BaseRepo & SoftDeleteMethods<Entity>

The second parameter is your base repository type — the result combines its methods with SoftDeleteMethods<Entity>:

type UserRepoWithSoftDelete = SoftDeleteRepository<User, Repository<User, Database>>

Database Schema Type

interface UsersTable {
  id: Generated<number>
  email: string
  name: string
  deleted_at: Date | null // Must be nullable
}

Plugin Type

const plugin: Plugin = softDeletePlugin({
  deletedAtColumn: 'deleted_at',
  tables: ['users', 'posts']
})

Performance Considerations

Batch Operations

Batch operations use efficient single queries:

Records	Loop Time	Batch Time	Speedup
10	200ms	15ms	13x
100	2000ms	20ms	100x
1000	20000ms	50ms	400x

Partial Index (PostgreSQL)

-- Optimizes queries for active records
CREATE INDEX idx_users_active ON users(id)
WHERE deleted_at IS NULL;

Query Performance

The soft-delete filter adds minimal overhead:

// Automatic filter adds WHERE clause
SELECT * FROM users WHERE deleted_at IS NULL

// With index, this is very fast (index-only scan)

Best Practices

1. Use Nullable deleted_at

interface UsersTable {
  deleted_at: Date | null // ✅ Must be nullable
}

2. Handle Cascade Delete

await db.transaction().execute(async trx => {
  const repos = createRepos(trx)

  // Soft delete children first
  const posts = await repos.posts.find({ where: { user_id: userId } })
  await repos.posts.softDeleteMany(posts.map(p => p.id))

  // Then soft delete parent
  await repos.users.softDelete(userId)
})

3. Clean Up Old Records

// Periodically hard delete old soft-deleted records
const cutoffDate = new Date()
cutoffDate.setDate(cutoffDate.getDate() - 90)

await db.deleteFrom('users').where('deleted_at', '<', cutoffDate).execute()

4. Combine with Other Plugins

const orm = await createORM(db, [
  softDeletePlugin(), // Soft delete
  timestampsPlugin(), // Automatic timestamps
  auditPlugin() // Audit logging
])

5. Use Explicit Methods

// ❌ WRONG - delete() does NOT soft delete
await userRepo.delete(userId)

// ✅ CORRECT - use softDelete() explicitly
await userRepo.softDelete(userId)

// ✅ CORRECT - use hardDelete() for permanent deletion
await userRepo.hardDelete(userId)

Error Handling

import { NotFoundError } from '@kysera/core'

try {
  await userRepo.softDelete(userId)
} catch (error) {
  if (error instanceof NotFoundError) {
    console.error(error.message) // 'Record not found'
    console.error(error.detail) // '{"id":123}' — JSON string of the lookup filters
  }
}

Complete Examples

Repository Pattern Example

import { createORM, createRepositoryFactory } from '@kysera/repository'
import { softDeletePlugin } from '@kysera/soft-delete'
import { z } from 'zod'

const orm = await createORM(db, [softDeletePlugin({ deletedAtColumn: 'deleted_at' })])

const userRepo = orm.createRepository(executor => {
  const factory = createRepositoryFactory(executor)
  return factory.create({
    tableName: 'users',
    mapRow: row => ({
      id: row.id,
      email: row.email,
      name: row.name,
      deletedAt: row.deleted_at
    }),
    schemas: {
      create: z.object({
        email: z.string().email(),
        name: z.string()
      })
    }
  })
})

// Soft delete
await userRepo.softDelete(userId)

// Restore
await userRepo.restore(userId)

// Find all (excludes deleted)
const activeUsers = await userRepo.findAll()

// Find all including deleted
const allUsers = await userRepo.findAllWithDeleted()

DAL Pattern Example

import { createQuery, createContext, withTransaction } from '@kysera/dal'
import { createExecutor, withPluginMetadata } from '@kysera/executor'
import { softDeletePlugin } from '@kysera/soft-delete'

// Create executor with plugin
const executor = await createExecutor(db, [softDeletePlugin({ deletedAtColumn: 'deleted_at' })])

// Define queries
const getActiveUsers = createQuery((ctx: DbContext<Database>) => ctx.db.selectFrom('users').selectAll().execute())
const getAllUsers = createQuery((ctx: DbContext<Database>) => ctx.db.selectFrom('users').selectAll().execute())

// Execute queries
const ctx = createContext(executor)
const activeUsers = await getActiveUsers(ctx) // Excludes soft-deleted

// Scoped opt-out: soft-delete filter off, all other plugins stay active
const withDeleted = withPluginMetadata(executor, { includeDeleted: true })
const allUsers = await getAllUsers(withDeleted) // Includes soft-deleted

CQRS-lite Example

import { createORM } from '@kysera/repository'
import { createQuery } from '@kysera/dal'
import { softDeletePlugin } from '@kysera/soft-delete'

const orm = await createORM(db, [softDeletePlugin()])

const getDashboardStats = createQuery((ctx: DbContext<Database>, userId: string) =>
  ctx.db
    .selectFrom('users')
    .leftJoin('posts', 'users.id', 'posts.user_id')
    .where('users.id', '=', userId)
    .select(['users.id', 'users.name', sql<number>`count(posts.id)`.as('post_count')])
    .groupBy('users.id')
    .executeTakeFirst()
)

await orm.transaction(async ctx => {
  // Repository for writes
  const userRepo = orm.createRepository(createUserRepository)
  const user = await userRepo.create({ name: 'Alice' })

  // DAL for complex reads (same transaction, same plugins)
  const stats = await getDashboardStats(ctx, user.id)
})

Installation​

Overview​

Exports​

Architecture​

How It Works​

softDeletePlugin​

SoftDeleteOptions​

Configuration Examples​

Repository Pattern​

Setup with Repository​

SoftDeleteMethods Interface​

softDelete​

restore​

hardDelete​

findWithDeleted​

findAllWithDeleted​

findDeleted​

softDeleteMany​

restoreMany​

hardDeleteMany​

DAL Pattern​

Setup with DAL​

Transaction Support (DAL)​

Bypassing Filters (DAL)​

CQRS-lite Pattern​

Automatic Query Filtering​

Query Interception Implementation​

Method Override Pattern​

Transaction Support​

Repository Pattern Transactions​

DAL Pattern Transactions​

Cascade Soft Delete Pattern​

withPluginMetadata() - Scoped Opt-Out​

Database Schema​

TypeScript Types​

SoftDeleteRepository​

Database Schema Type​

Plugin Type​

Performance Considerations​

Batch Operations​

Partial Index (PostgreSQL)​

Query Performance​

Best Practices​

1. Use Nullable deleted_at​

2. Handle Cascade Delete​

3. Clean Up Old Records​

4. Combine with Other Plugins​

5. Use Explicit Methods​

Error Handling​

Complete Examples​

Repository Pattern Example​

DAL Pattern Example​

CQRS-lite Example​

See Also​

Installation

Overview

Exports

Architecture

How It Works

softDeletePlugin

SoftDeleteOptions

Configuration Examples

Repository Pattern

Setup with Repository

SoftDeleteMethods Interface

softDelete

restore

hardDelete

findWithDeleted

findAllWithDeleted

findDeleted

softDeleteMany

restoreMany

hardDeleteMany

DAL Pattern

Setup with DAL

Transaction Support (DAL)

Bypassing Filters (DAL)

CQRS-lite Pattern

Automatic Query Filtering

Query Interception Implementation

Method Override Pattern

Transaction Support

Repository Pattern Transactions

DAL Pattern Transactions

Cascade Soft Delete Pattern

withPluginMetadata() - Scoped Opt-Out

Database Schema

TypeScript Types

SoftDeleteRepository

Database Schema Type

Plugin Type

Performance Considerations

Batch Operations

Partial Index (PostgreSQL)

Query Performance

Best Practices

1. Use Nullable deleted_at

2. Handle Cascade Delete

3. Clean Up Old Records

4. Combine with Other Plugins

5. Use Explicit Methods

Error Handling

Complete Examples

Repository Pattern Example

DAL Pattern Example

CQRS-lite Example

See Also