too many SQL variables

This error occurs when a query has more parameter placeholders than SQLite allows. Default limit is 999 variables (32766 in newer versions).

The too many SQL variables error means your query has exceeded the parameter placeholder limit.

Understanding the Error

Error: too many SQL variables

SQLite < 3.32: 999 variables max
SQLite >= 3.32: 32766 variables max

Common Causes

1. Large IN Clause

JAVASCRIPT

const ids = getThousandsOfIds();  // 5000 IDs
const placeholders = ids.map(() => '?').join(',');
const sql = `SELECT * FROM users WHERE id IN (${placeholders})`;
// Error: 5000 > limit

2. Bulk Insert

JAVASCRIPT

const users = getThousandsOfUsers();
const sql = `INSERT INTO users (name, email) VALUES ${
  users.map(() => '(?, ?)').join(',')
}`;
// 2 variables per row × thousands of rows

3. Dynamic Queries

Building queries with many dynamic conditions.

How to Fix It

Solution 1: Batch Operations

JAVASCRIPT

function batchQuery(db, ids, batchSize = 500) {
  const results = [];

  for (let i = 0; i < ids.length; i += batchSize) {
    const batch = ids.slice(i, i + batchSize);
    const placeholders = batch.map(() => '?').join(',');
    const sql = `SELECT * FROM users WHERE id IN (${placeholders})`;

    const batchResults = db.prepare(sql).all(batch);
    results.push(...batchResults);
  }

  return results;
}

Solution 2: Use Temp Table

JAVASCRIPT

function queryLargeIdList(db, ids) {
  // Create temp table
  db.exec('CREATE TEMP TABLE IF NOT EXISTS temp_ids (id INTEGER)');
  db.exec('DELETE FROM temp_ids');

  // Insert in batches
  const insert = db.prepare('INSERT INTO temp_ids VALUES (?)');
  const insertMany = db.transaction((ids) => {
    for (const id of ids) insert.run(id);
  });
  insertMany(ids);

  // Query using join
  const results = db.prepare(`
    SELECT u.* FROM users u
    INNER JOIN temp_ids t ON u.id = t.id
  `).all();

  db.exec('DROP TABLE temp_ids');
  return results;
}

Solution 3: Batch Inserts

JAVASCRIPT

function bulkInsert(db, users, batchSize = 100) {
  const insert = db.prepare(
    'INSERT INTO users (name, email) VALUES (?, ?)'
  );

  const insertMany = db.transaction((batch) => {
    for (const user of batch) {
      insert.run(user.name, user.email);
    }
  });

  for (let i = 0; i < users.length; i += batchSize) {
    const batch = users.slice(i, i + batchSize);
    insertMany(batch);
  }
}

Solution 4: Use JSON

SQL

-- Pass large list as JSON
SELECT * FROM users
WHERE id IN (SELECT value FROM json_each(?));

JAVASCRIPT

const ids = [1, 2, 3, /* ... thousands */];
db.prepare(sql).all(JSON.stringify(ids));

Best Practices

Batch large operations into chunks
Use temp tables for very large sets
Use transactions for batch inserts
Consider design - maybe don't need so many params
Use JSON functions for dynamic lists