column index out of range

This error occurs when you try to bind or access a parameter or column that doesn't exist. Usually caused by wrong index numbers or parameter count mismatch.

The column index out of range error means you're trying to access a column or parameter position that doesn't exist.

Understanding the Error

SQLITE_RANGE: column index out of range

You're referencing index N but only indexes 0 to M exist.

Common Causes

1. Parameter Index Too High

JAVASCRIPT

// Query has 2 placeholders
const stmt = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');

// Trying to bind 3 parameters - fails
stmt.run('Alice', 'alice@test.com', 'extra');

2. Zero vs One-Based Indexing

Different libraries use different indexing:

JAVASCRIPT

// Some libraries are 1-based
stmt.bind(1, 'value');  // First parameter
stmt.bind(0, 'value');  // Error: no parameter 0

// Some are 0-based
stmt.bind(0, 'value');  // First parameter

3. Column Index in Results

JAVASCRIPT

// Query returns 2 columns
const stmt = db.prepare('SELECT name, email FROM users');
const row = stmt.get();

// Accessing column index 5 - doesn't exist
row[5];  // undefined or error

4. Named Parameter Mismatch

JAVASCRIPT

// Query has :name and :email
const stmt = db.prepare('INSERT INTO users VALUES (:name, :email)');

// Binding wrong parameter name
stmt.run({ name: 'Alice', mail: 'test@test.com' });  // :email not bound!

How to Fix It

Solution 1: Match Parameter Count

JAVASCRIPT

// Count placeholders
const sql = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
// Three ? = three parameters needed

db.run(sql, ['Alice', 'alice@test.com', 25]);  // Correct: 3 values

Solution 2: Use Named Parameters

Clearer and less error-prone:

JAVASCRIPT

const stmt = db.prepare(
  'INSERT INTO users (name, email) VALUES (:name, :email)'
);

stmt.run({
  name: 'Alice',
  email: 'alice@test.com'
});

Solution 3: Check Column Count

JAVASCRIPT

// Get column information
const stmt = db.prepare('SELECT * FROM users');
const columns = stmt.columns();
console.log('Columns:', columns.length);

// Access safely
const row = stmt.get();
for (let i = 0; i < columns.length; i++) {
  console.log(columns[i].name, '=', row[i]);
}

Solution 4: Validate Before Binding

JAVASCRIPT

function safeInsert(db, values) {
  const placeholders = '?, '.repeat(values.length).slice(0, -2);
  const sql = `INSERT INTO users VALUES (${placeholders})`;

  // Verify parameter count matches
  const paramCount = (sql.match(/\?/g) || []).length;
  if (paramCount !== values.length) {
    throw new Error(`Expected ${paramCount} params, got ${values.length}`);
  }

  db.run(sql, values);
}

Best Practices

Use named parameters for clarity
Count placeholders when building dynamic SQL
Use array destructuring to catch mismatches early
Check library documentation for indexing convention