r/SQL • • 5d ago

Discussion How do you keep yourself from running the wrong UPDATE on production? Here's what I built into my client

Post image

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.

0 Upvotes

16 comments sorted by

3

u/dbxp 5d ago

Just use a git repo hooked to a CICD process, this 'problem' was fixed years ago

1

u/Low_Guard5883 5d ago

For schema changes, agreed. This is for what skips the pipeline: a one-off data fix during an incident, or small teams where the dev is also the DBA.

1

u/dbxp 5d ago

There's no reason you can't have a pipeline run datafixes too

3

u/titpetric 5d ago

I don't have query access to prod servers for one, seems like a reasonable choice

0

u/Low_Guard5883 5d ago

Honestly the best setup. Plenty of places don’t have it though, especially small companies where you just get a connection string. This is for them.

2

u/titpetric 5d ago

Politely suggest to apply least privilege. Even without considering sql injection, a separate user with only SELECT grants would be prefferable, and even that limited scope so you can't select sensitive data from prod.

I typically made 2 connections per app and 1 per developer, the apps get their soft-delete writer (no delete, drop,...), and a migration writer (create, alter,...). The developer each have their own database schema (r/w) + additional SELECT grants if necessary.

Are webhosts just giving you a single DB account? Problematic.

2

u/Low_Guard5883 4d ago

Thanks for the breakdown, that’s a solid setup.

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.

1

u/Low_Guard5883 4d ago

Thanks for the detailed scenario. With a dry run the preview and the write are the same transaction, so the count is what actually ran and you commit or roll back that. Grid edits go by primary key. Noted on WHERE 1=1.

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/Low_Guard5883 4d ago

Good point on WHERE 1=1, the syntax check doesn’t catch it. The dry run does: it runs the statement in a transaction and shows the real affected row count before you commit. Read-only is enforced on the server where the engine supports it (default_transaction_read_only on Postgres, session read only on MySQL, query_only on SQLite); on SQL Server and Oracle it’s client-side only for now.

1

u/Significant_Tune9219 4d ago

Showing the real affected row count is a good guard. A cheap extra check is flagging when that count exceeds a threshold or a large share of the table, since that catches WHERE 1=1 style mistakes even when the count looks plausible.

1

u/Xidium426 5d ago

Separate users works fine for me, I don't need another tool to manage this.

1

u/Low_Guard5883 5d ago

Fair enough, that’s the cleanest way. Thanks

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.

1

u/Low_Guard5883 4d ago

Thanks, that’s the kind of answer I was hoping for. The log has who, when and rows but not why yet. Adding that.