Cascade on delete

[tobiasBora]

I have some items (say Authors) and some things that link to these items (say Books), but if I delete a given Author, I'd like to delete all items that refer to this author, not to have broken links in the database (I think this corresponds for instance to cascade deletion in django). Is it possible to do that automatically with Dexie.js (either theoretically or in practice)?

[dfahlander]

Dexie does not have any explicit foreign keys nor automatic cascade deletion. But it has ACID consistency so you can easily create a service method or function deleteAuthor() that deletes an author consistently:

function deleteAuthor(authorId) {
  return db.transaction('rw', db.books, db.authors, () => {
    db.books.where({authorId: authorId}).delete();
    db.authors.delete(authorId);
  });
}

[tobiasBora]

Ok thanks. So I guess it is not planned?

My main issue with this solution is that I need to keep track of all such objects linked to a single author… (in my case there might be plenty) but I'm thinking that for my specific application it might make more sense to create a separate database for each "author", and put all objects related to this author in the same database, this way deleting a single database is trivial.

Also, sorry to hijack here, but since you mention transaction, I need to implement an undo mechanism in my app, does Dixiejs provide a simple way to keep track of all transactions to provide an efficient way to undo or redo a transaction?

[dfahlander]

Cascading deletes are only relevant in hierarchial structures. One useful pattern for this that I have been advocating for, is to use a parentPath property on the objects define the hierarchy. That way it is easier to move or delete entire subtrees in a single query. It's describe in the release notes of dexie@4.0.1-beta.14. That pattern is even simpler if using a single table to store all the objects and let a type property distinguish type instead of the table, but it is also possible to use with multiple tables and - just that it need multiple queries (one per table) when deleting or moving.

[tobiasBora]

I see, that would be great to have. Then we can maybe keep this issue and remove the question label?

Btw, I created another issue to discuss about undo here #1933

[NeodymiumFerBore]

Was also looking for a cascade on delete feature. I stumbled upon multiple solutions:

• the old Table.hook('query-type') API
  • Pros: I don't know, and didn't test it
  • Cons: while not being deprecated, the documentation explicitly states that the more recent DBCore API is preferred
• @dfahlander solution in his previous comment
  • Pros: easy to implement
  • Cons:
    • for multiple level relations (like "Items" having an FK to "Lists", having an FK to "Boards"), have to implement the whole CASCADE logic for each level of deletion (deleting a List should delete child Items first, and deleting a Board should also delete child Lists first, that have to delete their child Items first). This can get pretty heavy.
• Using the parentPath pattern described in v4.0.1-beta.14 release notes
  • Pros:
    • most modern way of achieving the desired cascade (?), with moving trees support
    • all logic in a set of functions, re-usable for any object in the tree
  • Cons:
    • have all items in a single table
    • for cross table relations, requires multiple queries, and all cascading FK to have a unique name ("parentPath" or so)
    • With the same previous example (Boards, Lists, Items), moving a list to another Board requires a single FK to be modified, but with the parentPath pattern, it requires the whole hierarchy to be altered.
    • Looks a bit complicated for cross table relations (maybe because I'm a Dexie noob)
• Using Dexie.use() API
  • Pros: have the logic implemented once at DBCore level, with conditions on DBCoreMutateRequest.type and table names
  • Cons:
    • if all involved stores are not included in the original transaction, then I don't know how to include a transaction that should be validated at the same time than (or at least awaited before) the original query.

Maybe I missed a crucial point in the documentation. I was hyped by the Dexie.use() solution, but having to include all stores in deletion queries made me doubt.

May I have some guidance for the example I gave (Boards, Lists and Items in separate tables)? Am I wrong running away from a "mono table"? Starting a new project with Dexie 4.0.1, should the parentPath pattern be preferred over Dexie.use()?

[dfahlander]

Traditional cascading deletes can be implemented using DBCore as follows:

1. Create a DBCore middleware and override transaction and table
2. In transaction make sure to always include related tables whenever a 'readwrite' transaction is requested. For example if a readwrite transaction on 'organizations' is requested, also append the 'organizationUnits' table if those belong to organizations and may be deleted as a result of deleting an organization.
3. In table, call downlevelDatabase.table() and override mutate method before returning your clone.
4. In your version of the mutate method, start by querying all objects from related tables that reference the objects that are about to be deleted and check if another cascading delete operation needs to take place before actually performing the deletions. This has to be done recursively.

Some type of declaration of the foreign keys' properties and tables need to be declared if this shall be implemented generically. This declaration could for example be an argument to the middleware factory function.

The add-on dexie-relationships does not implement cascading deletes but it does a trick to override Dexie's schema declaration and invents its own syntax for declaring indexes representing foreign keys. Maybe this approach could be used as an alternative to providing the foreign-key declaration as argument to middleware factory. That add-on is a bit old but the code is very small and it still works with dexie 4.