How to fix SQLITE_TOOBIG: String or BLOB exceeds size limit in SQLite

// Node.js example
const fs = require('fs');
const fileSize = fs.statSync('largefile.bin').size;
console.log(`File size: ${fileSize} bytes (${(fileSize / 1024 / 1024).toFixed(2)} MB)`);

if (fileSize > 500_000_000) { // 500 MB threshold
  console.warn('File may exceed SQLite recommended limits');
}

# Python example
import os

file_size = os.path.getsize('largefile.bin')
print(f"File size: {file_size} bytes ({file_size / 1024 / 1024:.2f} MB)")

if file_size > 500_000_000:
    print("Warning: File may exceed SQLite recommended limits")

// Node.js example with file storage
const fs = require('fs');
const path = require('path');
const crypto = require('crypto');

async function storeFile(fileBuffer, db) {
  // Generate unique filename
  const hash = crypto.createHash('sha256').update(fileBuffer).digest('hex');
  const filename = `${hash.substring(0, 16)}.bin`;
  const filePath = path.join('./uploads', filename);

  // Write to filesystem
  await fs.promises.writeFile(filePath, fileBuffer);

  // Store reference in database
  await db.run(
    'INSERT INTO files (filename, path, size, created_at) VALUES (?, ?, ?, ?)',
    [filename, filePath, fileBuffer.length, new Date().toISOString()]
  );

  return filename;
}

# Python example with file storage
import sqlite3
import hashlib
from pathlib import Path
from datetime import datetime

def store_file(file_data: bytes, db_conn):
    # Generate unique filename
    hash_obj = hashlib.sha256(file_data)
    filename = f"{hash_obj.hexdigest()[:16]}.bin"
    file_path = Path("./uploads") / filename

    # Write to filesystem
    file_path.parent.mkdir(exist_ok=True)
    file_path.write_bytes(file_data)

    # Store reference in database
    db_conn.execute(
        "INSERT INTO files (filename, path, size, created_at) VALUES (?, ?, ?, ?)",
        (filename, str(file_path), len(file_data), datetime.now().isoformat())
    )
    db_conn.commit()

    return filename

// Node.js chunking example
const CHUNK_SIZE = 1024 * 1024; // 1 MB chunks

async function insertLargeBlob(fileBuffer, fileId, db) {
  const totalChunks = Math.ceil(fileBuffer.length / CHUNK_SIZE);

  for (let i = 0; i < totalChunks; i++) {
    const start = i * CHUNK_SIZE;
    const end = Math.min(start + CHUNK_SIZE, fileBuffer.length);
    const chunk = fileBuffer.slice(start, end);

    await db.run(
      'INSERT INTO blob_chunks (file_id, chunk_index, chunk_data, total_chunks) VALUES (?, ?, ?, ?)',
      [fileId, i, chunk, totalChunks]
    );
  }
}

async function retrieveLargeBlob(fileId, db) {
  const chunks = await db.all(
    'SELECT chunk_data FROM blob_chunks WHERE file_id = ? ORDER BY chunk_index',
    [fileId]
  );

  return Buffer.concat(chunks.map(row => row.chunk_data));
}

# Python chunking example
CHUNK_SIZE = 1024 * 1024  # 1 MB chunks

def insert_large_blob(file_data: bytes, file_id: str, db_conn):
    total_chunks = (len(file_data) + CHUNK_SIZE - 1) // CHUNK_SIZE

    for i in range(total_chunks):
        start = i * CHUNK_SIZE
        end = min(start + CHUNK_SIZE, len(file_data))
        chunk = file_data[start:end]

        db_conn.execute(
            "INSERT INTO blob_chunks (file_id, chunk_index, chunk_data, total_chunks) VALUES (?, ?, ?, ?)",
            (file_id, i, chunk, total_chunks)
        )

    db_conn.commit()

def retrieve_large_blob(file_id: str, db_conn):
    cursor = db_conn.execute(
        "SELECT chunk_data FROM blob_chunks WHERE file_id = ? ORDER BY chunk_index",
        (file_id,)
    )

    chunks = [row[0] for row in cursor.fetchall()]
    return b''.join(chunks)

CREATE TABLE blob_chunks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  file_id TEXT NOT NULL,
  chunk_index INTEGER NOT NULL,
  chunk_data BLOB NOT NULL,
  total_chunks INTEGER NOT NULL,
  UNIQUE(file_id, chunk_index)
);

CREATE INDEX idx_blob_chunks_file ON blob_chunks(file_id);

-- Check current limit (returns value in bytes)
PRAGMA max_page_count;
PRAGMA page_size;

-- Attempt to set maximum blob size (not all implementations support this)
PRAGMA max_blob_size = 2000000000; -- ~2 GB

// Node.js with better-sqlite3
const Database = require('better-sqlite3');
const db = new Database('mydb.sqlite');

// Note: max_blob_size PRAGMA is not universally supported
try {
  db.pragma('max_blob_size = 2000000000');
  console.log('Blob size limit increased');
} catch (error) {
  console.error('Cannot adjust blob size at runtime:', error.message);
}

# Download SQLite source
wget https://www.sqlite.org/2025/sqlite-amalgamation-3XXXXXX.zip
unzip sqlite-amalgamation-3XXXXXX.zip
cd sqlite-amalgamation-3XXXXXX

# Compile with custom max length (2 GB - near absolute limit)
gcc -DSQLITE_MAX_LENGTH=2147483647 \
    -DSQLITE_MAX_SQL_LENGTH=2147483647 \
    -O2 -o sqlite3 shell.c sqlite3.c -lpthread -ldl

# Install custom build
sudo cp sqlite3 /usr/local/bin/sqlite3-custom

# Clone and modify better-sqlite3
git clone https://github.com/WiseLibs/better-sqlite3.git
cd better-sqlite3

# Edit deps/common.gypi to add compile flags
# Add to 'defines': ['SQLITE_MAX_LENGTH=2147483647']

npm install
npm run build-release