Simplifying data migration in Outsystems

Ever tried to delete an user, but ran into foreign key constraint errors? Deleting records can extremely frustrating. Especially when you have other entities referencing your record, forcing you to query all of those tables. That's annoying if you have a few, let alone if it's heavily used throughout your environment. But, there's an app for that, or rather: a Forge component. In this quick tutorial I will show you how you can use Record Delete Guardian to check references across tables.

Record Delete Guardian does the hard work for you in querying entities that have foreign keys to your table. It uses the Outsystems metamodel and some (advanced) SQL. It doesn't delete any data, from a safety point of view, but definitely make your life easier.

Deleting a user

In our case we want to delete a duplicate user, which was created due to an error in our login flow. Unfortunately, this user has created some data, and now we can't delete the duplicate user! Let's start investigating and cleaning up our tables so we can delete the user. We will use the UserChecker demo, which uses Record Delete Guardian to search through the tables. Both can be downloaded from the Forge

After you've installed Record Delete Guardian and the demo app, you can open up User Checker. Navigate to the User references screen and scroll down to the duplicate user. In my case that's this user.

Analysing impact

Select the user and click Validate occurrences. Record Delete Guardian start doing his magic and will pull up the tables that have references to this specific record.

Of all the entities that reference User, we now have much more clearer view of the impact. Luckily in our case the user didn't create a lot of data, and we only have to migrate the data of one table. We have a couple options to update the foreign keys:

Solution A: Manually run a migration SQL script Requires SQL knowledge and direct access to a database. Would not advise this route, for scalability and safety reasons.
Solution B: Create a migration server action Using aggregates and entity actions. This method adds some guard rails like type checking, and can be a quick solution to migrate your data.
Solution C: Automated dynamic SQL scripting Since Record Delete Guardian exposes metadata about the entity and attribute referenced, you could automate the whole thing and generate dynamic SQL scripts. However, that could quickly become complex and is harder to troubleshoot.

In most cases, creating an timer with solution B is the sensible solution. And since we need to migrate only one table, we will go with that solution.

Migrating referenced records

Start by creating a timer with the action MigrateUser. Looking at the results returned by Record Delete Guardian, we will need to query the UserTest entity, on the attribute UserId. First, we need to create two site properties to hold both the User Id we will need to migrate, and the one we will migrate it to. Name them SourceUserId and TargetUserId. After that drag in an aggregate for UserTest entity, and add the following filter:

UserTest.UserId = SourceUserId

Drag a For Each node after your aggregate and set the Record List property to GetUserTestsByUserId.List. In your loop, add an Assign node with the following assignment: GetUserTestsByUserId.List.Current.UserTest.UserId = Site.TargetUserId . After that, add an Update entity action (or any other CRUD wrapper) to update the UserId of the current record. Set the Source record to GetUserTestsByUserId.List.Current. That's it! Your action will look like this:

Publish your module and open Service Center to change the site properties you just created. The module can quickly be opened in Service Center using the little Settings icon. Open the tab Site properties.

Enter the desired User Id's in the site properties SourceUserId and TargetUserId. Now you're ready to run your timer, and all the referenced records will be migrated. Open the tab Timers, select your timer and click Run Now.

If you recheck the using in UserChecker again, you will notices that the referenced entities will be removed from the list. Great success!

Obviously, data migrations can be much more painful than this single entity migration. However, the process is roughly the same. Record Delete Guardian exposes both the EntityId and EntityAttributeId, so you're free to automate it as you see fit. Good luck!