0

When we're deleting data, often instead of deleting a DB row we instead set a date_deleted column (for non-privacy-sensitive data) so that the data is accessible later for auditing if necessary.

How can we use Objection, Knex, or Postgres to pre-filter on this column always (if it exists) and otherwise return all rows? (We only ever want to look at these columns manually, not through code.)

Looks like postProcessResponse will work fine in Knex - we just filter the returned rows checking for date_deleted. But this would of course be more efficient if we can find a way to always filter before the query fires, not after getting the results.

Freewalker
  • 6,329
  • 4
  • 51
  • 70

1 Answers1

1

Using postProcessResponse will give you various problems with paging etc.

You could use start event to modify each query:

knex.on('start', function(builder) {
    builder.whereNull('date_deleted')
});

knex.select('*')
  .from('users')
  .then(function(Rows) {
    //Only contains Rows where date_deleted is null
  });

But also that is quite error prone, for example if you use .orWhere in your query or to any other query that is not plain select...

Feature that you are looking for is not really convenient to implement with knex. For example with objection.js there are much more options how to do it.

For knex I would probably just extend the query builder with special function, which does something like this (since knex 0.19.1):

const Knex = require('knex');

Knex.QueryBuilder.extend('selectWithoutDeleted', function(tableName) {
  return this
    .with('tableWithoutDeleted',
      knex(tableName).whereNull('date_deleted')
    )
    .from('tableWithoutDeleted');
});

const res = await knex.selectWithoutDeleted('table')
  .where('col1', 'foo')
  .orWhere('col2', 'bar');

That should work in theory... CTE first limits results to not contain deleted rows and rest of where clauses will be applied to that limited result set.

Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • Comments on best practices with Objection? We're also using that library but have been considering whether we still need it. We have some ideas but would like to hear outside too. (Updated question.) – Freewalker Oct 08 '19 at 16:20
  • In objection.js you can implement for example toJson() methos for class to filter it out. I'm not sure which would be the best practice in your case, but one possibility would be to extend the query builder to always wrap selects to CTE thing and limit which kind of queries can be done, unless some special `.noFilter()` builder method is called. In gitter in objection.js channel people can give better solutions more suitable to your case. – Mikael Lepistö Oct 09 '19 at 08:05
  • It looks like this library works for Soft Delete in Objection and has been updated recently: https://www.npmjs.com/package/objection-js-soft-delete – Freewalker Aug 31 '22 at 19:50