Guardrails

I like you. I like your data. Let's take a look before we do this.

Before a write runs

When you run a write from the query editor, Lily looks at it first.

1. Read-only connections refuse it (see below).
2. Parameters are filled in.
3. Impact. For a single INSERT, UPDATE, DELETE, TRUNCATE or DROP, Lily estimates what it will touch, without running it, and shows it in a confirmation sheet with up to 20 of the affected rows.

What the estimate shows

Statement	How Lily counts
UPDATE / DELETE … WHERE	SELECT count(*) with your WHERE (exact)
UPDATE / DELETE without WHERE, TRUNCATE, DROP	The table's size (approximate, marked ~)
INSERT … VALUES	The rows in the statement
INSERT … SELECT	count(*) of the SELECT (approximate)

It also lists the tables whose foreign keys point at the one you are changing. If the estimate fails, it says so and shows the table size instead of a zero; a failed estimate never stops you.

Lily does not estimate statements with joins (UPDATE … FROM, DELETE … USING, multi-table deletes) or scripts with several statements; those get the warnings below without a number.

Warnings and typed confirmation

The sheet lists what is risky:

• UPDATE or DELETE with no WHERE (affects every row);
• TRUNCATE and DROP;
• any write on a Production connection, including CREATE, ALTER and GRANT;
• on Production, SQL Lily cannot read reliably (MySQL /*! */ comments, # comments, unterminated strings), flagged for manual review.

For TRUNCATE, DROP, and UPDATE or DELETE that hit every row of the table, you have to type the table name before Run unlocks. Otherwise Run anyway goes ahead.

Production connections

Tag a connection Production in its ground rules and:

• every write from the query editor asks first, even CREATE INDEX;
• dashboards wait for you before running and stop each tile after 30 seconds;
• the connection form suggests making it read-only.

Other tools that change data (Apply & prove in Investigate and Speed up, moving and migrating data, notebook cells, killing a session) do not add a production prompt of their own. For production, a read-only connection is the safer default.

Read-only connections

Read-only in the connection's ground rules makes Lily refuse writes everywhere it runs SQL: the editor, notebooks, SQL files, grid edits, schema changes, imports, fake data, killing sessions, and the write actions in the Redis, MongoDB and DynamoDB screens. Read-only connections are also left out of the targets for moving and migrating data.

Lily allows statements that start with SELECT, WITH, SHOW, DESCRIBE, EXPLAIN, VALUES, TABLE, PRAGMA, USE or SET, and refuses EXPLAIN ANALYZE, SELECT … INTO and WITH blocks that contain a write.

A check in Lily, not in the database

Read-only is enforced by Lily before each statement runs, not by a read-only database session. A SELECT that calls a function with side effects (nextval, setval, pg_terminate_backend, your own functions), SELECT … FOR UPDATE, and SET statements still run. For a hard guarantee, connect with a database user that only has read permissions.

Grid edits

Staged edits in the grid save in one transaction. Deleting rows always shows them first; deleting every loaded row asks for the table name. With Preview SQL before committing edits on, you see the statements before they run.

Schema changes

Every schema change shows its SQL before it runs. Truncate and every drop ask you to type the name.

Transactions

Run in Transaction (⌥ ⌘ ↵) rolls everything back if a statement fails on PostgreSQL, MySQL and SQLite. ClickHouse has no transactions, so its statements run one by one and are not rolled back. On Trino it depends on the connector.

After a dropped connection

When Lily reconnects after sleep or a network drop, it only repeats a statement that is certainly a read. A write whose outcome is unknown is not sent again; Lily tells you to check its result first.