Enabling and Using Soft Deletes in AdonisJS Lucid ORM (v5 & v6)

Desired outcome

Add soft‑delete support to a Lucid model so that calling delete() marks the row as trashed instead of removing it, allow queries to include or exclude trashed rows, and restore a trashed row when needed.

Necessary prerequisites

1. An AdonisJS project (version 5 or 6) with Lucid ORM configured and a database connection working.
2. Access to run Ace commands (typically node ace) with sufficient privileges to create and run migrations.
3. A model you want to soft‑delete (e.g., Post) and its corresponding table.

Focused procedure

1. Identify the AdonisJS major version

Run the version check in the project root:

node ace --version

The output will be 5.x.x or 6.x.x. Keep this number handy because the API for attaching the soft‑delete trait differs between the two major releases.

2. Add a nullable deleted_at column

Create a migration that adds the column to the target table. Replace posts with your table name.

node ace make:migration add_soft_deletes_to_posts

Edit the generated file (database/migrations/xxxxxx_add_soft_deletes_to_posts.ts) as follows:

import { BaseMigration } from '@adonisjs/lucid/database/migrations'

export default class extends BaseMigration {
  public async up () {
    this.schema.alterTable('posts', (table) => {
      table.timestamp('deleted_at', { useTz: true }).nullable()
    })
  }

  public async down () {
    this.schema.alterTable('posts', (table) => {
      table.dropColumn('deleted_at')
    })
  }
}

Run the migration:

node ace migration:run

3. Attach the SoftDeletes trait/mixin to the model

Open the model file (app/Models/Post.ts) and add the appropriate code based on the version you identified.

AdonisJS 5

import { BaseModel, column } from '@adonisjs/lucid/orm'
import { SoftDeletes } from '@adonisjs/lucid/orm/mixins/soft_deletes'

export default class Post extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public title: string

  // attach the mixin
  public static readonly softDeletes = new SoftDeletes()
}

AdonisJS 6

import { BaseModel, column } from '@adonisjs/lucid/orm'
import { SoftDeletes } from '@adonisjs/lucid/orm/mixins'

export default class Post extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public title: string

  // apply the mixin via static importer
  public static readonly softDeletes = new SoftDeletes()
}

Note: In v6 the mixin is imported from '@adonisjs/lucid/orm/mixins'; the class usage is identical, but ensure you have the latest Lucid package (@adonisjs/lucid@^6.0.0) installed.

4. Perform a soft delete

Using Ace interactive shell or any controller, fetch a row and call delete():

const post = await Post.find(1)
await post?.delete()

Lucids ORM will update the deleted_at column with the current timestamp instead of issuing a hard DELETE FROM posts.

5. Verify the row is hidden from default queries

Run a standard Lucid query:

const posts = await Post.all() // returns rows where deleted_at IS NULL

The post you just deleted should not appear in posts.

6. Include trashed rows when needed

Both versions provide a scope to bring back soft‑deleted rows:

AdonisJS 5

const allPosts = await Post.withTrashed().fetch()

AdonisJS 6

const allPosts = await Post.withTrashed()

You can also isolate only trashed rows:

const onlyTrashed = await Post.onlyTrashed()

7. Restore a trashed row

To undo the soft delete, clear the deleted_at column:

await Post.withTrashed().where('id', 1).restore()

After restoration, the row will again be returned by Post.all().

Expected checks

• After migration, inspect the table schema (e.g., via \d posts in psql) to confirm a nullable deleted_at timestamp column exists.
• After delete(), query the table directly: SELECT deleted_at FROM posts WHERE id = 1; should return a non‑null timestamp.
• Default Post.all() excludes the row; Post.withTrashed() includes it.
• After restore(), the column returns to NULL and the row reappears in default queries.

Relevant recovery options

Soft‑delete operations only change the deleted_at column; they do not remove data. If you accidentally run a hard delete via raw SQL or the query builder (Post.query().where(...).delete()), the row is permanently lost unless you have a database backup or point‑in-time recovery enabled. To guard against unintended hard deletes:

• Review codebases for direct delete() calls on the query builder that bypass the model.
• Consider adding a database trigger that prevents updates to deleted_at when the column is already non‑null, if you want to enforce a “delete‑only‑once” rule.
• Regularly dump the table or enable logical replication to have a restore point.

Limitations and practical verification

Soft deletes operate at the ORM layer:

• Unique indexes that include the trashed row may still cause conflicts if the index does not ignore deleted_at. Adjust the index definition to include a partial index (WHERE deleted_at IS NULL) if needed.
• Relationships eager‑loaded with load() will not fetch soft‑deleted related models unless the relation query itself uses withTrashed().
• Bulk operations through the query builder (Post.query().where(...).delete()) bypass the model’s delete method and therefore perform a hard delete unless you explicitly add withTrashed() before the call.

To confirm that your implementation respects these limits, run the following diagnostic steps in a development environment:

1. Create a unique index on (title, deleted_at) and attempt to insert two rows with the same title where one is soft‑deleted; the insert should succeed only if the index ignores deleted_at.
2. Eager load a relationship (Post.with('author')) after trashing the related author and verify that the author is missing unless you call Post.withTrashed().with('author').
3. Execute a bulk delete (Post.query().where('id', '<', 10).delete()) and check the table rows; they should be removed entirely, proving the bypass.

If any of these checks produce unexpected results, adjust your queries or schema accordingly.