Offensive Security Researcher & Quantum Cryptography Analyst

SQLite standard return codes

Abdul Wahab Junaid

The first time one of my SQLite queries failed silently inside a Python script, I learned a hard lesson: understanding SQLite’s return codes isn’t optional if you’re writing anything beyond the most trivial application. These codes are how the SQLite C API — and by extension, every language binding built on top of it — communicates exactly what happened after every single operation. In this article, I want to walk through the standard SQLite return codes, what they actually mean, and how I handle them in real code.

Why Return Codes Matter

SQLite’s core C API is function-call based, not exception-based. Every important function — sqlite3_open(), sqlite3_step(), sqlite3_exec() — returns an integer result code indicating success or the specific type of failure. Higher-level language bindings, like Python’s sqlite3 module, translate these codes into exceptions automatically, but understanding the underlying codes still matters enormously for debugging, especially when errors get wrapped in a generic exception message that doesn’t tell the whole story.

The Primary Result Codes

SQLite defines a set of primary result codes, each represented as a small integer constant in the C API.

SQLITE_OK (0)

The operation completed successfully. This is the code every function aims to return.

SQLITE_ERROR (1)

A generic, catch-all error — something went wrong, often related to SQL syntax or a general failure that doesn’t fit into a more specific category.

SELECT * FROM nonexistent_table;
-- Error: no such table: nonexistent_table

In Python, this would surface as:

import sqlite3
conn = sqlite3.connect("test.db")
try:
    conn.execute("SELECT * FROM nonexistent_table")
except sqlite3.OperationalError as e:
    print(e)

SQLITE_INTERNAL (2)

An internal logic error inside the SQLite library itself. In practice, I’ve essentially never encountered this in real usage — it indicates a bug in SQLite itself rather than anything an application typically causes.

SQLITE_PERM (3)

Access permission was denied — typically a filesystem-level permission issue preventing SQLite from accessing the database file the way it needs to.

SQLITE_ABORT (4)

The operation was aborted before completion, often because of a callback returning non-zero during an operation, or a conflict resolution triggering an abort.

SQLITE_BUSY (5)

This is one I’ve run into constantly in real applications. It means the database file is locked by another connection, and the current operation couldn’t proceed. Since SQLite serializes writes, this happens most often when two processes try to write at the same time without proper retry logic.

import sqlite3
conn = sqlite3.connect("test.db", timeout=10)

Setting an explicit timeout (as shown above) tells the connection to keep retrying for that many seconds before actually raising a SQLITE_BUSY error, which resolves the vast majority of transient locking issues in low-to-moderate concurrency applications.

SQLITE_LOCKED (6)

Similar to SQLITE_BUSY, but specifically indicates a conflict with another statement within the same database connection, often due to a shared table lock, rather than a separate connection entirely.

SQLITE_NOMEM (7)

SQLite ran out of memory while trying to complete the operation. Rare outside of severely memory-constrained embedded environments.

SQLITE_READONLY (8)

An attempt was made to write to a database opened in read-only mode, or to a database file that the filesystem has marked as read-only.

SQLITE_INTERRUPT (9)

The operation was interrupted by a call to sqlite3_interrupt(), typically used by an application to cancel a long-running query intentionally.

SQLITE_IOERR (10)

A low-level disk I/O error occurred — a genuine hardware or filesystem-level failure while reading or writing the database file.

SQLITE_CORRUPT (11)

The database file itself appears to be corrupted. This is one of the more serious codes to encounter, and it usually warrants running PRAGMA integrity_check; immediately to assess the extent of the damage.

PRAGMA integrity_check;

SQLITE_NOTFOUND (12)

An internal code indicating something wasn’t found — largely used internally rather than surfaced directly to typical application code.

SQLITE_FULL (13)

The disk is full, and SQLite cannot write additional data to the database file.

SQLITE_CANTOPEN (14)

SQLite was unable to open the database file at all — often due to an incorrect path, missing directory, or permission issue.

SQLITE_PROTOCOL (15)

A problem with the locking protocol itself, typically indicating some form of filesystem-level locking inconsistency, often related to network filesystems that don’t handle file locking reliably.

SQLITE_EMPTY (16)

Historically indicated an empty database, though it’s largely unused in modern SQLite versions.

SQLITE_SCHEMA (17)

The database schema changed unexpectedly between when a prepared statement was compiled and when it was executed, requiring the statement to be re-prepared.

SQLITE_TOOBIG (18)

A string or blob exceeded SQLite’s configured size limit.

SQLITE_CONSTRAINT (19)

One of the codes I encounter most often in normal application development — a constraint violation, such as violating a UNIQUE, NOT NULL, CHECK, or FOREIGN KEY constraint.

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

INSERT INTO users (email) VALUES ('a@example.com');
INSERT INTO users (email) VALUES ('a@example.com');
-- Error: UNIQUE constraint failed: users.email

try:
    conn.execute("INSERT INTO users (email) VALUES ('a@example.com')")
except sqlite3.IntegrityError as e:
    print("Constraint violation:", e)

SQLITE_MISMATCH (20)

A data type mismatch occurred — relevant more often in STRICT tables, since SQLite’s default dynamic typing is generally forgiving about type mismatches.

SQLITE_MISUSE (21)

The SQLite API was used incorrectly by the calling application — for example, calling a function on a connection that’s already been closed.

SQLITE_ROW (100) and SQLITE_DONE (101)

These two are special — they’re not errors at all. SQLITE_ROW indicates that sqlite3_step() has produced another row of data to read, and SQLITE_DONE indicates that a statement has finished executing completely with no more rows to return. Every row-by-row iteration through query results in the C API is built around checking for these two codes repeatedly.

Extended Result Codes

Beyond the primary codes, SQLite also provides “extended result codes” that give more granular detail. For example, SQLITE_CONSTRAINT alone doesn’t tell you which type of constraint failed, but the extended codes do:

These are enabled with:

sqlite3_extended_result_codes(db, 1);

I’ve found extended codes genuinely useful when building applications that need to give the user a specific, actionable error message rather than a generic “something went wrong with your data” message.

Handling Return Codes in Python

Python’s sqlite3 module maps these underlying codes to a hierarchy of exception classes:

import sqlite3

try:
    conn = sqlite3.connect("app.db")
    conn.execute("INSERT INTO users (email) VALUES (?)", ("duplicate@example.com",))
    conn.commit()
except sqlite3.IntegrityError as e:
    print("Integrity error (likely a constraint violation):", e)
except sqlite3.OperationalError as e:
    print("Operational error (e.g. locked database, syntax error):", e)
except sqlite3.DatabaseError as e:
    print("General database error:", e)

I structure error handling this way deliberately — catching the most specific exception types first, falling back to broader categories, rather than catching a single generic exception and losing the ability to respond differently to different failure types.

Handling SQLITE_BUSY Gracefully

Because SQLITE_BUSY is so common in real applications with any concurrent access, I typically implement retry logic explicitly:

import sqlite3
import time

def execute_with_retry(conn, sql, params=(), retries=5, delay=0.1):
    for attempt in range(retries):
        try:
            conn.execute(sql, params)
            conn.commit()
            return
        except sqlite3.OperationalError as e:
            if "locked" in str(e) and attempt < retries - 1:
                time.sleep(delay)
                continue
            raise

Best Practices

Frequently Asked Questions

What’s the difference between SQLITE_BUSY and SQLITE_LOCKED? SQLITE_BUSY typically indicates contention with a completely separate database connection, while SQLITE_LOCKED indicates a conflict within the same connection, often due to a shared lock on a table.

Why did my query fail with SQLITE_CONSTRAINT? It means the operation violated a UNIQUE, NOT NULL, CHECK, FOREIGN KEY, or PRIMARY KEY constraint defined on the table. Enable extended result codes to see exactly which constraint was violated.

Is SQLITE_ROW an error? No — it’s a normal, expected status indicating that a query has produced another row of data to read. It’s part of the normal flow of iterating through query results, not a failure.

What should I do if I get SQLITE_CORRUPT? Run PRAGMA integrity_check; immediately to assess the damage, stop writing to the database if possible, and restore from a known-good backup.

How do extended result codes help in practice? They let you distinguish between different failure reasons that would otherwise all appear as the same generic code, letting your application respond with more specific, useful error handling or messaging.

Wrapping Up

Learning to actually read and respond to SQLite’s return codes, instead of treating every failure as an undifferentiated exception, made a real difference in how robust my applications became. Codes like SQLITE_BUSY and SQLITE_CONSTRAINT show up constantly in real-world usage, and understanding exactly what they mean — and how to handle them gracefully — is one of those unglamorous but essential skills that separates a fragile SQLite integration from a genuinely reliable one.

Exit mobile version