Catch UPDATE/DELETE without WHERE before it runs

Everyone who has worked with SQL long enough has a version of this story. You write a careful two-line statement, highlight what you think is all of it, press Run, and the status bar reports a row count with far too many digits.

-- what you wrote
DELETE FROM orders
WHERE order_id = 1042;

-- what you actually selected and ran
DELETE FROM orders

The fix people usually reach for is "be more careful", which works right up until the one time you are tired, in a hurry, or looking at the wrong tab. This post walks through what SQLly does instead: several small, independent guardrails that each catch a different version of the mistake, set per connection so ordinary reads stay pleasantly boring.

Three layers, each with one job

SQLly splits its safety and guardrails into layers that apply in order, and the split is deliberate:

1. The editor warns. While you type, validation underlines the shapes that tend to go wrong, such as an UPDATE or DELETE with no WHERE, before anything has run.
2. The run gate asks. When you press Run, SQLly analyses the batch and decides whether it needs a confirmation, an AI review, or a refusal. Nothing has been sent to the server yet.
3. The driver enforces. Read-only and rollback-wrap are applied per GO batch inside the driver, on the way out, no matter which part of the app started the run.

The two layers that could in principle be fooled by clever SQL only ever warn or ask. The layer that decides what actually reaches the server is the simple, boring one. That is the right way round for a safety system.

Catching the missing WHERE

Before a batch runs, SQLly checks it for UPDATE and DELETE statements without a WHERE clause. It masks strings and comments first, so the word DELETE inside a string literal or a comment does not raise a false alarm. Connection policy decides what happens next: warn about the statement, or block it outright.

One setting to know about: the pre-run stop for unbounded statements is opt-in per connection. New connections start with Allow unbounded mutations ticked, so nothing gets in the way of someone who has not asked for it. Untick it on the connections where a broad UPDATE would ruin your afternoon, and the confirmation comes back for every UPDATE or DELETE with no WHERE.

A confirmation that says what it is about to do

A generic "Are you sure?" dialog trains people to click Yes. SQLly's confirmation reads the batch and reports what it found in unambiguous capitals, such as WE WILL MAKE UPDATE, DELETE, AND TRUNCATE CHANGES. It recognizes UPDATE, INSERT, DELETE, TRUNCATE, and MERGE, and calls out any statement that affects every row separately, because that is the shape of the mistake people actually make.

A confirmation is required when any of these is true for the target connection:

• You turned on Confirm data changes before executing for the connection, or something it inherits from.
• The batch is an unbounded UPDATE or DELETE and the connection has not opted into unbounded mutations.
• The connection is read-only and something non-read reached the gate, so the refusal is surfaced rather than swallowed.
• AI review is required for the connection.
• Production writes are temporarily unlocked (more on that below).

The gate sits in front of the run itself, not in front of the Run button. The same dialog appears whether the statement came from the editor, a notebook cell, a Run on… fan-out across several connections (it names every one), or an AI or MCP-proposed action. An auto-refreshing result is treated differently: it will not silently re-run a data-changing statement in the background at all, because nobody is watching for a dialog.

Read-only connections that mean it

For a replica, an analyst-facing environment, or anything that should never be at the mercy of a stray script, make the connection read-only. SQLly then refuses anything that is not a read before it reaches the server, per GO batch, so a refusal never becomes a round trip.

• A statement must begin with SELECT or WITH, and a WITH batch must actually contain a SELECT, so a CTE feeding an INSERT is not mistaken for a query.
• A mutating keyword anywhere in the statement blocks it, not just at the front.
• Read-shaped statements that reach the server's file system or shell are refused too: xp_cmdshell and OPENROWSET on SQL Server, pg_read_file and COPY on PostgreSQL, LOAD_FILE on MySQL and MariaDB, load_extension on SQLite, and more.

Refusals name the statement and the reason, and the editor highlights the exact offending statement. One honest caveat, straight from the docs: this is a lexical gate, not a permission. SQLly reads your SQL; it does not change what your login is allowed to do. For a guarantee, pair it with a database-level read-only role. The gate catches the mistake early, and the server catches anything the gate cannot see, such as a write inside a stored procedure you only SELECT from.

Rehearse the change, then throw it away

Sometimes you do need to run the UPDATE, but you want to see what it does first. With auto-wrap in a transaction on, anything beyond a plain read runs inside an outer transaction that SQLly rolls back. The statement really executes, so you see the row counts, the constraint violation, the trigger firing, and then the database is left exactly as it was.

Where a wrap cannot keep its promise, SQLly refuses rather than fakes it. MySQL implicitly commits DDL such as CREATE, ALTER and DROP, so inside the wrap those changes would quietly persist; SQLly refuses the batch and names the verb. The docs are also clear about the limits: consumed identity and sequence values are not returned, and work done through a linked server is outside the transaction. The wrap is an excellent rehearsal; it is not a time machine.

Production is its own layer

Give a connection an environment and the environment brings safety with it. A Production connection is read-only with rollback-wrap on by default, whatever its own flags say, and a Staging connection forces the rollback wrap. These defaults only ever turn safety on.

When you genuinely need to write to production, unlock writes for 1, 5, or 15 minutes from the status-bar chip or the command palette. The chip counts down while the window is open, every data-changing batch still has to be confirmed with the exact SQL in front of you, and when time runs out the connection locks itself again. The unlock is held in memory only: closing SQLly re-locks production, always.

Let your peripheral vision help

Environment colors and cues give clients, projects, environments and servers their own colors and icons, which show up in explorer group headers, server rows, and a status-bar badge. Color production an angry red. The docs are candid that a cue is a reminder, not a guard: it does nothing when you are not looking, which is why it pairs with the policies above rather than replacing them.

Coming along: an optional AI review that explains a batch's side effects before it runs in a protected place is in progress. The gate, the deterministic findings and the dialog are in place; connecting the explanation to each model backend is the remaining work. Identity gates that ask for an OS-level identity check before risky actions are also in progress.

A sensible setup in five minutes

1. Mark your production connections as Production. You get read-only, rollback-wrap and the timed unlock for free.
2. Mark staging as Staging, so writes there are rehearsals by default.
3. Untick Allow unbounded mutations anywhere a missing WHERE would hurt.
4. Make replicas and reporting connections read-only, and back that with a read-only database role.
5. Pick colors. It takes a minute and it is the cheapest layer you will ever add.

Because policies are set on a client, project, environment, server or database and flow down the connection tree, you usually do this once, not once per connection. The full reference lives under Safety & guardrails.