Statement Operations

// Available on Database instance
prepare(sql: ISqlite.SqlType, ...params: any[]): Promise<Statement<Stmt>>;

class Statement<S extends sqlite3.Statement = sqlite3.Statement> {
  /** Returns the underlying sqlite3 Statement instance */
  getStatementInstance(): S;

  /**
   * Bind parameters to the prepared statement
   * Completely resets the statement object and removes all previously bound parameters
   * @param params - Parameters to bind to the statement
   * @returns Promise that resolves when binding is complete
   */
  bind(...params: any[]): Promise<void>;

  /**
   * Reset the row cursor and preserve parameter bindings
   * Use this to re-execute the same query with the same bindings
   * @returns Promise that resolves when reset is complete
   */
  reset(): Promise<void>;

  /**
   * Finalize the statement to free resources
   * After finalization, all further function calls will throw errors
   * @returns Promise that resolves when finalization is complete
   */
  finalize(): Promise<void>;
}

class Statement<S extends sqlite3.Statement = sqlite3.Statement> {
  /**
   * Execute the statement (INSERT, UPDATE, DELETE, DDL)
   * @param params - Optional parameters to bind before execution
   * @returns Promise with execution result including lastID and changes
   */
  run(...params: any[]): Promise<ISqlite.RunResult>;

  /**
   * Execute statement and retrieve the first result row
   * Can leave database locked - call finalize() or reset() when done
   * @param params - Optional parameters to bind before execution
   * @returns Promise resolving to first row or undefined
   */
  get<T = any>(...params: any[]): Promise<T | undefined>;

  /**
   * Execute statement and retrieve all result rows
   * @param params - Optional parameters to bind before execution
   * @returns Promise resolving to array of all rows
   */
  all<T = any[]>(...params: any[]): Promise<T>;

  /**
   * Execute statement and call callback for each result row
   * @param params - Parameters followed by callback function
   * @returns Promise resolving to number of rows processed
   */
  each<T = any>(...params: any[]): Promise<number>;
}

import { open } from "sqlite";
import sqlite3 from "sqlite3";

const db = await open({
  filename: ":memory:",
  driver: sqlite3.Database
});

// Create table
await db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, active BOOLEAN)");

// Prepare INSERT statement
const insertStmt = await db.prepare("INSERT INTO users (name, email, active) VALUES (?, ?, ?)");

// Execute multiple times
const result1 = await insertStmt.run("Alice", "alice@example.com", true);
console.log(`Inserted Alice with ID: ${result1.lastID}`);

const result2 = await insertStmt.run("Bob", "bob@example.com", false);
console.log(`Inserted Bob with ID: ${result2.lastID}`);

// Finalize when done
await insertStmt.finalize();

// Prepare SELECT statement
const selectStmt = await db.prepare("SELECT * FROM users WHERE active = ?");

// Use with different parameters
const activeUsers = await selectStmt.all<{id: number, name: string, email: string, active: boolean}[]>(true);
console.log("Active users:", activeUsers);

const inactiveUsers = await selectStmt.all<{id: number, name: string, email: string, active: boolean}[]>(false);
console.log("Inactive users:", inactiveUsers);

// Reset and reuse with same parameters
await selectStmt.reset();
const firstActiveUser = await selectStmt.get<{id: number, name: string, email: string, active: boolean}>(true);

await selectStmt.finalize();

// Bind parameters at execution time
const stmt = await db.prepare("SELECT * FROM users WHERE name = ? AND active = ?");
const user = await stmt.get("Alice", true);

// Pre-bind parameters, then execute
const stmt2 = await db.prepare("SELECT * FROM users WHERE name = ? AND active = ?");
await stmt2.bind("Alice", true);
const user2 = await stmt2.get();

// Named parameters
const stmt3 = await db.prepare("SELECT * FROM users WHERE name = :name AND active = :active");
const user3 = await stmt3.get({ ":name": "Alice", ":active": true });

// Array parameters
const stmt4 = await db.prepare("SELECT * FROM users WHERE name = ? AND active = ?");
const user4 = await stmt4.get(["Alice", true]);

const stmt = await db.prepare("SELECT * FROM large_table WHERE category = ?");

// Process rows one by one
const rowCount = await stmt.each("electronics", (err, row) => {
  if (err) {
    console.error("Row processing error:", err);
    return;
  }
  
  // Process individual row
  console.log(`Processing row:`, row);
});

console.log(`Processed ${rowCount} rows`);
await stmt.finalize();

// Efficient batch operations
const insertStmt = await db.prepare("INSERT INTO products (name, price, category) VALUES (?, ?, ?)");

const products = [
  ["Laptop", 999.99, "electronics"],
  ["Book", 29.99, "books"],
  ["Headphones", 199.99, "electronics"]
];

// Reuse the same prepared statement
for (const [name, price, category] of products) {
  await insertStmt.run(name, price, category);
}

await insertStmt.finalize();

// Query optimization with parameter reuse
const categoryStmt = await db.prepare("SELECT COUNT(*) as count FROM products WHERE category = ?");

const categories = ["electronics", "books", "clothing"];
for (const category of categories) {
  const result = await categoryStmt.get<{count: number}>(category);
  console.log(`${category}: ${result.count} products`);
}

await categoryStmt.finalize();

let stmt;
try {
  stmt = await db.prepare("SELECT * FROM users WHERE id = ?");
  
  const user = await stmt.get(1);
  if (!user) {
    console.log("User not found");
  } else {
    console.log("Found user:", user);
  }
} catch (error) {
  console.error("Statement error:", error);
} finally {
  // Always finalize to free resources
  if (stmt) {
    await stmt.finalize();
  }
}

interface RunResult {
  /** Statement object */
  stmt: Statement;
  /** Row ID of inserted row (INSERT statements only) */
  lastID?: number;
  /** Number of rows changed (UPDATE/DELETE statements only) */
  changes?: number;
}