Ability to add SQL migration script to an entity on publish


We have had a few situations where changes to an attribute in an entity have resulted in a brand new field being created instead of the old field being updated. Seems to happen the most when a field is renamed. This has resulted in the old field which has the data being set to inactive and a new field created that has no data. End result is when this gets published through to production the customers complain that their data has been lost and we then need to do an SQL cleanup script to transfer the data from the old to the new field.

It would be ideal if the system could automatically do a bulk update in these situations.

What would also be good is if we could attach an Advanced SQL query to an entity change that has access to both active and inactive attributes. This would give us the ability to do more advanced transformations on fields during a publish. I know we can do an on publish timer event but then you end up with a lot of old events or have to go through and delete them after deploying, also accessing the inactive attributes is a bit harder that way.

Created on 18 Dec 2018
Comments (6)

Hello John,

As an FYI: in fact renaming an attribute will always result in a new one being created. 

Still, I agree with you. We need a way to run an SQL script that can still "look" at the old attributes to be able to perform some kind of migration. Specially in Cloud where people don't have easy access to production databases

Yes it would be very nice to be able to see the old value, for now rather than renaming an attribute: 

  1. you create a new attribute and change reference in application from old to new attribute
  2. deploy with conversion of values on on publish from old attribute to new attribute
  3. remove conversion code and old attribute
  4. deploy again

Being able to look at the old attribute value would limit the process to one deployment.

But also why limit it to an SQL script?

I would just like to be able to call a server action to do any kind of logic to get the new attribute  value populated.

Indeed Daniel. Logic would be much better :)

Changed the category to 1CP

Yes a server action for "Migration" or "upgrade" would be good although the reason I suggested SQL script is because the Outsystems deploy process already generates and runs SQL change script as part of the deployment process and being able to add to (or override) this script would be useful and probable relatively easy to implement and would make use of the transaction rollback on deployment. 

On the other hand running an action would need to be done after the espace had actually been deployed to the server and other SQL scripts had already been run giving a possible problem should the action fail. Also if it was just an action then we can already do that by using an on publish timer and putting in logic to stop it being run multiple times, but again these don't roll back a deployment if they fail.

Aha! A DbUp implementation. Nice. This makes a serious implementation in Database.