attempt to write a readonly database

What it means

Reading works, but the first statement that writes fails with SQLITE_READONLY (code 8). To write, SQLite needs write permission on the database file, on the folder it’s in (to create the -journal or -wal file next to it), and on the -wal and -shm files if the database uses WAL mode. It also needs to have been opened for writing.

The message is the same whatever the reason. The extended result code, if your driver shows it, narrows it down: SQLITE_READONLY_DIRECTORY (1544) means the folder isn’t writable, SQLITE_READONLY_DBMOVED (1032) means the file was moved or deleted while open.

Common causes

1. The file belongs to another user. It was created by sudo, by a web server or service account, or by a container running as a different user ID.
2. The folder isn’t writable. Even with a writable file, SQLite must create a journal next to it while it writes.
3. The -wal or -shm file isn’t writable, often because a command once ran on the database as root and left those files owned by root.
4. It was opened read-only: sqlite3 -readonly, a URI with mode=ro or immutable=1, the SQLITE_OPEN_READONLY flag, or PRAGMA query_only = ON.
5. The file was replaced or moved while the app had it open: a deploy or restore copied a new database over the old one, or moved it away.
6. It lives somewhere read-only: a mounted disk image, a read-only volume, or inside an app’s bundle.

How to fix it

Check owner and permissions on all of it

Look at the file, its WAL files and the folder:

ls -l /path/to/data/app.db*
ls -ld /path/to/data

The user that runs your process needs write permission on every one of them. Fix ownership or permissions to match (replace <user> with that account):

sudo chown <user> /path/to/data /path/to/data/app.db*
chmod u+w /path/to/data /path/to/data/app.db*

In Docker, the user inside the container needs write access to the mounted folder; the user ID is what counts, not the name.

Check how the database is opened

Search your configuration and code for mode=ro, immutable=1, -readonly, SQLITE_OPEN_READONLY and query_only. Some libraries open a database read-only when you ask for a read-only connection or pool. If you meant to write, open it read-write.

Don’t replace the file under a running app

Stop the app before you copy a database into place, or restore through SQLite (.restore in the shell, or the backup API), which writes into the existing file under SQLite’s locks instead of replacing it. After a file was swapped, restart the process.

Copy bundled databases somewhere writable

A database shipped inside an app or on a disk image is read-only. On first launch, copy it to a writable folder (on a Mac, under ~/Library/Application Support/<app>) and open the copy.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, on a small notes.db. A file without write permission:

chmod 444 notes.db
sqlite3 notes.db "INSERT INTO notes(body) VALUES ('x');"

Error: stepping, attempt to write a readonly database (8)

The shell adds “Error: stepping,” and the result code; SQLite’s message is attempt to write a readonly database. SELECT count(*) FROM notes still worked. Each of these gave the same error on a writable file:

• the folder made read-only with chmod 555 .;
• sqlite3 -readonly notes.db, file:notes.db?mode=ro and file:notes.db?immutable=1;
• PRAGMA query_only = 1 before the insert;
• in WAL mode, the notes.db-shm file made read-only, and separately the notes.db-wal file.

Through Python’s sqlite3 module (SQLite 3.53.4), the extended codes were SQLITE_READONLY for the read-only file and mode=ro, SQLITE_READONLY_DIRECTORY for the read-only folder, and, after renaming the file while the connection was open, SQLITE_READONLY_DBMOVED.

In Inlet

In Inlet, a connection tagged production opens read-only on purpose: the database refuses writes until you unlock it, and unlocking lasts ten minutes. If the connection isn’t tagged production, check the file, folder and -wal and -shm permissions above.