1

In a microservice architecture I have a BREAD (CRUD) service that has methods for interacting with database that holds some number of different entities that reference each-other. I also use a Message Broker to publish messages when a change occurs to the entities (Add, Edit, Delete methods) after the query returns successfully.

Problem

When deleting rows from a table in a database that with it being referenced in another table, there are only two options: delete rows that are referencing it first and using ON DELETE CASCADE option. In my case, that alone is not enough. The problem I need to solve is:

How to delete entity from the database with rows referencing it, whilst keeping the ability to publish changes to MQ?

Considered Solutions

I. Channels (those languages that have them)

Complexity: High

Create a bidirectional channel for every entity. When a slave entity is subscribed to the master entity's channel it will send a registration message on the channel that will notify the master entity what entity depends on it. When an entity is to be deleted, push a message to the channel that will notify listeners that it will be deleted. Wait for a confirmation messages of all the slaves to be received and then delete the entity.

Create user, email, attachment channels (or a single one with filtering in each service)
Create user, email, attachment services with its master channel and channels of the parent dependency.  

(later)

Request (delete user) 
  -> publish on user channel that a user will be deleted
    -> email entity receives, send a requests in its channel that it will be deleted
      -> attachment entity receives, deletes the attachments for the email
      <- publish change to MQ and respond that the attachments are deleted
    <- publish change to MQ and respond that the emails have been deleted
    -> contact entity receives, deletes all the user contacts
    <- publish change to MQ and respond that the contacts have been deleted
  <- deletes the user, responds to a requests

II. Callbacks

Complexity: Medium

Create a callback that will be called before the entity is deleted. The callback will call delete methods on all dependent entities.

Create user, email, attachment services
Create user, email, attachment callbacks with references to its dependencies
Attach callback to service

(later)

Request (delete user) 
  -> run user callback
    -> email entity `Delete()` is called. run email callback
      -> attachment entity `Delete()` is called, deletes the attachments for the email
      <- publish change to MQ and return that the attachments are deleted
    <- publish change to MQ and return that the emails are deleted
    -> contact entity `Delete()` is called, deletes all the user contacts
    <- publish change to MQ and return that the contacts are deleted
  <- deletes the user, publish change to MQ and responds to a requests

III. DB Engine Notification

Complexity: Medium

Some Database engines have internal channels for sending and listening of notifications

Create listener on user, email, attachment DB channels (or a single channel with filtering in each service)
Create user, email, attachment services and pass the channel listener(s)

(later)

Request (delete user) 
  -> run user callback
  <- deletes the user with `CASCADE` and responds to a requests
    -> email entity listener handler is called - publish change to MQ
    -> attachment entity listener handler is called - publish change to MQ
    -> contact entity listener handler is called - publish change to MQ

Not applicable solutions

SQL CASCADE

If using on delete cascade (without notify), I would lose the ability to publish events of, in the above example, email, attachment and contact entities and therefore I wouldn't be able to send the change to MQ

Solutions for streaming changes at a database level (ex Debezium)

I would like to have a full control of when, how and where the messages are send and would not like to introduce a 3rd Party Dependency if its not strictly necessary.

Conclusion

I would like to hear your opinions of handling deleting with the given requirements and your thoughts on the stated solutions above.

3

The DELETE and Notify actions need to be separate. Before deleting the entities you will need to find all entities that will be deleted and queue up notifications. This doesn't necessarily mean you need to publish those messages. You just need to keep track of them in a list or collection of some sort (don't forget to guard against duplicate messages).

After that, perform the delete operations. Upon committing that transaction, publish the notifications.

Since you must query and discover all items that will be deleted, an ON DELETE CASCADE might not be the best solution in case there were other INSERTs in between collecting the notifications to be sent and performing the DELETE operation. Once you have logic surrounding the removal of an entity, you are better off removing entities from your application layer rather than having the database do this.

Unless, of course, you want to do this with {Font: Dripping blood}TRIGGERS{/Font} and a table for storing pending notifications...

  • too bad your font change doesn't render properly :-) – Bart van Ingen Schenau Apr 20 '20 at 11:31
  • I actually like the idea of implementing an AFTER DELETE trigger to fill a table for pending notifications. In a system where all tables have a uniform PK, this solution, should actually not be too hard to implement, and it works for deletes with and without cascade constraints. – Doc Brown Apr 20 '20 at 14:17
  • @DocBrown: Using triggers and a "pending notifications" table does work. I've always been weary of triggers, because they always execute. It is difficult to build an abstraction around a trigger, because triggers always deal with concrete data. Triggers work great until you don't want to publish notifications. I'm not 100% against triggers for this solution, but it would not be my first preference. – Greg Burghardt Apr 20 '20 at 14:26
  • @GregBurghardt: I am pretty sure filling something like a "delete log table" by a trigger is not really problematic, and I see the fact "triggers always (= reliably) execute" as a benefit. The application can still decide what they do with the entries in this log table, if, when and how notifications are created from it. To keep this maintainable, of course, I would care for keeping the responsiblity of the trigger very small - it should be only provide a general db mechanism for tracing deletions, without any business logic of generating notifications. ... – Doc Brown Apr 20 '20 at 18:56
  • ... and that is actually the difference to what the OP suggested in apporach III – Doc Brown Apr 20 '20 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.