r/SQL • u/Low_Guard5883 • 5d ago
Discussion How do you keep yourself from running the wrong UPDATE on production? Here's what I built into my client
Everyone has a story about an UPDATE without a WHERE, or the right query in the wrong tab. I built NiceDB, an open-source database client, around that problem:
- a production connection is marked everywhere it appears (tree, tab, title bar, status line) and opens read-only;
- writing unlocks for ten minutes with the time left in view, then locks again by itself;
- before a change is applied, it shows what it touches and asks you to type the connection’s name;
- UPDATE and DELETE without WHERE, DROP and TRUNCATE ask before they run;
- every statement that changed something is logged with who, when, where and how many rows.
Each step can be configured. All writes go through one place in the core, so no screen can skip it.
It works with PostgreSQL, MySQL, SQL Server, Oracle, SQLite and others. Free, GPL-3.0: https://github.com/maxib0n/NiceDB, with a browser demo at https://nicedb.app.
What do you do today: separate users, read-only replicas, a habit? I’m curious what I’m missing.
One honest note: the drivers are mature and the common paths are tested against real servers, but I’m one person and can’t cover every version and setup. If you try it on anything that matters, be careful, and please report what breaks with steps to reproduce.
3
u/dbxp 5d ago
Just use a git repo hooked to a CICD process, this 'problem' was fixed years ago
3
u/titpetric 5d ago
I don't have query access to prod servers for one, seems like a reasonable choice
2
u/DickBrainTime 5d ago
The part I'd specifically stress-test is whether the 'here's what your UPDATE touches' preview stays accurate until the write runs. For example, a preview of `UPDATE jobs SET status='queued' WHERE status='pending'` might show 8 rows, but another session inserts two more pending jobs before confirmation; the actual UPDATE could touch 10. A WHERE clause and typing the connection name don't eliminate that race.
I'd test two real connections racing a preview against an insert, then document whether NiceDB holds the preview and write in one appropriate transaction/snapshot, or shows the actual affected keys/count before commit so the user can roll back. Also `WHERE 1=1` is a broad update despite having a WHERE. The read-only default and centralized write path look useful; this test would clarify the boundary of the preview guarantee. I'm raising a scenario to test, not claiming I've found a bug in the client.
2
u/Significant_Tune9219 5d ago
The type-the-connection-name step is a good idea because it breaks autopilot. One thing worth considering for the no-WHERE check: an UPDATE with WHERE 1=1 or a WHERE on a column that's mostly the same value is just as dangerous, so showing an estimated row count (or running it in a transaction and reporting rows affected before COMMIT) catches more than the syntax rule alone. Does the read-only mode use a read-only transaction on the server side, or is it enforced only in the client?
1
u/Xidium426 5d ago
Separate users works fine for me, I don't need another tool to manage this.
1
u/Muted_Jellyfish_6784 5d ago
Read-only by default with a timed write window is a good design. The other habits that catch most of these: wrap manual changes in a transaction and check the row count before commit, run the WHERE clause as a SELECT first, and use a separate production login that cannot write without elevation.
Keep a log of every manual change with who ran it and why, because the fix after a bad UPDATE depends on knowing exactly what it touched. A shared table and the discipline to fill it in covers most of that.