Safe mode and review in SQLite

Use SQLite read-only and safe mode, review BEGIN IMMEDIATE and catch changed rows with a content token.

SQLite over SSH has been requested in other tools since 2023, with 45 votes.

SQLite connections expose read-only and safe mode. The safe-mode choices are dangerous (default), confirm writes and confirm all, with the same meanings used by the shared connection metadata.

Protect the connection

  1. Choose read-only when the file must not be changed through this connection.
  2. Use confirm writes when every INSERT, UPDATE, DELETE and DDL operation should stop for confirmation.
  3. Use confirm all when SELECT should also require confirmation.

If you change journal mode, the warning is stronger than a session preference: it changes the FILE, is stored in it and applies to every program that opens it. WAL creates -wal and -shm files next to it and does not work on a network folder. In a read-only connection, the app reports the choice as ignored because the connection never changes the file.

Review the write

A staged update is rendered as BEGIN IMMEDIATE, a re-read of the target row, the UPDATE or DELETE, and COMMIT. The re-read selects the same row that the operation will change. Tablyne compares its raw values with the token captured when the row was read.

The comparison is not hidden in a guessed WHERE expression. It happens in the application after the re-read. If the row changed, the batch is rolled back and no later operation in that batch survives. The rendered SQL includes the re-read so the review shows the database interaction honestly.

Use Ctrl+S to save pending changes, Ctrl+Z to undo and Ctrl+Y to redo. If the file is shared, resolve a conflict by reloading the current row and staging the intended value again.