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.
