SQLITE_SCHEMA: database schema has changed

This error occurs when the database schema changes while a prepared statement is running. Common in applications where multiple connections modify the schema.

The database schema has changed error occurs when a prepared statement becomes invalid due to schema modifications.

Understanding the Error

SQLITE_SCHEMA: database schema has changed

A statement was prepared, then the schema changed (table altered, index created, etc.), making the prepared statement invalid.

Common Causes

1. Schema Modified by Another Connection

JAVASCRIPT

// Connection 1: Prepares statement
const stmt = db1.prepare('SELECT * FROM users');

// Connection 2: Modifies schema
db2.exec('ALTER TABLE users ADD COLUMN age INTEGER');

// Connection 1: Tries to execute - fails
stmt.all();  // SQLITE_SCHEMA error

2. Schema Modified Between Prepare and Execute

JAVASCRIPT

const stmt = db.prepare('SELECT * FROM users');

// Schema changes
db.exec('CREATE INDEX idx_users_email ON users(email)');

// Prepared statement is now invalid
stmt.all();

3. Application Updates While Running

Migrations running while queries are in progress.

How to Fix It

Solution 1: Retry the Query

Most applications should retry on SQLITE_SCHEMA:

JAVASCRIPT

function queryWithRetry(db, sql, params, maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      const stmt = db.prepare(sql);
      return stmt.all(params);
    } catch (err) {
      if (err.code === 'SQLITE_SCHEMA' && i < maxRetries - 1) {
        continue;  // Retry with fresh prepared statement
      }
      throw err;
    }
  }
}

Solution 2: Re-prepare Statements After Schema Changes

JAVASCRIPT

class QueryManager {
  constructor(db) {
    this.db = db;
    this.statements = new Map();
  }

  prepare(name, sql) {
    this.statements.set(name, { sql, stmt: this.db.prepare(sql) });
  }

  invalidateAll() {
    for (const [name, { sql }] of this.statements) {
      this.statements.set(name, { sql, stmt: this.db.prepare(sql) });
    }
  }

  run(name, params) {
    const { stmt } = this.statements.get(name);
    try {
      return stmt.run(params);
    } catch (err) {
      if (err.code === 'SQLITE_SCHEMA') {
        this.invalidateAll();
        return this.run(name, params);
      }
      throw err;
    }
  }
}

Solution 3: Use Immediate Statements

Don't hold prepared statements across schema changes:

JAVASCRIPT

// Instead of preparing once and reusing
function getUsers(db) {
  // Prepare fresh each time (simpler but slower)
  return db.prepare('SELECT * FROM users').all();
}

Solution 4: Coordinate Schema Changes

Run migrations when no queries are active:

JAVASCRIPT

async function runMigrations(db) {
  // Acquire exclusive lock
  db.exec('BEGIN EXCLUSIVE');

  try {
    // Run all migrations
    db.exec('ALTER TABLE users ADD COLUMN age INTEGER');
    db.exec('COMMIT');
  } catch (err) {
    db.exec('ROLLBACK');
    throw err;
  }
}

Best Practices

Handle SQLITE_SCHEMA in your error handling
Run migrations during low-traffic periods
Don't cache prepared statements forever
Use connection pooling that handles schema changes