Best Practice - Named Query Updates & Deletes

Ignition
1

Any best practices for named queries that are intended to UPDATE or DELETE a single record in a table?

For example I have a lot of named queries like:

DELETE FROM my_example_table
WHERE id = :id

I can't help but be a little concerned that this SQL statement could go from deleting a single record to deleting an entire table by the following accidents:

  • WHERE condition row of text is deleted or commented out
  • 'id' gets a colon placed in front of it
  • colon gets removed from the ':id'

I'm relatively new to databases and the permanence of running the wrong command spooks me a bit. The couple things I've done to prevent these potential accidents is to add a comment line beforehand, and prefix the WHERE condition's parameter name to have a little bit bigger different between the table's column name:

DELETE FROM my_example_table
---  !!!CAUTION WHERE CONDITION!!!
WHERE id = :param_id
2
  1. Add a limit clause
DELETE FROM my_example_table
WHERE id = :param_id
LIMIT 1
  1. Do not really delete the record, instead add an archived field, make it a key, then when users delete something, archive it instead. Then you can really delete it later via a script if the archived date is older than x weeks or similar.
4 Likes
3

Also, not all JDBC drivers will handle the comment properly (comments are SQL scripting) because they are not part of the JDBC standard.

4

I've been working with databases for 15+ years (probably longer really) and this is still true for me. IMO, this is a great mindset to have when approaching any UPDATE operation (delete or otherwise).

In addition to @dkhayes117 recommendation for achieving deleted rows, I would also recommend having a proper database archiving scheme. There will come a day when you or another DBA does something you which you hadn't and having a good disaster recovery plan in place will make you glad you went through the effort.

Unfortunately, even though I understand the sentiment. Adding a comment makes this a SQL script, which support for is not enforced in the JDBC specification. And while it may work with your database now, there is nothing in place to insure that it continues to work in the future, or if you move to another DB platform. You might be pulling your hair out and this have been the issue all along.

3 Likes