Interfacing with records on remote DB from localhost Application Logic

Question

There are times when I have to fix/update records on a Production and/or Staging environment for a Web App. These are remote DBs.

Rather than push application code to fix the data, I've been "interfacing" my localhost's version of the running app with the remote DBs. I can do this by simply changing an ENV var for the DB URI.

I'm doing this as opposed to shelling into DB and updating records because I need some actual code to massage data too.

Any insights on this pattern? Thanks!

Answer 1

The best practice of modifying database is through migrations. Although migrations usually imply schema changes, there is nothing wrong with data-only migrations.

For data migrations specifically, it may be a good idea to implement these as/via higher layer components (above the business layer), to ensure that all relevant validation and other logic could run normally. Ideally, you want to have exclusive access to the DB while performing data massaging, that is: lock relevant tables or put the system in the maintenance mode.

All that may seem an overkill for (a "simple") data fix-up, but allowing something quick-and-dirty to mess with production is really bad idea. Consider the possible issues:

• Persistence layer may not be limited to a DB, some procedures may require coordination (queues, semaphores, etc).
• Interacting with DB directly goes around the business-layer validations and other assumptions.
• Interference from other processes may render script's state obsolete mid-run.
  • Staging server lacks same interactions, so testing there is not going to help.
• If (when) anything goes wrong unnoticed for some time, you'd certainly want to remember what you did to the DB.
• An one-off interaction script probably won't be as good and well-tested as proper data migration script, simply due to a difference in a mindset.

Any decent ORM should have built-in migration framework.