How to fix SQLITE_READONLY_CANTINIT: Unable to initialize WAL file in SQLite

Check the permissions of your database file and its associated WAL files:

# List all database-related files with permissions
ls -la /path/to/database.db*

# Expected output should show:
# database.db
# database.db-wal
# database.db-shm

Ensure your process user has read and write permissions on all three files. The typical permission should be 644 (rw-r--r--) or 664 (rw-rw-r--) depending on your security requirements.

WAL mode requires write access to the directory containing the database to create temporary files:

# Check directory permissions
ls -ld /path/to/database/

# Grant write permission to directory (adjust as needed)
chmod 755 /path/to/database/

# Or for group write access
chmod 775 /path/to/database/

The directory needs execute (x) permission for access and write (w) permission for creating WAL files.

If the database files are owned by a different user, change ownership:

# Change ownership of database files
sudo chown appuser:appgroup /path/to/database.db*

# Verify ownership
ls -l /path/to/database.db*

Replace appuser and appgroup with the user and group your application runs as. This is particularly important in containerized environments where the process may run as a non-root user.

If you need true read-only access without write permissions, switch the database to DELETE journal mode before deploying:

-- Connect with write access and change journal mode
PRAGMA journal_mode=DELETE;

Or using the SQLite command-line tool:

sqlite3 /path/to/database.db "PRAGMA journal_mode=DELETE;"

DELETE mode doesn't require shared memory files, making it suitable for read-only deployments. Note this must be done before the database is placed in a read-only environment.

For SQLite 3.22.0 and later, open the database with the immutable flag when it's truly read-only:

# Python example with sqlite3
import sqlite3

# Open as immutable read-only database
conn = sqlite3.connect('file:/path/to/database.db?immutable=1', uri=True)

// Node.js with better-sqlite3
const Database = require('better-sqlite3');
const db = new Database('/path/to/database.db?immutable=1', {
  readonly: true,
  fileMustExist: true
});

The immutable parameter tells SQLite the database will never change, allowing it to skip WAL initialization.

Before deploying to a read-only environment, checkpoint the WAL and persist the files:

-- Checkpoint WAL to merge changes back to main database
PRAGMA wal_checkpoint(TRUNCATE);

-- Keep WAL files after closing for read-only access
PRAGMA persist_wal=1;

Or using the C API:

// Before closing the database connection
sqlite3_file_control(db, NULL, SQLITE_FCNTL_PERSIST_WAL, NULL);
sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_TRUNCATE, NULL, NULL);

This leaves the database in a state that allows read-only access without requiring write permissions.