SQLITE_NOMEM: out of memory

This error occurs when SQLite can't allocate enough memory for an operation. Usually caused by extremely large queries or results, or system memory exhaustion.

The out of memory error means SQLite couldn't allocate memory needed for an operation.

Understanding the Error

SQLITE_NOMEM: out of memory

Either SQLite's internal memory limit was hit, or the system is out of memory.

Common Causes

1. Huge Result Sets

JAVASCRIPT

// Loading millions of rows into memory
const allRows = db.prepare('SELECT * FROM huge_table').all();
// Each row needs memory!

2. Large String Operations

SQL

-- Creating huge strings
SELECT GROUP_CONCAT(content) FROM all_articles;
-- Could be gigabytes!

3. Complex Queries

Many JOINs or subqueries consuming memory.

4. Memory Leaks

Not finalizing prepared statements:

JAVASCRIPT

// Statements accumulate
for (let i = 0; i < 1000000; i++) {
  const stmt = db.prepare('SELECT * FROM users WHERE id = ?');
  stmt.get(i);
  // Statement not finalized!
}

5. System Memory Exhausted

Other processes using all available RAM.

How to Fix It

Solution 1: Stream Results

Don't load everything at once:

JAVASCRIPT

// Instead of .all()
const stmt = db.prepare('SELECT * FROM huge_table');
for (const row of stmt.iterate()) {
  processRow(row);
  // Only one row in memory at a time
}

Solution 2: Paginate

JAVASCRIPT

function* paginate(db, sql, pageSize = 1000) {
  let offset = 0;

  while (true) {
    const rows = db.prepare(`${sql} LIMIT ? OFFSET ?`)
      .all(pageSize, offset);

    if (rows.length === 0) break;

    yield* rows;
    offset += pageSize;
  }
}

for (const row of paginate(db, 'SELECT * FROM huge_table')) {
  processRow(row);
}

Solution 3: Limit Results

SQL

-- Don't fetch more than needed
SELECT * FROM logs
ORDER BY created_at DESC
LIMIT 100;

Solution 4: Use Indexes

Reduce memory for sorting:

SQL

-- Without index: sorts in memory
SELECT * FROM logs ORDER BY created_at;

-- With index: uses index order
CREATE INDEX idx_logs_created ON logs(created_at);

Solution 5: Set Memory Limits

SQL

-- Limit cache size (in pages, default page = 4096 bytes)
PRAGMA cache_size = -2000;  -- Negative = KB, so 2MB

-- Limit temp storage
PRAGMA temp_store = FILE;  -- Use disk instead of memory

Solution 6: Free Memory Periodically

JAVASCRIPT

// Force garbage collection in Node.js
if (global.gc) {
  global.gc();
}

// Release SQLite cache
db.pragma('shrink_memory');

Solution 7: Use 64-bit SQLite

32-bit builds have lower memory limits.

Monitoring Memory

SQL

-- Memory used by SQLite
SELECT * FROM pragma_database_list;

-- Cache statistics
PRAGMA cache_size;
PRAGMA page_count;
PRAGMA page_size;

JAVASCRIPT

// Node.js memory usage
console.log(process.memoryUsage());

Best Practices

Stream large results instead of loading all
Use pagination for large datasets
Set cache limits appropriate for your environment
Finalize statements when done
Monitor memory in production
Use 64-bit builds for large databases