Implementing CRUD Wrappers — Removing Data

Alright! We just figured out how to get data into the database… and now it’s time to get rid of it when it is no longer needed. Time to remove data. (As always, the full implementation of this code can be found in the Application Framework.)

By the way, please note how I wrote remove and not delete. Any ideas why? You got it, because in general I prefer to not delete data. Before we go on, let’s clear up some terminology and weigh the pros and cons of how we get data out of our applications. In this article, and all of my other articles, I will always use “hard delete” and “soft delete” to differentiate between “a permanent removal” and “a reversible removal”, and I will refer to both of them together as “remove”.

I can’t remember the last time I burned a CD-R, but I still have a stack of them in my desk “just in case”

Hard Delete

When you “delete” data (typically called a “hard delete”, which is the term I will be using), that means that the data is physically removed from the database. We have done something which is the equivalent of called a DELETE in SQL, or running rm (in *Nix), del (Windows command line), etc. where the data is gone and cannot be retrieved again without some expensive calls to a data recovery specialist. Let’s see the pros and cons in the context of an OutSystems application.

Pros

• Saves space in the database.
• If you need the record to be 100% GONE for legal, privacy, regulatory, etc. reasons, this is how to do it.
• Impossible to accidentally write code that shows the data.

Cons

• No way to get the data back in case of accidental removal.
• Any audit trail of the removal (who and when) must be done separately from the data itself
• Your removal code must do something with child records, or you need to set your referential protection level to “ignore” or “delete”; if either of those choices do not make you cringe for all but the most unique use cases, they should.

Soft Delete

When you “remove” data without actually deleting it (commonly known as a “soft delete” which I will be using going forwards) the data itself remains in the database, but has been marked with a flag to indicate that it is no longer wanted. OutSystems very nicely supports this pattern; in our Entities we can select a Boolean Attribute as the “Is Active Attribute” and any Aggregates made that have this Entity as its first Entity added will automatically filter on that Attribute. Typically we name this Attribute “IsActive”, though some OutSystems system entities and Static Entities by default will, regretfully (in my opinion) name it “Is_Active”.

This was a game changer when I first started using OutSystems

Pros

• You never lose data; this is absolutely vital to many applications.
• OutSystems’ awareness of the flag makes it very difficult to omit it by accident and show data that should be hidden.
• Maintain referential integrity without having to necessarily do anything about child records.
• Very easy to maintain an audit trail of the removal (who and when).
• Easy to take the next step of having a separate archive system for the removed data.

Cons

• You take up more space in the database; unless you are storing very large pieces of data (which… could be argued… should go to a cheaper data store like S3 that is cheaper than database storage anyways) or have a high ratio of inactive records to active records, this should not be a very large “con” to play a role in your decision making.
• If you have a data model where there is a difference between “this record is removed, please do not show it” and “this record should be hidden, but it is common to want to show it”, you may need a second Attribute and add it to your queries.

Which To Use?

I always use a soft delete except in the following situations:

• The business logic, usually for a legal, regulatory, or privacy reason, requires a hard delete. Even in those situations, I usually use soft delete as my day-to-day usage within the application, and have separate hard delete functionality for the situations (such as a user closing their account and requesting data removal) for those scenarios.
• The data will be quite large, and I know it will never ever need to return and does not ever need audit trail.

That detour was a bit longer than I expected… back to our regularly scheduled program of deleting data. To refresh your memory, we previously made an Entity called SampleEntity where we added an IsActive flag, set it as the “Is Active Attribute”, and created the Action SampleEntity_Upsert in a logic folder named “SampleEntity” to facilitate saving the data.

Validation of Removal

We make an Action called SampleEntity_GetCanRemove, with an input named “Id” of type SampleEntity Identifier, and an output of EntityActionResult of the same type. GetCanRemove is an Action (set it to Public and Function) to validate that a record may be removed.

We set up a DatabaseException handler and have it re-throw a generic message, to prevent DB problems from sending ugly SQL messages to a user or helping an attacker. In the roughly 8 years I have been using this pattern, I have never once seen a GetCanRemove actually cause a DatabaseException, but better safe than sorry!

The logic of the GetCanRemove:

1. We run an Aggregate to retrieve the record. You’ll note that instead of letting this Aggregate be called “GetSampleEntityById” I called it “GetById”. This reduces the amount of things to remember to change when copy/pasting the CRUD Wrappers to work with new Entities. Add an “Max Records” = 1 to the query for performance.
2. Check to see if a record was actually found. If it wasn’t, use the Action “EntityActionResult_BuildFromError” to return a nice message that the record does not exist at all.
3. Otherwise, we check to see if the record has already been removed. If it has, again, use “EntityActionResult_BuildFromError” to let the user know that the record is already marked to be removed. If you are implementing a hard delete, skip this check.
4. Not shown in the implementation below (because it is not the common use), if removing this record should also remove child records (typically only needed on a hard delete), query for those child records, loop over the results, and run the “GetCanRemove” for each child record as well. Any records that cannot be removed should have the “Messages” of their EntityActionResult added to our EntityActionResult’s Messages.
5. Finally, we use “EntityActionResult_BuildFromSuccess” to create an EntityActionResult that indicates success. If we were checking child records (step #4) and discovered a failure, instead we should call “EntityActionResult_CombineEntityActionMessages” to combine our messages, and then set our EntityActionResult to use its output. That will ensure that the status of the child records will determine if the record may be removed or not, and provide a nice, combined list of issues for the user to resolve before re-trying the removal, instead of frustrating them with an error that they fix, then giving them a different error.

Typical implementation of a GetCanRemove Action

Removal

The SampleEntity_Remove Action is super easy to make, now that all of the hard work has been done. Like GetCanRemove, set it to “Public” and “Function”, and give it an input parameter named “Id” of type “SampleEntity Identifier” and an output of “EntityActionResult” with the same type. Like our Upsert logic, we have a DatabaseException handler which builds an EntityActionResult with a generic message and an AllExceptions handler that turns the Exception Message into an EntityActionResult. The main logic is:

1. Call SampleEntity_GetCanRemove.
2. If that fails, throw a ValidationException with its output as the message.
3. If any child records need to be removed as well (remember, almost always this is for a hard delete), query them, loop over them, and call their Remove Action as well. Any failures should immediate cause your code to throw a “ProcessingException” bubbling up the result from the Remove.
4. If it passes, do a “GetSampleEntityForUpdate” on the record.
5. Set the IsActive flag to false, “UpdatedOnDateTime” to CurrDateTime(), and “UpdatedByUserId” to “Session_GetNormalizedSessionUserId()”.
6. Update the record.
7. Build an EntityActionResult with a success and a user-friendly message.

If you are following a hard delete pattern, replace steps 4, 5, and 6 with “DeleteSampleEntity”.

Not too bad at all!

Wrapup

That is all there is to it! In our next article, we will see exactly how to write a CRUD Wrapper specifically for reading. While this is a rare occurance (I never make them unless I need to), it is something needed in certain applications. After that, we will take a look at how these all come together as they are used in an actual application.

J.Ja