Soft Deletes

Soft deletes let you "delete" a record without removing it from the database. Instead of issuing a DELETE statement, d1-eloquent sets a deleted_at timestamp on the row. This preserves the data for audit trails, undo functionality, and foreign key integrity.

Enable Soft Deletes on a Model

Add static softDeletes = true to your model class:

import { BaseModel } from '@orphnet/d1-eloquent'

interface PostAttrs {
  id: string
  title: string
  body: string
  deleted_at?: string | null
  created_at?: string
  updated_at?: string
}

export class Post extends BaseModel<PostAttrs> {
  static table = 'posts'
  static primaryKey = 'id'
  static softDeletes = true
}

Include deleted_at in your attributes interface as string | null — it will be null on live records and an ISO timestamp string on deleted ones.

Migration Requirement

Your database table must have a deleted_at TEXT column. Add it to your migration using the schema builder:

import type { TMigration } from '@orphnet/d1-eloquent/cli'
import { Schema } from '@orphnet/d1-eloquent/cli'

const migration: TMigration = {
  name: '20260101000000_create_posts_table',

  up: (schema: Schema) => {
    schema.createTable('posts', (t) => {
      t.id()
      t.text('title')
      t.text('body')
      t.softDeletes()
      t.timestamps()
    })
  },

  down: (schema: Schema) => {
    schema.dropTable('posts')
  },
}

export default migration

Existing tables

If you are adding soft deletes to an existing table, use schema.table() with t.addText() in a new migration instead of recreating the table:

import type { TMigration } from '@orphnet/d1-eloquent/cli'
import { Schema } from '@orphnet/d1-eloquent/cli'

const migration: TMigration = {
  name: '20260101000000_add_deleted_at_to_posts',

  up: (schema: Schema) => {
    schema.table('posts', (t) => {
      t.addText('deleted_at', { nullable: true })
    })
  },

  down: (schema: Schema) => {
    // SQLite does not support DROP COLUMN directly
    schema.table('posts', (t) => {
      t.dropSoftDeletes()
    })
  },
}

export default migration

Default Query Behavior

Once softDeletes = true, every query automatically excludes soft-deleted rows. You do not need to add WHERE deleted_at IS NULL yourself:

// Returns only live (non-deleted) posts
const posts = await Post.query().get(env.DB)

// Returns single live post — returns null if deleted
const post = await Post.find(env.DB, postId)

Soft-Deleting a Record

Call delete() as normal — d1-eloquent detects the softDeletes flag and sets deleted_at instead of issuing a DELETE:

const post = await Post.find(env.DB, postId)

if (post) {
  await post.delete(env.DB)
  console.log(post.attrs.deleted_at)  // "2026-03-01T14:23:00.000Z"
}

Restoring a Record

Call restore() on a soft-deleted instance to clear deleted_at:

// You need withTrashed() to find a deleted record first
const post = await Post.query()
  .withTrashed()
  .whereEq('id', postId)
  .first(env.DB)

if (post && post.attrs.deleted_at) {
  await post.restore(env.DB)
  console.log(post.attrs.deleted_at)  // null
}

After restore(), the record is visible again in normal queries.

Query Scopes

Include Deleted Rows — withTrashed()

// Returns all posts — live and deleted
const allPosts = await Post.query()
  .withTrashed()
  .orderBy('created_at', 'desc')
  .get(env.DB)

Only Deleted Rows — onlyTrashed()

// Returns only soft-deleted posts
const deletedPosts = await Post.query()
  .onlyTrashed()
  .get(env.DB)

// Count how many posts have been deleted
const count = await Post.query().onlyTrashed().count(env.DB)

Checking Deleted Status in Code

const post = await Post.query().withTrashed().whereEq('id', postId).first(env.DB)

if (post) {
  if (post.attrs.deleted_at !== null) {
    console.log(`Deleted at: ${post.attrs.deleted_at}`)
  } else {
    console.log('Post is live')
  }
}

Permanent Delete

There is no force-delete method on the model — soft-delete and hard-delete are distinct operations. To permanently remove a soft-deleted row, execute the SQL directly via D1:

await env.DB.prepare('DELETE FROM posts WHERE id = ?')
  .bind(postId)
  .run()

Use this sparingly — permanent deletes cannot be undone and may break revision history if revision tracking is enabled.

Pivot tables must NOT have a deleted_at column

If you use soft deletes on a parent model that has a belongsToMany relationship, do not add deleted_at to the pivot table.

The soft-delete scope appends deleted_at IS NULL without table qualification. In JOIN queries, this creates an ambiguous column reference (deleted_at could belong to either table) and D1 will return an error.

Keep pivot tables (e.g. post_tags, user_roles) free of deleted_at columns. Soft delete only on the parent model itself.