How to implement soft delete?

[anttilinno]

I see hooks for beforeInsert and beforeUpdate, but there is no hook for beforeDelete. I guess restored_at could be handled by beforeUpdate.
Also, where to inject condition "deleted_at IS NULL" to all CRUD queries (except the ones that have some configuration, that suppresses the condition)?

If this is kind of obvious, then I humbly apologize, I'm new to js and have problems with OOP, promises etc. 😄

[koskimas]

Sorry it took me so long to answer this.

There is no direct support for soft delete. You need to use an update to do this. You can always create your own softDelete method. The following example also adds methods for your other question:

class SoftDeleteQueryBuilder extends objection.QueryBuilder {
  constructor(modelClass) {
    super(modelClass);

    this.onBuild(builder => {
      if (!builder.context().withArchived) {
        builder.whereNull('deleted_at');
      }
    });
  }

  withArchived(withArchived) {
    this.context().withArchived = withArchived;
    return this;
  }

  softDelete() {
    return this.patch({deleted_at: new Date().toIsoString()});
  }
}

objection.Model.QueryBuilder = SoftDeleteQueryBuilder;
objection.Model.RelatedQueryBuilder = SoftDeleteQueryBuilder;

Now you can do stuff like this:

Person
  .query()
  .softDelete()
  .where('foo', '<', 42)

Person
  .query()
  .where('foo', '<', 42)
  .withArchived(true)

You can read more about the used features from these:

• Custom query builder
• Query context
• onBuild hook

I didn't test the code, so there may be some problems with it 😄

[koskimas]

I'm closing this. Please open another issue or join the gitter chat if you have more questions about this.

[tylerjbainbridge]

Is it possible to call $beforeDelete/$afterDelete from the query builder?

[jordaaash]

Here's an approximation of how I do it to trigger $beforeDelete/$afterDelete. Caveat: this code won't work out of the box (it relies on some other methods from my own subclass of Objection's Model class).

/* @flow */
'use strict';

const SoftDelete = function <T: Class<Model>> (Model: T): T {
    class SoftDeleteQueryBuilder extends Model.QueryBuilder {
        delete (...rest: any[]): SoftDeleteQueryBuilder {
            return super.delete(...rest).onBuild(function (query: SoftDeleteQueryBuilder): void {
                const operation: QueryBuilderOperation = query.findLastOperation(/delete/);

                operation.onBuildKnex = function (knexQuery: QueryBuilder): void {
                    const deletedAt: string  = new Date;
                    const onAfter2: Function = this.onAfter2;

                    this.onAfter2 = function (query: SoftDeleteQueryBuilder, updated: number): any {
                        if (updated > 0) {
                            const instance: SoftDelete = this.instance;
                            if (instance != null) {
                                instance.deletedAt = deletedAt;
                            }
                        }
                        return onAfter2.call(this, query, updated);
                    };

                    knexQuery.update({ deletedAt }).whereNull('deletedAt');
                };
            });
        }

        isDeleted (): SoftDeleteQueryBuilder {
            return this.whereNotNull('deletedAt');
        }

        notDeleted (): SoftDeleteQueryBuilder {
            return this.whereNull('deletedAt');
        }

        undelete (properties: ?Object): SoftDeleteQueryBuilder {
            return this.mergeContext({ undelete: true })
                .isDeleted()
                .patch({
                    ...properties,
                    deletedAt: null
                });
        }
    }

    return class SoftDeleteModel extends Model {
        static get QueryBuilder (): Class<SoftDeleteQueryBuilder> {
            return SoftDeleteQueryBuilder;
        }

        async $beforeDelete (options: ModelOptions, context: Object): void {
            if (this.deletedAt != null) {
                throw this.constructor.createIllegalError(context);
            }

            await super.$beforeDelete(options, context);
        }

        async $beforeUpdate (options: ModelOptions, context: Object): void {
            if (context.undelete) {
                if (options.old.deletedAt == null) {
                    throw this.constructor.createIllegalError(context);
                }

                options.operation = 'undelete';
            }

            await super.$beforeUpdate(options, context);
        }
    };
};

module.exports = SoftDelete;

This allows you to call model.delete() as normal and have it transparently soft delete instead.

[trenunu]

@koskimas thanks for the soft delete example and continuous support to this great lib.

I've got the same question here: How can you trigger $beforeDelete/$afterDelete from the customized SoftDelete query builder easily?

Also, How to reference to the instance from the query builder?

[kaarelkk]

this.onBuild(builder => {
  if (!builder.context().withArchived) {
    builder.whereNull('deleted_at');
  }
});

Currently migrating from objection 1 to 2 and am using similar approach @koskimas mentioned. I'm trying to use grouped chain of where queries:

Person
  .query()
  .where('foo', '<', 42)
  .andWhere(builder => {
    builder.where('bar', 'like', '%bar%')
  }).debug();

The generated SQL in v2 is different from v1 in a way that the onBuild seems to be applied also to the grouped chain query and the output is similar to this:

select * from persons where foo < 42 and deleted_at is null and (bar like '%bar%' and deleted_at is null)

Any suggestions how to exclude the whereNull from the grouped chain query without having to add the .withArchived() all over the place?

.where(builder => {
  builder.where('bar', 'like', '%bar%').withArchived(true)
}

[hanstf]

this.onBuild(builder => {
  if (!builder.context().withArchived) {
    builder.whereNull('deleted_at');
  }
});

Currently migrating from objection 1 to 2 and am using similar approach @koskimas mentioned. I'm trying to use grouped chain of where queries:

Person
  .query()
  .where('foo', '<', 42)
  .andWhere(builder => {
    builder.where('bar', 'like', '%bar%')
  }).debug();

The generated SQL in v2 is different from v1 in a way that the onBuild seems to be applied also to the grouped chain query and the output is similar to this:

select * from persons where foo < 42 and deleted_at is null and (bar like '%bar%' and deleted_at is null)

Any suggestions how to exclude the whereNull from the grouped chain query without having to add the .withArchived() all over the place?

.where(builder => {
  builder.where('bar', 'like', '%bar%').withArchived(true)
}

Hello, we are facing similar problem as well. This is how we do it but it's abit hacky.

this.onBuild(q => {
      if (!q['isPartial']() && q.isFind() && !q.context(). withArchived) {
          q.whereNull('deletedAt');
        }
    });

the !q'isPartial' is to add the whereNull at the main query only.

[1mike12]

Like everyone here, I too was looking at a way to do it and the 3 major plugins just weren't delivering.

Here's some copy pasta for a method that actually worked like soft deletes in other major ORMS.

1. we use a customizeable "deletedAt" column,
2. override delete so it now soft deletes,
3. we apply exclusion of soft deleted models automatically
4. (along with related models)
5. and works with version 3 and typescript (kind of). The typings are a real mess and there a changes that would need to be made in the library itself

export class SoftDeleteQueryBuilder<M extends Model, R = M[]> extends QueryBuilder<M, R> {
  ArrayQueryBuilderType!: SoftDeleteQueryBuilder<M, M[]>
  SingleQueryBuilderType!: SoftDeleteQueryBuilder<M, M>
  MaybeSingleQueryBuilderType!: SoftDeleteQueryBuilder<M, M | undefined>
  NumberQueryBuilderType!: SoftDeleteQueryBuilder<M, number>
  PageQueryBuilderType!: SoftDeleteQueryBuilder<M, Page<M>>

  constructor(modelClass: ModelClass<M>) {
    //@ts-ignore
    super(modelClass)
    // @ts-ignore
    const softDeleteName: string = modelClass.softDeleteName
    if (softDeleteName === undefined) {
      throw new Error("Model does not have a softDeleteName")
    }
    this.onBuild(q => {
      if (q.isFind() && !q.context().includeSoftDeleted) {
        q.whereNull(`${q.tableRefFor(this.modelClass())}.${softDeleteName}`)
      }
    })
  }

  withGraphFetched(expression: RelationExpression<M>, options?: GraphOptions): this {
    return super.withGraphFetched(expression, options)
    .modifyGraph(expression, builder => {
      const modelClass = builder.modelClass()
      // @ts-ignore
      const softDeleteName = modelClass.softDeleteName
      if (softDeleteName !== undefined && !builder.context().includeSoftDeleted) {
        void builder.whereNull(`${builder.tableRefFor(builder.modelClass())}.${softDeleteName}`)
      }
    })
  }

  withGraphJoined(expr: Objection.RelationExpression<M>, options?: Objection.GraphOptions): this {
    return super.withGraphJoined(expr, options)
    .modifyGraph(expr, builder => {
      const modelClass = builder.modelClass()
      // @ts-ignore
      const softDeleteName = modelClass.softDeleteName
      if (softDeleteName !== undefined && !builder.context().includeSoftDeleted) {
        void builder.whereNull(`${builder.tableRefFor(builder.modelClass())}.${softDeleteName}`)
      }
    })
  }

  delete() {
    this.context().softDelete = true
    // @ts-ignore
    const column = this.modelClass().softDeleteName
    // @ts-ignore
    return this.patch({ [column]: new Date().toISOString() })
  }

  // copied from official plugin
  hardDelete(): SoftDeleteQueryBuilder<M, number> {
    return super.delete()
  }

  // copied from official plugin
  undelete(): SoftDeleteQueryBuilder<M, number> {
    this.context().undelete = true
    // @ts-ignore
    const column = this.modelClass().softDeleteName
    return this.patch({ [column]: null })
  }

  withSoftDeleted(): SoftDeleteQueryBuilder<M, R> {
    this.context().includeSoftDeleted = true
    return this
  }
}

export default class BaseDatedModel extends Model {

  public deletedAt: string | null
  public id: string

  static softDeleteName = "deletedAt"
  QueryBuilderType!: SoftDeleteQueryBuilder<this>
  static QueryBuilder = SoftDeleteQueryBuilder
}

caveats

depends on this PR cus typing for constructor is busted #2659
types in general are kinda wonky and I had to use a bunch of ts ignores because of the way querybuiders and their model classes are typed.
I just came up with this the last few days of pulling my hair out. I haven't fully tested everything but it seems to work and my tests in the rest of my app are fine.