EC
SQLITE_TOOBIG
SQLiteERRORCommonResource LimitHIGH confidence

string or blob too big

What this means

SQLITE_TOOBIG (result code 18) is returned when a string or BLOB value exceeds the compile-time or runtime maximum allowed size. The default maximum is 1 billion bytes (1 GB) per value, but the sqlite3_limit() API or the SQLITE_MAX_LENGTH compile option can lower this. SQLite is not intended as a store for multi-gigabyte binary objects.

Why it happens
  1. 1Inserting a BLOB or TEXT value larger than SQLITE_MAX_LENGTH (default 1,000,000,000 bytes)
  2. 2A SQL expression produces an intermediate string result that exceeds the limit
  3. 3The application lowered the per-connection limit via sqlite3_limit(SQLITE_LIMIT_LENGTH) and then tried to insert a value that previously fit
How to reproduce

A BLOB larger than the configured limit is inserted.

trigger — this will error
trigger — this will error
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t (data BLOB)')

# Default limit is 1 GB; lower it for demonstration
conn.execute('SELECT sqlite_compileoption_used("MAX_LENGTH")')

# Generate a 2-byte-over-limit blob (in practice use actual limit)
big = b'x' * (1_000_000_001)  # > 1 GB
conn.execute('INSERT INTO t VALUES (?)', (big,))  # triggers SQLITE_TOOBIG

expected output

sqlite3.OperationalError: string or blob too big

Fix

Store large objects on the filesystem and store only the path in SQLite

WHEN When the value genuinely needs to be large (images, documents, videos).

Store large objects on the filesystem and store only the path in SQLite
import sqlite3, pathlib

# Store the file on disk
pathlib.Path('/data/uploads/file.bin').write_bytes(large_data)

# Store only the reference in SQLite
conn = sqlite3.connect('app.db')
conn.execute('CREATE TABLE files (id INTEGER PRIMARY KEY, path TEXT)')
conn.execute("INSERT INTO files (path) VALUES ('/data/uploads/file.bin')")
conn.commit()

Why this works

SQLite is optimised for structured relational data. Large binary objects are better served by a filesystem or object store, with SQLite holding metadata and file paths. This also avoids page fragmentation from large overflow pages.

What not to do

✕ Raise SQLITE_MAX_LENGTH to multi-gigabyte values

Very large values cause severe page fragmentation and slow all operations on the table. SQLite's B-tree structure is not efficient for row sizes that exceed a page.

Version notes
All versions

The default SQLITE_MAX_LENGTH compile-time limit is 1,000,000,000 bytes. The absolute maximum is 2,147,483,647 bytes (2 GiB - 1).

Sources

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All SQLite errors