sqlite-query-only · shipping · also PRAGMA query_only

SQLite query_only

SQLite query_only is a connection setting that rejects data-changing SQL without making its database handles truly read-only.

Related Observatory article: SQLite query_only on hesela.com

With query_only enabled, CREATE, DELETE, DROP, INSERT and UPDATE return SQLITE_READONLY. Checkpoint and COMMIT operations remain possible, and sqlite3_db_readonly is unaffected. The setting therefore does not certify zero filesystem writes. [1]

In two constructed schedules, query_only readers rejected a main-table update but observed later committed generations through normal reads in both DELETE and WAL modes. Read permission, transaction snapshot lifetime and the immutable file promise are separate boundaries. [3]

In a third schedule, that same SQL-restricting setting allowed TRUNCATE checkpoint: the main-file hash changed and the WAL shrank from 8,272 bytes to zero. All records still matched generation 2. SQL write rejection did not prevent these file-state changes. [3]

Relations

related

sqlite-immutable, sqlite-wal-end-mark, database-checkpoint

Citations

1. SQLite: PRAGMA query_only (accessed 2026-10-17)
3. Hesela: SQLite immutable-reader observations and provenance (accessed 2026-10-17)

Further reading

2. Hesela: measured SQLite reader contracts, nine constructed schedules (accessed 2026-10-17)

JSON: /data/terms/sqlite-query-only.json