SQLly logoSQLly

Read-only connections

✓ functional

Make a connection genuinely read-only when browsing is all it should ever do.

Make a connection truly read-only: SQLly refuses anything that is not a read before it reaches the server. It is a good fit for a replica, an analyst-facing environment, or any server that should never be at the mercy of a stray script.

Where the gate sits

The check runs in SQLly, on the way out, and it runs per batch - each GO-separated chunk is classified on its own. A refusal never becomes a round trip: the statement is not sent, so there is nothing for the server to log, roll back, or partially apply.

What counts as a read

  • A statement must begin with SELECT or WITH. A WITH batch also has to contain a SELECT somewhere, so a CTE feeding an INSERT is not mistaken for a query.
  • USE <database> is allowed. Switching session context changes nothing in the data, and any real mutation after it is still caught.
  • A mutating keyword anywhere in the statement blocks it, not just at the front - which is what stops SELECT … INTO-style and composed statements from slipping through.

Refusals say which statement and why - “statement starts with UPDATE, not SELECT”, “statement contains mutating keyword MERGE” - and the editor highlights the exact offending statement rather than the whole tab.

Read-shaped, but not harmless

Some statements return rows and still reach the server's file system, shell, or module loader. A read-only connection exists so it is safe to point at production, so those are refused too even though they technically start with SELECT:

EngineRefused artifacts
SQL Serverxp_cmdshell, sp_configure, sp_OACreate, sp_OAMethod, OPENROWSET, OPENDATASOURCE
PostgreSQLpg_read_file, pg_read_binary_file, pg_ls_dir, lo_import, lo_export, and COPY (file and PROGRAM I/O), DO
MySQL / MariaDBLOAD_FILE, OUTFILE, DUMPFILE, LOAD DATA/LOAD XML, INSTALL/UNINSTALL, CALL
SQLiteload_extension, readfile, writefile, ATTACH/DETACH

Matching is tokenizer-driven, so a name inside a string literal or an ordinary comment is inert, while quoted and bracketed spellings still match - [xp_cmdshell] and "OPENROWSET" are caught. MySQL and MariaDB executable comments (/*! … */, /*M! … */) are deliberately exposed to the scan first: they run on the server, so they cannot be allowed to smuggle a verb past it.

It 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 rather than a guardrail, pair it with a database-level read-only role - the gate then 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.

Set it per connection, or inherit it from a Production environment, which turns it on by default. Related: auto-wrap in a transaction for connections that do need to write.