SQLITE_TOOBIG: string or BLOB exceeds size limit

This error occurs when you try to store data larger than SQLite's limits. Default maximum is 1 billion bytes, but it can be configured lower.

The string or BLOB exceeds size limit error means your data is larger than SQLite allows.

Understanding the Error

SQLITE_TOOBIG: string or BLOB exceeds size limit

SQLite has limits on how large strings and BLOBs can be.

Default Limits

Maximum string/BLOB size: 1,000,000,000 bytes (1 GB)
Maximum SQL statement length: 1,000,000,000 bytes
Can be compiled with lower limits

Common Causes

1. Storing Large Files

JAVASCRIPT

// Trying to store a huge file
const bigFile = fs.readFileSync('video.mp4');  // 2GB file
db.run('INSERT INTO files (data) VALUES (?)', [bigFile]);  // Error

2. Concatenating Too Much Data

SQL

-- Building a huge string
SELECT GROUP_CONCAT(content) FROM all_articles;  -- Could exceed limit

3. Application Configured Lower Limit

Some SQLite builds have smaller limits:

SQL

-- Check current limits
SELECT sqlite_limit(SQLITE_LIMIT_LENGTH);  -- Max string length

How to Fix It

Solution 1: Store Files Externally

Store file path, not file content:

JAVASCRIPT

// Save file to disk
const filePath = `/uploads/${uuid()}.mp4`;
fs.writeFileSync(filePath, fileData);

// Store only the path
db.run('INSERT INTO files (path) VALUES (?)', [filePath]);

Solution 2: Chunk Large Data

Split into smaller pieces:

JAVASCRIPT

const CHUNK_SIZE = 1000000;  // 1MB chunks

function storeBlob(db, data) {
  const fileId = uuid();
  const chunks = Math.ceil(data.length / CHUNK_SIZE);

  for (let i = 0; i < chunks; i++) {
    const chunk = data.slice(i * CHUNK_SIZE, (i + 1) * CHUNK_SIZE);
    db.run(
      'INSERT INTO blob_chunks (file_id, chunk_num, data) VALUES (?, ?, ?)',
      [fileId, i, chunk]
    );
  }

  return fileId;
}

Solution 3: Use External Blob Storage

JAVASCRIPT

// Store large data in S3, GCS, etc.
const url = await uploadToS3(largeData);
db.run('INSERT INTO files (url) VALUES (?)', [url]);

Solution 4: Compress Data

JAVASCRIPT

const zlib = require('zlib');

// Compress before storing
const compressed = zlib.gzipSync(largeData);
db.run('INSERT INTO files (data) VALUES (?)', [compressed]);

// Decompress when reading
const row = db.get('SELECT data FROM files WHERE id = ?', [id]);
const data = zlib.gunzipSync(row.data);

Best Practices

Don't store large files in SQLite
Use file system or object storage for blobs
Store references (paths, URLs) instead
Set reasonable application limits before data reaches SQLite
Consider PostgreSQL for heavy blob storage