Hacker News Reader

HNHacker News

Top New Best Ask Show Jobs

Comment by jimrhods23 | Hacker News Reader

Full thread

jimrhods23·

DELETE * FROM <table>

Yeah, I forgot the WHERE.

This was 15 years ago at my first job and I haven't done it since. I was lucky that we had backups.

View on HN

I learned early on mutating queries: I type "WHERE" first, then go to the head of the line and type the query.

I wonder whether tooling that is commonly used to query a database should detect these common mistakes and warn you. `psql` could easily pop up a warning "Are you sure you wanna drop this entire table?".

An alternative would be for databases to add an option to prevent accidental deletion. When enabled, would make it impossible to truncate or delete entire tables. I would enable such a setting on my production databases.

We have code in place to reject any SQL query without a where clause.

I'm always surprised this isn't default. Imagine "rf" deleted everything if you didn't specify a file.

A front end that (perhaps only had a mode that) required delete or update queries to be preceded by select queries, and then styled as “DELETE IT;” or “UPDATE IT SET ...;” which use the FROM and (if present) WHERE clause from the preceding SELECT would be interesting.

This seems such a good idea that I doubt nobody ever tried to implement something like this.

At least for PostgreSQL, someone tried:

https://www.postgresql.org/message-id/12104.1150425319%40sss...

But it doesn't seem like it got much serious attention.

That seems to be someone asking for a back-end change to prevent such queries, not a front-end change.

I think front-end makes sense, where SQL is a UI, not back-end, where it is an API.

JetBrains DataGrip does put up a warning like this before you can do a DELETE on a table, unadorned by a WHERE clause.

DataGrip is my #1 DB tool for my team for reasons like this.

Reply on news.ycombinator.com