SQLite Read-Only Mode: Open Databases Safely (2026)

Opening an SQLite database in read-only mode can prevent accidental data corruption, but it might surprise you how many ways a read-only file can still be modified.

Let’s see it in action. Imagine you have a database file named my_database.db.

# Create a dummy database
sqlite3 my_database.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);"
sqlite3 my_database.db "INSERT INTO users (name) VALUES ('Alice');"
sqlite3 my_database.db "INSERT INTO users (name) VALUES ('Bob');"

# Attempt to open in read-only mode using the command-line shell
sqlite3 -readonly my_database.db

# Inside the SQLite shell, try to modify data
sqlite> UPDATE users SET name = 'Alicia' WHERE name = 'Alice';
Error: attempt to write a readonly database

# This is the expected behavior. The shell itself enforces read-only.
# But what about the underlying file permissions?
.quit

The -readonly flag for the sqlite3 CLI is a good start, but it only prevents direct modifications through that specific SQLite connection. It doesn’t protect against other processes or system-level actions.

Here’s the core idea: SQLite databases are just files. When you open a database, SQLite interacts with the operating system to read and write to this file. Read-only mode tells SQLite, "Don’t issue any write commands to the OS for this file." However, the OS itself might have its own ideas about file permissions, and other applications might not respect SQLite’s read-only flag.

The primary mechanism for protecting your database is a combination of SQLite’s internal flags and your operating system’s file permissions.

Common Pitfalls and How to Avoid Them

Operating System File Permissions: This is the most fundamental layer of protection. If the OS doesn’t allow writing to the file, nothing can write to it, regardless of SQLite’s internal flags.
- Diagnosis: Check the file permissions on your operating system.
```
ls -l my_database.db
```
  You’ll see output like -rw-r--r--. The first w indicates write permission for the owner. If it’s -r--r--r--, then no one can write.
- Fix: Remove write permissions for the user and group.
```
chmod a-w my_database.db
```
  This changes permissions to -r--r--r-- (read for owner, group, and others). SQLite will still be able to read, but the OS will block any write attempts from any process, including the one running SQLite.
- Why it works: This is the most robust protection. The OS is the ultimate gatekeeper. If the OS says "no writes," SQLite cannot override it.
The SQLITE_OPEN_READONLY Flag in Application Code: When you’re using SQLite programmatically (e.g., in Python, Node.js, C), you typically pass flags when opening the database connection. Forgetting SQLITE_OPEN_READONLY or accidentally passing SQLITE_OPEN_READWRITE is a common oversight.
- Diagnosis: Review your application’s code where the database connection is established. Look for the flags used with sqlite3_open_v2 (in C) or equivalent functions in other languages.
- Fix (Conceptual - Python example):
```
import sqlite3

# Incorrect (or potentially writeable if OS allows)
# conn = sqlite3.connect("my_database.db")

# Correct for read-only
conn = sqlite3.connect("file:my_database.db?mode=ro", uri=True)
```
  In Python’s sqlite3 module, using a uri=True connection string with mode=ro is the idiomatic way to enforce read-only at the SQLite connection level. For other languages, the specific flag might be SQLITE_OPEN_READONLY.
- Why it works: This tells the SQLite library itself to operate in read-only mode for that specific connection, preventing it from issuing write commands.
Wal Mode and Journal Files: SQLite uses write-ahead logging (WAL) and rollback journals for durability. Even in read-only mode, these files can be created or modified if not properly managed, especially if the OS file permissions are too permissive.
- Diagnosis: Look for .wal and .shm files (for WAL mode) or .db-journal files (for rollback mode) alongside your main database file. If these exist and are writable by the process, it’s a potential vulnerability.
- Fix: Ensure that the directory containing the database file has restrictive write permissions, and that the main database file itself is read-only (chmod a-w my_database.db). If WAL mode is enabled, you might need to disable it for absolute read-only safety if you can’t control the directory permissions, though this impacts performance.
```
# To disable WAL mode (run this *before* opening in read-only if possible)
sqlite3 my_database.db "PRAGMA journal_mode=DELETE;"
# Then ensure file permissions are restrictive
chmod a-w my_database.db
```
- Why it works: If the directory is not writable, SQLite cannot create or update these auxiliary files. Disabling WAL ensures no new write-ahead log is generated.
Shared Cache (Less Common for Read-Only Issues): While primarily a performance feature, shared cache can sometimes have subtle interactions. If a read-only database is opened in a shared cache configuration where another connection can write, there can be edge cases.
- Diagnosis: Check your SQLite connection string or API calls for any SQLITE_OPEN_SHAREDCACHE flags or similar configurations.
- Fix: Avoid SQLITE_OPEN_SHAREDCACHE when opening a database you intend to keep strictly read-only, especially if other connections might be writing. Ensure all connections accessing the database use SQLITE_OPEN_READONLY and SQLITE_OPEN_PRIVATECACHE (or no cache flag, which defaults to private).
- Why it works: Private cache ensures that the read-only connection’s view of the database is isolated and not influenced by potential writes from other connections that might be sharing a cache.
File System Snapshots or Backups: While not a modification by SQLite, if your system performs file system snapshots (like ZFS or Btrfs) or backups, the read-only nature of the database file is preserved. However, if the snapshotting process itself has write permissions on the directory or the database file before the snapshot, this could be a theoretical vector.
- Diagnosis: This is more of an infrastructure concern. Check your backup and snapshot policies.
- Fix: Ensure that the process performing snapshots or backups has read-only access to the database file itself and its containing directory at the time of the snapshot.
- Why it works: This prevents any accidental writes during the critical snapshot window.

The Unseen Danger: Temporary Files

Even when a database is opened in read-only mode, SQLite might still need to create temporary files for operations like sorting large result sets or performing complex joins. If the directory where these temporary files are created is writable by the process, these temp files can be modified, potentially leading to unexpected behavior or resource exhaustion.

Diagnosis: Monitor disk space and look for temporary files (often with names like sqlite_temp_...) in your system’s temporary directory (/tmp on Linux/macOS, %TEMP% on Windows) or the current working directory if not specified.
Fix: Ensure the directory designated for temporary files (PRAGMA temp_store_directory) is either read-only for the SQLite process or, if writable, that the OS permissions are tightly controlled. You can explicitly set a read-only directory for temporary files if necessary, though this is complex. More practically, ensure the main database file itself is read-only (chmod a-w my_database.db) and the directory containing it is not writable by the application process.
Why it works: By making the main database file truly read-only at the OS level, SQLite cannot perform operations that require modifying its internal structures, forcing it to use temporary files. If the temp directory is also protected, these operations will fail gracefully, rather than allowing indirect modification.

The next issue you’ll likely encounter is dealing with database schema changes when you only have read access to the data itself.

Common Pitfalls and How to Avoid Them

The Unseen Danger: Temporary Files

More Deep Dives in Sqlite