Ask HN: Best approach for manual data fixes in db?

2 points by mitchpatin 3 years ago | 3 comments

What processes or tools do teams use when making manual SQL data changes?

I'm particularly interested in data changes, not schema migrations. Most teams I've worked on just gave production write access to all engineers who were on call. Is this the norm?

For example, if a support request came in (either due to a bug or user error) and there wasn't an existing admin endpoint to make the necessary change, the on-call engineer would write a SQL UPDATE statement to run directly in the production db.

A few issues I see with this: (1) A bad query (accidental or malicious) could have serious consequences (data loss and/or service interruption). (2) There is not an easy way (afaik) to audit which changes were made in this manner or why they were made. This also seems like it could bring compliance issues.

How have you seen changes like this handled - what tools/processes are seen as best practice?

surprisetalk 3 years ago |

  begin;

  update example_table ...

  -- > 1 row changed

  commit;



  begin;

  update example_table ...

  -- > 1,000 rows changed

  rollback;

I know it's not exactly what you're asking, but you can use transactions to prevent "YOLO query" situations.

ttctciyf 3 years ago |

In your example, a ticketing system is a pretty usual audit trail, I think.

So the problem ticket is raised, a developer or on-call person picks it up, writes the SQL update to the ticket, runs it on a test version of the production db, pastes the result in the ticket request, then the ticket goes to a DB admin who executes it by cut/paste from the ticket (maybe after their own review!) again pasting the result into the ticket resolution.

moremetadata 3 years ago |

> Is this the norm?

I'd say no if you were to treat your data with the same requirements as accountancy systems, like double entry bookkeeping accountancy systems.

So accountancy systems and thus your app would let you make changes to data that forces the reason for change to be documented, but either way its documented using the app, so that when looking back, just like accountants and auditors know why a change has occurred when doing year end or quarterly reports, you can also do the same. It also helps you highlight devs not cutting the mustard and it also highlights any potential fraud occurring inside the business with staff gaming the system.

I'd never allow any direct change to the db, but then my apps would also be encrypting data making it near impossible to perform changes to a db, other than messing around with the unencrypted data in index fields, which forces hackers to acquire the app and reverse engineer it, and then they have to understand the obfuscation built into the apps besides the obfuscation built into the db, but this approach makes rebuilding corrupted db indexes easier should they ever occur.

I know some people prefer having all the logic in the backend, but its about balancing the logic between the backend and the front end, ie spreading risk to make hackers jobs harder.

> This also seems like it could bring compliance issues.

This approach (making and documenting changes from within the app(s)) would meet all compliance and ISO standards.