I believe the issue is only presented on major updates. EE2 changed the database structure of EE, including changing database table names, adding new tables and then repopulating them with data.
Generally, updates may add or delete fields (data columns), but don't do much to existing fields. We had modified a few fields size to MEDIUMTEXT and one or two to LONGTEXT for different clients and had the same issue with the EE2 upgrade, but no prior issues before that.
I think generally speaking, almost anything that is an update may contain minor database changes, but won't revert basic fields. Upgrades though can be a different beast. Ellis Lab was pretty good throughout all of 1.x to 1.7.3. I recall making the database modifications in the early days of EE1 and not a single problem until the upgrade to EE2.
Hindsight being 20/20, I of course realized that the database structure would be changed in EE2, I had just forgotten that I had modified the database myself before. But that's why backups are very important.
Truth is, in a major update such as EE2, I wouldn't have wanted to adjust all the INSERTS and ALTERS at the code level anyway, for fear that I would overlook something. So our process for clients was to backup, restore current database to our development server, then upgrade. Our original plans included running Navicat to compare the databases, but with the names of tables changing etc. that didn't really work. We ended up printing out table structure from old and new then modifying the new. This then required an INSERT of the fields data again, but worked.
To the best of my knowledge, I've never heard of any add-ons that would help for this situation. Any solution would need to be able to scan the update/upgrade files for specified database changes, but since EE is "shutdown" during the upgrade, I'm not certain if an add-on would work.
The Release Notes/Change Log for updates contains quite a bit of information that can be helpful in planning for the upgrade as well.
I think for most updates, it's not a worry. For major updates, I'd be concerned. But a quick look at the update file, from the
system/installer/updates/ folder would tell you if the database is being altered.