Objection.js: 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. :smile:

[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 :smile:

[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?

[jordansexton]

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.