How to fix SQLITE_READONLY: Attempt to write a readonly database in SQLite

SQLiteBEGINNERHIGH

This error occurs when your application tries to write to a SQLite database that is opened in read-only mode or lacks write permissions. Common causes include incorrect file permissions on the database or its parent directory, stale journal files, multiple processes writing simultaneously, or the database being opened explicitly as read-only.

What this error means

The SQLITE_READONLY error with "Attempt to write a readonly database" indicates that SQLite has prevented a write operation because the database is not writable by the current process. This is a protection mechanism to prevent data corruption. SQLite databases can become read-only for several reasons: the database file itself lacks write permissions for the user running the application, the parent directory lacks write permissions (SQLite needs to create temporary journal files in the same directory), the file is marked as read-only by the operating system, the database was explicitly opened with the readonly flag, or another process holds an exclusive lock on the database. Unlike some errors that indicate corrupted data or internal bugs, SQLITE_READONLY is usually a configuration or permission issue that has a straightforward solution.

How to fix "SQLITE_READONLY: Attempt to write a readonly database"

1Check file permissions on the database file

Verify that the user running your application has write permissions to the database file itself:

On Linux/macOS:

bash

# Check permissions on the database file
ls -l /path/to/your/database.db

# Example output: -rw-r--r-- user group
# You need at least rw- (600) for your user

# Add write permission if missing
chmod u+w /path/to/your/database.db

# Or set broader permissions if needed
chmod 664 /path/to/your/database.db  # user and group can read/write

On Windows:

powershell

# Right-click the database file > Properties > Security
# Ensure your user has "Modify" permission
# If not, click Edit > select your user > check "Modify" > Apply

The database file must be writable by the user account that runs your application.

2Check permissions on the parent directory

SQLite creates temporary journal files (.db-journal, .db-wal, .db-shm) in the same directory as the database. The directory itself must be writable:

On Linux/macOS:

bash

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

# Example output: drwxr-xr-x  (755)
# You need write permission (w) for your user

# Add write permission if needed
chmod u+w /path/to/database/

# Or grant group write permission for web servers
chmod g+w /path/to/database/  # if web server runs as group member

On Windows:

powershell

# Right-click the folder > Properties > Security
# Ensure your user has "Modify" permission on the folder itself
# This allows SQLite to create and manage temporary files

This is often the overlooked culprit because developers focus only on the database file itself.

3Verify file ownership matches the running user

If the database was created by one user (e.g., with sudo) but is now being accessed by another user, ownership may cause permission issues:

On Linux/macOS:

bash

# Check who owns the file
ls -l /path/to/your/database.db
# Output example: -rw-r--r-- root root database.db

# If owned by root but app runs as www-data (web server), change ownership
sudo chown www-data:www-data /path/to/your/database.db

# Or change to current user
sudo chown $USER:$USER /path/to/your/database.db

# Change directory ownership too
sudo chown www-data:www-data /path/to/database/

On Windows:

powershell

# Use icacls to check and fix ownership
icacls "C:\path\to\database.db"

# Grant full control to your user
icacls "C:\path\to\database.db" /grant "%USERNAME%:F"

Ownership matters on Unix-like systems; ensure the application's user owns or has write access to the database.

4Delete stale journal and WAL files

If a previous process was interrupted, temporary files may prevent new writes. Safely remove them:

bash

# List temporary files
ls -la /path/to/database.db*

# Example output:
# -rw-r--r-- database.db
# -rw-r--r-- database.db-journal  (stale journal)
# -rw-r--r-- database.db-wal      (write-ahead log)
# -rw-r--r-- database.db-shm      (shared memory)

# Stop your application first!
# Then safely remove journal and WAL files
rm /path/to/database.db-journal
rm /path/to/database.db-wal
rm /path/to/database.db-shm

# Restart your application

These temporary files are normally cleaned up by SQLite, but interruptions (crashes, SIGKILL) can leave them behind.

5Ensure only one process writes at a time

SQLite only supports one writer at any given moment. If multiple processes try to write simultaneously, some will get SQLITE_READONLY:

bash

# Check what processes have the database open
lsof /path/to/your/database.db

# Example output:
# COMMAND     PID   USER      FD   TYPE DEVICE SIZE NODE NAME
# node       1234   user      5u   REG   10,5 8192  456 database.db
# python     5678   user      6u   REG   10,5 8192  456 database.db

If multiple processes are accessing it:
- Add write locks in your application to serialize writes
- Use a connection pool with exclusive write access
- Implement a queue system for write operations

For web applications, ensure your app server instance is the only writer.

6Check database connection flags

Verify that your application isn't explicitly opening the database as read-only:

Node.js (better-sqlite3):

javascript

// Wrong - opens as read-only
const db = new Database('/path/to/database.db', { readonly: true });
db.exec('INSERT INTO users (name) VALUES ("John")');  // Fails with SQLITE_READONLY

// Correct - opens for reading and writing
const db = new Database('/path/to/database.db');
db.exec('INSERT INTO users (name) VALUES ("John")');  // Works

Node.js (sqlite3):

javascript

// Wrong - flags specify read-only
sqlite3.OPEN_READONLY  // Opens as read-only

// Correct - flags allow writing
sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE  // Allows reading and writing

Python (sqlite3):

python

# Wrong - URI with read-only flag
conn = sqlite3.connect('file:/path/to/database.db?mode=ro', uri=True)

# Correct - allows writing
conn = sqlite3.connect('/path/to/database.db')

Ensure your database connection uses write-enabled flags unless read-only access is intentional.

7Move database to a writable location

If the database is on a system directory or mounted as read-only, move it to a writable location:

On Linux/macOS:

bash

# If database is in /usr/share (system directory) or mounted read-only
# Move it to /var/lib or /home
sudo mv /usr/share/myapp/database.db /var/lib/myapp/database.db

# Update your application's database path configuration
# Then restart the application

On Windows:

powershell

# If database is in C:\Program Files (protected)
# Move it to C:\Users\username\AppData or C:\ProgramData
Move-Item "C:\Program Files\MyApp\database.db" "C:\ProgramData\MyApp\database.db"

# Update application config to point to new location

Protected system directories often have special restrictions that prevent even privileged users from writing. User data directories are always writable by your application.

8Verify the filesystem is writable

Check if the filesystem itself is mounted as read-only:

bash

# Check mount status
mount | grep /path/to/database

# Example output:
# /dev/sda1 on /var/data type ext4 (ro)  <- read-only!
# /dev/sda1 on /var/data type ext4 (rw)  <- read-write (correct)

# If read-only, remount as read-write (if you have permissions)
sudo mount -o remount,rw /var/data

# For network drives, check mount options
sudo mount -t nfs -o rw server:/path /local/path

Cloud volumes and network drives may be mounted read-only. Work with your systems team to remount as read-write.

Advanced notes

Web Server Permissions (Apache, Nginx, Node.js):

When running SQLite from a web server, the web server's user/group must have write access:

- Apache/PHP: Database needs write permission for www-data or apache user
- Nginx: Database needs write permission for nginx or www-data user
- Node.js: Database needs write permission for the user running Node

Common fix:

bash

# For Apache/PHP
sudo chown www-data:www-data /path/to/database.db /path/to/database/
sudo chmod 770 /path/to/database.db /path/to/database/

# For Nginx
sudo chown nginx:nginx /path/to/database.db /path/to/database/

WAL Mode (Write-Ahead Logging):

SQLite 3.22.0+ supports read-only WAL databases. If using WAL mode, you need:
1. Write permission on database file itself
2. Write permission on directory (for .db-wal and .db-shm files)
3. Only one writer process

sql

-- Enable WAL mode
PRAGMA journal_mode = WAL;

-- Check current journal mode
PRAGMA journal_mode;  -- Returns: wal

If in WAL mode and getting SQLITE_READONLY, the directory permissions are critical.

Linux AppArmor/SELinux:

Security modules can prevent database writes even with correct file permissions:

bash

# Check if SELinux is blocking writes
getenforce  # Returns: Enforcing, Permissive, or Disabled

# View denied operations
grep "database.db" /var/log/audit/audit.log

# Fix context (if needed)
sudo chcon -R -t user_home_t /path/to/database/

Docker/Container Environments:

In Docker, volume mounts and user IDs cause SQLITE_READONLY frequently:

dockerfile

# Dockerfile - ensure app user has write permission
FROM node:16
WORKDIR /app
RUN mkdir -p /data && chown node:node /data
COPY --chown=node:node . .
USER node

yaml

# docker-compose.yml
version: '3'
services:
  app:
    build: .
    volumes:
      - ./data:/data  # Mount as writable
    environment:
      DATABASE_URL: /data/database.db

Testing Permissions:

Quick test to verify write access:

bash

# Create a test database
sqlite3 /path/to/database.db "CREATE TABLE test (id INTEGER); INSERT INTO test VALUES (1);"

# If this fails with SQLITE_READONLY, permissions are definitely the issue

How to fix SQLITE_READONLY: Attempt to write a readonly database in SQLite

What this error means

Typical symptoms

Common causes

How to fix "SQLITE_READONLY: Attempt to write a readonly database"

Advanced notes

Related errors

Official resources & further reading