Delete objects diretly on the Mendix database by SQL command

0
Hello Mendix Community,in our Mendix App we have a Master Entity and a Detail Entity connected by an association.In this association we forgot to define that the Details should also be deleted when the Master is deleted.Now we have the situation that we have already deleted a lot of Master Objects and the corresponding Details are still available. We want to delete this orphaned Detail objects.We can select and delete the orphaned Detail Objects directly on the database by SQL commands.--> But is it a good approach to do this directly on the database?We are using a Mendix Server on Premise with Mendix Version 10.24.4.Thanks in advance.Best regrards Uwe
asked
3 answers
1

Hello Uwe



What you can do is to simply create a microflow to delete the hanging Details objects and trigger it in your cloud environment as an admin user.


In the future I advise you to enable delete behaviour on the association between your objects. This will ensure that no hanging Details objects will be kept in the database if the Master objects are removed. (it's called cascading delete and you can read more about it
here -> https://academy.mendix.com/link/modules/722/Ensuring-your-Data-is-Valid-and-Consistent
or here -> https://docs.mendix.com/refguide/configuring-a-domain-model/#delete-behavior


Hope this helps!



answered
1

Uwe,

In case this is helpful, the Xpath for finding orphaned Detail objects in Mendix would look like this:

[not(MyFirstModule.Detail_Master/MyFirstModule.Master)]

When I started out with Mendix, this Xpath took me some time to find and learn.


So the microflow would look like this:


Maybe that will be helpful.

Mike

answered
0

In addition to the given answer, the following points can also be mentioned to strengthen the solution:


Direct SQL deletion is not recommended

  • Deleting data directly in the database bypasses Mendix logic such as before/after delete events, validations, and possible custom business rules. This can lead to data inconsistencies, cache issues, or missing audit information. SQL should only be considered as a last resort and with extreme caution.


Batch or scheduled cleanup approach

  • If there is a large number of orphaned Detail records, it is safer to delete them using a batch-based microflow or a Scheduled Event. Processing records in smaller chunks helps avoid performance issues and long-running locks on the database.


Safe selection of orphan records

  • In the microflow, Detail objects should be retrieved with a clear filter, such as checking that the Master association is empty. This ensures that only truly orphaned records are removed and prevents accidental deletion of valid data.


Always test in non-production first

  • Cleanup logic should be executed in a test or acceptance environment first. Once the results are verified, it can be safely run in production using an admin account.


Prevent future issues

  • Besides enabling delete behavior (cascading delete) on the association, it can also be useful to add additional safeguards such as validation rules, consistency checks, or even a soft-delete pattern for critical data.


In short:

While deleting orphan records via SQL is possible, the best practice in Mendix is to handle cleanup through microflows in a controlled, traceable, and safe way.


answered