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
- Choose read-only when the file must not be changed through this connection.
- Use confirm writes when every INSERT, UPDATE, DELETE and DDL operation should stop for confirmation.
- 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.