Common SQLite Errors: Locked DB, Readonly, Malformed, and More

Errors Are Just SQLite Telling You Something

SQLite's error messages are short and sometimes cryptic, but they map to a small set of underlying problems. Most of what you'll hit in production falls into five buckets: locking, permissions, corruption, schema mismatches, and constraint violations. This page walks through each one — what triggers it, what it actually means, and how to fix it.

The error strings come paired with numeric codes (extended codes are even more specific). You'll see both forms in logs:

Error: database is locked          -- code 5 (SQLITE_BUSY)
Error: unable to open database     -- code 14 (SQLITE_CANTOPEN)
Error: attempt to write a readonly -- code 8 (SQLITE_READONLY)
Error: database disk image is      -- code 11 (SQLITE_CORRUPT)

Knowing the code helps when searching — SQLITE_BUSY returns far better results than the plain English message.

database is locked (SQLITE_BUSY)

The most common SQLite error in any application that writes from more than one place. SQLite serializes writes: only one connection can hold the write lock at a time. If a second writer can't get the lock within the busy timeout, you get this error.

Three fixes, in order of impact:

-- 1. Turn on WAL mode. Readers no longer block writers,
--    and one writer no longer blocks readers.
PRAGMA journal_mode = WAL;

-- 2. Raise the busy timeout so contended writes wait
--    instead of failing instantly. 5000 = 5 seconds.
PRAGMA busy_timeout = 5000;

-- 3. Verify both settings stuck.
PRAGMA journal_mode;
PRAGMA busy_timeout;

WAL mode alone solves the locking problem for most workloads. The busy timeout is your safety net for actual write contention. Beyond settings, audit your code: a transaction left open while the program does network I/O will hold the lock for that whole time. Keep transactions short, and COMMIT (or ROLLBACK) as soon as the work is done.

unable to open database file (SQLITE_CANTOPEN)

SQLite tried to open the file and the OS said no. The file path or its directory is the problem 95% of the time:

-- Things to check:
-- 1. Does the path exist?            ls -l /path/to/db.sqlite
-- 2. Does the parent directory exist? SQLite creates the file
--    but not the directory above it.
-- 3. Does the user running your process have read+write
--    permission on the directory (not just the file)?
-- 4. Is the volume mounted, not full, and not read-only?

A subtle case: SQLite needs to create sidecar files (-journal, -wal, -shm) next to the database. If the file itself is writable but the directory isn't, opens succeed and writes fail. Always grant directory-level write permission.

attempt to write a readonly database (SQLITE_READONLY)

A close cousin of the previous one. The file opened fine but writes fail. Causes, ranked by frequency:

• The OS user lacks write permission on the file or its directory.
• The connection was opened with a read-only flag (SQLITE_OPEN_READONLY, or mode=ro in a URI).
• The volume is mounted read-only (common with Docker bind mounts and some cloud filesystems).
• The database is on a network filesystem that doesn't support the locking SQLite needs.

-- Confirm whether SQLite itself thinks the DB is read-only:
PRAGMA query_only;

-- And what file it actually opened:
PRAGMA database_list;

Fix the permissions or remount the volume. If you're in Docker, make sure the bind mount isn't :ro and that the container user owns the directory.

database disk image is malformed (SQLITE_CORRUPT)

The file's bytes no longer match SQLite's format. Real causes are usually environmental: killed processes mid-write on filesystems without proper fsync, copying the database while a writer was active, hardware failures, or syncing the file via Dropbox/iCloud.

First, confirm the damage:

PRAGMA integrity_check;

If integrity_check returns ok, your database is fine and the error came from somewhere else (often a stale connection). If it returns a list of problems, you need to recover.

The cleanest recovery path uses the CLI's .recover command, which extracts whatever data it can into a fresh database:

sqlite3 corrupt.db ".recover" | sqlite3 recovered.db
sqlite3 recovered.db "PRAGMA integrity_check;"

If you have a recent backup, restore from it instead — it's faster and avoids the ambiguity of "we got most of it back." See the backup-and-restore page for the right way to copy a live database (hint: not cp).

no such table and no such column

These mean exactly what they say, but the cause is usually one of two things: you're connected to a different database than you think, or a migration didn't run.

-- See which file you're actually connected to:
PRAGMA database_list;

-- See what's in it:
SELECT name FROM sqlite_master WHERE type = 'table';

-- See a specific table's columns:
SELECT name, type FROM pragma_table_info('users');

Check your application's connection string — relative paths are evaluated against the current working directory, which differs between your terminal, your IDE, and your production process. An in-memory database (:memory:) is fresh every time, which trips people up when they expect persistence.

Identifier quoting matters too. Unquoted names are case-insensitive, but "User" and "user" are different identifiers. If you created a table with quotes around the name, you have to keep quoting it.

Constraint violations

SQLite refuses writes that would break a constraint. The error message names which one:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INTEGER CHECK (age >= 0)
);

INSERT INTO users (email, age) VALUES ('a@x.com', 30);
INSERT INTO users (email, age) VALUES ('a@x.com', 25);
-- Error: UNIQUE constraint failed: users.email

INSERT INTO users (email, age) VALUES ('b@x.com', -5);
-- Error: CHECK constraint failed: age >= 0

INSERT INTO users (age) VALUES (40);
-- Error: NOT NULL constraint failed: users.email

Each failure is a different code under the hood (SQLITE_CONSTRAINT_UNIQUE, SQLITE_CONSTRAINT_CHECK, SQLITE_CONSTRAINT_NOTNULL). The fix is almost always at the application layer — validate input before writing, or use INSERT ... ON CONFLICT to handle duplicates intentionally.

FOREIGN KEY constraint failed deserves its own note: foreign keys are off by default in SQLite. If you don't enable them, invalid references go in silently, then fail later when you finally turn enforcement on. Set the pragma on every connection:

PRAGMA foreign_keys = ON;

CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE book (
    id INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES author(id)
);

INSERT INTO book (author_id) VALUES (999);
-- Error: FOREIGN KEY constraint failed

cannot start a transaction within a transaction

You called BEGIN while a transaction was already open. SQLite doesn't allow nested transactions — but it does allow nested savepoints, which give you the same effect:

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);

BEGIN;
INSERT INTO users (email) VALUES ('outer@x.com');

SAVEPOINT inner_work;
INSERT INTO users (email) VALUES ('inner@x.com');
RELEASE inner_work;       -- or: ROLLBACK TO inner_work;

COMMIT;

SELECT * FROM users;

If your ORM or framework manages transactions, you've probably told it to start one twice. Check whether autocommit is on, and whether your connection pool is reusing a connection that already has an open transaction.

disk I/O error (SQLITE_IOERR)

The OS rejected a read or write. Disk full, network filesystem hiccup, or the file got deleted out from under SQLite. The first thing to check is df -h. The second is whether the database lives on something flaky like NFS or a cloud-synced folder — SQLite assumes a local POSIX filesystem with working fsync. If you can't move it, accept that corruption risk goes up.

syntax error near "..."

SQLite's parser tells you which token confused it. The fix is usually three lines back from where the error points — a missing comma, an unquoted identifier that collides with a keyword, or a string with single quotes that need escaping ('it''s', not 'it's').

-- Reserved words as column names need quoting:
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    "order" INTEGER,
    "from" TEXT
);

INSERT INTO events ("order", "from") VALUES (1, 'web');
SELECT "order", "from" FROM events;

Use parameter binding (? placeholders) for user input rather than building SQL with string concatenation — you'll dodge an entire category of syntax errors and SQL injection at the same time.

A Diagnostic Checklist

When something breaks in production, this sequence covers most cases in under a minute:

-- Where am I?
PRAGMA database_list;

-- Is the file healthy?
PRAGMA integrity_check;

-- What mode is the journal in?
PRAGMA journal_mode;

-- Are foreign keys enforced?
PRAGMA foreign_keys;

-- How long will writers wait before SQLITE_BUSY?
PRAGMA busy_timeout;

Five pragmas, five answers. Combined with the error code from the failed query, you'll know which bucket the problem belongs to and which page of the docs to open next.

Wrapping Up the Curriculum

That's the tour. You've gone from CREATE TABLE through joins, indexes, transactions, WAL mode, backups, and now the failure modes that show up when SQLite meets the real world. The patterns repeat: short transactions, enabled foreign keys, WAL mode, regular backups, and a healthy respect for PRAGMA integrity_check. Keep those habits and SQLite will quietly run for years.