How to fix TypeError: This database connection is busy executing a query in SQLite

const Database = require('better-sqlite3');
const db = new Database('database.db');

// Fetch all results first
const users = db.prepare('SELECT * FROM users').all();

// Then iterate and modify
for (const user of users) {
  db.prepare('UPDATE users SET active = 1 WHERE id = ?').run(user.id);
}

// Don't try to iterate and modify simultaneously
for (const row of db.prepare('SELECT * FROM users').iterate()) {
  // ERROR: connection is busy with the SELECT iterator
  db.prepare('UPDATE users SET status = ? WHERE id = ?')
    .run('active', row.id);
}

const Database = require('better-sqlite3');
const db = new Database('database.db');

// Use .all() instead of .iterate()
const rows = db.prepare('SELECT id, name FROM large_table').all();

// Now you can safely run other queries
for (const row of rows) {
  db.prepare('UPDATE large_table SET processed = 1 WHERE id = ?').run(row.id);
}

db.close();

const db = new Database('database.db');

const allUsers = db.prepare('SELECT id FROM users WHERE inactive = 1').all();

// Wrap updates in a transaction
const updateUser = db.prepare('UPDATE users SET active = 1 WHERE id = ?');
const transaction = db.transaction((ids) => {
  for (const id of ids) {
    updateUser.run(id);
  }
});

transaction(allUsers.map(u => u.id));
db.close();

const Database = require('better-sqlite3');
const db = new Database('database.db');

// Define your prepared statements
const getRows = db.prepare('SELECT id, value FROM data WHERE processed = 0');
const updateRow = db.prepare('UPDATE data SET processed = 1 WHERE id = ?');

// Create a transaction function
const processRows = db.transaction(() => {
  const rows = getRows.all();
  for (const row of rows) {
    updateRow.run(row.id);
  }
  return rows.length;
});

// Execute the transaction
const count = processRows();
console.log(`Processed ${count} rows`);

db.close();

const processRowsSafe = db.transaction(() => {
  const rows = getRows.all();
  for (const row of rows) {
    try {
      updateRow.run(row.id);
    } catch (error) {
      console.error(`Failed to process row ${row.id}: ${error.message}`);
      throw error; // Rollback the transaction
    }
  }
  return rows.length;
});

try {
  const count = processRowsSafe();
  console.log(`Success: ${count} rows`);
} catch (error) {
  console.error('Transaction failed, all changes rolled back');
}

const Database = require('better-sqlite3');
const db = new Database('database.db');

// BAD: Trying to run queries while iterating
// const iterator = db.prepare('SELECT id FROM huge_table').iterate();
// for (const row of iterator) {
//   db.prepare('PROCESS ...').run(row.id); // ERROR: connection busy
// }

// GOOD: Collect what you need, then process
const batchSize = 1000;
let offset = 0;

while (true) {
  const batch = db
    .prepare('SELECT id FROM huge_table LIMIT ? OFFSET ?')
    .all(batchSize, offset);

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

  // Process this batch (now safe to run other queries)
  const update = db.prepare('UPDATE huge_table SET processed = 1 WHERE id = ?');
  for (const row of batch) {
    update.run(row.id);
  }

  offset += batchSize;
  console.log(`Processed: ${offset}`);
}

db.close();

const Database = require('better-sqlite3');

// Use one connection for reading
const readDb = new Database('database.db');
const writeDb = new Database('database.db');

// Now you can safely iterate on one and write on another
for (const row of readDb.prepare('SELECT id FROM users').iterate()) {
  writeDb.prepare('UPDATE users SET status = 1 WHERE id = ?').run(row.id);
}

readDb.close();
writeDb.close();

const Database = require('better-sqlite3');
const db = new Database('database.db');

// BAD: Trying to close while iterator is active
// const iterator = db.prepare('SELECT * FROM users').iterate();
// setTimeout(() => db.close(), 100); // ERROR: may close while iterating

// GOOD: Finish iteration before closing
async function processUsers() {
  const iterator = db.prepare('SELECT * FROM users').iterate();
  const allUsers = [...iterator]; // Consume the entire iterator

  db.close(); // Safe to close now
  return allUsers;
}

// Or explicitly complete the iterator
const users = [];
const iterator = db.prepare('SELECT * FROM users').iterate();
for (const user of iterator) {
  users.push(user);
}
// Iterator is now complete

db.close();

const Database = require('better-sqlite3');

function withDatabase(callback) {
  const db = new Database('database.db');
  try {
    return callback(db);
  } finally {
    db.close();
  }
}

// Usage
const users = withDatabase((db) => {
  return db.prepare('SELECT * FROM users').all();
});

console.log(users);

const Database = require('better-sqlite3');

const readDb = new Database('database.db');
const writeDb = new Database('database.db');

try {
  // Safe to iterate on readDb while using writeDb
  const selectStmt = readDb.prepare('SELECT id FROM items WHERE active = 0');
  const updateStmt = writeDb.prepare('UPDATE items SET active = 1 WHERE id = ?');

  for (const row of selectStmt.iterate()) {
    updateStmt.run(row.id);
  }
} finally {
  readDb.close();
  writeDb.close();
}

const Database = require('better-sqlite3');

const readDb = new Database('database.db');
const writeDb = new Database('database.db');

// Enable WAL mode for better concurrency
readDb.pragma('journal_mode = WAL');

try {
  // Better concurrency between read and write
  const selectStmt = readDb.prepare('SELECT id FROM items LIMIT 100');
  const updateStmt = writeDb.prepare('UPDATE items SET status = ? WHERE id = ?');

  for (const row of selectStmt.iterate()) {
    updateStmt.run('processed', row.id);
  }
} finally {
  readDb.close();
  writeDb.close();
}