Chapter 12 · Views, Triggers, ATTACH, Multiple Databases, and Schema-Level Automation
URI Filenames, Read-Only/Immutable Opens, Shared Cache Caveats, and Connection Options
Open SQLite files through documented URI filename options, build read-only inspection workflows, understand immutable-mode trust assumptions and VFS selection, and avoid obsolete shared-cache patterns.
Learning outcomes
An SQLite filename can be more than a path. With URI filename processing enabled, SQLite recognizes connection parameters that change how a file is opened. These options are powerful because they express safety policy at open time—but dangerous when copied without understanding their assumptions.
Explain URI filename syntax and the enablement/driver caveat.
Differentiate mode=ro, mode=rw, mode=rwc, and mode=memory.
Use read-only opening as a deployment/inspection safety pattern.
Explain why immutable=1 asserts that the file cannot change and disables locking/change detection.
Use cache=private/shared and vfs=NAME only with current documented semantics.
Explain why SQLite explicitly discourages shared-cache mode and why it is not the same as the OS file cache.
URI filenames add parameters to the database name
-- Conceptual filenames passed to SQLite open APIs / URI-aware drivers:file:fieldnotes.db?mode=rofile:fieldnotes.db?mode=rwfile:fieldnotes.db?mode=rwcfile:fieldnotes.db?mode=ro&cache=privatefile:fieldnotes.db?mode=ro&immutable=1file:fieldnotes.db?vfs=NAMEURI filenames have existed since SQLite 3.7.7. Core URI processing can be enabled by compile-time/start-time/open flags, and high-level drivers vary in how they expose it. Therefore, application examples must match the driver rather than assuming that putting ?mode=ro in any language string automatically activates URI semantics.
mode= expresses creation/write policy
| Parameter | Meaning |
|---|---|
| mode=ro | Open an existing database read-only. |
| mode=rw | Open an existing database read-write; fail if it does not exist. |
| mode=rwc | Open read-write and create if needed. |
| mode=memory | Use a pure in-memory database for that URI name. |
For an inspection tool, mode=ro is stronger than “I promise not to execute UPDATE.” The connection itself is opened without write capability.
CLI read-only inspection workflow
sqlite3 -readonly fieldnotes.db-- Inside the shell:.databases.schemaPRAGMA query_only;SELECT COUNT(*) FROM device;PRAGMA integrity_check;The CLI's -readonly option is often clearer than requiring shell users to understand URI processing. In application code, prefer the driver's explicit read-only/open flags when available; use URI parameters when they are the documented interface.
PRAGMA query_only can prevent changes through a connection, but it is a runtime connection setting. An actual read-only open is a stronger file-open policy. Use the mechanism appropriate to your threat/error model.
immutable=1 is a trust assertion, not “extra read-only”
immutable=1 tells SQLite that the database file is on read-only media and cannot be modified by any process. SQLite opens it read-only and skips file locking and change detection. If the file changes anyway, current documentation warns that SQLite may return incorrect results or SQLITE_CORRUPT errors.
| Use immutable when… | Do not use immutable when… |
|---|---|
| The artifact is cryptographically/versioned deployment content that truly never changes while open. | Another process might update, replace, checkpoint, or migrate the database. |
| The file lives on genuinely immutable/read-only media. | You merely want “my code should not write.” Use read-only mode instead. |
Never use immutable=1 to bypass normal coordination on a live application database.
cache=private versus cache=shared
The URI cache parameter chooses private or shared page/schema cache behavior for eligible connections. This is SQLite's shared-cache mode, not the operating system page cache and not an application result cache.
Current SQLite documentation is unusually direct: shared-cache is an obsolete feature, its use is discouraged, and most former use cases are better served by WAL. Some builds are encouraged to omit shared-cache support entirely.
Keep private caches unless you have a specialized, measured reason and fully understand shared-cache locking semantics. Do not enable cache=shared as a generic concurrency or memory optimization.
vfs= chooses a registered Virtual File System implementation
The VFS is SQLite's portability layer for opening, locking, syncing, randomness, time, memory mapping, and related OS-facing services. A URI vfs=NAME asks SQLite to use a registered VFS for that connection/file. If the named VFS is unavailable, opening fails.
file:fieldnotes.db?vfs=unix-dotfileDo not copy a VFS name from another OS as a universal recommendation. Most applications should use the platform/default VFS unless they have a documented deployment requirement.
Do not “solve” broken filesystems with nolock
SQLite URI filenames also document low-level parameters such as nolock=1. Disabling locking while multiple connections can access the same writable file can cause corruption. This chapter does not recommend it. If your storage cannot satisfy SQLite's locking assumptions, revisit the deployment architecture rather than suppressing the mechanism that protects the file.
Python example: explicit URI opt-in
import sqlite3con = sqlite3.connect( "file:fieldnotes.db?mode=ro&cache=private", uri=True,)try: print(con.execute("PRAGMA database_list").fetchall()) print(con.execute("SELECT COUNT(*) FROM device").fetchone()) try: con.execute("CREATE TABLE should_fail(x)") except sqlite3.OperationalError as exc: print("expected read-only failure:", exc)finally: con.close()The important part is the driver contract: Python's standard sqlite3 module requires uri=True to opt into URI interpretation for this style of filename.
Lab: safe read-only inspection
# Pythonimport sqlite3, ospath = "chapter12_readonly.db"if os.path.exists(path): os.remove(path)w = sqlite3.connect(path)w.execute("CREATE TABLE probe(id INTEGER PRIMARY KEY, label TEXT NOT NULL)")w.execute("INSERT INTO probe(label) VALUES ('baseline')")w.commit(); w.close()r = sqlite3.connect(f"file:{path}?mode=ro&cache=private", uri=True)print(r.execute("SELECT * FROM probe").fetchall())try: r.execute("INSERT INTO probe(label) VALUES ('must fail')")except sqlite3.OperationalError as exc: print(type(exc).__name__, str(exc))r.close()Expected behavior: the SELECT succeeds and the INSERT fails with a read-only database error. This proves the safety property rather than assuming it from a connection string.
Verification checkpoint
Connection-options checkpoint
Choose connection options from guarantees, not folklore.
- What is required before URI query parameters are interpreted?
- How do mode=rw and mode=rwc differ?
- Why is mode=ro useful for inspectors?
- What dangerous assumption does immutable=1 make?
- Why is cache=shared not a default optimization recommendation?
- What does vfs=NAME select?
- Why is nolock=1 dangerous on a shared writable database?
Review the answers
URI processing must be enabled by the build/start/open API or driver. rw requires an existing writable file; rwc may create it. ro makes write capability unavailable. immutable asserts the file cannot change at all, so SQLite skips locking/change detection. Shared cache is explicitly discouraged/obsolete for general use. vfs selects a registered Virtual File System. nolock disables protection that writable concurrent access needs and can permit corruption.
Production judgment and bridge
Connection options answer “how may I open this file?” Lesson 5 answers the next question: “how do I know this file is the kind of FieldNotes database my application expects, and which schema generation is it?”